DatabaseError :当我使用 pandas.io.sql.to_sql() 时“并非所有参数都在字符串格式化期间转换" [英] DatabaseError : “not all arguments converted during string formatting” when I use pandas.io.sql.to_sql()
问题描述
我有一张表:
我尝试通过 sqlalchemy 使用这个导入这个表,代码是:
And I try to use this import this table by sqlalchemy , the code is:
import sqlalchemy as db
import pandas.io.sql as sql
username = 'root'
password = 'root'
host = 'localhost'
port = '3306'
database = 'classicmodels'
engine = db.create_engine(f'mysql+pymysql://{username}:{password}@{host}:{port}/{database}')
con = engine.raw_connection()
#readinto dataframe
df = pd.read_sql(f'SELECT * FROM `{database}`.`offices`;', con)
print(df[:2])
df_append = pd.DataFrame([{'officeCode': 8,'city':'Taipei',
'phone':'1234567891','addressLine1':'Taipei DaAn',
'addressLine2':'Taipei DaAn2','state':'Taipei',
'country':'Taiwan','postalCode':'123','territory':'Asia'}])
df2 = pd.concat([df,df_append],ignore_index=True)
sql.to_sql(frame = df2, name='proj55.address_book5',con=con, if_exists='append',index=False)
但我总是遇到这些错误:
But I always encounter these errors:
如果我将
con = engine.raw_connection()
传递给to_sql()
,我会得到这个:
If I pass
con = engine.raw_connection()
toto_sql()
, I'll get this:
- 数据库错误:sql 'SELECT name FROM 执行失败sqlite_master WHERE type='table' AND name=?;':并非所有参数在字符串格式化期间转换
如果我使用 con = engine.connect()
,并传递给 read_sql()
,我会得到错误:
If I use con = engine.connect()
, and pass to read_sql()
, I'll get error:
- AttributeError: 'Connection' 对象没有属性 'cursor'
如果我将 engine
传递给 to_sql()
和 read_sql()
,我会得到这个:
If I pass engine
to to_sql()
and read_sql()
, I'll get this:
- AttributeError: 'Engine' 对象没有属性 'cursor'
我该怎么办?
推荐答案
pandas.read_sql 使用:
pandas.read_sql use either:
- 一个 sqlalchemy 连接对象
engine.connect()
- 一个 db api 对象仅用于 sqlite
通过使用 raw_connection()
,你有一个 db api 连接对象,所以 Pandas 相信它是一个到 sqlite 数据库的连接(正如我们在你的错误中看到的那样FROM sqlite_master WHERE
)
by using raw_connection()
, you have a db api connection object, so pandas believes it's a connection to a sqlite database (as we see in your error FROM sqlite_master WHERE
)
你需要使用 con = engine.connect()
import sqlalchemy
import pandas
engine = sqlalchemy.create_engine('...')
with engine.connect() as conn:
print('sqla:', list(conn.execute('select * from users')))
df = pandas.read_sql('select * from users', conn)
print('df:', df)
df.to_sql('users2', conn)
print('sqla:', list(engine.connect().execute('select * from users2')))
输出:
sqla: [(1, 'toto'), (2, 'titi'), (3, 'tutu'), (4, 'tata')]
df: id name
0 1 toto
1 2 titi
2 3 tutu
3 4 tata
sqla: [(1, 'toto'), (2, 'titi'), (3, 'tutu'), (4, 'tata')]
符合预期
这篇关于DatabaseError :当我使用 pandas.io.sql.to_sql() 时“并非所有参数都在字符串格式化期间转换"的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!