data type mismatch: "a number is required, not str"

[Brian Jones]

Hi all,

So, again, I'm trying to do a simple execute(), and I'm running into
really unexpected errors. I have, of course, tried perhaps 5 different
ways of putting the query together by now, and the error has not
changed.

Here's what I *currently* have in place in my code:

sql = """INSERT INTO urls VALUES (%d, %d, %s, %s)"""
params = [int(self.user_id), int(self.site_id), self.url_type
, self.url]
self.cursor.execute(sql,params)

Of course, that's after trying simpler forms of the same thing.
Namely, when I started this was all one line of code, and I didn't do
explicit casts.

self.cursor.execute("""INSERT INTO urls VALUES (%d, %d, %s, %s)""" %
(self.user_id, self.site_id, self.url_type , self.url))

The error I'm getting is:
TypeError: %d format: a number is required, not str

Explicit casting to int does not make this go away. Also, I tested by
printing out "type(i) for i in params", and it prints out the types
that I expect things to be, and the types I think I'm passing to the
query, in the proper order.

Does anyone see anything obvious that I might have goofed up?
Thanks,
brian

[Brian Jones]

Just an added note. Just ran the query through mogrify() as well, and
it returns the same error, so this is a psycopg2 issue (or, my issue
with psycopg2), not a db-related thing.

Post by Brian Jones
Hi all,
So, again, I'm trying to do a simple execute(), and I'm running into
really unexpected errors. I have, of course, tried perhaps 5 different
ways of putting the query together by now, and the error has not
changed.
       sql = """INSERT INTO urls VALUES (%d, %d, %s, %s)"""
       params = [int(self.user_id), int(self.site_id), self.url_type
, self.url]
       self.cursor.execute(sql,params)
Of course, that's after trying simpler forms of the same thing.
Namely, when I started this was all one line of code, and I didn't do
explicit casts.
self.cursor.execute("""INSERT INTO urls VALUES (%d, %d, %s, %s)""" %
(self.user_id, self.site_id, self.url_type , self.url))
TypeError: %d format: a number is required, not str
Explicit casting to int does not make this go away. Also, I tested by
printing out "type(i) for i in params", and it prints out the types
that I expect things to be, and the types I think I'm passing to the
query, in the proper order.
Does anyone see anything obvious that I might have goofed up?
Thanks,
brian
--
Brian K. Jones
Python Magazine  http://www.pythonmagazine.com
My Blog          http://www.protocolostomy.com

[Federico Di Gregorio]

Post by Brian Jones
sql = """INSERT INTO urls VALUES (%d, %d, %s, %s)"""

Don't use %d. The %s is a placeholder (think "?") and is not used to
determine the output format, so just avoid anything except %s (no %d, no
%f, etc.)

federico

[Karsten Hilbert]

Post by Brian Jones
So, again, I'm trying to do a simple execute(), and I'm running into
really unexpected errors. I have, of course, tried perhaps 5 different
ways of putting the query together by now, and the error has not
changed.
sql = """INSERT INTO urls VALUES (%d, %d, %s, %s)"""

You must use

INSERT INTO urls VALUES (%s, %s, %s, %s)

The %s are NOT Python string substitution formatters despite
that they look exactly like them.

Karsten

[Adrian Klaver]

Post by Brian Jones
Hi all,
So, again, I'm trying to do a simple execute(), and I'm running into
really unexpected errors. I have, of course, tried perhaps 5 different
ways of putting the query together by now, and the error has not
changed.
sql = """INSERT INTO urls VALUES (%d, %d, %s, %s)"""
params = [int(self.user_id), int(self.site_id), self.url_type
, self.url]
self.cursor.execute(sql,params)
Of course, that's after trying simpler forms of the same thing.
Namely, when I started this was all one line of code, and I didn't do
explicit casts.
self.cursor.execute("""INSERT INTO urls VALUES (%d, %d, %s, %s)""" %
(self.user_id, self.site_id, self.url_type , self.url))
TypeError: %d format: a number is required, not str
Explicit casting to int does not make this go away. Also, I tested by
printing out "type(i) for i in params", and it prints out the types
that I expect things to be, and the types I think I'm passing to the
query, in the proper order.
Does anyone see anything obvious that I might have goofed up?
Thanks,
brian

Yes, use only %s. From the docs(extensions.rst):
- The variables placeholder must always be a ``%s``, even if a different
placeholder (such as a ``%d`` for an integer) may look more appropriate