unpacking a sql select into a pandas dataframe

Question

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

Answer 1

You can pass a cursor object to the DataFrame constructor. For postgres:

import psycopg2
conn = psycopg2.connect("dbname='db' user='user' host='host' password='pass'")
cur = conn.cursor()
cur.execute("select instrument, price, date from my_prices")
df = DataFrame(cur.fetchall(), columns=['instrument', 'price', 'date'])

then set index like

df.set_index('date', drop=False)

or directly:

df.index =  df['date']

Answer 2

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.

pandas_read_sql_table

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

pandas_read_sql_query

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.