将SQL查询结果导出到Pandas数据框 [英] Exporting SQL query results to pandas dataframe

问题描述

id    date       temp       prcp
1   2015-01-01  -27.18      0
1   2015-01-02  -25.9       1.03
1   2015-01-03  -17.89      9.44
1   2015-01-04  -17.89      9.44
1   2015-01-05  -17.89      9.44

import dataset
import pandas as pd

db = dataset.connect(path_to_database_on_AWS)
res = db.query(SELECT * FROM tbl WHERE id=1 and date >= '2015-01-03' and date <= '2015-01-05')
pd.read_sql(res, con=db)

在上面的代码中，我正在使用查询从python数据集库中读取表中的方法，然后想将结果导出为pandas数据框，但是，我遇到了这个错误：

In the above code, I am using the query method from the python dataset library to read in from a table and then want to export the results as a pandas dataframe, however, I get this bug:

*** AttributeError: 'Database' object has no attribute 'cursor'

如何将查询结果导出到熊猫数据框？

How do I export query results to pandas dataframe?

推荐答案

您可以执行以下操作：

   import sqlite3
   import pandas as pd
   con = sqlite3.connect('path_to_your_sql')
   myFrames = pd.read_sql_query('your query', con)

编辑：对于非sqlite数据库，可以使用此连接：

for non sqlite db you could use this for the connection:

from sqlalchemy import create_engine
con = create_engine('dialect+driver://username:password@host:port/database')

create_engine的文档

这篇关于将SQL查询结果导出到Pandas数据框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！