I'm finding the %sql magic function extremely useful. It turns IPython into a very nice front-end to Postgresql.

First, make sure you have the ipython-sql extension installed:

pip install ipython-sql

https://pypi.python.org/pypi/ipython-sql

Then we load the extension

%load_ext sql

Then we set up our database connection.

%%sql
postgresql://testuser:password@localhost/test

u'Connected: testuser@test'

And now we can start interacting directly with the database as if we were at the psql command line.

%%sql
CREATE TABLE people (first text, last text, drink text);
INSERT INTO people (first,last,drink)
VALUES
    ('zaphod','beeblebrox','pan galactic gargle blaster'),
    ('arthur','dent','tea'),
    ('ford','prefect','old janx spirit')
    ;

Done.
3 rows affected.

[]

%sql select * from people

3 rows affected.

We can access the results as a python object:

result = %sql select * from people
len(result)

3

And we can even get our recordset as a pandas dataframe

%config SqlMagic.autopandas=True

frame = %sql select * from people
frame

frame['first'].str.upper()

0    ZAPHOD
1    ARTHUR
2      FORD
Name: first, dtype: object