Chris Withers Chris Withers - 3 months ago 13
SQL Question

unpacking a sql select into a pandas dataframe

Suppose I have a select roughly like this:

select instrument, price, date from my_prices;

How can I unpack the prices returned into a single dataframe with a series for each instrument and indexed on date?

To be clear: I'm looking for:

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: ...
Data columns (total 2 columns):
inst_1 ...
inst_2 ...
dtypes: float64(1), object(1)

I'm NOT looking for:

<class 'pandas.core.frame.DataFrame'>
DatetimeIndex: ...
Data columns (total 2 columns):
instrument ...
price ...
dtypes: float64(1), object(1)

...which is easy ;-)


Update: recent pandas have the following functions: read_sql_table and read_sql_query.

First create a db engine (a connection can also work here):

from sqlalchemy import create_engine
# see sqlalchemy docs for how to write this url for your database type:
engine = create_engine('mysql://scott:tiger@localhost/foo')

See sqlalchemy database urls.


table_name = 'my_prices'
df = pd.read_sql_table(table_name, engine)


df = pd.read_sql_query("SELECT instrument, price, date FROM my_prices;", engine)

The old answer had referenced read_frame which is has been deprecated (see the version history of this question for that answer).

It's often makes sense to read first, and then perform transformations to your requirements (as these are usually efficient and readable in pandas). In your example, you can pivot the result:

df.reset_index().pivot('date', 'instrument', 'price')

Note: You could miss out the reset_index you don't specify an index_col in the read_frame.