使用 to_sql 和 sqlalchemy 到 mariadb 数据库的 pandas 数据框

2022-01-15 00:00:00 python pandas sqlalchemy mariadb mysql-connector

问题描述

我想使用 pandas 函数 to_sql 将数据框写入 MariaDB 数据库.我在 PyCharm 中的 Python 代码如下所示:

I want to use the pandas function to_sql to write a dataframe into a MariaDB database. My Python code inside PyCharm looks as follows:

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

myd = pd.read_csv('/[path]/[filename].csv')

engine = create_engine('mysql+mysqlconnector://[user]:[pw]@127.0.0.1/[dbname]')

myd.to_sql(name='[tablename]', con=engine, if_exists='replace', index=False)

在控制台中执行最后一行时，出现以下错误:

When executing the last line in the console I get the following error:

Error on sql SELECT name FROM sqlite_master WHERE type='table' AND name='[tablename]'; 
Traceback (most recent call last):   
   File "/usr/lib/python3.4/code.py", line 90, in runcode
     exec(code, self.locals)   
   File "<input>", line 1, in <module>   
   File "/usr/lib/python3/dist-packages/pandas/core/frame.py", line 1261, in to_sql
     self, name, con, flavor=flavor, if_exists=if_exists, **kwargs)   
   File "/usr/lib/python3/dist-packages/pandas/io/sql.py", line 207, in write_frame
     exists = table_exists(name, con, flavor)   
   File "/usr/lib/python3/dist-packages/pandas/io/sql.py", line 275, in table_exists
     return len(tquery(query, con)) > 0   
   File "/usr/lib/python3/dist-packages/pandas/io/sql.py", line 90, in tquery
     cur = execute(sql, con, cur=cur)   
   File "/usr/lib/python3/dist-packages/pandas/io/sql.py", line 44, in execute
     cur = con.cursor() 
AttributeError: 'Engine' object has no attribute 'cursor'

这里有人有同样的情况有一点错误.然而，在有人解决问题之前它就消失了.你知道怎么回事吗?

Here someone had the same error at one point. However, it had disappeared before someone solved the problem. Do you know what is wrong?

解决方案

仅从 pandas 0.14.0 开始支持传递 sqlalchemy 引擎

Passing sqlalchemy engines is only supported starting from pandas 0.14.0

要将 to_sql 与旧版 pandas 一起使用，您需要传递原始连接 (engine.raw_connection()) 和 flavor='mysql' 到 to_sql:

To use to_sql with older pandas version, you need to pass the raw connection (engine.raw_connection()) and flavor='mysql' to to_sql:

myd.to_sql(name='[tablename]', con=engine.raw_connection(), flavor='mysql', if_exists='replace', index=False)

但是，我建议升级您的 pandas 版本(不推荐使用传递原始连接，并且在较新的 pandas 版本中将不再支持，然后将仅支持 sqlalchemy 引擎/连接)

However, I recommend to upgrade your pandas version (passing raw connections is deprecated and will not be supported anymore in newer pandas versions, then only sqlalchemy engines/connections will be supported)

相关文章