A.12. Updating Queries

Part of the reason that migration from SQLObject to SQLAlchemy is not too onerous is that SQLAlchemy has largely adopted SQLObject's query style.

The first change you need to be aware of is that SQLObject's q object for referring to columns has been named c in SQLAlchemy. Consider the case of a wiki:

class Page(ActiveMapper):
        class mapping:
            pagename = Column(String(40), unique=True)
            data = Column(String)

In the equivalent SQLObject definition, if you want to query on pagename you do something like this: Page.select(Page.q.pagename > 'F') . In SQLAlchemy, you would do this: Page.select(Page.c.pagename > 'F') . The only difference between those two is that Page.q has been replaced by Page.c!

The operator handling is similar between the two packages. One difference is that SQLObject uses functions called AND, OR, and NOT. SQLAlchemy uses and, or, and not_ (with the trailing underscore being the PEP 8 listed style for naming a function the same as a Python keyword). If you use the & and operators, those work the same between SQLObject and SQLAlchemy.

Both packages support startswith and endswith and both use func to refer to database engine functions.

One important difference to note: In SQLObject, select() returns an iterable SelectResults object. SQLAlchemy's select() call, on the other hand, returns a list directly. In SQLObject, you can say Page.select().count() to get a count of the pages. To do the same in SQLAlchemy, you run Page.count() . SQLObject lets you place limits on a query by using Python's slice ([x:y]) notation; for example, Page.select()[0:10] . In SQLAlchemy, you add the limits more explicitly before running the query as part of the select call Page.select(limit=10, offset=1).execute() .

A.13. Final Thoughts

SQLAlchemy's application programming interfaces (APIs) are well thought out and generally stable, but it is likely that there will still be some fluctuation. The code itself is solid and has an extensive set of tests. If you are willing to accept the need to make minor changes to your code from time to time as SQLAlchemy evolves, you will find that it is an incredibly powerful and yet easy-to-use object relational mapper and database-independent SQL generator.