CODEWITHSUNDEEP
pythonsqlalchemyschemapyramid
Thomas Farvour
Thomas Farvour

Reputation: 1103

Using a different schema for the same declarative Base in sqlalchemy

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

Upvotes: 16

Views: 19169

Answers (4)

Ben
Ben

Reputation: 1291

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"})

Upvotes: 2

Tom Willis
Tom Willis

Reputation: 5303

just a guess

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

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

Upvotes: -1

jkmacc
jkmacc

Reputation: 6427

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

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

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

  2. 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'

  3. Do the same for other schema.

Upvotes: 21

Paul Yin
Paul Yin

Reputation: 1759

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

Upvotes: 0

Related Questions