CODEWITHSUNDEEP
pythonmysqldatabase
navinpai
navinpai

Reputation: 995

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

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

Upvotes: 1

Views: 3128

Answers (4)

Dave Challis
Dave Challis

Reputation: 3906

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)

Upvotes: 0

William Jackson
William Jackson

Reputation: 1165

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)

Upvotes: 2

Endophage
Endophage

Reputation: 21483

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.

Upvotes: 1

user2311904
user2311904

Reputation: 1

Try surrounding your values in single quotes in the query.

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

Upvotes: -1

Related Questions