python导入数据集语法_使用python将值导入MySQL数据库时出现SQL语法错误

python和MySQL的新手,正在通过互联网和书籍自学.我正在努力将值导入数据库表中.我不断收到错误消息,指出SQL语法不正确.在Windows 8计算机上使用Python 2.7.9和MySQL Connector 5.6.

这是我收到的错误；

Traceback (most recent call last):

File “C:\Users\Oliver\Desktop\test.py”, line 43, in

cursor.execute(newitem, newdata) # executes SQL statement for adding new item

File “C:\Python27\lib\site-packages\mysql\connector\cursor.py”, line 507, >in execute

self._handle_result(self._connection.cmd_query(stmt))

File “C:\Python27\lib\site-packages\mysql\connector\connection.py”, line >722, in cmd_query

result = self._handle_result(self._send_cmd(ServerCmd.QUERY, query))

File “C:\Python27\lib\site-packages\mysql\connector\connection.py”, line >640, in _handle_result

raise errors.get_exception(packet)

ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check >the manual that corresponds to your MySQL server version for the right syntax to use near ‘desc) VALUES (‘5012583200796′,’test’)’ at line 1

这是我编写的代码；

# Add Stock bare bones

# Program that will add stock to the database

import mysql.connector

print "\t\tWelcome to the stock adding page\n"

selection = raw_input("Did you want to add stock or a new item? (Y,N)").lower()

while selection == "y":

bcode = " " # Generate barcode variable

while bcode == " ": # while barcode variable is empty

bcode = raw_input("Please scan an object ") # ask for user to input barcode

print "The barcode you scanned is", bcode

cnx = mysql.connector.connect(user='root', password='Blackops123!', # variable to connect to foody friend database

host='127.0.0.1',

database='food_friend')

cursor = cnx.cursor() # cursor to make changes to database

query = ("SELECT barcode FROM stock_list " # query stock_list database tabelf ro barcode scanned in by user

"WHERE barcode = %s")

cursor.execute(query, (bcode,))# execute the query within the database

result = cursor.fetchone() # fetch result from the query and assign variable

if result == None: # If the barcode does not exist add a new item

print "New item to database!\n"

description = raw_input("Please describe product (45 character limit)") # user input for object description, limited to 45 chars as per database

newitem = ("INSERT INTO stock_list " # SQL statement to add new item into the database with description

"(barcode, desc) "

"VALUES (%s,%s)")

newdata = (bcode, description)

cursor.execute(newitem, newdata) # executes SQL statement for adding new item

cnx.commit() # accept addition of the new item

print "Please scan item again as new item to add stock level" # as no stock level is added the user has to run around again. Will fix eventually

selection = raw_input("Did you want to add stock or a new item? (Y,N)").lower()

print "returning to main menu"

end = raw_input("click something to end")

我已经尝试过按照MySQL文档中的建议在元组和字典中设置值条目,但是由于错误中显示的值被拾取,因此将其设置为原来的样子.我已经尝试了带单括号和无单括号的％s,但无济于事.

谁能建议我可能出了什么问题？

解决方法:

desc是SQL中的保留字(用于在order by子句中定义降序).如果要将其用作列名,则应使用`s对其进行转义：

newitem = ("INSERT INTO stock_list " # SQL statement to add new item into the database with description

"(barcode, `desc`) "

"VALUES (%s,%s)")

newdata = (bcode, description)

编辑：

或者更好的是,如评论中的讨论所建议的那样-只需将有问题的列重命名为非保留字,例如描述.

标签：import,sql-insert,python,sql,mysql