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_cookieThis 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 selectThis 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_, selectThis 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.