Change SqlAlchemy declarative model table schema at runtime

Question

I am trying to build a declarative table that runs in both postgres and sqlite. The only difference between the tables is that the postgres table is going to run within a specific schema and the sqlite one will not. So far I've gotten the tables to build without a schema with the code below.

metadata = MetaData()

class Base(object):

    __table_args__ = {'schema': None}

Base = declarative_base(cls=Base, metadata=metadata)


class Configuration(Base):
    """
        Object representation of a row in the configuration table
    """

    __tablename__ = 'configuration'

    name = Column(String(90), primary_key=True)
    value = Column(String(256))

    def __init__(self, name="", value=""):
        self.name = name
        self.value = value


def build_tables(conn_str, schema=None):

    global metadata

    engine = create_engine(conn_str, echo=True)

    if schema:
        metadata.schema=schema

    metadata.create_all(engine)

However, whenever I try to set a schema in build_tables(), the schema doesn't appear to be set in the newly built tables. It only seems to work if I set the schema initially at metadata = MetaData(schema='my_project') which I don't want to do until I know which database I will be running.

Is there another way to set the table schema dynamically using the declarative model? Is changing the metadata the wrong approach?

Related, if not duplicate: https://stackoverflow.com/questions/9298296/sqlalchemy-support-of-postgres-schemas — Ilja Everilä, Oct 25 '17 at 10:36
@IljaEverilä the accepted answer in the question you've referenced is suggesting the exact thing the OP of this question says didn't work. Hard to see how that could be a duplicate. — Philip Couling, Jul 27 '21 at 22:44
This is good question and one of the few (if not the only) results in google. I hit similar case when trying to reuse models between different apps. I need to replace the Base tho and attempting to create dynamically a new type. Did you ever find the answer? — urban, Dec 25 '21 at 17:12

score 0 · Answer 1 · answered Dec 25 '21 at 17:45

Altho this is not 100% the answer to what you are looking for, I think @Ilja Everilä was right the answer is partly in https://stackoverflow.com/a/9299021/3727050.

What I needed to do was to "copy" a model to a new declarative_base. As a result I faced a similar problem with you: I needed to:

Change the baseclass of my model to the new Base
Turns out we also need to change the autogenerated __table__ attribute of the model to point to the new metadata. Otherwise I was getting a lot of errors when looking up PK in that table

The solution that seems to be working for me is to clone the mode the following way:

def rebase(klass, new_base):
    new_dict = {
        k: v
        for k, v in klass.__dict__.items()
        if not k.startswith("_") or k in {"__tablename__", "__table_args__"}
    }
    # Associate the new table with the new metadata instead
    # of the old/other pool
    new_dict["__table__"] = klass.__table__.to_metadata(new_base.metadata)

    # Construct and return a new type
    return type(klass.__name__, (new_base,), new_dict)

This in your case can be used as:

...
# Your old base
Base = declarative_base(cls=Base, metadata=metadata)

# New metadata and base
metadata2 = MetaData(schema="<new_schema>")
Base2 = declarative_base(cls=Base, metadata=metadata)

# Register Model/Table in the new base and meta
NewConfiguration = rebase(Configuration, Base2)
metadata2.create_all(engine)

Notes/Warnings:

The above code is not tested
It looks to me too verbose and hacky ... there has to be a better solution for what you need (maybe via Pool configs?)

Change SqlAlchemy declarative model table schema at runtime

1 Answers1