CODEWITHSUNDEEP
pythonormsqlalchemy
Chris R
Chris R

Reputation: 17926

How can I re-use a sqlalchemy ORM model on multiple databases and schemas?

I have a SQLAlchemy ORM model that currently looks a bit like this:

Base = declarative_base()

class Database(Base):

  __tablename__ = "databases"
  __table_args__ = (
    saschema.PrimaryKeyConstraint('db', 'role'),
    {
      'schema' : 'defines',
      },
    )


  db = Column(String, nullable=False)
  role = Column(String, nullable=False)
  server = Column(String)

Here's the thing, in practice this model exists in multiple databases, and in those databases it'll exist in mutiple schemas. For any one operation I'll only use one (database, schema) tuple.

Right now, I can set the database engine using this:

Session = scoped_session(sessionmaker())
Session.configure(bind=my_db_engine)
# ... do my operations on the model here.

But I'm not sure how I can change the __table_args__ at execution time so that the schema will be the right one.

Upvotes: 2

Views: 2021

Answers (2)

jkmacc
jkmacc

Reputation: 6427

I'm also looking for an elegant solution for this problem. If there are standard tables/models, but different table names, databases, and schemas at runtime, how is this handled? Others have suggested writing some sort of function that takes a tablename and schema argument, and constructs the model for you. I've found that using __abstract__ helps. I've suggested a solution here that may be useful. It involves adding a Base with a specific schema/metadata into the inheritance.

Upvotes: 1

Nathan Villaescusa
Nathan Villaescusa

Reputation: 17659

One option is to use the create_engine to bind the models to a schema/database rather than do so in the actual database. 

#first connect to the database that holds the DB and schema
engine1 = create_engine('mysql://user:[email protected]/schema1')

Session = session(sessionmaker())
session = Session(bind=engine1)

#fetch the first database
database = session.query(Database).first()

engine2 = create_engine('mysql://user:pass@%s/%s' % (database.DB, database.schema))
session2 = Session(bind=engine2)

I don't know that this is ideal, but it is one way to do it. If you cache the list of databases before hand then in most cases you are only having to create one session.

Upvotes: 1

Related Questions