python mysql Incorrect string value warning

Question

I am trying to enter values into a mysql db using python. To this end, I have something like:

import MySQLdb
variables=[]*10 #list of variables.
n=len(variables)

#create python-mysql cursor connection
..
#load values into mysql:
for i in range(0,n):
    cursor.executemany(sql, variables[i])

This shoots out some errors in rows (approx 1% of the rows):

Warning: Incorrect string value: '\xD0\xA2\xD0\xB5\xD0\xBA...' for column 'Link' at row 395
cursor.executemany(sql, variables[i])
mergeDB.py:85: Warning: Incorrect string value: '\xD0\x91\xD0\xB8\xD1\x82...' for column 'Link' at row 431
cursor.executemany(sql, variables[i])
mergeDB.py:85: Warning: Incorrect string value: '\xD0\x91\xD0\xB8\xD1\x82...' for column 'Link' at row 568
cursor.executemany(sql, variables[i])
mergeDB.py:85: Warning: Incorrect string value: '\xD0\xA7\xD0\xB5\xD0\xBB...' for column 'Link' at row 665
cursor.executemany(sql, variables[i])
mergeDB.py:85: Warning: Incorrect string value: '\xD0\x96\xD0\xB5\xD0\xBB...' for column 'Link' at row 827
cursor.executemany(sql, variables[i])
mergeDB.py:85: Warning: Incorrect string value: '\xD0\x9B\xD1\x8E\xD0\xB1...' for column 'Link' at row 1242
cursor.executemany(sql, variables[i])
mergeDB.py:85: Warning: Incorrect string value: '\xD0\xA2\xD0\xB0\xD0\xB9...' for column 'Link' at row 1310
cursor.executemany(sql, variables[i])
mergeDB.py:85: Warning: Incorrect string value: '\xD0\x9B\xD1\x8E\xD0\xB1...' for column 'Link' at row 1468
cursor.executemany(sql, variables[i])
mergeDB.py:85: Warning: Incorrect string value: '\xD0\x94\xD0\xBD\xD0\xB5...' for column 'Link' at row 1687
cursor.executemany(sql, variables[i])
mergeDB.py:85: Warning: Incorrect string value: '\xD0\x91\xD0\xB5\xD1\x81...' for column 'Link' at row 1848
cursor.executemany(sql, variables[i])

From previous SOF threads, I have tried using inside my python file:

ALTER TABLE dbx.tablex MODIFY Link VARCHAR(255) CHARACTER SET utf8; COLLATE utf8_general_ci NOT NULL;

but this gives me an error:

ALTER TABLE dbx.tablex MODIFY Link VARCHAR(255) CHARACTER SET utf8; COLLATE utf8_general_ci NOT NULL;
          ^
SyntaxError: invalid syntax

How does one solve this problem? How can I simply delete (tell python not to load the rows) the rows which have the "Incorrect String Value?" within this loop:

for i in range(0,n):
    cursor.executemany(sql, variables[i])

Rob Wouters · Accepted Answer · 2012-01-28 18:20:00Z

4

ALTER TABLE dbx.tablex MODIFY Link VARCHAR(255) CHARACTER SET utf8; COLLATE utf8_general_ci NOT NULL;

This is a MySQL query, not a Python statement. You need to pass the above to MySQL.

Also, [] * 10 does not do what you think it does. And the proper way to iterate over a list of variables is:

for variable in variables:
    cursor.execute(sql, variable)

But you can do this directly:

cursor.executemany(sql, variables)

answered Jan 28, 2012 at 18:20

Rob Wouters

16.4k3 gold badges44 silver badges36 bronze badges

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

1 Comment

JohnJ Over a year ago

Rob, OK. but when I execute the mySQL query (in mySQL), the problem with Incorrect String Value still remains. []*10 was just a quick one I write for the post.

Collectives™ on Stack Overflow

python mysql Incorrect string value warning

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related