Sqlalchemy cheat sheet
A mental map dump of pyhon/sqlalchemy concepts. intended to help transition from c#/entity framework to python/sqlalchemy.
This is by no means attemtping to be authoritative or academic and likely wrong.
- declaritive vs classic (on new projects use declaritive)
- Engine:
self.engine = create_engine(self.plan_db_uri, echo = True, poolclass=NullPool)
schema_base.metadata.create_all(self.engine)
self.sessionmaker = sessionmaker(bind=self.engine)
then, create a session:
session = self.sessionmaker()
session.add() --> equivalent to BEGIN TRAN session.flush() --> add all the crud operations to the DB - the benefit of flush is that it causes for the affected row to be locked (this helps to avoid race conditions)session.commit() --> equivalent to COMMIT TRAN (Commit also calls flush())
Common operations:
- report = session.query(Report).filter(Report.user_name == user_name, Report.name == report_name ).one()
-
.one() --> return exactly one or NoResultFound or MultipleResultsFound error
-
.first() --> return first row or None
-
.one_or_none() --> return either one or none or MultipleResultsFound exception
-
.all() --> return list of entities (select * from tablica)
Joins:
- two ways to join, implicit join (old way) and explicit (using .join())
- Explicit:
- joined = session.query(Customer).join(Invoice).filter(Invoice.amount == 8500).all()
- Implicit (notice no .join):
-
partition_key_query = conn.session.query(
OptimizationPartitions, TableName).\
filter(TableNames.id == OptimizationPartitions.table_id).\
filter(OptimizationPartitions.optimization_id == optimization_id).\
filter(TableNames.name == Tables.WORKLIST.value)
partition_key = partition_key_query.one()
- get, delete and synchronize_session:
when deleting, how to refresh the session with actual data from the db after the commit?
engine vs session vs ...
To summarize, the way that we currently instantiate the engine:
engine = get_engine() --> this is a wrapper
with engine.connect() as conn:
df = pd.read_sql_query(sql, conn)
what is wrong here? this can lead to a connection/memory leak. This snippet properly closes the particular session,
but engine never gets disposed after requests and holds on to its pooled connections after requests.
In reality: at least until the next garbage collection cycle?
This can be solved by calling engine.dispose() after each call (maybe inside try/finally)
or allow engine creation with poolclass=NullPool
parameter, which prevents connection pooling.
TODO: Test both approaches and monitor conn leak.
- To print out raw SQL from a sqlalchemy session:
from sqlalchemy.dialects import postgresql
print(query.statement.compile().params)
print str(query.statement.compile(dialect=postgresql.dialect()))