EJ

sqlalchemy-101

4 min read

Core vs ORM

SQLAlchemy Core is lower-level, closer to raw SQL. You can use text() to run queries. Its focus in on the DB schema (tables, columns) and you have more control but potentially more verbose. SQLAlchemy Object-Relational Mapping (ORM) is higher-level, more object-oriented / abstract. Its focus is Python classes which are domain objects. It is more convenient but potentially less control.

Basically, with Core you can build SQL queries directly whereas with ORM you work with the database as Python objects. ORM is built on top of Core and you can use both in the same project.

An Engine

Start off with an Engine which has primary endpoints, the Connection and Result what is a fascade known as the Session?

A Connection

The Connection crates an open resource against the DB so when running a connection (engine.connect()) we should do so inside a context. For example:

from sqlalchemy import create_engine, text
engine = create_engine("sqlite+pysqlite:///:memory:", echo=True)
 
with engine.connect() as conn:
	result = conn.execute(text("SELECT 'hello world!'"))
	print(result.all())

Note: Using textual SQL (text()) is the exception and not the rule. Always use SQLAlchemy’s Expression Language

The result:

170 INFO sqlalchemy.engine.Engine BEGIN (implicit)
170 INFO sqlalchemy.engine.Engine SELECT 'hello world!'
171 INFO sqlalchemy.engine.Engine [generated in 0.00067s] ()
[('hello world!',)]
172 INFO sqlalchemy.engine.Engine ROLLBACK

Note: The numbers on the left (170, 171) are the milliseconds part of the logs. I’ve trimmed the YYYY-MM-DD HH:MM:SS from the logs. From the result, we have a transaction and a rollback.

Understanding Transactions

A transaction is a sequence of db operations treated as a single logical unit of work. It guarantees that all operations within that transaction are successfully applied to the db, or none of them are. This is to maintain data integrity.

We also have commits, which is when the changes (SQL statements) within that transaction succeed and those changes are able to be permanently saved to the db.

Then we have a rollback, which is when a all of the changes made within a transaction are discarded and the db returns to the state before the transaction began.

Committing Changes

Use Connection.commit() inside a context of engine.connect() to “commit as you go”:

with engine.connect() as conn:
	conn.execute(text("CREATE TABLE some_table (x int, y int)"))
	conn.execute(
		text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
		[{"x": 1, "y": 1}, {"x": 2, "y": 4}],
	)
	conn.commit() # actually insert the values

Use engine.begin() context to either create a COMMIT or a ROLLBACK (depending if an exception was raised). This is also known as “begin once”:

with engine.begin() as conn:
	conn.execute(
		text("INSERT INTO some_table (x, y) VALUES (:x, :y)"),
		[{"x": 6, "y": 8}, {"x": 9, "y": 14}],
	)

Fetching Rows

When a Result object is returned, it is an iterable object of the resulting Rows. You can also use Result.all() which returns a list of all of the rows.

The Row objects can be thought of as Python named tuples. You can also use integer index, attribute name or mapping objects. docs

Sending parameters

with engine.connect() as conn:
	result = conn.execute(text("SELECT x, y FROM some_table WHERE y > :y"), {"y": 2})
	for row in result:
	print(f"x: {row.x}, y: {row.y}")

Note: never stringify a parameter into the SQL string directly, always use parameters

ORM Session

Think of this as a segway to ORM constructs. When using the ORM you use Session instead of the Connection although Session uses Connection internally which it then uses to emit SQL. It is also “commit as you go”, meaning you have to session.commit() to alter any data.

Session has a lot more to offer than this first example but to get started, this is to illustrate the most basic one that replaces the Connection examples that we used before:

from sqlalchemy.orm import Session
 
stmt = text("SELECT x, y FROM some_table WHERE y > :y ORDER BY x, y")
with Session(engine) as session:
	result = session.execute(stmt, {"y": 6})
	for row in result:
		print(f"x: {row.x} y: {row.y}")

The result:

804 INFO sqlalchemy.engine.Engine BEGIN (implicit)
805 INFO sqlalchemy.engine.Engine SELECT x, y FROM some_table WHERE y > ? ORDER BY x, y
805 INFO sqlalchemy.engine.Engine [generated in 0.00043s] (6,)
 
 
808 INFO sqlalchemy.engine.Engine ROLLBACK

sqlalchemy-working-with-db-metadata

References

https://docs.sqlalchemy.org/en/20/tutorial/dbapi_transactions.html

Graph View

Backlinks