使用 sqlalchemy、mysql 和 pandas 读取框架
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20401392/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
read frame with sqlalchemy, mysql and pandas
提问by tschm
I am trying to connect to a mysql database, works fine with Option 1:
我正在尝试连接到 mysql 数据库，使用选项 1 可以正常工作：
from sqlalchemy import create_engine
engine = create_engine('mysql://root:root@localhost/lend', echo=True)
cnx = engine.connect()
x = cnx.execute("SELECT * FROM user")
but breaks down here:
但在这里分解：
from pandas.io import sql
xx = sql.read_frame("SELECT * FROM user", cnx)
cnx.close()
with
和
AttributeError: 'Connection' object has no attribute 'rollback'
AttributeError: 'Connection' 对象没有属性 'rollback'
回答by van
You need to have a raw database connection, and not an instance of Connection. In order to get it call either engine.raw_connection()or engine.connect().connection:
您需要有一个原始数据库连接，而不是Connection. 为了让它调用engine.raw_connection()或engine.connect().connection：
from pandas.io import sql
#cnx = engine.connect().connection # option-1
cnx = engine.raw_connection() # option-2
xx = sql.read_frame("SELECT * FROM user", cnx)
cnx.close()
回答by Dan Allan
Use the MySQLdb module to create the connection. There is ongoing progresstoward better SQL support, including sqlalchemy, but it's not ready yet.
使用 MySQLdb 模块创建连接。有持续的进步朝着更好的SQL支持，包括SQLAlchemy的，但它还没准备好。
If you are comfortable installing the development version of pandas, you might want to keep an eye on that linked issue and switch to using the development version of pandas as soon as it is merged. While pandas' SQL support is usable, there are some bugs around data types, missing values, etc., that are likely to come up if you use Pandas + SQL extensively.
如果您愿意安装 Pandas 的开发版本，您可能希望密切关注该链接问题，并在合并后立即切换到使用 Pandas 的开发版本。虽然 Pandas 的 SQL 支持是可用的，但在数据类型、缺失值等方面存在一些错误，如果您广泛使用 Pandas + SQL，可能会出现这些错误。
回答by Idodo
This is an old question but still relevant apparently. So past 2018 the way to solve this is simply use the engine directly:
这是一个老问题，但显然仍然相关。所以到了2018年解决这个问题的方法就是直接使用引擎：
xx = sql.read_sql("SELECT * FROM user", engine)
(originally posted by Midnighter in a comment)
（最初由 Midnighter 在评论中发表）
