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
Now describe the tables using using table metadata objects
First connect to a database
from sqlalchemy import *The option echo is set so we can view the generated SQL.
engine = create_engine("sqlite://filename=mydb",echo=True)
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,Get some data with a simple select with where clause and order by
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")
cursor = entries.select(entries.c.status=='published',Now a simple join
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
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,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.
comments.submitter, comments.submit_date
FROM comments, entries
WHERE comments.entry_id = entries.entry_id
