Python MySql connector raises SyntaxError when creating table

Question

Here is a function in my program

def OrderTable():
        c = mysql.connector.connect(user = 'root', password = 'pass', database = 'storeroomps')
        cur = c.cursor()
        tbl = '''CREATE TABLE IF NOT EXISTS ORDER(
        ORDER_NO CHAR(20) NOT NULL,
        ORDER_DATE CHAR(10),
        ORDER_STATUS CHAR(5),
        CUST_NO CHAR(20),
        ITEM_ID INT
        )'''
        cur.execute(tbl)
        c.close()

when I execute the function I get this error

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 'ORDER(
                ORDER_NO CHAR(20) NOT NULL,
                ORDER_DATE CHAR(10),
                ORDER_STATUS CHAR(' at line 1

I have another identical function which creates a manufacturer's table

def ManufTable():
        c = mysql.connector.connect(user = 'root', password = 'pass', database = 'storeroomps')
        cur = c.cursor()
        tbl = '''CREATE TABLE IF NOT EXISTS MANU(
        M_NAME CHAR(10) NOT NULL,
        M_CODE CHAR(20) NOT NULL,
        M_EMAIL CHAR(20),
        M_STATUS CHAR(5),
        ITEM_ID INT,
        ORDER_NO CHAR(20)
        )'''
        cur.execute(tbl)
        c.close()

this one works fine but the order one raises the syntax error? Where have I gone wrong?

Avoid MySQL keywords like ORDER for table or column identifiers. Also, avoid abbreviations like MANU for readability and maintenance. — Parfait
– Parfait, Commented Sep 7, 2021 at 0:24

duffymo · Accepted Answer · 2021-09-07 00:17:07Z

1

ORDER is a SQL keyword (e.g. ORDER BY).

You might try changing the name of your table. Change it to CUSTOMER_ORDER and see if it works.

answered Sep 7, 2021 at 0:17

duffymo

310k46 gold badges376 silver badges570 bronze badges

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

Collectives™ on Stack Overflow

Python MySql connector raises SyntaxError when creating table

1 Answer 1

Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Related