SQLAlchemy and simple WHERE clauses using AND

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:

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.

strmode() function declaration buglet fixed

Was converting files to proper ANSI C function declarations and a user tripped over the fact that old 4.4 BSD's function prototype of strmode() had int as a parameter, whereas it has been mode_t for a long, long time (read 1994 at least).

This broke buildworld of course.

Also asked Dima Dorfman to fix this for FreeBSD (broken in 4.x, 5.x, and 6.x).

NetBSD is fixed (as was to be expected to be honest).

And OpenBSD made the parameter int everywhere with a XXX comment in the strmode.c file that it should be mode_t actually. Weird.