Problem with format string / MySQL cursor

[Florian Lindner]

Hello,
I have a string:

INSERT INTO mailboxes (`name`, `login`, `home`, `maildir`, `uid`,
`gid`, `password`) VALUES (%s, %s, %s, %s, %i, %i, %s)

that is passed to a MySQL cursor from MySQLdb:

ret = cursor.execute(sql, paras)

paras is:

('flindner', '(e-mail address removed)', '/home/flindner/', '/home/
flindner/Mail/test', 1001, 1001, '123')

But that gives me an error:

Traceback (most recent call last):
File "account.py", line 188, in ?
main()
File "account.py", line 33, in main
execute(action, account_type, options)
File "account.py", line 129, in execute
executeSQL(sql, options.username, options.login, options.home,
options.directory, options.uid, options.gid, options.password)
File "/home/flindner/common.py", line 29, in executeSQL
ret = cursor.execute(sql, paras)
File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
148, in execute
query = query % db.literal(args)
TypeError: int argument required


I don't see errors in the format string or some other problem....

What's wrong?

Thanks,

Florian

 

[timaranz]

Hello,
I have a string:

INSERT INTO mailboxes (`name`, `login`, `home`, `maildir`, `uid`,
`gid`, `password`) VALUES (%s, %s, %s, %s, %i, %i, %s)

that is passed to a MySQL cursor from MySQLdb:

ret = cursor.execute(sql, paras)

paras is:

('flindner', '(e-mail address removed)', '/home/flindner/', '/home/
flindner/Mail/test', 1001, 1001, '123')

But that gives me an error:

Traceback (most recent call last):
File "account.py", line 188, in ?
main()
File "account.py", line 33, in main
execute(action, account_type, options)
File "account.py", line 129, in execute
executeSQL(sql, options.username, options.login, options.home,
options.directory, options.uid, options.gid, options.password)
File "/home/flindner/common.py", line 29, in executeSQL
ret = cursor.execute(sql, paras)
File "/usr/lib/python2.4/site-packages/MySQLdb/cursors.py", line
148, in execute
query = query % db.literal(args)
TypeError: int argument required

I don't see errors in the format string or some other problem....

What's wrong?

Thanks,

Florian

You should be using '?' for parameter bindings in your sql string not
python format specifiers (you are after all writing sql here not
python).

INSERT INTO mailboxes (`name`, `login`, `home`, `maildir`, `uid`,
`gid`, `password`) VALUES (?, ?, ?, ?, ?, ?, ?)

Cheers
Tim

 

[Paul McNett]

You should be using '?' for parameter bindings in your sql string not
python format specifiers (you are after all writing sql here not
python).

INSERT INTO mailboxes (`name`, `login`, `home`, `maildir`, `uid`,
`gid`, `password`) VALUES (?, ?, ?, ?, ?, ?, ?)

Sorry Tim, but that isn't correct:


'format'

Florian, what happens when you replace your %i with %s?

 

[Florian Lindner]

Sorry Tim, but that isn't correct:


'format'

Florian, what happens when you replace your %i with %s?

That works! Thanks! But a weird error message for this solution...

 

[Dennis Lee Bieber]

That works! Thanks! But a weird error message for this solution...

Not really... The MySQLdb adapter converts all parameters to
properly delimited STRINGS (I don't know of any SQL system that passes
/binary/ numerics). But MySQLdb also uses Python % formatting to then
insert those delimited strings into the query. So... ONLY %s
placeholders (or for dictionary versions "%(key)s") are valid... %i
wants a numeric data type, but MySQLdb has already converted any numeric
parameter into a string: boom! type mismatch.
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Gabriel Genellina]

En Fri, 19 Oct 2007 05:32:04 -0300, Dennis Lee Bieber

Not really... The MySQLdb adapter converts all parameters to
properly delimited STRINGS (I don't know of any SQL system that passes
/binary/ numerics). But MySQLdb also uses Python % formatting to then

I'd say the opposite: only poorly implemented systems convert numeric
arguments to text.
*Real* bound variables are passed as pointers. The SQL sentence can be
prepared and re-used with different sets of values several times, without
having to re-parse it again and again.
If the MySQLdb adapter actually converts and inserts the arguments itself
into the supplied SQL sentence, generating a new string for each call,
this advantage -and many others- are cancelled, defeating the purpose of
bound variables.

 

[Dennis Lee Bieber]

If the MySQLdb adapter actually converts and inserts the arguments itself
into the supplied SQL sentence, generating a new string for each call,
this advantage -and many others- are cancelled, defeating the purpose of
bound variables.

Okay, I'm a bit behind the time... The book I keep on the floor next
to my computer is the old brown/black version covering version 3.x and
introducing ver 4.0 (my newer books are across the room in shelves
behind the couch that take time to reach)

It can't be fully blamed on the MySQLdb adapter... After crawling
through too many reference books, it looks like MySQL itself didn't
support "prepared statements" until sometime in the ver 4.1.x period --
I believe the MySQLdb adapter is still ver 3.x compatible, using the
mysql_query(connection, SQL_string) call, rather than the half-dozen
newer functions for separately binding parameters to prepared
statements.

Since, to my experience, db-api 2 doesn't expose the concepts of
prepared statements to the user, about the only place using them would
offer a true speed-up would be in the .executemany() call (and even then
it would seem to depend upon which is more costly: formatting a properly
escaped string, sending across to server, having server parse/execute
it, repeat for next set of parameters... vs: preparing the query (and
does that get saved on the server side, or is it -- in whatever form --
sent each time), binding a set of parameters, submitting for execution,
binding next set, submit, repeat...)

If the api exposed them to the user, I could see a potential use for
them: an application with, say, multiple forms (each of one or more DB
tables)... By preparing and saving a set of statements for each form
(parameterized select, update, insert, delete, say) during application
startup (or conditionally on first entry to a form) one would only need
to reference the prepared statements for the forms as the end-user
navigates the forms.

{At least we're not discussing Firebird... 1000+ page book on it and the
closest it comes to discussing a C-language API is to mention that the C
header file defining the functions is included with the binary
install... And for other APIs it just gives URLs}
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Dennis Lee Bieber]

Talking to myself...

Okay, I'm a bit behind the time... The book I keep on the floor next
to my computer is the old brown/black version covering version 3.x and
introducing ver 4.0 (my newer books are across the room in shelves
behind the couch that take time to reach)

I should also add that I'm still using a somewhat older MySQLdb (one
reason I haven't upgraded Python to 2.5 series -- finding trusted binary
builds of the third-party stuff is a pain...), perhaps MySQLdb /has/
been modified to support prepared statements?

--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[Gabriel Genellina]

En Sat, 20 Oct 2007 18:40:38 -0300, Dennis Lee Bieber

Okay, I'm a bit behind the time... The book I keep on the floor next
to my computer is the old brown/black version covering version 3.x and
introducing ver 4.0 (my newer books are across the room in shelves
behind the couch that take time to reach)

It can't be fully blamed on the MySQLdb adapter... After crawling
through too many reference books, it looks like MySQL itself didn't
support "prepared statements" until sometime in the ver 4.1.x period --
I believe the MySQLdb adapter is still ver 3.x compatible, using the
mysql_query(connection, SQL_string) call, rather than the half-dozen
newer functions for separately binding parameters to prepared
statements.

Yes, if the database doesn't allow for prepared statements the poor
adapter can't do much...

Since, to my experience, db-api 2 doesn't expose the concepts of
prepared statements to the user, about the only place using them would
offer a true speed-up would be in the .executemany() call (and even then
it would seem to depend upon which is more costly: formatting a properly
[...]

Some databases automatically cache SQL statements. I know DB2 does that,
and I think Oracle does too (or is it the JDBC layer?). So it's not
required that user code explicitely prepares SQL statements.
Prepared statements have other advantages, apart from the speed gain when
using repetitive queries: they help to keep the statement length below
some size limit, they help to lower the cpu load on the server (by not
having to parse and optimize the query again), and they avoid SQL
injection (else, you have to rely on the quoting and escaping being well
done, even on the DB-API adapter).

If the api exposed them to the user, I could see a potential use for
them: an application with, say, multiple forms (each of one or more DB
tables)... By preparing and saving a set of statements for each form
(parameterized select, update, insert, delete, say) during application
startup (or conditionally on first entry to a form) one would only need
to reference the prepared statements for the forms as the end-user
navigates the forms.

But DB-API 2.0 already allows that. PEP 249 says, when describing
cursor.execute:

"A reference to the operation will be retained by the
cursor. If the same operation object is passed in again,
then the cursor can optimize its behavior. This is most
effective for algorithms where the same operation is used,
but different parameters are bound to it (many times)."

So nothing special is required to enable prepared statements; the adapter
is free to prepare and re-use queries if desired.
BTW, the situation is similar to the re module: you can pre-compile your
regular expressions, but it's not required, as the re module caches
compiled expressions, reusing them later when the same r.e. is seen.

{At least we're not discussing Firebird... 1000+ page book on it and the
closest it comes to discussing a C-language API is to mention that the C
header file defining the functions is included with the binary
install... And for other APIs it just gives URLs}

Nice book!

 

[Dennis Lee Bieber]

But DB-API 2.0 already allows that. PEP 249 says, when describing
cursor.execute:

"A reference to the operation will be retained by the
cursor. If the same operation object is passed in again,
then the cursor can optimize its behavior. This is most
effective for algorithms where the same operation is used,
but different parameters are bound to it (many times)."

So nothing special is required to enable prepared statements; the adapter
is free to prepare and re-use queries if desired.

Okay... Guess I better get used to browsing source code of adapters
to see what they actually are doing <G>

MySQLdb is mostly in Python so browsing it is easy -- the "hidden"
DLL level is mainly (from what I've seen) the real communication channel
stuff.

But the reuse capability you quote leaves many interpretations too,
which may not be easily answered by the documentation of a given
adapter... "same operation object" I would take to be the SQL string
itself -- but "passed in again" could be taken to mean immediate reuse
of the same string on the cursor, and it is thrown out if a different
operation is performed on the same cursor; or that the cursor object can
create, say, an LRU style list (cache) of strings (or id() of them)
mapped against the prepared statement previously created... If the id()
of the string is used, either could fail if the queries are
algorthmically generated (I'm thinking something simplistic where the
query consists of a WHERE clause built based upon non-empty fields of a
search form).

At this point, I'm just picking nits...
--
Wulfraed Dennis Lee Bieber KD6MOG
(e-mail address removed) (e-mail address removed)
HTTP://wlfraed.home.netcom.com/
(Bestiaria Support Staff: (e-mail address removed))
HTTP://www.bestiaria.com/

 

[John Nagle]

I should also add that I'm still using a somewhat older MySQLdb (one
reason I haven't upgraded Python to 2.5 series -- finding trusted binary
builds of the third-party stuff is a pain...)

What, you don't trust the build from a World of Warcraft guild?

John Nagle