Django MySql Raw Query Error - Parameter index out of range

Question

This view is running fine on plain pyton/Django/mysql on Windows
I'm porting this to run over jython/Django/mysql and it gives error -

Exception received is : error setting index [10] [SQLCode: 0]    
Parameter index out of range (10 > number of parameters, which is 0). [SQLCode: 0],  
[SQLState: S1009]

The Query is -

cursor.execute("select value from table_name    
where value_till_dt >= str_to_date('%s,%s,%s,%s,%s', '%%m,%%d,%%Y,%%H,%%i')    
AND value_till_dt <=  str_to_date('%s,%s,%s,%s,%s', '%%m,%%d,%%Y,%%H,%%i')    
and granularity='5'    
ORDER BY value_till_dt",    
[int(tempStart.month),int(tempStart.day), int(tempStart.year), int(tempStart.hour), int(tempStart.minute),    
int(tempEnd.month), int(tempEnd.day), int(tempEnd.year), int(tempEnd.hour), int(tempEnd.minute)])

As you see there are 10 parameters being passed to this query. Does the error mean that the query is not getting the parameters ?

I have printed out the parameters just before the execution and they are showing as being passed correctly -

1 - Start Parameters being passed are : 1 11 2010 10 0   
2 - End Parameters being passed are : 1 11 2010 10 5

The only different in the second environment is that there is no data available for this date range. But the error does not seem to be related to data.

Any thoughts are appreciated.

Answer 1

Are you sure that the parameter marker is %s and not ? or even :parameter? Check the paramstyle argument of the DB-API module to find out.

Answer 2

It's indeed a parameter style problem. You have to use ? instead of %s.

Here is how you reproduce the error you are getting:

shell> jython
>>> from com.ziclix.python.sql import zxJDBC
>>> (d, v) = "jdbc:mysql://localhost/test", "org.gjt.mm.mysql.Driver"
>>> cnx = zxJDBC.connect(d, None, None, v)
>>> cur = cnx.cursor()
>>> cur.execute("SELECT %s", ('ham',))
..
zxJDBC.Error: error setting index [1] [SQLCode: 0]
Parameter index out of range (1 > number of parameters,
  which is 0). [SQLCode: 0], [SQLState: S1009]

Now, if you use quotes around the ?-mark, you'll get the same problem:

>>> cur.execute("SELECT '?'", ('ham',)) 
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
zxJDBC.Error: error setting index [1] [SQLCode: 0]
Parameter index out of range (1 > number of parameters,
  which is 0). [SQLCode: 0], [SQLState: S1009]

The point is to not use quotes and let the database interface do it for you:

>>> cur.execute("SELECT ?", ('ham',))  
>>> cur.fetchall()
[(u'ham',)]

Here is how I would do it in the code. You first make the strings you are going to use for the str_to_date() functions like this:

start = "%d,%d,%d,%d,%d" % (int(tempStart.month),
  int(tempStart.day), int(tempStart.year),int(tempStart.hour), 
  int(tempStart.minute))
stop = "%d,%d,%d,%d,%d" % (int(tempEnd.month),
  int(tempEnd.day), int(tempEnd.year), int(tempEnd.hour),
  int(tempEnd.minute))

You make the SELECT statement, but don't use any quotes, and pass it on to the cursor. The database interface will do the job for you. Also, we put 'granularity' value as a parameter.

select = """SELECT value FROM table_name
  WHERE value_till_dt >= str_to_date(?, '%%m,%%d,%%Y,%%H,%%i')
  AND value_till_dt <= str_to_date(?, '%%m,%%d,%%Y,%%H,%%i')
  AND granularity=?
  ORDER BY value_till_dt
"""
cursor.execute(select, (start,stop,5))

I hope this helps!