Python insert into SQL unicode error

Question

I have the following error :

    db = MySQLdb.connect(host="localhost", # host
                 user="root", # username
                  passwd="", # password
                  db="test",charset='utf8') #

    cur = db.cursor() 
    x = "испытание" # random unicode characters
    sql = "INSERT INTO links(test) VALUES(N'%s');"
    lst = ( x ) #x is unicode data
    cur.execute(sql,lst)

The error I get is : MySQL Error [1064]: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax ...

bobince · Accepted Answer · 2015-01-23 15:13:45Z

1

x = "испытание" # random unicode characters

(What Python version are you using? If 2.x, those are not Unicode characters, they're bytes.)

sql = "INSERT INTO links(test) VALUES(N'%s');"

When you use parameterised queries, you don't include the string literal delimiters ''. For MySQLdb where the parameter marker is %s, it should just be:

sql = "INSERT INTO links(test) VALUES(%s);"

(Note also NVARCHAR is unnecessary in MySQL.)

lst = ( x ) #x is unicode data

Here lst is the same value as x, you haven't got a tuple. If you really want a tuple-of-one then say (x,), but probably using an actual list [x] is clearer.

answered Jan 23, 2015 at 15:13

bobince

538k111 gold badges675 silver badges846 bronze badges

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

1 Comment

bobince Over a year ago

You've only mentioned a Syntax Error, what is the Unicode Error? If MySQL complains about malformed UTF-8 and this is Python 2, then probably your source file isn't saved as UTF-8 so the byte string passed to it can't be read.

Sectona · Accepted Answer · 2015-01-22 22:52:50Z

0

what value are you trying to insert? nothing. what does N'%s' represents. I guess you are trying to insert from a form so I have work on this code, tested it and is running. First create database with 2 column employee_id and lastname. Then test this code. it will work for you. If you get it right please tick this as answer. Sectona...

#!c:/python25/python

import MySQLdb

# Import modules for CGI handling 
import cgi, cgitb 

# Create instance of FieldStorage 
form = cgi.FieldStorage() 

# get form data from form field

employee_id = form.getvalue('employee_id')
lastname  = form.getvalue('lastname')



# Open database connection
db = MySQLdb.connect("localhost","sectona","sectona90","sectona_db" )

# prepare a cursor object using cursor() method
cursor = db.cursor()


sql = "INSERT INTO EMPLOYEE(employee_id, \
       lastname) \
       VALUES ('%s', '%s')" % \
       (employee_id, lastname)

try:
   # Execute the SQL command
   cursor.execute(sql)
   # Commit your changes in the database
   db.commit()
except:
   # Rollback in case there is any error
   db.rollback()

# disconnect from server
db.close()
print "Content-type:text/html\r\n\r\n"
print '<html>'
print '<body>'
print '<h2>Data Submitted Successfully</h2>'
print "<b>Employee Identity:</b> %s<br>" % (employee_id)
print "<b>Last Name:</b> %s<br>" % (lastname)

print '</body>'
print '</html>'

answered Jan 22, 2015 at 22:52

Sectona

981 silver badge9 bronze badges

1 Comment

bobince Over a year ago

Note this code suffers from SQL-injection. Prefer to use parameterised queries. Also HTML-injection (XSS).

Collectives™ on Stack Overflow

Python insert into SQL unicode error

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest