大家好,我是爱编程的喵喵。双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],则解决了上述错误。