Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
814 views
in Technique[技术] by (71.8m points)

python - SQLAlchemy ORM with dynamic table schema

I am trying to task SQLAlchemy ORM to create class Field that describes all fields in my database:

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Field(Base):
    __tablename__ = 'fields'
    __table_args__ = {'schema':'SCM'}
    id = Column(String(20), primary_key=True)

The issue is that table fields describes different fields in different schemas, i.e.

SCM.fields
TDN.fields
...

I need class Field to

  • Be initialized with object fieldset before records can be read from db
  • Schema determined by fieldset.get_schema() before table <schema>.fields is read.

Something like this:

session.query(Field(fieldset))).filter(Field.id=='some field')

However, adding

def __init__(self, fieldset)
    pass

to class Field results in

__init__() takes 1 positional argument...

  • I could lump all fields tables into one schema and add column 'schema_name' but I still need Field have link to its fieldset.

Can this be done using SQLAlchemy ORM or should I switch to SqlAlchemy Core where I would have more control over object instantiation?

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

So the problem is solvable and the solution is documented as Augmenting the Base

from sqlalchemy import Column, Integer, String
from sqlalchemy.ext.declarative import declared_attr, declarative_base

class Field:
    __tablename__ = 'fields'

    @declared_attr
    def __table_args__(cls):
        # schema will be part of class name, which works for me
        # Example: class FieldSCM --> Field with schema SCM
        return dict(schema=cls.__name__[5:].upper())

    id = Column(String(20), primary_key=True)

Field = declarative_base(cls=Field)


class FieldSet:
    def __init__(self, schema):
        self.fieldtype  = type('Field' + schema.upper(), (Field,), {})

Proof of concept:

FieldSet('ork').fieldtype.__table__

Table('fields', MetaData(bind=None), Column('id', String(length=20), table=, primary_key=True, nullable=False), schema='ORK')


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...