CODEWITHSUNDEEP
pythondatabasepostgresqlormsqlalchemy
Melnyk Andrii
Melnyk Andrii

Reputation: 13

SQLAlchemy: reflect table into explicitly created class

Suppose I have two schemas in the single PostgreSQL database and each schema contain table with the same name. For example: schema1.table, schema2.table.

I use SQLAlchemy for working with the database.

The first issue is that I can't reflect table from database specifying concrete schema into explicitly created class. For example:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.declarative import DeferredReflection

Base = declarative_base()

class Table(DeferredReflection, Base):
    __tablename__ = 'table'

## somehow specify schema for table

engine = create_engine(
    'postgresql+psycopg2://localhost/postgres',
    isolation_level='READ UNCOMMITTED'
)

DeferredReflection.prepare(engine)

## do something using reflected table

The second issue is that I am looking for a way to bind one explicitly created class with tables from different schemas and use it as follows:

session = Session()
with schema_context('schema1'):
    data = session.query(Table).all()  # Table refers to schema1.table 
    ...

with schema_context('schema2'):
    data = session.query(Table).all() # Table refers to schema2.table
    ...

Is there some way to work around or solve described issues?

Upvotes: 1

Views: 1571

Answers (1)

RazerM
RazerM

Reputation: 5492

The SQLAlchemy Table object allows you to pass a schema argument.

Using declarative, arguments are passed to the underlying Table object using __table_args__, as documented here.

class MyTable(DeferredReflection, Base):
    __tablename__ = 'my_table'
    __table_args__ = {'schema': 'schema2'}

You must create separate tables for different schemas.

Upvotes: 1

Related Questions