How to convert SQL Query result to PANDAS Data Structure?
Edit: Mar. 2015
As noted below, pandas now uses SQLAlchemy to both read from (read_sql) and insert into (to_sql) a database. The following should work
import pandas as pddf = pd.read_sql(sql, cnxn)Previous answer:Via mikebmassey from a similar question
import pyodbcimport pandas.io.sql as psqlcnxn = pyodbc.connect(connection_info) cursor = cnxn.cursor()sql = "SELECT * FROM TABLE"df = psql.frame_query(sql, cnxn)cnxn.close()
Here's the shortest code that will do the job:
from pandas import DataFramedf = DataFrame(resoverall.fetchall())df.columns = resoverall.keys()You can go fancier and parse the types as in Paul's answer.
If you are using SQLAlchemy's ORM rather than the expression language, you might find yourself wanting to convert an object of type sqlalchemy.orm.query.Query to a Pandas data frame.
The cleanest approach is to get the generated SQL from the query's statement attribute, and then execute it with pandas's read_sql() method. E.g., starting with a Query object called query:
df = pd.read_sql(query.statement, query.session.bind)