SQLAlchemy in 5 Minutes

A whirlwind tour

Author: Jason Kirtland
Date: July 10th, 2007

What is SQLAlchemy?

  1. an ORM
  2. a powerful layer just above DBAPI
  3. a toolkit for SQL-literate applications

Database Support

  • SQLite
  • PostgreSQL
  • MySQL
  • SQL Server
  • Oracle
  • Firebird
  • Informix
  • ...and more on the way

SQLAlchemy can use any of these- or all of them, all at once.

Pythonic SQL Generation

Database Schema

SQLAlchemy maintains a schema catalog for SQL generation.

Database Schema

You can specify table schemas generically

slides = Table('slides', catalog,
               Column('id', Integer, primary_key=True),
               Column('title', String(50)))
bullets = Table('bullets', metadata,
                Column('id', Integer, primary_key=True),
                Column('slide_id', Integer, ForeignKey('slides.id'),
                       nullable=False),
                Column('pos', Integer),
                Column('text', String))

SQL Types

Or read tables from the database via reflection

slides = Table('slides', catalog, autoload=True)
bullets = Table('bullets', catalog, autoload=True)

Or just snarf everything

catalog = MetaData('sqlite://file.db', autoload=True)

SQL Types

Or target features of a particular database engine

slides = Table('slides', catalog,
               Column('id', mysql.Integer(unsigned=True),
                      primary_key=True)
               Column('title', mysql.SmallText(collate='utf8_bin'),
                      nullable=False))

SQL Types

The catalog of tables and columns is used for SQL generation and can also generate DDL. Tables can be easily created and dropped:

  • slides.create()
  • catalog.drop_all()

Generic DDL is adapted to the database engine on creation. Cross-platform is easy, and exploiting the capabilities of a single engine is easy too.

Simple INSERTs and SELECTs

>>> slides.insert().execute(title='slide 1')
<sqlalchemy.engine.base.ResultProxy object at 0x1531f70>
>>> row = slides.select().execute().fetchone()
>>> row
(1, u'slide 1')
>>> row.title
u'slide 1'

Expressions

slides.select(slides.c.id == 1)

SELECT slides.id, slides.title
FROM slides
WHERE slides.id = ?

Expressions

slides.select(slides.c.title.in_('slide 1', 'slide 2'))

SELECT slides.id, slides.title
FROM slides
WHERE slides.title IN (?, ?)

Inner Joins

slides.join(bullets).select()

SELECT slides.id, slides.title, bullets.id, bullets.slide_id,
       bullets.pos, bullets.text
FROM slides JOIN bullets ON slides.id = bullets.slide_id

Outer Joins

outerjoin(slides, bullets).select(slides.c.title.like('%SQL Types%'))

SELECT slides.id, slides.title, bullets.id, bullets.slide_id,
       bullets.pos, bullets.text
FROM slides LEFT OUTER JOIN bullets ON slides.id = bullets.slide_id
WHERE slides.title LIKE ?

Aggregates

select([bullets.c.slide_id,
        func.count(bullets.c.slide_id).label('total')],
       group_by=[bullets.c.slide_id])

SELECT bullets.slide_id, count(bullets.slide_id) AS total
FROM bullets GROUP BY bullets.slide_id

Subqueries

cs = select([ bullets.c.slide_id,
              func.count(bullets.c.slide_id).label('total') ],
            group_by=[bullets.c.slide_id]).alias('totals')
slides.select(and_(slides.c.id==cs.c.slide_id, cs.c.total>1))

SELECT slides.id, slides.title
FROM slides, (SELECT bullets.slide_id AS slide_id,
              count(bullets.slide_id) AS total
FROM bullets GROUP BY bullets.slide_id) AS totals
WHERE slides.id = totals.slide_id AND totals.total > ?

And More...

  • Supports all SQL operators and functions.
  • Joins, unions, correlated and uncorrelated subqueries, ...
  • Queries can be built up or modifified programaticaly

SQLAlchemy as an ORM

  • An ORM is supplied with the distribution
  • Others are possible, and remixes of the default are available
  • Default ORM uses the "data mapper" model
  • "active record" available through Elixir ORM extension, others

Mapping Is Simple

class Slide(object):
    def __repr__(self):
        return 'Slide("%s")' % self.title
class Bullet(object):
    def __repr__(self):
        return 'Bullet("%s")' % self.text

mapper(Slide, slides, properties={
      'bullets': relation(Bullet, cascade='all, delete-orphan')
       })
mapper(Bullet, bullets)

Working With Instances

>>> session = create_session()
>>> s1 = session.query(Slide).get(1)
>>> s1
Slide("slide 1")
>>> s1.bullets
[Bullet("foo"), Bullet("bar"), Bullet("baz")]
>>> s1.title = 'new title!'
>>> s1.bullets.pop()
Bullet("baz")
>>> session.flush()

info: BEGIN
info: UPDATE slides SET title=? WHERE slides.id = ?
info: ['new title!', 1]
info: DELETE FROM bullets WHERE bullets.id = ?
info: [3]
info: COMMIT

Working With Instances

You can mix SQL and objects

>>> q = session.query(Bullet).filter(bullets.c.text.startswith('b'))
>>> q
<sqlalchemy.orm.query.Query object at 0x1537db0>
>>> list(q)
[Bullet("bar"), Bullet("baz")]

So Much More

  • Object mapping is very flexible
  • Work with any legacy schema or schemas
  • Multi-database
  • Rich relations, collections, cascade rules
  • Eager and lazy loading, whole object graphs or individual columns
  • Stored procedures and triggers, no problem
  • Optional! The SQLAlchemy ORM is decoupled from the base library.

Summary

  • The RDBMS is a powerful tool, use it
  • Object encapsulation is a powerful tool, use it
  • SQLAlchemy lets you find the right mix for your problem and performance requirements, without hacks

Thanks!

Date:July 10th, 2007
Copyright:Jason Kirtland
License:Creative Commons Attribution-Share Alike 3.0
Version:1
ui/cc.png