I’ve been meaning to sit down and play with sqlalchemy for a while now. As an old person though my needs are a little different. Most people use the ORM model to relieve themselves of the burden of dealing with SQL database engines. This is all fine and dandy if you have the luxury of using an SQL database solely as a repository for data with permanence. However, SQL databases can do much more than that. In this vein the sqlalchemy tutorials don’t tell you much about database introspection, that is figuring out what the layout of a table is from the information available in the database. Introspection is very important to me because I frequently create tables (and contraints and triggers etc) on the database. I also have a frequent need to an SQL feature called views but that’s a story for a different day. I came up with this code:
#! /usr/bin/env python
''' ============================================================================================
Program: sqla.py -- A test of sqlalchemy's ability to introspect tables from a database.
============================================================================================ '''
from sqlalchemy import *
from sqlalchemy.orm import *
metadata = MetaData("mysql://scott:tiger@mysql.example.com/test")
workEntryMeta = Table('work_entry', metadata,
Column('work_entry_id', Integer, primary_key=True),
autoload=True, )
class WorkEntry(object):
pass
def sqlToStr(c):
if c is None:
return "NULL"
else:
return str(c)
mapper(WorkEntry, workEntryMeta)
session = create_session()
q = session.query(WorkEntry)
entries = q.all()
headers = None
for e in entries:
if headers is None:
headers = [ c for c in e.__dict__.keys() if c[0] <> '_' ]
print headers
print "\t".join(headers)
d = [ sqlToStr(e.__dict__[c]) for c in headers ]
print "\t".join(d)
This creates an ORM object for the table work_entry in the current python program and grabs all the rows from the database printing each one as it goes.