Passing params to db-api queries

Falling mostly into the aide-memoire category, but in case it’s helpful to anyone else…

You have a more-or-less complex SQL query which you’re executing via, eg, pyodbc (or some other dbapi-compliant module) and you need to pass in a set of positional parameters. So you have a where clause which looks something like this (although with better names, obviously):

WHERE
(
  t1.x = ? AND
  (t2.y = ? OR (t3.z = ? AND t2.y < ?))
)
OR
(
  t1.x > ? AND
  (t2.y BETWEEN ? AND ?)
)

So your Python code has to pass in seven parameters, in the right order, several of which are probably the same value. And then you realise that the WHERE clause is slightly wrong. So you adjust it, but now you have eight parameters, and two of the previous ones have changed, and there’s a new one. And then…

There’s no way to use named params with pyodbc, so you end up with a list/tuple of positional parameters which you have to eyeball-match up with the corresponding question marks in the query:

import pyodbc

...

cursor.execute (
  SQL, [
  from_date, threshold, threshold, to_date, interval, threshold]
)

Unless… you use a derived table in the query and use that to generate pseudo-named parameters. This is possible in MSSQL; I don’t know if it would work with other databases, although I can’t see why not. So your code becomes something like (NB no attempt at consistency here; it’s an example):

SELECT
  *
FROM
  t1
JOIN t2 ON t2.t1_id = t1.id
JOIN
(
  SELECT
    from_date = ?,
    to_date = ?,
    max_value = ?,
    interval = ?,
    threshold = ?
) AS params ON
(
  t1.x = params.from_date AND
  (t2.y = params.threshold OR
    (t3.z = params.interval AND t2.y < params.to_date)
  )
)
OR
(
  t1.x > params.threshold AND
  (t2.y BETWEEN params.from_date  AND params.to_date)
)

All you need to do then is to line up the order of params in your cursor.execute with the order of columns in the params derived table.

Alternatives? Well, you could use an ORM of some sort — goodness knows there are enough of them about — but maybe, like me, you find that learning another syntax for something which you can do perfectly well in its native SQL is onerous. Another approach is to set up local variables in your executed statement and use these in much the same way, eg:

DECLARE
  @v_from_date DATETIME,
  @v_to_date DATETIME,
  @v_threshold INT

SELECT
  @v_from_date = ?,
  @v_to_date = ?,
  @v_threhold = ?

SELECT
  *
FROM
  ..
WHERE
  (t1.x < @v_from_date ...)

This works (and is, in fact, how we generate lightweight SQL-to-Excel reports). But there’s a bit more boilerplate involved.

4 Comments so far »

  1. Brandon Craig Rhodes said,

    Wrote on August 28, 2009 @ 3:37 pm

    You should try SQLAlchemy. Not their ORM, and not their Pythonic method-based method for building queries; but their old-fashioned interpolated-string way of doing SQL calls. To make things easy, they use “:names” to do interpolation, regardless of what the database back-end wants; if it wants something different, they translate the “:names” for you, so that you just have to worry about clean, name-based interpolation regardless of how obnoxious the real database back-end is. Check out their documentation for the feature:

    http://www.sqlalchemy.org/docs/05/session.html#using-sql-expressions-with-sessions

    It looks like exactly what you want!

  2. tim said,

    Wrote on August 28, 2009 @ 3:55 pm

    @Brandon: thanks for the tip. I have played around with SQLAlchemy at different levels, and I probably should go back again. It’s just that I’ve got so much code invested in my very lightweight sql-row wrappers that any extra overhead is overhead.

  3. OpenIDhttp://zzzeek.blogspot.com/ said,

    Wrote on August 28, 2009 @ 5:49 pm

    If you’re averse to 3rd party toolkits, the ? as bind parameter issue is easily solved using a small regexp:

    import re

    binds = re.compile(r’:(\w+)’)
    def stmt_and_args(stmt, params):
    positional = []
    def repl(m):
    bindname = m.group(1)
    positional.append(params[bindname])
    return “?”
    return binds.sub(repl, stmt), positional

    stmt = “select * from foo where id=:bar and bat=:foo”

    print stmt_and_args(stmt, {’bar’:5, ‘foo’:12})

  4. tim said,

    Wrote on August 28, 2009 @ 7:04 pm

    @zzzeeek: Neat trick; never thought of that one.

    FWIW I’m not that averse to 3rd party toolkits, and if I ever have the need to use an ORM then sqlalchemy’s pretty much top of the list. It’s just that I’ve spent so long writing and maintaining quite complex SQL structures, triggers, procedures, etc. that anything on top seems cumbersome. (As long as I don’t have to pass too many parameters in :) )

Comment RSS · TrackBack URI

Leave a Comment

OpenID

Sign in with your OpenID ?

Anonymous

Name: (Required)

E-mail: (Required)

Website:

Comment: