使用pymysql创建MySQL表报错，请求排查并提供修复方案

我编写了一段用pymysql连接MySQL并创建vehicle表的Python代码，运行后触发pymysql.err.ProgrammingError，提示SQL语法有误。

原代码 #

import pymysql

# 连接数据库
def the():
    return pymysql.connect(host='localhost',database='project1',
                           user='root',password='Chitransh@994',
                           port=3306)

# 创建表
def Table():
    conn = the()
    cursor = conn.cursor()
    query = "Create Table vehicle(modelno int primary key auto_increment, name VARCHAR(50)," \
            "owner name VARCHAR(50), MRP int)"
    cursor.execute(query)
    conn.commit()
    print("Table created")
    conn.close()
Table()

报错信息 #

File "C:\Users\MY DESK\PycharmProjects\pythonProject\main.py", line 29, in <module>
    Table()
  File "C:\Users\MY DESK\PycharmProjects\pythonProject\main.py", line 23, in Table
    cursor.execute(query)
  File "C:\Users\MY DESK\PycharmProjects\pythonProject\venv\Lib\site-packages\pymysql\cursors.py", line 148, in execute
    result = self._query(query)
             ^^^^^^^^^^^^^^^^^^
  File "C:\Users\MY DESK\PycharmProjects\pythonProject\venv\Lib\site-packages\pymysql\cursors.py", line 310, in _query
    conn.query(q)
  File "C:\Users\MY DESK\PycharmProjects\pythonProject\venv\Lib\site-packages\pymysql\connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
                          ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\MY DESK\PycharmProjects\pythonProject\venv\Lib\site-packages\pymysql\connections.py", line 775, in _read_query_result
    result.read()
  File "C:\Users\MY DESK\PycharmProjects\pythonProject\venv\Lib\site-packages\pymysql\connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "C:\Users\MY DESK\PycharmProjects\pythonProject\venv\Lib\site-packages\pymysql\connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "C:\Users\MY DESK\PycharmProjects\pythonProject\venv\Lib\site-packages\pymysql\protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "C:\Users\MY DESK\PycharmProjects\pythonProject\venv\Lib\site-packages\pymysql\err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.ProgrammingError: (1064, "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 'name VARCHAR(50), MRP int)' at line 1")

错误原因 #

SQL创建表的语句中，owner name VARCHAR(50)是错误写法——字段名被重复写了两次（owner和name），正确的字段定义格式应为字段名 数据类型，此处需改为owner VARCHAR(50)。

修复后的代码 #

import pymysql

# 连接数据库
def the():
    return pymysql.connect(host='localhost',database='project1',
                           user='root',password='Chitransh@994',
                           port=3306)

# 创建表
def Table():
    conn = the()
    cursor = conn.cursor()
    # 修正SQL语句中的字段定义错误
    query = "Create Table vehicle(modelno int primary key auto_increment, name VARCHAR(50)," \
            "owner VARCHAR(50), MRP int)"
    cursor.execute(query)
    conn.commit()
    print("Table created")
    conn.close()
Table()

内容的提问来源于stack exchange，提问作者Chitransh