pandas 如何使用pymysql将mySQL查询结果存储到pandas DataFrame中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/47328402/
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
How to store mySQL query result into pandas DataFrame with pymysql?
提问by msoderstrom
I'm trying to store a mySQL query result in a pandas DataFrame using pymysqland am running into errors building the dataframe. Found a similar question hereand here, but it looks like there are pymysql-specific errors being thrown:
我正在尝试将 mySQL 查询结果存储在 Pandas DataFrame 中，pymysql并且在构建数据帧时遇到错误。在此处和此处找到了一个类似的问题，但似乎pymysql抛出了特定错误：
import pandas as pd
import datetime
import pymysql
# dummy values
connection = pymysql.connect(user='username', password='password', databse='database_name', host='host')
start_date = datetime.datetime(2017,11,15)
end_date = datetime.datetime(2017,11,16)
try:
with connection.cursor() as cursor:
query = "SELECT * FROM orders WHERE date_time BETWEEN %s AND %s"
cursor.execute(query, (start_date, end_date))
df = pd.DataFrame(data=cursor.fetchall(), index = None, columns = cursor.keys())
finally:
connection.close()
returns: AttributeError: 'Cursor' object has no attribute 'keys'
返回： AttributeError: 'Cursor' object has no attribute 'keys'
If I drop the indexand columnsarguments:
如果我删除index和columns参数：
try:
with connection.cursor() as cursor:
query = "SELECT * FROM orders WHERE date_time BETWEEN %s AND %s"
cursor.execute(query, (start_date, end_date))
df = pd.DataFrame(cursor.fetchall())
finally:
connection.close()
returns ValueError: DataFrame constructor not properly called!
回报 ValueError: DataFrame constructor not properly called!
Thanks in advance!
提前致谢！
回答by MaxU
Use Pandas.read_sql()for this:
query = "SELECT * FROM orders WHERE date_time BETWEEN ? AND ?"
df = pd.read_sql(query, connection, params=(start_date, end_date))
回答by Negmat Mullodzhanov
Thank you for your suggestion to use pandas.read_sql(). It works with executing a stored procedure as well! I tested it in MSSQL 2017 environment.
感谢您建议使用 pandas.read_sql()。它也适用于执行存储过程！我在 MSSQL 2017 环境中对其进行了测试。
Below is an example (I hope it helps others):
下面是一个例子（我希望它可以帮助其他人）：
def database_query_to_df(connection, stored_proc, start_date, end_date):
# Define a query
query ="SET NOCOUNT ON; EXEC " + stored_proc + " ?, ? " + "; SET NOCOUNT OFF"
# Pass the parameters to the query, execute it, and store the results in a data frame
df = pd.read_sql(query, connection, params=(start_date, end_date))
return df
