Using a different schema for the same declarative Base in sqlalchemy

Question

I am new to both Pyramid and SQLAlchemy. I am working on a Python Pyramid project with SQLAlchemy. I have a simple model set up below. How would I go about being able to use this with different schemas at run-time? This will be a PostgreSQL database backend. Right now, "public" is hard-coded into the declarative base model. I would need the ability to use this same model with different schema. What is the best approach? Unless I missed it, the documentation at SQLAlchemy seemed unclear to me.

    from sqlalchemy.ext.declarative import declarative_base
    from sqlalchemy import Column, BigInteger

    __all__ = [
        "LoadTender"
    ]
    __all__.sort()

    Base = declarative_base()


    class LoadTender(Base):
        __tablename__ = "load_tenders"
        __table_args__ = {"schema": "public"}

        id = Column("pkey", BigInteger, primary_key=True)

        def __repr__(self):
            return "" % self.id

EDIT: I have appeared to solve my issue, I am updating the snippet to show what I did below.

from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, BigInteger

__all__ = [
    "LoadTender"
]
__all__.sort()

Base = declarative_base()

class ClientMixin(object):
    __table_args__ = {"schema": "client_schema_name"}


class LoadTenderMixin(object):
    __tablename__ = "load_tenders"

    id = Column("pkey", BigInteger, primary_key=True)

    def __repr__(self):
        return "" % self.id


class ClientLoadTender(LoadTenderMixin, ClientMixin, Base):
    pass

I believe I may have solved this by using Declarative Base and using multiple inherited Mixins. I'll update when I'm certain I have this working ... — Thomas Farvour, Dec 27 '13 at 20:10
This looks like a great answer, but it may become a problem if any classes earlier in the hierarchy also have a `__table_args__`, as this property will get overwritten. For example, `ClientLoadTender` may define `PrimaryKeyConstraint`s or `UniqueConstraint`s in `__table_args__`. I've not found a simple way to merge `__table_args__` down the hierarchy. — jkmacc, Apr 29 '14 at 14:39
Yes you are correct. That is a limitation of this arrangement. However it fit my needs for the application I was applying it to. I would imagine one would have to write some kind of helper method on the final class in the chain to merge those in manually... — Thomas Farvour, Apr 30 '14 at 16:13

jkmacc · Accepted Answer · 2013-12-30T20:09:01.563

21

I think you need a different model for each schema. __abstract__ can make this less painful. This follows on to Paul Yin's answer...

Define an __abstract__ LoadTender model, so you don't have to keep coding it.

#base.py
class LoadTender(Base):
    __abstract__ = True
    id = ...
    def __repr__ ...

Put a schema-specific Base in the hierarchy for each schema.

#schema1.py
from base import LoadTender

PublicBase = declarative_base(metadata=MetaData(schema='public'))

class LoadTender(PublicBase, LoadTender):
    __tablename__ = 'load_tenders'

Do the same for other schema.

edited Dec 30 '13 at 20:09

answered Dec 28 '13 at 17:23

jkmacc

6,125
3
30
27

This seems to be the closest answer the answered what I was trying to accomplish. I actually accomplished it by using multiple inheritance just like this with Mixin classes. – Thomas Farvour Jan 14 '14 at 21:02
As I'm trying to find a better answer myself, would you mind posting your solution? – jkmacc Jan 15 '14 at 15:06
I used Python's multiple inheritance to mix-in the properties I needed for each schema. The `ClientMixin` could probably have had an `__init__` call to set it to a dynamic setting at run-time, but defining each client as a separate, empty class in my business case was OK. – Thomas Farvour Jan 15 '14 at 17:44
Can you bind an engine in Base and declare metadata for schema in PublicBase? – Chris May 23 '18 at 15:59

score 1 · Answer 2 · answered Feb 15 '23 at 14:09

1

Just another method that didn't seem to have been mentioned often as it deserved:

engine.update_execution_options(schema_translate_map={None: "target_schema"})

answered Feb 15 '23 at 14:09

Ben

1,133
1
15
30

score 0 · Answer 3 · answered Dec 27 '13 at 06:49

0

You can have a base module in package model

app\
    models\
        base.py
        schema1.py
        schema2.py
    views\
    ...

declare Base in base.py, then import it to other schemas

answered Dec 27 '13 at 06:49

Paul Yin

1,753
2
13
19

score -1 · Answer 4 · edited May 11 '15 at 17:02

-1

just a guess

LoadTender.__table_args__["schema"] = "whatever"

Probably best to put it somewhere where your app configurator is creating the app

edited May 11 '15 at 17:02

ThiefMaster

310,957
84
592
636

answered Dec 27 '13 at 00:27

Tom Willis

5,250
23
34

nope, that won't work, because `__table_args__ seem to be passed on once upon class definition and do not propagate later on. `LoadTender.__table__.schema = "whatever"` works but is not nice either. – j08lue Sep 17 '19 at 10:37

Using a different schema for the same declarative Base in sqlalchemy

4 Answers4

Linked