pandas - to_sql() error querying sqlite_master table when writing to MySQL

Question

I have a question about how to save a dataframe to my local mysql.

import MySQLdb
import pandas as pd
conn=MySQLdb.connect(host="localhost",user='root',passwd="matt123",db="ada")
df=pd.DataFrame(['A','B'],columns=['new_tablecol'])
df.to_sql(name='new_table',con=conn,if_exists='append')

after typing this code , it says

pandas.io.sql.DatabaseError: Execution failed on sql 'SELECT name FROM sqlite_master WHERE type='table' AND name=?;': not all arguments converted during string formatting

I am confused about this.I can query and create table . but I can't save this dataframe.

Answer 1

Your way is not supported anymore.

con : SQLAlchemy engine or DBAPI2 connection (legacy mode)

Using SQLAlchemy makes it possible to use any DB supported by that library. If a DBAPI2 object, only sqlite3 is supported.

flavor : ‘sqlite’, default None

Deprecated since version 0.19.0: ‘sqlite’ is the only supported option if SQLAlchemy is not used.

pandas.DataFrame.to_sql

Try this?

from sqlalchemy import create_engine
import pandas as pd


engine = create_engine("mysql://root:matt123@localhost/ada")
con = engine.connect()
df = pd.DataFrame(['A','B'],columns=['new_tablecol'])
df.to_sql(name='new_table',con=con,if_exists='append')
con.close()

Syntax is:

engine = create_engine("mysql://USER:PASSWORD@HOST/DATABASE")

More information about sqlalchemy can be found here