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 ...
2 Answers 2
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.
1 Comment
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>'