Sunday, April 16, 2006

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.

2 comments:

andrew said...

Very nice!
Will SQLAlchemy work with ironpython now?

Mark Rees said...

Seo Sanghyeon and myself have had a little success in getting some of SQLAlchemy to work with IronPython. It requires a small patch and the dbapi modules for IP need some changes. Once I check these changes in, I will write a post to give a status update.