MySQL-Python Wont create the table

Question

I am trying to create a few tables using Python, the first few worked and this specific one not. This is my code:

sql = "CREATE TABLE citizens(\
    ID INT PRIMARY KEY AUTO_INCREMENT,\
    full_name VARCHAR(100),\
    age INT,\
    Gender ENUM('F', 'M'),\
    cultivation VARCHAR(100),\
    rank INT,\
    isRouge ENUM('Yes', 'No'),\
    sect_id INT)"

mycursor.execute(sql)

and this is the error it gives me:

MySQLInterfaceError: 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 'rank INT,    isRouge ENUM('Yes', 'No'),    sect_id INT)' at line 1

what is it thaat I am missing?

Your error may be caused by using a MySQL reserved word as rank. — lemon
– lemon, Commented Aug 4, 2022 at 17:15

DSKalugin · Accepted Answer · 2022-08-04 17:22:34Z

2

"rank" bad field name. This is a reserved word https://dev.mysql.com/doc/refman/8.0/en/keywords.html

answered Aug 4, 2022 at 17:22

DSKalugin

561 bronze badge

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

1 Comment

Armonia Over a year ago

indeed it's working good now ! thanks

Pablo LION · Accepted Answer · 2022-08-06 11:53:40Z

Why this happens

Problem here is rank is a reserved keyword, as in @DSKalugin 's answer. Change it to other name like my_rank will resolve this.

An extra tip

In addition. using triple quoted strings is an easier way for multi-line string.

Python's triple quotes comes to the rescue by allowing strings to span multiple lines, including verbatim NEWLINEs, TABs, and any other special characters.

from https://www.geeksforgeeks.org/triple-quotes-in-python/

So a more readable way is like:

sql_request = """
    CREATE TABLE citizens(
        ID INT PRIMARY KEY AUTO_INCREMENT,
        full_name VARCHAR(100),
        age INT,
        Gender ENUM('F', 'M'),
        cultivation VARCHAR(100),
        my_rank INT,
        isRouge ENUM('Yes', 'No'),
        sect_id INT)
    """

TL;DR

When reviewing First Answers, Stack Overflow cannot accept my edit, saying "The edit queue is full at the moment - try again in a few minutes!"

I came here and saw the correct answer was already here. I post my edited version of @Sem Lion 's answer here anyway in case if it helps.

Collectives™ on Stack Overflow

MySQL-Python Wont create the table

2 Answers 2

1 Comment

Why this happens

An extra tip

TL;DR

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Why this happens

An extra tip

TL;DR

Comments

Related