Varibles in SQL statements

[Paul Boddie]

Rob Elder wrote:
> 
> Hello,
> 
>    I don't understand how to use (or format really) varibles in SQL.With
> the sequence
> 
> var = '1'
> mycursor.execute('SELECT userID FROM userTable WHERE userID=var')
> 
> I get a too few paramerters error

This would never work unless a column called 'var' existed in 'userTable', and
then it would give you some unexpected results - the above statement treats
'var' as a column name, and doesn't involve your Python variable 'var' at all.

> This does work:
> 
> mycursor.execute("SELECT userID FROM userTable WHERE userID='1'")

I would recommend using the following:

  mycursor.execute("SELECT userID FROM userTable WHERE userID=?", ('1',))

or:

  mycursor.execute("SELECT userID FROM userTable WHERE userID=?", (var,))

Note that we pass a tuple as the second argument to 'execute' in order to tell
the database module what to replace the '?' with in the query string. Of course,
you may need to look up the syntax that your database system actually uses for
bind variables; it might be ':1', ':2' and so on, or '%s', described in the
DB-API specification:

  http://www.python.org/topics/database/DatabaseAPI-2.0.html

Look for 'paramstyle' in the above document.

Paul