Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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
like image 973
Thomas Farvour Avatar asked Dec 26 '13 15:12

Thomas Farvour


People also ask

What is Declarative base in SQLAlchemy?

A base class stores a catlog of classes and mapped tables in the Declarative system. This is called as the declarative base class. There will be usually just one instance of this base in a commonly imported module. The declarative_base() function is used to create base class.

How do I create a schema in SQLAlchemy?

You can also import the method as such: from sqlalchemy. schema import CreateSchema . And use it directly with engine. execute(CreateSchema(schema_name)) .

What does base MetaData Create_all do?

create_all() creates foreign key constraints between tables usually inline with the table definition itself, and for this reason it also generates the tables in order of their dependency.

What is SQLAlchemy ext Declarative?

function sqlalchemy.ext.declarative. has_inherited_table(cls) Given a class, return True if any of the classes it inherits from has a mapped table, otherwise return False. This is used in declarative mixins to build attributes that behave differently for the base class vs. a subclass in an inheritance hierarchy.


1 Answers

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.

like image 158
jkmacc Avatar answered Sep 22 '22 05:09

jkmacc