ProgrammingError: 1064 (42000): You have an error in your SQL syntax； check the manual...解决方案

  大家好，我是爱编程的喵喵。双985硕士毕业，现担任全栈工程师一职，热衷于将数据思维应用到工作与生活中。从事机器学习以及相关的前后端开发工作。曾在阿里云、科大讯飞、CCF等比赛获得多次Top名次。现为CSDN博客专家、人工智能领域优质创作者。

  本文主要介绍了ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual…解决方案，希望能对使用python将csv转换为MySQL的同学们有所帮助。

1. 问题描述

  今天在使用python将csv转换为MySQL时，具体来说是在创建数据表时，却出现了ProgrammingError: 1064 (42000): 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 '0的错误提示，具体如下图所示：

  在经过了亲身的实践后，终于找到了解决问题的方案，最终将逐步的操作过程总结如下。希望能对遇到同样bug的同学有所帮助。

2. 解决方案

  首先通过pip命令安装将csv转换为MySQL的Python库，具体命令和截图如下所示：

pip install mysql-connector-python

  当看到Sucessfully installed mysql-connector-python则说明安装成功了：

  然后先进行导包，命令如下所示：

import pandas as pd
import mysql.connector
from mysql.connector import Error

  然后连接数据库、创建数据库、创建数据库表的函数如下所示：

def create_connection_no_database(host, user, password):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password
        )
        print("MySQL connection successful")
    except Error as e:
        print(f"Error: {e}")

    return connection

def create_connection_with_database(host, user, password, database):
    connection = None
    try:
        connection = mysql.connector.connect(
            host=host,
            user=user,
            password=password,
            database=database
        )
        print("MySQL connection successful")
    except Error as e:
        print(f"Error: {e}")

    return connection

def close_connection(connection):
    if connection:
        connection.close()
        print("MySQL connection closed")

def create_database(connection, database_name):
    cursor = connection.cursor()
    create_database_query = f"CREATE DATABASE IF NOT EXISTS {database_name}"
    cursor.execute(create_database_query)
    print(f"Database '{database_name}' created successfully")

def create_table(connection, table_name, columns):
    cursor = connection.cursor()
    create_table_query = f"CREATE TABLE IF NOT EXISTS {table_name} ({', '.join(columns)})"
    cursor.execute(create_table_query)
    connection.commit()
    print(f"Table '{table_name}' created successfully")

def insert_data(connection, table_name, data):
    cursor = connection.cursor()
    for index, row in data.iterrows():
        row_values = tuple(row)
        placeholders = ", ".join(["%s"] * len(row_values))
        insert_query = f"INSERT INTO {table_name} VALUES ({placeholders})"
        cursor.execute(insert_query, row_values)
    connection.commit()
    print(f"Data inserted into table '{table_name}'")

  读取CSV数据，创建数据库和数据库表的代码具体如下所示：

csv_file = '/home/data/sample.csv'
df = pd.read_csv(csv_file)
# Set up MySQL connection
host = 'localhost'
user = 'root'
password = 'xxxxxx' # 请自行进行设置
connection_no_database = create_connection_no_database(host, user, password)

database_name = "sample_database" # 数据库名称

# Create database and insert data
if connection_no_database:
    create_database(connection_no_database, database_name)

connection_with_database = create_connection_with_database(host, user, password, database_name)

# Create table and insert data
if connection_with_database:
    table_name = 'sample_table' # 数据库表名
    database_schema = [i.replace(' ', '_') + ' VARCHAR(255)' for i in df.columns]
    create_table(connection_with_database, table_name, database_schema)
    insert_data(connection_with_database, table_name, df)
    close_connection(connection_with_database)

  执行代码成功的截图如下所示：

  之前报错的原因是database_schema设置不对，之前不仅没有设置每一个字段的数据格式，而且每个字段包含了空格，所以才会出错。使用database_schema = [i.replace(’ ‘, ‘_’) + ’ VARCHAR(255)’ for i in df.columns]，则解决了上述错误。