On ORMs
It so happens that I end up dealing with the Python ORM SQLObject pretty often. I don't really like ORMs very much, since in my experience they make those 80% of database things that are already easy to do with plain SQL easier, while making the other 20% of database things which are already hard impossible. They do save some boiler-plate, and let you express your schema and queries in Python (or whatever programming language you are using) instead of SQL - but this tends to break down at a certain level of complexity, and just gets in the way. You end up doing a huge amount of wrangling with the ORM to do something which is very simple in plain old SQL. Fundamentally, SQL was designed as a declarative query language for the relational model and not to represent object hierarchies in the same way programming languages do, hence ORMs are always going to be a nasty hack in my opinion.
SQLObject vs. SQLAlchemy
I actually much prefer SQLAlchemy to SQLObject. SQLAlchemy has a more explicit divide between its various components - you aren't forced to use the ORM stuff if you don't want to. It can be used just as a handy database abstraction layer with programmatic SQL and connection pooling and so on if you want. And if you truly want to go the full ORM mapping route, they provide that too. For truly tricky things, SQLAlchemy will be happy to provide you with a DB-API 2.0 cursor so that you can execute whatever custom SQL you wish.
Monolithic vs. modular
This is my main problem with SQLObject - its very difficult to figure out how to get at the underlying database connection. I don't know how its possible to use the connection pooling and programmatic SQL builder without using the ORM but perhaps it is doable. The documentation for SQLObject is far inferior to the documentation of SQLAlchemy I'm sorry to say. Just try to figure out how to use transactions reliably with SQLObject! Even when I managed to put together some code which according to the documentation should work, SQLObject decided to interleave the actions in separate transactions. With SQLAlchemy I never had this problem.
Getting at the cursor
While seemingly undocumented, it is in fact possible to get the underlying driver's connection object, and from there grab a DB-API cursor. The pattern is:
# Set up the SQLObject connection as usual
connection = connectionForURI('sqlite:/:memory:')
# Grab the database connection
dbobj = connection.getConnection()
# Get a cursor from the low-level driver
cursor = dbobj.cursor()
#
cursor.close()
Et voila.
Niall O'Higgins is an author and software developer. He wrote the O'Reilly book MongoDB and Python. He also develops Strider Open Source Continuous Deployment and offers full-stack consulting services at FrozenRidge.co.
