How to query a pre-existing table in SQLAlchemy ORM?

Question

I'm using SQLAlchemy to do some data processing and create some tables.

I'm loading data from a table orm_table defined by the class ORMTable (which inherits from a Base class which itself inherits from a DeclarativeBase class), so I can query that table using session.query(ORMTable).all().

However, I also need to query another table non_orm_table that already exists in the database and is not defined in the orm.

How do I query this table from within the same session? I don't have a class associated with it so wondering what is the standard practice for such cases?

• Simon: Could you define a model for the preexisting table?
• sfactor: if that is needed I could. But the table is already there, so I was hoping there's a way to just pull that information for particular tables I'm interested in into what the ORM can work with.
• Simon: Take a look at sqlalchemy.ext.automap. :-)

Answer 1

Here is the code snippet to make it:

from sqlalchemy import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import sessionmaker
engine = create_engine('<db_connection_string>', echo=True)
Base = declarative_base(engine)
class NonOrmTable(Base):
 """
 eg. fields: id, title
 """
 __tablename__ = 'non_orm_table'
 __table_args__ = {'autoload': True}
def loadSession():
 """"""
 metadata = Base.metadata
 Session = sessionmaker(bind=engine)
 session = Session()
 return session
if __name__ == "__main__":
 session = loadSession()
 res = session.query(NonOrmTable).all()
 print res[1].title

The key is to use SqlAlchemy’s autoload attribute. It will map the existing table field names to the class dynamically.

I hope it helps.

• sfactor: had to also use autoload_with in the table_args but yah this certainly did help. exactly what I was looking for. thanks ! i've accepted your answer.
• ichbinblau: yeah, you still have to bind the class with the existing table.

Answer 2

You can use the reflect method of MetaData objects:

 #Considering account is your table and first_name is a column
 from sqlalchemy import create_engine, select, MetaData, Table
 CONN = create_engine('postgresql+psycopg2://username:password@localhost:5432/dbname', 
 client_encoding="UTF-8") 
 META_DATA = MetaData(bind=CONN, reflect=True)
 account = META_DATA.tables['account']
 stmt = select([account]).where(account.columns.first_name == 'Michael')
 connection = CONN.connect()
 results = connection.execute(stmt).fetchall()
 # to print the result
 for result in results:
 print(result)

• Matt: I'd second the reflect method here (as has been pointed out in other answers to questions like this); extremely convenient!!!
• robertspierre: You need a context manager for the connection

Answer 3

You can map all the tables in the existing db, including the ones you didn't define, with the automap extension.

from sqlalchemy.ext.automap import automap_base
engine = create_engine(f"duckdb:///{path_to_db}")
Base = automap_base()
Base.prepare(autoload_with=engine)

Then query the table by accessing the Base.metadata.tables dictionary:

with Session(engine) as session:
 query = select(Base.metadata.tables["non_orm_table"])
 df = pd.read_sql(query, session.bind)