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.