Writing to MySQL database with pandas using SQLAlchemy, to_sql

Question

trying to write pandas dataframe to MySQL table using to_sql. Previously been using flavor='mysql', however it will be depreciated in the future and wanted to start the transition to using SQLAlchemy engine.

sample code:

import pandas as pd import mysql.connector from sqlalchemy import create_engine  engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False) cnx = engine.raw_connection() data = pd.read_sql('SELECT * FROM sample_table', cnx) data.to_sql(name='sample_table2', con=cnx, if_exists = 'append', index=False) 

The read works fine but the to_sql has an error:

DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': Wrong number of arguments during string formatting

Why does it look like it is trying to use sqlite? What is the correct use of a sqlalchemy connection with mysql and specifically mysql.connector?

I also tried passing the engine in as the connection as well, and that gave me an error referencing no cursor object.

data.to_sql(name='sample_table2', con=engine, if_exists = 'append', index=False) >>AttributeError: 'Engine' object has no attribute 'cursor' 

Answer 1

Using the engine in place of the raw_connection() worked:

import pandas as pd import mysql.connector from sqlalchemy import create_engine  engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False) data.to_sql(name='sample_table2', con=engine, if_exists = 'append', index=False) 

Not clear on why when I tried this yesterday it gave me the earlier error.

Answer 2

Alternatively, use pymysql package...

import pymysql from sqlalchemy import create_engine cnx = create_engine('mysql+pymysql://[user]:[pass]@[host]:[port]/[schema]', echo=False)  data = pd.read_sql('SELECT * FROM sample_table', cnx) data.to_sql(name='sample_table2', con=cnx, if_exists = 'append', index=False)