Questions about pandas.to_sql

Question

I have a question on how to save a data framework in 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 entering this code, he 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 by this. I can query and create a table. but I can not save this data file.

Answer 1

Your path is no longer supported.

con: SQLAlchemy engine or DBAPI2 connection (deprecated mode)

Using SQLAlchemy allows you to use any database supported by this library. If the DBAPI2 object is supported only by sqlite3.

: 'sqlite, default is None

Deprecated from version 0.19.0: "sqlite is the only supported option if SQLAlchemy is not used.

pandas.DataFrame.to_sql

?

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

:

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

sqlalchemy