These posts use code from the Trac project. I’m using the question mark notation for in-place variable substitution, this is where you normally use either direct variables or an indirection mechanism.

If you have done SQL before you are familiar with the syntax such as:

SELECT name FROM auth_cookie WHERE cookie = ? AND ipnr = ?;

So, how does one do this with SQLAlchemy?

With SQLAlchemy (SA in short) you first declare a schema within Python:

auth_cookie = Table('auth_cookie', metadata,
        Column('cookie', String, primary_key=True),
        Column('name', String, primary_key=True),
        Column('ipnr', String, primary_key=True),
        Column('time', Integer))

Next you import this schema (living within Trac as trac/db/schema.py) as follows:

from trac.db.schema import auth_cookie

This allows direct manipulation using direct calls to auth_cookie. So for a SQL select we need to extend our code as follows:

from sqlalchemy import select

This allows us to build an almost equivalent statement as follows:

statement = select([auth_cookie.c.name], auth_cookie.c.cookie==?)

To add the AND clause SA has a very simple function to add into your code:

from sqlalchemy import and_, select

This allows us to extend the previous statement as such:

statement = select([auth_cookie.c.name], and_(auth_cookie.c.cookie==?, auth_cookie.c.ipnr==?)

Similarly there’s an or_() function as well, which works exactly the same.

Now the difficulty arose due to the fact this SQL query changed its WHERE-clause depending on an if/else. The regular case was the first statement we created, the other case added the cookie’s IP number into the equation. So how to deal with that?

statement = select([auth_cookie.c.name], auth_cookie.c.cookie==?)
if self.check_ip:
    statement.append_whereclause(and_(auth_cookie.c.ipnr==?))

As you can see, depending on whether or not check_ip is set it changes the statement in-place and expands the WHERE-clause with an AND for ipnr.