Moderate Realism

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.

I’ve been using cherrypy for a production server at work, hosting a web interface to our helpdesk system. It’s been stable and usable even over several upgrades (I started off somewhere at cherrypy 2.x). I recently raised an issue concerning a multipart form (such as you use with a file upload control) with non-ascii text dropped into it. And a fix was applied within a couple of weeks.

I’d like to think that the trouble I took to narrow the problem down to a repeatable case helped things along. (And, goodness knows, I’ve encouraged enough people to do that on the Python lists). But in any case I very much appreciate the response from the cherrypy developers. This is one of those annoying technical things which users just can’t understand: “But why does it crash when I put a pound sign into the text?” (or when you cut-and-paste from a Word doc and you get those smart-quotes).

But not only are they responsive to bug reports; they also keep their docs up-to-date, including the very useful sections which indicate what’s changed from previous versions.

Just a quick aide-memoire and a note to anyone else who’s caught out… when using Python’s smtplib module to send email. If you’re like me, you may have missed the following documented behaviour:

This method will return normally if the mail is accepted for at least one recipient. Otherwise it will throw an exception. That is, if this method does not throw an exception, then someone should get your mail. If this method does not throw an exception, it returns a dictionary, with one entry for each recipient that was refused. Each entry contains a tuple of the SMTP error code and the accompanying error message sent by the server.

In other words, sendmail will return successfully even if some of the recipients couldn’t receive the email. You can work out which recipients failed from the dictionary returned. I’d assumed that if *any* recipient didn’t get the email then an exception would be raised. As I say, the actual behaviour is clearly documented, but just in case…