Python操作MySQL创建表报1064(42000)语法错误如何解决？

问题原因 #

你遇到的1064报错是MySQL标准语法错误，本次报错的核心原因是CREATE TABLE语句的括号未闭合：你定义字段的左括号没有对应的右括号收尾，MySQL解析到语句末尾都找不到闭合标识，因此抛出语法错误。

修复方案 #

• 核心修复：在SQL语句末尾补全右括号)，即可解决语法报错
• 可选优化：新增IF NOT EXISTS判断，避免表已存在时重复创建触发报错；同时调整变量名和异常提示的语义，和建表操作匹配

修正后完整代码 #

import mysql.connector
from mysql.connector import Error
try:
     connection = mysql.connector.connect(host='localhost',
                                          database='project2',
                                          user='root',
                                          password='123')
     # 补全括号，新增IF NOT EXISTS判断
     create_table_sql = "CREATE TABLE IF NOT EXISTS customer (custID int,custName VARCHAR(25), zip int, city VARCHAR(25), state VARCHAR(25))"
     cursor = connection.cursor()
     cursor.execute(create_table_sql)
     print("customer Table is Created in the Database")
except mysql.connector.Error as e:
     print("Error creating MySQL table", e)
finally:
     if connection.is_connected():
          cursor.close()
          connection.close()
          print("MySQL connection is closed")

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