Michael Bayer: SQLAlchemy

published May 11, 2012

Michael Bayer delivers the keynote on SQLAlchemy at PyGrunn.

See the PyGrunn website for more info about this one-day Python conference in Groningen, The Netherlands.

"Space Invaders! Relational Modeling! And Domain Models! A mashup."

Computer life

My computer life started in the 1980s with a TRS-80. At school I used that to print Snoopy in ascii art. Then there was the Atari 800. It's still at my parent's house. I started programming on that one, which meant using assembly code. I got as far as creating a cool title screen before giving up.

In the nineties I used Rational Rose for some time to do architectural design. So real waterfall development. But I switch to iterative development to create the real code.

Domain Models

Looking a WordPress code you have request handling, sql statements, e-mailing and html all in one file. This is the water slide model, you just go down the file, without real control.

Compare that to the domain model: a conceptual model of a domain of interest. You model the problem agnostic of implementation details. Create explicit adaptations between the outside world and the model. Keep these adaptations as distinct from each other as possible. You have a request, security concerns, services, data handling, persistence and decide how that interacts with your model. When you paint a picture of this, you get the feeling of a pipeline: you are more in control of how it flows.

Relational Modeling

The relational model represents information as collections of rows, consisting of columns. It allows fluid manipulation of rows organized into sets. It encourages normalized form, to minimize repetition and dependency. It provides declarative query languages such as SQL to access and manipulate data. ACID guarantees.

  • Joins: intersection of rows.
  • Unions: combine sets of rows.
  • Grouping: divide rows into sub groups, apply aggregations.

Imperative programming is what you do in day to day python: "Mr. Computer, open this file -> okay."

SQL is generally declarative. We state our intent. "Mr. Database, give me all the rows in table A that starts with 'Q' -> okay."

How do we reconcile the relational model with the domain model? Represent discrete objects as elements in relational sets, that is: object-relational mapping. Expose "relational geometry" within the object relational mapping system. Automate database tasks, don't hide the database. Allow efficient questions to the database. Consider object instances as table rows. Map compositions to relational structure: an object may have child objects, which in the database is modeled with foreign keys.

Keeping the 'R' in 'ORM'. You should expose relational concepts, otherwise you cannot easily represent some SQL statements. So you should allow nested SELECT statements; subqueries should be possible. It is not good to abstract this away. You should also allow efficient 'questions'. Queries should be combined when possible. Do not do one query and they for each row in the result do an extra query. This is what JOINs are for.


Now for the fun part. Warning: Armin thought this was rediculous. We are going to build a space invaders game in Python. It will store the state of the game in a SQLite database in memory.

We have a domain model. Every object you paint on the screen is called a glyph. There are enemy gliphs, player gliphs, message glyphs, etc.

The code uses @hybrid_property so a method can get data back from an instance or get a SQL statement back from a class. Checking whether a missile hits an enemy or an enemy reaches the bottom of the screen, is done using SQL JOINs.

Of course this is all using SQLAlchemy, a Python SQL Toolkit and Object Relational Mapper.

He played a demo game and won, with lots and lots of SQL statements scrolling by. Seriously cool. He will put the code on his blog.


Are all SQL statements supported? Nothing big comes to mind. There are a few corner cases that you may not be able to express.

Can indexing be improved? You can use EXPLAIN, look at the query plans.

Find Michael Bayer on Twitter: @zzzeek.