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

Question

我正在尝试使用 pymysql 将 mySQL 查询结果存储在 pandas DataFrame 中，但在构建数据帧时遇到错误。 在这里 和 这里 发现了类似的问题，但看起来有 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' object has no attribute '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 constructor not properly called!

提前致谢！

原文由 msoderstrom 发布，翻译遵循 CC BY-SA 4.0 许可协议

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))

原文由 MaxU - stop russian terror 发布，翻译遵循 CC BY-SA 3.0 许可协议

Answer 2

尝试这个：

 import pandas as pd
import pymysql

mysql_connection = pymysql.connect(host='localhost', user='root', password='', db='test', charset='utf8')

sql = "SELECT * FROM `brands`"
df = pd.read_sql(sql, mysql_connection, index_col='brand_id')
print(df)

原文由 Alexandre Crivellaro 发布，翻译遵循 CC BY-SA 4.0 许可协议