CODEWITHSUNDEEP
pythonmysqlmysql-pythonmysql-connector-python
Armonia
Armonia

Reputation: 77

MySQL-Python Wont create the table

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?

Upvotes: 0

Views: 109

Answers (2)

Pablo LION
Pablo LION

Reputation: 1435

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.

Upvotes: 0

DSKalugin
DSKalugin

Reputation: 56

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

Upvotes: 2

Related Questions