psycopg2 TypeError: not all arguments converted during string formatting [duplicate]
Asked Answered
N

7

76

I'm trying execute a simple query, but getting this error no matter how I pass the parameters.

Here is the query (I'm using Trac db object to connect to a DB):

cursor.execute("""SELECT name FROM "%s".customer WHERE firm_id='%s'""" % (schema, each['id']))

schema and each['id'] both are simple strings

print("""SELECT name FROM "%s".customer WHERE firm_id='%s'""" % (schema, each['id']))

Result: SELECT name FROM "Planing".customer WHERE firm_id='135'

There is on error is a remove quote after firm_id=, but that way parameter is treated a an integer and ::text leads to the very same error.

Notability answered 3/2, 2014 at 10:12 Comment(2)
Could you add the ouput of the print("""SELECT name FROM "%s".customer WHERE firm_id='%s'""" % (schema, each['id']))?Jehiel
I've updated my question with the output resultNotability
M
11

It is recommended to not use string interpolation for passing variables in database queries, but using string interpolation to set the table name is fine as long as it's not an external input or you restrict the allowed value. Try:

cursor.execute("""
    SELECT name FROM %s.customer WHERE firm_id=%%s
    """ % schema, (each['id'],))

Rules for DB API usage provides guidance for programming against the database.

Madox answered 3/2, 2014 at 17:49 Comment(1)
from the docs: Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint. And they even specifically say that you should never use '%' for any query that has placeholders.Howbeit
C
193

In my case I didn't realize that you had to pass a tuple to cursor.execute. I had this:

cursor.execute(query, (id))

But I needed to pass a tuple instead

cursor.execute(query, (id,))
Corneliacornelian answered 23/12, 2018 at 14:12 Comment(8)
This is the stupidest thing in the world. Of all the modules I've ever used in any programming language ever, this has got to be one of the most bush league gotchas I've seen. They couldn't make this work without having to randomly insert a comma (convert to tuple)?Zoologist
BTW, even when my input was a Tuple, I still had to convert it to a tuple (so a tuple of tuples). Agree with @NateL - this sucks assPer
Python at its best! Hate this snake.Rattlebox
ohhh How I've missed this! nice gotchaHeighttopaper
@NateL parentheses can mean different things depending on context. They become a tuple if they contain a comma-separated list of items, otherwise if they contain just one thing, it becomes a single expression. So if you want a tuple of one item, the comma tells Python to consider it a tuple instead of an expression.Decoction
Add me in as one of the people that racked my head for hours, tried all kinds of things, then just happened to see this. THANK YOU. Agree with the frustration of others :(Matins
@NateL it's worth noting the core sqlite3 does the same thing. It's pretty wild that a list isn't used instead of a tuple.Harter
@NateL and anyone interested. It's possible to use an array or dict in psycopg2 - psycopg.org/psycopg3/docs/basic/params.htmlHarter
C
35

I got this same error and couldn't for the life of me work out how to fix, in the end it was my mistake because I didn't have enough parameters matching the number of elements in the tuple:

con.execute("INSERT INTO table VALUES (%s,%s,%s,%s,%s)",(1,2,3,4,5,6))

Note that I have 5 elements in the values to be inserted into the table, but 6 in the tuple.

Caucasia answered 15/5, 2018 at 23:31 Comment(4)
Thank you! This observation helped me check and find that I'd done something similarly silly! :)Liberalize
Thanks! I made the same mistake and only checked after reading your answerBirgit
Thanks. Not obvious to interpret the error message that way!Subfusc
If using to_records function, be sure to use the argument df.to_records(index=False)Judgeship
M
11

It is recommended to not use string interpolation for passing variables in database queries, but using string interpolation to set the table name is fine as long as it's not an external input or you restrict the allowed value. Try:

cursor.execute("""
    SELECT name FROM %s.customer WHERE firm_id=%%s
    """ % schema, (each['id'],))

Rules for DB API usage provides guidance for programming against the database.

Madox answered 3/2, 2014 at 17:49 Comment(1)
from the docs: Warning Never, never, NEVER use Python string concatenation (+) or string parameters interpolation (%) to pass variables to a SQL query string. Not even at gunpoint. And they even specifically say that you should never use '%' for any query that has placeholders.Howbeit
F
9

Use AsIs

from psycopg2.extensions import AsIs

cursor.execute("""
    select name 
    from %s.customer 
    where firm_id = %s
    """, 
    (AsIs(schema), each['id'])
)
Flange answered 3/2, 2014 at 23:4 Comment(0)
M
7

You could try this:

cursor.execute("INSERT INTO table_name (key) VALUES(%s)",(value1,))

You will get an error if you are missing a (,) after value1.

Mazurek answered 6/9, 2021 at 13:0 Comment(1)
This was already mentioned in this answer...Frock
K
5

The correct way to pass variables in a SQL command is using the second argument of the execute() method. And i think you should remove single quotes from second parameter, read about it here - http://initd.org/psycopg/docs/usage.html#the-problem-with-the-query-parameters.

Note that you cant pass table name as parameter to execute and it considered as bad practice but there is some workarounds:
Passing table name as a parameter in psycopg2
psycopg2 cursor.execute() with SQL query parameter causes syntax error

To pass table name try this:

cursor.execute("""SELECT name FROM "%s".customer WHERE firm_id=%s""" % (schema, '%s'), (each['id'],))
Krusche answered 3/2, 2014 at 10:17 Comment(3)
Yeah, that's what I found out while searching, but this way I get the following error: LINE 1: SELECT name FROM "'Planing'".customer WHERE firm_id='135' -- is there a way to remove single quote marks near Planing?Notability
How is it different from my print("""SELECT name FROM "%s".customer WHERE firm_id='%s'""" % (schema, each['id']))? This will work, but isn't it the incorrect way as you said earlier?Notability
Thanks, I've read through the link before. The main problem for me here is that schema name might change so hardcoding it is not a best option either, and the same time the correct way ensures that the strings I pass as a table values will be escaped. I guess I'll have to stick to hardcoding it for now.Notability
S
2

Every time I have this kind of error, I am passing the wrong amount of values. Try check it

Suttle answered 12/2, 2022 at 15:55 Comment(1)
This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From ReviewCrofton

© 2022 - 2024 — McMap. All rights reserved.