SQLAlchemy ORM with dynamic table schema

Question

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?

Answer 1

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')