TypeError: not all arguments converted during string formatting when inserting with parameters

Question

I'm trying to insert an interger, a string and a list as a single record into a database, but am striking an error.

Here is my code:

values=nprnd.randint(10, size=48) #NUMPY ARRAY
valuelist= map(None, values); #Convert to list
guid=''.join(random.choice(string.ascii_uppercase + string.digits) for x in range(20)) #Generate guid
for x in range(4):
    var_string = ', '.join('?' * 48)
    valuelist.insert(0,x)
    valuelist.insert(0,ent_guid)
    #50 coloums in table, guid, x and 48 randomly generated values
    query_string = 'INSERT INTO schema1 VALUES (%s,%d,%s);' % (guid, x, var_string) 
    cursor.execute(query_string, valuelist)

I keep getting an error saying:

Traceback (most recent call last):
  File "script.py", line 19, in <module>
    cursor.execute(query_string, valuelist)
  File "/Library/Python/2.7/site-packages/MySQL_python-1.2.4b4-py2.7-macosx-10.8-intel.egg/MySQLdb/cursors.py", line 184, in execute
    query = query % db.literal(args)

I know the cause of this error (Even SO has a few questions on the same error), but none of the solutions I've tried have solved this error for me

Any help would be appreciated

if you need to generate a guid, you should really use the uuid module... it's there for that purpose. I suggest from uuid import uuid4; guid = uuid4().hex — Endophage
– Endophage, Commented Apr 23, 2013 at 15:10
@navinpal indeed it is, but see my answer for the possible solution :-) — Endophage
– Endophage, Commented Apr 23, 2013 at 15:14

Community · Accepted Answer · 2017-05-23 10:29:13Z

2

I believe MySQL-python uses printf format codes ('%s' for example) instead of '?' for placeholders. Try setting var_string like this:

    var_string = ', '.join(['%s'] * 48)

I also agree with Endophage that valuelist appears to be too long. I don't think you need to insert al and ent_guid.

You also need to put quotes around the guid when you insert it:

    query_string = 'INSERT INTO schema1 VALUES (\'%s\',%d,%s);' % (guid, x, var_string)

edited May 23, 2017 at 10:29

CommunityBot

11 silver badge

answered Apr 23, 2013 at 15:33

William Jackson

1,17512 silver badges25 bronze badges

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

5 Comments

navinpai Over a year ago

Tried this... it gives me: TypeError: %d format: a number is required, not str

William Jackson Over a year ago

Did you try it without the valuelist.insert() lines?

William Jackson Over a year ago

Can you try it with %s instead of %d?

navinpai Over a year ago

This gives me a different error: _mysql_exceptions.OperationalError: (1054, "Unknown column 'EU4YMTVOLT5LLJ5IZ8UQ' in 'field list'") ..., where EU4YMTVOLT5LLJ5IZ8UQ is the generated guid

William Jackson Over a year ago

Updated. Try quoting the guid when it is inserted.

Endophage · Accepted Answer · 2013-04-23 15:46:53Z

1

After you generate your 48 '?'s you insert 2 more elements into valuelist, have you taken those into account? The following code will be more robust:

values=nprnd.randint(10, size=48) #NUMPY ARRAY
valuelist= map(None, values); #Convert to list
guid=''.join(random.choice(string.ascii_uppercase + string.digits) for x in range(20)) #Generate guid
for x in range(4):
    valuelist.insert(0,al)
    valuelist.insert(0,ent_guid)

    # moved this line and using len(valuelist)
    var_string = ', '.join('?' * len(valuelist)) 

    #50 coloums in table, guid, x and 48 randomly generated values
    query_string = 'INSERT INTO schema1 VALUES (%s,%d,%s);' % (guid, x, var_string) 
    cursor.execute(query_string, valuelist)

Update:

From you comment below, it sounds like you're trying to double insert the guid and x values, therefore, change the query_string assignment (with the other changes I also made above) to:

query_string = 'INSERT INTO schema1 VALUES (%s);' % (var_string)

This is safer than your current string interpolation as cursor.execute will ensure the values are appropriately escaped.

edited Apr 23, 2013 at 15:46

answered Apr 23, 2013 at 15:13

Endophage

21.6k13 gold badges62 silver badges93 bronze badges

4 Comments

navinpai Over a year ago

Tried that as well.... It give me the same error, hence tried assigning the guid and x seperately. In this case, it generates (guid,x,?[50]), but we need (guid,x,?[48]).... The whole table has 50 coloums, one guid (string), one x (int) and 48 other coloums (again, all ints).

navinpai Over a year ago

This gives TypeError: not enough arguments for format string ... as we're passing only var_string but need to fill in the spaces for %s,%d,%s

Endophage Over a year ago

@navinpai sorry, wasn't paying enough attention, it's now as I intended

navinpai Over a year ago

I'm getting a: _mysql_exceptions.OperationalError: (1136, "Column count doesn't match value count at row 1")

Dave Challis · Accepted Answer · 2013-04-23 16:44:54Z

0

It looks like you're increasing the size of valuelist by 2 each time you go through the loop.

valuelist.insert adds a new item to the start of an existing list, so first time through the loop, valuelist contains 50 elements, then 52 the next, 54 the next, etc.

query_string only contains placeholders for 48 items though, so this will cause an error.

If you remove those lines, and change the '?' to '%s', you should have better results, e.g.:

for x in range(4):
    var_string = ', '.join('%s' * 48) 
    query_string = "INSERT INTO schema1 VALUES ('%s',%d,%s);" % (guid, x, var_string) 
    cursor.execute(query_string, valuelist)

answered Apr 23, 2013 at 16:44

Dave Challis

3,9264 gold badges43 silver badges69 bronze badges

Comments

user2311904 · Accepted Answer · 2013-04-23 15:04:18Z

-1

Try surrounding your values in single quotes in the query.

query_string = "INSERT INTO schema1 VALUES ('%s','%d','%s');" % (guid, x, var_string)

answered Apr 23, 2013 at 15:04

user2311904

1

Collectives™ on Stack Overflow

TypeError: not all arguments converted during string formatting when inserting with parameters

4 Answers 4

5 Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

5 Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related