SQLAlchemy, more than an ORM

I remember seeing references to Michael Bayers' SQLAlchemy and thinking yet another Python ORM to look at sometime in the future. I have used SQLObject for various hobby projects, but since I deal with legacy databases (some database schema's just do not map well to an ORM) at the day job, I have tended to use SQL and the Python DBI for my database access work. Part of my job is to evaluate solutions to make my team more productive, and in reviewing where we allocate our time during development, I noted that a good portion is ensuring our SQL works across multiple RDBMS's. So I have been looking for Python solutions that abstract the creation of SQL against different RDBMS's. On investigating SQLAlchemy I was surprised to see that it you can use it to do exactly what I wanted. Also one of goals of Ian Bickings' SQL-API is to offer this functionality, but what follows is a discussion of how SQLAlchemy could help.

First connect to a database

from sqlalchemy import *
engine = create_engine("sqlite://filename=mydb",echo=True)

The option echo is set so we can view the generated SQL.

Now describe the tables using using table metadata objects

entries = Table('entries', engine,
        Column('entry_id', Integer, primary_key = True),
        Column('title', String(255), nullable = True),
        Column('content', String(), nullable = True),
        Column('status', String(10), nullable = False),
        Column('creation_date', DateTime()),
        Column('publish_date', DateTime())
    )

comments = Table('comments', engine,
        Column('comment_id', Integer, primary_key = True),
        Column('entry_id', Integer, ForeignKey("entries")),
        Column('comment', String(255), nullable = False),
        Column('submitter', String(60), nullable = False),
        Column('submit_date', DateTime())
    )

# Create the tables
entries.create()
comments.create()

Now load some data.

entries.insert().execute(entry_id=1,
            title="My first blog entry",
            content="A blogging I will go.....",
            status="published",
            creation_date="2006-04-17 08:15:30",
            publish_date="2006-04-17 08:25:00")

entries.insert().execute(entry_id=2,
            title="My second blog entry",
            content="Another day, another blog entry....",
            status="draft",
            creation_date="2006-04-18 09:35:30")

comments.insert().execute(comment_id=1,
            entry_id=1,
            comment="The entry needs more substance.",
            submitter="joe.blogger@home.net",
            submit_date="2006-04-17 09:05:00")

comments.insert().execute(comment_id=2,
            entry_id=1,
            comment="I disagree with the first comment.",
            submitter="jane.doe@rip.net",
            submit_date="2006-04-17 09:05:00")

Get some data with a simple select with where clause and order by

cursor = entries.select(entries.c.status=='published',
                order_by=[entries.c.publish_date]).execute()

rows = cursor.fetchall()
for row in rows:
    # Get column data by index
    title = row[1]
    # by column name
    content = row['content']
    # by column accessor
    status = row.status

Now a simple join

cursor = comments.select(comments.c.entry_id==entries.c.entry_id).execute()

which generates the following SQL:

SELECT comments.comment_id, comments.entry_id, comments.comment, 
comments.submitter, comments.submit_date
FROM comments, entries
WHERE comments.entry_id = entries.entry_id

Of course, this quick overview only scratches the surface of what you can do with SQLAlchemy. It also has support for outer joins, subqueries, unions, updates and deletes. See here for more in depth documentation.