Section A.12. Updating Queries

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().