执行在SQL＆＃x27;从sqlite_master的选择名称中失败，其中type =＆＃x27; table＆＃x27; and name =？;＆＃x27; ::

Question

我如何使用mysql.connector轻松地将PANDAS DataFrame写入MySQL数据库？

import mysql.connector as sql
import pandas as pd


db_connection = sql.connect(host='124685.eu-central-1.rds.amazonaws.com', 
        database="db_name", user='user', password='pw')
query = 'SELECT * FROM table_name'
df = pd.read_sql(sql=query, con=db_connection)

df["Person_Name"] = "xx"

df.to_sql(con=db_connection, name='table_name', if_exists='replace')

尝试了此操作，但它给了我一个错误：

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Not all parameters were used in the SQL statement

mysql.connector没有df.to_sql函数吗？

这些是Col名称：

Col names Index(['Person_ID', 'AirTable_ID_Person', 'Person_Name', 'Gender', 'Ethnicity',
       'LinkedIn_Link_to_the_Profile_of_Person', 'Jensen_Analyst',
       'Data_Source', 'Created_Time', 'Last_Modified_Time', 'Last refresh',
       'createdTime', 'Gender_ID', 'Ethnicity_ID', 'Jensen_Analyst_ID',
       'Data_Source_ID', 'Position_ID', 'Egnyte_File', 'Comment', 'Move',
       'Right_Move', 'Bio-Import-Assistant', 'Diversity'],
      dtype='object')

Answer 1

Pandas需要SQLalchemy引擎才能将数据写入SQL。您可以采用以下两种方法，第一种方法是使用连接器执行的连接器执行，第二种方法使用pandas.to_sql语句使用引擎。

它的工作原理与您的熊猫读取功能非常相似。

import pandas as pd
import mysql.connector as sql
db_connection = sql.connect(host='124685.eu-central-1.rds.amazonaws.com', 
        database="db_name", user='user', password='pw')

query = 'SELECT * FROM table_name'
df = pd.read_sql(sql=query, con=db_connection)
df["Person_Name"] = "xx"
df_temp = df[['Person_Name', 'Person_ID']]

query_insert = 'insert into table_name(Person_Name) values %s where Person_ID = %s'
pars = df_temp.values.tolist()
pars = list(map(tuple, pars))
cursor = db_connection.cursor()
cursor.executemany(query, pars)
cursor.commit()
cursor.close()

或者，您可以建立用于上传的引擎。

import pandas as pd
from sqlalchemy import create_engine
import mysql.connector as sql

# engine = create_engine('mysql+pymysql://username:password@host/database')
# or in your case-
engine = create_engine('mysql+pymysql://user:[email protected]/db_name')

db_connection = sql.connect(host='124685.eu-central-1.rds.amazonaws.com', 
        database="db_name", user='user', password='pw')

query = 'SELECT * FROM table_name'
df = pd.read_sql(sql=query, con=db_connection)
df["Person_Name"] = "xx"

df.to_sql(con=engine, name='table_name', if_exists='replace')

对于此方法，请务必在使用PIP安装Pymysql运行之前安装PymySQL，您应该很好。