solovyov.net

SQLAlchemy tutorial: how to start

Alexander Solovyovpython, programming, sql, db

SQLAlchemy currently is a best Python ORM, but its documentation, while being thorough and detailed, in my opinion lacks easy descriptions of steps you need to go through to start using it. Which is why I’ve decided to write small introductory tutorial.

Level 1: hand-written SQL

First step is obtaining a connection to a database:

>>> from sqlalchemy import create_engine
>>> e = create_engine('mysql://user:pass@host/db')
>>> for row in e.execute('select * from table where id < %s', 2):
...     print dict(r)
{u'id': 1, u'info': u'first row'}

If you want to use named parameters, there is text():

>>> from sqlalchemy import text
>>> result = e.execute(text('select * from table where id < :id'), {'id': 2})

From objects that you receive by iterating over result – RowProxy - data can be obtained by index, by key or by attribute:

>>> row[0] == row['id'] == row.id
    True

Would you like to execute everything inside of transaction?

>>> conn = e.connect()
>>> conn.begin()
>>> # work work work
>>> conn.commit() # try/except: conn.rollback() optionally :)

That’s already better than using raw DB API, especially given that parameters will be escaped.

Level 2: SQL-expressions in Python

You can autoguess tables from database and then work with them, if that’s more suitable for you:

>>> from sqlalchemy import Table, MetaData
>>> meta = MetaData(bind=e, reflect=True)
>>> table = meta.tables['table']
>>> list(e.execute(table.select(table.c.id < 2)))
    [(1, u'first row')]

That was the same query, but using Python DSL, without constructing SQL query from string.

Level 3: ORM

And if you prefer working with mapped objects, where you can add some behavior:

>>> from sqlalchemy import orm
>>> class Table(object):
...     pass
>>> orm.Mapper(Table, meta.tables['table'])
>>> s = orm.Session(bind=e)
>>> s.query(Table).filter(Table.id < 2).first().info
    u'first row'

In this case you can use full power of ORM:

>>> class Artist(object):
...     pass
>>> orm.Mapper(Artist, meta.tables['artist'])
>>> class Album(object):
...     pass
>>> orm.Mapper(Album, meta.tables['album'])
>>> class Song(object):
...     pass
>>> orm.Mapper(Song, meta.tables['song'])
>>> s.query(Song).join(Album).filter(Album.id == 10).count()
    12L
>>> # Song is first in a query, and we have to declare joins against it,
>>> # that's why we're joining here with Album
>>> s.query(Song.name, Album.name).join(Album).join(Artist).filter(Artist.id == 2).first()
    (u'Hex', u'Inflikted')
>>> # And this is how you look at query generated by SQLAlchemy
>>> print s.query(Song.name, Album.name).join(Album).join(Artist).filter(Artist.id == 2)
SELECT song.name AS song_name, album.name AS album_name
FROM song JOIN album ON album.id = song.album_id JOIN artist ON artist.id = album.artist_id
WHERE artist.id = %(id_1)s

Also if you’re going to use Session.execute(), it accepts strings with named parameters by default:

>>> list(s.execute('select * from table where id < :id', {'id': 2}))
    [(1, u'first row')]

Miscellaneous

Should be noted that by default Engine already has a pool of connections, which is useful.

MetaData with reflection and early binding is not a preferred way to work with SQLA, it’s there mostly for ad-hoc scripts and working in REPL. Usually you want to construct your tables/classes at first and then bind Engine to MetaData somewhere later – when you are configuring your application, for example (by doing meta.bind = e).

Session often is not used directly, especially in multi-threaded application – there is orm.scoped_session, which creates thread-local session class.

That is basically all I wanted to tell you, for further information there is documentation. :)