使用to_sql和sqlalchemy将pandas数据框转换为mariadb数据库

问题描述

我想使用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?

推荐答案

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

Passing sqlalchemy engines is only supported starting from pandas 0.14.0

要将to_sql与较早的熊猫版本一起使用，您需要将原始连接(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)

这篇关于使用to_sql和sqlalchemy将pandas数据框转换为mariadb数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！