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:
    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.

Office 2003, Visual Basic editor and AppLocale

So I was working with a Japanese .xla (Excel add-in) file. I needed to look at something in the source so I fired up the Visual Basic editor within Excel. Upon investigating the form and the various captions it turns out that the Visual Basic editor only displayed them in gibberish (typical decoding issues) or question marks (substituting the .notdef glyph for codepoints). So it seems the Visual Basic editor is either not multi-byte capable (typing directly a string in Japanese into the caption yielded question marks) or it is bound to the locale of the system.

I then remembered AppLocale and fired up Excel through it, setting it to think it is on a Japanese system. Then within Excel I proceeded to start the Visual Basic editor and, sure enough, the text was showing me the Japanese I needed.

I am not sure if I should find this lame or understandable.