Skip to main content

RDBMS access via IPython

Project description

Introduces a %sql (or %%sql) magic.

Connect to a database, using SQLAlchemy connect strings, then issue SQL commands within IPython or IPython Notebook.

screenshot of ipython-sql in the Notebook


In [1]: %load_ext sql

In [2]: %%sql postgresql://will:longliveliz@localhost/shakes
   ...: select * from character
   ...: where abbrev = 'ALICE'
Out[2]: [(u'Alice', u'Alice', u'ALICE', u'a lady attending on Princess Katherine', 22)]

In [3]: result = _

In [4]: print(result)
charid   charname   abbrev                description                 speechcount
Alice    Alice      ALICE    a lady attending on Princess Katherine   22

In [4]: result.keys
Out[5]: [u'charid', u'charname', u'abbrev', u'description', u'speechcount']

In [6]: result[0][0]
Out[6]: u'Alice'

In [7]: result[0].description
Out[7]: u'a lady attending on Princess Katherine'

After the first connection, connect info can be omitted:

In [8]: %sql select count(*) from work
Out[8]: [(43L,)]

Connections to multiple databases can be maintained. You can refer to an existing connection by username@database:

In [9]: %%sql will@shakes
   ...: select charname, speechcount from character
   ...: where  speechcount = (select max(speechcount)
   ...:                       from character);
Out[9]: [(u'Poet', 733)]

In [10]: print(_)
charname   speechcount
Poet       733

You may use multiple SQL statements inside a single cell, but you will only see any query results from the last of them, so this really only makes sense for statements with no output:

In [11]: %%sql sqlite://
   ....: CREATE TABLE writer (first_name, last_name, year_of_death);
   ....: INSERT INTO writer VALUES ('William', 'Shakespeare', 1616);
   ....: INSERT INTO writer VALUES ('Bertold', 'Brecht', 1956);
Out[11]: []

Bind variables (bind parameters) can be used in the “named” (:x) style. The variable names used should be defined in the local namespace:

In [12]: name = 'Countess'

In [13]: %sql select description from character where charname = :name
Out[13]: [(u'mother to Bertram',)]


Connection strings are SQLAlchemy standard.

Some example connection strings:


Note that mysql and mysql+pymysql connections (and perhaps others) don’t read your client character set information from .my.cnf. You need to specify it in the connection string:



Query results are loaded as lists, so very large result sets may use up your system’s memory. There is no autolimit by default.

You can set an autolimit by adding this to your file:

c.SqlMagic.autolimit = 1000

You can similarly change the table printing style to any of prettytable’s defined styles (currently DEFAULT, MSWORD_FRIENDLY, PLAIN_COLUMNS, RANDOM): = 'PLAIN_COLUMNS'

You can create and find your file from the command line:

ipython profile create
ipython locate profile

See for more details on IPython configuration.


Once your data is in IPython, you may want to manipulate it with Pandas:

In [3]: import pandas as pd

In [4]: result = %sql SELECT * FROM character WHERE speechcount > 25

In [5]: dataframe = pd.DataFrame(result, columns=result.keys)


Install the lastest release with:

pip install ipython-sql

or download from and:

cd ipython-sql sudo python install




Release date: 21-Mar-2013

  • Initial release


Release date: 29-Mar-2013

  • Release to PyPI
  • Results returned as lists
  • print(_) to get table form in text console
  • set autolimit and text wrap in configuration


Release date: 29-Mar-2013

  • Python 3 compatibility
  • use prettyprint package
  • allow multiple SQL per cell


Release date: 30-May-2013

  • Accept bind variables (Thanks Mike Wilson!)


Release date: 15-June-2013

  • Recognize socket connection strings
  • Bugfix - issue 4 (remember existing connections by case)

Project details

Release history Release notifications

History Node


History Node


History Node


History Node


History Node


History Node


History Node


History Node


History Node


History Node

History Node


This version
History Node


History Node


History Node

History Node

History Node


Download files

Download the file for your platform. If you're not sure which to choose, learn more about installing packages.

Filename, size & hash SHA256 hash help File type Python version Upload date
ipython_sql-0.2.1-py3.3.egg (11.2 kB) Copy SHA256 hash SHA256 Egg 3.3 Jun 15, 2013
ipython-sql-0.2.1.tar.gz (6.3 kB) Copy SHA256 hash SHA256 Source None Jun 15, 2013

Supported by

Elastic Elastic Search Pingdom Pingdom Monitoring Google Google BigQuery Sentry Sentry Error logging CloudAMQP CloudAMQP RabbitMQ AWS AWS Cloud computing Fastly Fastly CDN DigiCert DigiCert EV certificate StatusPage StatusPage Status page