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

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?

user308827
  • 21,227
  • 87
  • 254
  • 417
  • What is `db` here? Can you show how it's created? Typically an `.execute()` method is called, and not `.query()`. – mechanical_meat Apr 01 '17 at 01:27
  • `pandas.read_sql` – Paul H Apr 01 '17 at 01:32
  • thanks @bernie, I am using the query method from the dataset library: https://dataset.readthedocs.io/en/latest/api.html#dataset.Database.query. Also, added code to show how I am connecting to database using python dataset library – user308827 Apr 01 '17 at 01:53
  • That library isn't supported by `.read_sql()`. Are you able to use another way to execute your query? – mechanical_meat Apr 01 '17 at 01:57
  • thanks @bernie, it is built on SQL Alchemy, isn't that supported by `read_sql()`? – user308827 Apr 01 '17 at 01:58
  • SQLAlchemy is, but it doesn't look like `dataset` is. SQLAlchemy has a `.cursor()` method: http://docs.sqlalchemy.org/en/latest/core/connections.html – mechanical_meat Apr 01 '17 at 02:01
  • yep, it works with sqlalchemy. Use create_engine(*args) and connection = engine.connect() and finally pd.read_sql_query(query, connection) – Rutger Hofste May 22 '18 at 13:59

1 Answers1

1

You could do something like this:

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

Edit: 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')

docs for create_engine

MoQ93
  • 341
  • 2
  • 12