SQLAlchemy ORM with dynamic table schema
<p>I am trying to task <strong>SQLAlchemy ORM</strong> to create class <code>Field</code> that describes all fields in my database:</p>

<pre><code>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)
</code></pre>

<p>The issue is that table <code>fields</code> describes different fields in different schemas, i.e. </p>

<pre><code>SCM.fields
TDN.fields
...
</code></pre>

<p>I need class Field to</p>

<ul>
<li>Be initialized with object <code>fieldset</code> before records can be read from db</li>
<li>Schema determined by <code>fieldset.get_schema()</code> before table <code>&lt;schema&gt;.fields</code> is read.</li>
</ul>

<p>Something like this:</p>

<p>session.query(Field(fieldset))).filter(Field.id=='some field')</p>

<p>However, adding </p>

<pre><code>def __init__(self, fieldset)
    pass
</code></pre>

<p>to <code>class Field</code> results in </p>

<blockquote>
  <p><code>__init__() takes 1 positional argument...</code></p>
</blockquote>

<ul>
<li>I could lump all <code>fields</code> tables into one schema and add column 'schema_name' but I still need Field have link to its fieldset.</li>
</ul>

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

<p>So the problem is solvable and the solution is documented as <a href="http://docs.sqlalchemy.org/en/latest/orm/extensions/declarative/mixins.html#augmenting-the-base" rel="nofollow">Augmenting the Base</a></p>

<pre><code>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 --&gt; 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,), {})
</code></pre>

<p>Proof of concept:</p>

<pre><code>FieldSet('ork').fieldtype.__table__
</code></pre>

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

python sqlalchemy