python - 如何使用 pymysql 将 mySQL 查询结果存储到 pandas DataFrame 中？

Question

我正在尝试使用 pymysql 将 mySQL 查询结果存储在 pandas DataFrame 中，并且在构建数据帧时遇到错误。发现了一个类似的问题 here和 here ，但似乎抛出了 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()

返回:AttributeError: 'Cursor' 对象没有属性 'keys'

如果我删除 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()

返回 ValueError: DataFrame 构造函数未正确调用!

提前致谢!

Answer 1

使用 Pandas.read_sql()为此:

query = "SELECT * FROM orders WHERE date_time BETWEEN ? AND ?"
df = pd.read_sql(query, connection,  params=(start_date, end_date))