Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;':

Question

How can I easily write my pandas dataframe to a MySQL database using mysql.connector?

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')

Tried this but it gives me an error that:

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

Does the mysql.connectornot have a df.to_sqlfunction?

These are the col names:

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')

Amphiboly

For a better response, I would suggest naming the question a little better. This question should've been named "Writing to mysql database with mysq.connector and pandas" or something similar that describes your problem. – Muscular

Answer 1

Pandas requires an SQLAlchemy engine to write data to sql. You can take up the following two approaches, the first being writing with a connector execure and the second using the engine with a pandas.to_sql statement.

It works very similar to your pandas read function.

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()

Or you can establish an engine for uploading.

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')

For this method be sure to install pymysql before running with pip install pymysql and you should be good to go.

Muscular