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.

Friday, April 14, 2006

Pylons - another great tool for my programming toolkit

In a previous post I mentioned that I had been looking at Pylons, a WSGI enabled web framework and had a project that would be a good fit. Finally have found some time to start work on it and my experiences with Pylons so far have been good. I picked Pylons for a number of reasons:
I originally had concerns about learning yet another template syntax - Myghty, Pylons default template package, but have found it easy enough to learn and use. So one of the reasons I choose Pylons - support of different template packages really has no substance. But some of the other things I have discovered while using Pylons certainly should be added to the reasons list. Both of them are re-implementations of Rails functionality.
  • Routes - makes it easy to create nice and concise URL's like http://timesheets.com/user/fred-smith/edit. No more ugly http://timesheets.com/user-edit.html?name=fred-smith
  • WebHelpers - functions that simplify web development with template languages by providing common view patterns in re-usable modules. In addition to the Rails webhelpers, there helpers for HTML generation and pagination for collections and ORMs.
And if you are not interested in using Pylons, both these packages can be used standalone.

As I progress with my project, I am sure there will be more posts about my Pylons experiences.