DB-API execute params, am I missing something?

[Gabriel Rossetti]

Hello everyone, I am trying to use dbapi with mysql and I get this error:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "<stdin>", line 2, in getUnitParams
File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 151,
in execute
query = query % db.literal(args)
TypeError: int argument required

with this code :

import MySQLdb

def getParams(curs):
curs.execute("select * from param where id=%d", 1001)
return curs.fetchall()

cp = MySQLdb.connect(host="localhost",
port=3306,
user="root",
passwd="123",
db="test")

curs = cp.cursor()
result = getParams(curs)

I checked MySQLdb.paramstyle and it uses "format". I also tried passing
(1001,) instead of just 1001 as the param but this changes nothing. What
am I doing wrong?

Thank you,
Gabriel

 

[Diez B. Roggisch]

Hello everyone, I am trying to use dbapi with mysql and I get this error:

Traceback (most recent call last):
File "<stdin>", line 1, in <module>
File "<stdin>", line 2, in getUnitParams
File "/var/lib/python-support/python2.5/MySQLdb/cursors.py", line 151,
in execute
query = query % db.literal(args)
TypeError: int argument required

with this code :

import MySQLdb

def getParams(curs):
curs.execute("select * from param where id=%d", 1001)
return curs.fetchall()

cp = MySQLdb.connect(host="localhost",
port=3306,
user="root",
passwd="123",
db="test")

curs = cp.cursor()
result = getParams(curs)

I checked MySQLdb.paramstyle and it uses "format". I also tried passing
(1001,) instead of just 1001 as the param but this changes nothing. What
am I doing wrong?

AFAIK you need to use %s, regardless of what type you pass.

Diez

 

[Paul Boddie]

def getParams(curs):
    curs.execute("select * from param where id=%d", 1001)

First of all, you should use the database module's parameter style,
which is probably "%s" - something I've thought should be deprecated
for a long time due to the confusion with string substitution that
this causes, regardless of whether that mechanism is actually used
internally by the module (as seen in your traceback).

You also have to provide a sequence of parameters: unlike string
substitution, you cannot provide a single parameter value and expect
the execute method to do the right thing. Some database modules seem
to insist on either lists or tuples of parameters, but I think any
sequence type should work.

Try this:

curs.execute("select * from param where id=%s", (1001,))

Paul

 

[Gabriel Rossetti]

Gabriel Rossetti wrote:



AFAIK you need to use %s, regardless of what type you pass.

Diez

Ok, thank you, that was the problem. I also learned not to put the
quotes in the SQL statement (e.g. SELECT * FROM toto WHERE
toto.name='%s') since the call to literal() adds them

Thank you,
Gabriel

 

[Gabriel Rossetti]

First of all, you should use the database module's parameter style,
which is probably "%s" - something I've thought should be deprecated
for a long time due to the confusion with string substitution that
this causes, regardless of whether that mechanism is actually used
internally by the module (as seen in your traceback).

You also have to provide a sequence of parameters: unlike string
substitution, you cannot provide a single parameter value and expect
the execute method to do the right thing. Some database modules seem
to insist on either lists or tuples of parameters, but I think any
sequence type should work.

Try this:

curs.execute("select * from param where id=%s", (1001,))

Paul

Thank you for the explanation Paul!

Gabriel