SQLAlchemy and SqlSoup Rule!

SQLAlchemy is a python ORM mapper along the lines of Hibernate.

This morning I was trying to figure out how to map all the tables into model objects so I could expose them in this system inventory app I'm working on. Also, I wanted similar functionality to the hibernate session manager. After writing a bunch of code, I discovered that this does everything I need:

db = SqlSoup('postgres://%s:%s/%s' % (dbhost, dbport, dbname))

(where the db* vars are either passed in or derived from a config). I have a table in my database called pc_hardware, after executing the above, with no further configuration whatsoever in the program, I can do the following:

newpc = db.pc_hardware.insert(mac_address='00:16:cb:8c:b5:86', model='macbook pro')
db.flush()

(Confusingly flush() actually means commit)
Then in a later session I can do this:

print db.pc_hardware.select()
[MappedPc_hardware(hardware_id=3L, mac_address='00:16:cb8c:b5:86', cpu_info=None, cpu_cores=None, gb_ram=None, power_supplies=None, console=None, model='macbook pro', form_factor=None, purchase_date=None, receive_date=None, vendor=None, location=None, status='new', last_modified=datetime.datetime(2007, 6, 19, 12, 45, 6, 659993),notes=None)]

pc = db.pc_hardware.get(3)
pc.gb_ram = 16
db.flush()

I can even get at the column definitions if need be:

pprint(list(db.pc_hardware.c))
[Column(u'hardware_id', PGInteger(), primary_key=True, nullable=False,
default=PassiveDefault()),
Column(u'mac_address', PGMacAddr(), nullable=False),
Column(u'cpu_info', PGText(length=None, convert_unicode=False)),
Column(u'cpu_cores', PGInteger()),
Column(u'gb_ram', PGInteger()),
Column(u'power_supplies', PGInteger()),
Column(u'console', PGBoolean()),
Column(u'model', PGText(length=None, convert_unicode=False)),
Column(u'form_factor', PGText(length=None, convert_unicode=False)),
Column(u'purchase_date', PG2Date()),
Column(u'receive_date', PG2Date()),
Column(u'vendor', PGText(length=None, convert_unicode=False)),
Column(u'location', PGText(length=None, convert_unicode=False)),
Column(u'status', PGText(length=None, convert_unicode=False), ForeignKey
(u'hardware_status.status'), nullable=False, default=PassiveDefault()), Column(u'last_modified', PG2DateTime(timezone=False), nullable=False, default=PassiveDefault
()),
Column(u'notes', PGText(length=None, convert_unicode=False))]

this is just one of the tables in the db, it automagically maps all the others as well if I access them. SQLAlchemy also creates
associations based on foreign key contraints, but I haven't played with that yet. Here are some more docs on the SqlSoup extension:

http://www.sqlalchemy.org/trac/wiki/SqlSoup