Adding Tuple to execute?

[Alsandair]

Hi all, I have a problem passing a tuple to cursor.execute()

this works:

sql="SELECT NAME FROM ListsTable WHERE ID=%s"%(ListID)

c.execute(sql)

as does this (IE the variable as a single element):

sql="SELECT NAME FROM ListsTable WHERE ID=%s"

c.execute(sql, ListID)

but passing it as a single element tuple doesn't:

sql="SELECT NAME FROM ListsTable WHERE ID=%s"

c.execute(sql,(ListID,))

(This doesn't always throw an error but sometimes it gives:)

File "/var/www/cgi-bin/MySQLdb/cursors.py", line 159, in execute

query = query % db.literal(args)

TypeError: not enough arguments for format string

This is a problem as other queries have multiple variables so i need to pass
them as a tuple.. anyone got any ideas? I'm using python 2.3.6 and mysqldb
1.2.3

cheers

[Cat slave]

In your context, it's weird to have multiple values for "ID=?". Can you show
the content of "ListID" ?

As for example, here is what you may be able to do:

sql="SELECT NAME FROM ListsTable WHERE ID IN (%s)" % (','.join(['%s'] * len(ListID)),)
c.execute(sql, ListID)   or c.execute(sql, *ListID)

I can't remember the exact syntax.

Each element of ListID need a '%s' to be replaced by execute().

[Alsandair]

there's only one value for ID, ListID is a single numeric value, the example
above was just descriptive of the problem, sorry :)

I'm fine to use ListID as a single parameter in the way that works above, but
other queries will need multiple parameters which is where the problem kicks
in

[Cat slave]

You still should not used this way as you will get used to it. Imagine your
value for another query would be:

value = "it's ok"
sql = "select * from t where status = %s" % (value,)
c.execute(sql)

will break because of invalid sql.

[Alsandair]

That's strange, the docs said mysqldb doesn't accept single parameters but
would accept a single element tuple? i thought that was how it was supposed to
be done?

How would i pass 2 parameters like i'm trying to do here?

sql="SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s"

c.execute(sql,(Page,(Page*20)+20))

[Alsandair]

this one works btw so i'm confident the parameters are fine:

sql="SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s"%(Page,
(Page*20)+20)

c.execute(sql)

[Cat slave]

If you have a table like this:

mysql> create table t (lastname varchar(30), firstname varchar(30));

Your method only works with "non-delimited values" (like numbers), but won't
work with strings.

sql = 'select * from t where lastname=%s and firstname=%s' % ('last', 'first',)
cur.execute(sql)
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/lib64/python2.6/site-packages/MySQLdb/cursors.py", line 166, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib64/python2.6/site-packages/MySQLdb/connections.py", line 35, in defaulterrorhandler
    raise errorclass, errorvalue
_mysql_exceptions.OperationalError: (1054, "Unknown column 'last' in 'where clause'")

You would need to do it like this:

>>> cur.execute('select * from t where lastname=%s and firstname=%s', ('last', 'first'))

This will also prevent SQL injections by correctly escaping the values.

Hope this clears things for you.

[Alsandair]

yeah because the script uses user data I'd like to paramaterise the data like
your second example, unfortunately the string substitution method you used
(first one) works, whereas the correct way with tuples in your second example
doesn't work, as in my code snippets above :(

[Alsandair]

Just to clarify what I'm asking:

Is there any reason why your tuple method wouldn't work even though the data
is definitely fine? I've posted examples of my attempts above.

String substitution method works fine

Passing the values as a tuple doesn't work (The values and table are
definitely fine, as they work for string substitution)

No idea why.

[Cat slave]

Can you repost exactly what you did that worked and that not worked.

Use BBCode please ( b l a h b l a h )

[Alsandair]

Just as a quick check, is mysqldb compatible with python 2.3.6? it said it was
on the site I got it from, but the errors I get are from within the mysqldb
library itself (any mention of conn.literal gives the errors like below:)

File "/var/www/cgi-bin/MySQLdb/cursors.py", line 159, in execute

query = query % db.literal(args)

TypeError: not enough arguments for format string

even if i try to use .literal in my main code to rule out the mysqldb library

[Alsandair]

anyway, to answer your request.

This works:

sql="SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s"%(Page, (Page*20)+20)
c.execute(sql)

When I try to paramaterize it however, it won't accept the arguments in a
tuple:

sql="SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s"
var=Page
var2=(Page*20)+20
params=(var,var2)
c.execute(sql,params)

instead, the latter gives this error:

Traceback (most recent call last):
  File "process.py", line 202, in ?
    main()
  File "process.py", line 97, in main
    c.execute(sql,params)
  File "/var/www/cgi-bin/MySQLdb/cursors.py", line 148, in execute
    query = query % db.literal(args)
TypeError: not enough arguments for format string

I've tried downgrading to mysqldb 1.2.1_p2 but it's giving this same result
(My version of python is 2.3.6)

[Anonymous]

Can you insert a print statement before your execute() call and print sql,
params

[Alsandair]

hi, because it's on a webserver i normally just dump values to a text file,
here's what I've done for those values:

dumped.write("params is "+str(params[0])+","+str(params[1])+"\nsql is "+sql)

the result:

params is 0,20
sql is SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s

(dumping params to the text file was erroring as they're ints so i had to cast
to str, if that helps)

[Anonymous]

... Try this instead:

dumped.write("sql = "+repr(sql)+"; params = "+repr(params)")

[Anonymous]

Err, remove that last "

[Alsandair]

cheers :) that gives:

sql = 'SELECT * FROM ListsTable ORDER BY ID DESC LIMIT %s,%s'; params = (0, 20)

[Anonymous]

And for that combination, you get the TypeError?

[Alsandair]

yup :( it's weird, I was sure in the docs it said the input ALWAYS had to be a
tuple, but like i said in my first post, i can pass a single int as the 2nd
element, but if i tried to send it as a single-element tuple it failed :/

to test it, I just tried to output this from my main, but got the same error
as I usually get in th elib, just this time in m ymain (db is the name of my
database object)

 db.literal((1,2))

trying to print that from main gave the same TypeError, which was why I was
wondering maybe if the versions of python and mysqldb I'm using aren't
compatible

beyond that I've no clue :(

[Alsandair]

The same variables work normally in a string substitution so i know the values
etc should be fine, I'm just going over the script now that i know it all
works and trying to move them instead as second paramaters to execute for
security and that's where it all dies, so the actual sql logic and values
passed to it should definitely be fine

[Cat slave]

Sorry, I don't have access to a python 2.3 to test it. I only have 2.6
available.

[Alsandair]

That's ok, I've already downgraded my mysqldb to 1.2.1, i might downgrade it a
few more times to test it in a few minutes

[Alsandair]

ah ok, downgrading to mysqldb 1.0.0 changes from the script crashing and
producing the above error in the error log, at least this time it behaves a
big better and just triggers an except which writes the following to a text
file:

('not enough arguments for format string',)

which is the same problem I guess, I think it's cursed

[Alsandair]

oh, also something else (it's 4am here and my mind's wandering..) when i
downgraded to 1.0.0 i got this error:

File "/var/www/cgi-bin/MySQLdb/__init__.py", line 31, in ?
raise ImportError, "this is MySQLdb version %s, but _mysql is version %s" %\\
ImportError: this is MySQLdb version (1, 0, 0, 'final', 1), but _mysql is version (1, 2, 1, 'final', 2)
Premature end of script headers: process.py

also had this with 1.2.3 (but not 1.2.1)

since i wiped the library off my webserver and copied everything from the same
package on this site I don't know why that error would be there (I just
commented out the error check in init to test it)

don't want to doubt the libraries provided on this project page though, I'm
assuming them to be ok..

[Alsandair]

Does anyone have any ideas?