SQL Syntax error while using python to import values into MySQL Database

Question

New to python and MySQL and am teaching myself from internet and books. I am struggling to import values into a database table. I keep getting an error stating that the SQL syntax is incorrect. Using Python 2.7.9 and MySQL Connector 5.6 on a Window 8 machine.

Here is the error that I receive;

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

Here is the code that I have written;

# 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")

I have tried both setting the value entries in a tuple and in a dictionary as suggested in the MySQL documentation, but have left it how it is as the values are picked up as shown in the error. I have tried the %s with and without single brackets to no avail.

Can anyone suggest where I may have been going wrong?

Mureinik · Accepted Answer · 2015-06-02 08:32:17Z

3

desc is a reserved word in SQL (it's used to define descending order in an order by clause). If you want to use it as a column name, you should escape it by using `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)

EDIT:
Or, better yet, like the discussion in the comments suggests - just rename the offending column to something that isn't a reserved word, such as description.

edited Jun 2, 2015 at 8:32

answered Jun 1, 2015 at 19:14

Mureinik

316k54 gold badges399 silver badges405 bronze badges

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

3 Comments

Digital Chris Over a year ago

But really you don't want to use it, or any other mysql reserved words as column names. Save yourself the headache.

Mureinik Over a year ago

@DigitalChris Avoiding such names altogether would be preferable, agreed. Since OP only provided the insert part, I was assuming that the table's structure was out of his control at this point.

osmith Over a year ago

It was as simple as that! Thanks for the help, I have just changed the column name to description rather than being lazy and it worked fine.

Collectives™ on Stack Overflow

SQL Syntax error while using python to import values into MySQL Database

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related