有关pandas.to_sql的问题 [英] questions about pandas.to_sql

问题描述

我有一个关于如何将数据帧保存到本地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')

输入此代码后，它说

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

对此我感到困惑.我可以查询并创建table.但我无法保存此数据框.

解决方案

您的方式不再受支持.

con: SQLAlchemy引擎或DBAPI2连接(传统模式)

使用SQLAlchemy可以使用该数据库支持的任何数据库 图书馆.如果是DBAPI2对象，则仅支持sqlite3.

风味:"sqlite"，默认为无

从0.19.0版开始不推荐使用:"sqlite"是唯一受支持的选项 如果不使用SQLAlchemy.

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的更多信息，请参见此处

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.

解决方案

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

这篇关于有关pandas.to_sql的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！