IPython is an interactive shell for Python that is
well-suited to do data-analysis. It has integration for plotting, data-analysis
libraries, parallel computing etc.
IPython-sql is an IPython
extension that provides an easy way to load data from a database into the
ipython environment to do the data-analysis. Here's an example.
In [1]: %load_ext sql
In [2]: %sql postgres://localhost:5432/misago_testforum
Out[2]: u'Connected: None@misago_testforum'
In [3]: %sql select * FROM django_migrations where id = 5
1 rows affected.
Out[3]: [(5, u'admin', u'0001_initial', datetime.datetime(2014, 11, 24, 14, 26, 43, 625938,
tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-480, name=None)))]
We can now manipulate the data that was imported from the database.
The trouble is ipython doesn't have the ability to do auto-completion for the
sql statements. So it's a bit hard to iterate on the sql statement to get the
right data.
Pgcli on the other hand is a postgres client that provides
auto-completion for SQL statements.
Thanks to Darik Gamble we can now embed pgcli
directly inside the ipython console. We can iterate over the sql statements in
pgcli with the comfort of auto-completion and preview the results before
loading it into IPython. When we're satisfied with the results, all we do is
quit pgcli, and we'll drop back to IPython loaded with the results from the
last query.
Let's see how that works.
# IPython Console
In [2]: %load_ext pgcli.magic
In [3]: %pgcli postgres://localhost:5432/misago_testforum
Connected: None@misago_testforum
# pgcli repl
misago_testforum> SELECT * FROM django_migrations WHERE id = 5
+------+-------+--------------+----------------------------------+
| id | app | name | applied |
|------+-------+--------------+----------------------------------|
| 5 | admin | 0001_initial | 2014-11-24 14:26:43.625938-08:00 |
+------+-------+--------------+----------------------------------+
SELECT 1
misago_testforum> exit
GoodBye!
1 rows affected.
# Back on IPython Console.
Out[3]: [(5, u'admin', u'0001_initial', datetime.datetime(2014, 11, 24, 14, 26, 43, 625938,
tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-480, name=None)))]
In [4]: _
Out[4]: [(5, u'admin', u'0001_initial', datetime.datetime(2014, 11, 24, 14, 26, 43, 625938,
tzinfo=psycopg2.tz.FixedOffsetTimezone(offset=-480, name=None)))]
That's it. So if you're using IPython for data-analysis and have your data
residing in a Database, you can easily import it in using ipython-sql and
pgcli.
Installation
Install pgcli and ipython-sql and you're all set to go.
$ pip install pgcli ipython ipython-sql
If you have feedback, please reach out via
twitter or
github. Thank you!