Reputation: 995
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
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
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
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
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