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?

Do the tables called fields already exist per schema? Could you work with reflection? — Ilja Everilä
What I mean is I'd approach this a bit differently: reflect the fields tables from the schemas separately to a dictionary, where the schema is the key. — Ilja Everilä
@Ilja Everilä fields already exist. It looks like reflection is part of SQLAlchemy Core. I am trying to deploy SQLAlchemy in place of my "DIY" library. Do you suggest I go with Core, or use Core to create reflection and go with ORM? — Muposat
There's automapper as well, if you wish to reflect as declarative classes (orm). — Ilja Everilä
@Ilja Everilä Looks like reflection/automapper is when database determines metadata, which would be bad for me. I will probably create a common table with "schema_name" column instead. — Muposat

Muposat Muposat · Accepted Answer · 2016-10-10T20:17:50

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

SQLAlchemy ORM with dynamic table schema

1 Answers