how to resolve python error "not all arguments converted while string formatting"

Question

When i execute below program

import MySQLdb
cn = MySQLdb.connect(host="localhost", user="root", passwd="mysqlroot", 
db="sv_data")
cursor = cn.cursor()
cursor.execute("select addressline1, zipcode from sv_address where zipcode = '10011'")
for (addressline1, zipcode) in cursor:
print(addressline1, zipcode)
cursor.close()
cn.close()

it works fine. However when i try to add a parameter in query, like below

import MySQLdb
 cn = MySQLdb.connect(host="localhost", user="root", passwd="****",     
db="sv_data")
cursor = cn.cursor()

a="10011"
cursor.execute("select addressline1, zipcode from sv_address where zipcode = 
%s", (a))
for (addressline1, zipcode) in cursor:
 print(addressline1, zipcode)

cursor.close()
cn.close()

getting error ProgrammingError: not all arguments converted during string formatting

can you please advise how to fix this. I tried various options. zipcode is varchar field in mysqldb

did you try - select addressline1, zipcode from sv_address where zipcode = '%s' as query ? — Anand S Kumar
– Anand S Kumar, Commented Aug 12, 2017 at 6:00
in <module> cursor.execute("select addressline1, zipcode from sv_address where zipcode = '%s'", (a)) File "C:\ProgramData\Anaconda3\lib\site-packages\MySQLdb\cursors.py", line 240, in execute self.errorhandler(self, ProgrammingError, str(m)) File "C:\ProgramData\Anaconda3\lib\site-packages\MySQLdb\connections.py", line 52, in defaulterrorhandler raise errorclass(errorvalue) ProgrammingError: not all arguments converted during string formatting — Adisheshaiah
– Adisheshaiah, Commented Aug 12, 2017 at 6:14

Vishnu Upadhyay · Accepted Answer · 2017-08-12 06:13:33Z

2

You are not passing the argument. you are rather giving it a string.

Try this.

cursor.execute("select addressline1, zipcode from sv_address where zipcode = %s" % a )

The best way to do is to separate your arguments and sql_query

_sql = "select addressline1, zipcode from sv_address where zipcode = {0}"
cursor.execute(_sql.format(a))

edited Aug 12, 2017 at 6:13

answered Aug 12, 2017 at 6:02

Vishnu Upadhyay

5,0611 gold badge17 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

9 Comments

Cory Madden Over a year ago

The docs show that this is valid: dev.mysql.com/doc/connector-python/en/…

Adisheshaiah Over a year ago

Thanks. This works. However having a % is security issue. can you please help with passing as argument also

cs95 Over a year ago

This shouldn't be an accepted answer. This is horribly unsafe.

Cory Madden Over a year ago

@Adisheshaiah what if you use a dict argument like shown in the docs? Does it say the same thing?

Adisheshaiah Over a year ago

I am using the second part. by separating arguments and query, and that works fine

|

Cory Madden · Accepted Answer · 2017-08-12 06:28:45Z

1

I believe the issue is that cursor is reading (a) as just a string and sees multiple values in it that aren't being converted(1, 0, 0, 1, 1). So if you add a comma like so:

cursor.execute("select addressline1, zipcode from sv_address where zipcode = %s", (a,))

I believe that will work properly.

For instance, look at this function and the outcome:

In [28]: def t(arg):
    ...:     print(type(arg))
    ...:     

In [29]: t(('a'))
<class 'str'>

The argument isn't being read as a tuple, it's being read as a string.

answered Aug 12, 2017 at 6:28

Cory Madden

5,23327 silver badges37 bronze badges

Collectives™ on Stack Overflow

how to resolve python error "not all arguments converted while string formatting"

2 Answers 2

9 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

9 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related