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
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.
You can also import the method as such: from sqlalchemy. schema import CreateSchema . And use it directly with engine. execute(CreateSchema(schema_name)) .
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.
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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With