How to Correctly Write Pandas DataFrames to MySQL Using SQLAlchemy?

Using SQLAlchemy with MySQL to Write Pandas DataFrames to MySQL

When attempting to write a pandas DataFrame to a MySQL table using the to_sql method while transitioning from the deprecated 'flavor='mysql'' syntax to the recommended SQLAlchemy engine approach, users may encounter an error similar to:

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

This error suggests that SQLite is being used instead of MySQL. To resolve this, ensure the correct use of an SQLAlchemy connection with MySQL and specifically mysql.connector.

Solution

The error can be resolved by using the engine created with SQLAlchemy directly as the connection for the to_sql method, instead of obtaining a raw connection from the engine. Here's the corrected code:

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

# Create an SQLAlchemy engine
engine = create_engine('mysql+mysqlconnector://[user]:[pass]@[host]:[port]/[schema]', echo=False)

# Read data from the MySQL table
data = pd.read_sql('SELECT * FROM sample_table', engine)

# Write the DataFrame to a new table
data.to_sql(name='sample_table2', con=engine, if_exists='append', index=False)

By using the engine as the connection, the SQLAlchemy connection is properly established and the error regarding SQLite is eliminated. This allows the DataFrame to be successfully written to the MySQL table.

The above is the detailed content of How to Correctly Write Pandas DataFrames to MySQL Using SQLAlchemy?. For more information, please follow other related articles on the PHP Chinese website!