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

问题描述

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

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