skip to navigation
skip to content

Not Logged In

ipython-sql 0.3.6

RDBMS access via IPython


:Author: Catherine Devlin,

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

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

.. image::
:width: 600px
:alt: screenshot of ipython-sql in the Notebook


.. code-block:: python

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

.. code-block:: python

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

.. code-block:: python

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

.. code-block:: python

In [12]: name = 'Countess'

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

As a convenience, dict-style access for result sets is supported, with the
leftmost column serving as key, for unique values.

.. code-block:: python

In [14]: result = %sql select * from work
43 rows affected.

In [15]: result['richard2']
Out[15]: (u'richard2', u'Richard II', u'History of Richard II', 1595, u'h', None, u'Moby', 22411, 628)


Connection strings are `SQLAlchemy`_ standard.

Some example connection strings::


.. _SQLAlchemy:

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 and/or hang your browser. There is no autolimit
by default. However, `autolimit` (if set) limits the size of the result
set (usually with a `LIMIT` clause in the SQL). `displaylimit` is similar,
but the entire result set is still pulled into memory (for later analysis);
only the screen display is truncated.

.. code-block:: python

In [2]: %config SqlMagic
SqlMagic options
Current: 0
Automatically limit the size of the returned result sets
Current: False
Return Pandas DataFrames instead of regular result sets
Current: 0
Automatically limit the number of rows displayed (full result set is still
Current: True
Print number of rows affected by DML
Current: True
Don't display the full traceback on SQL Programming Error<unicode>
Current: 'DEFAULT'
Set the table printing style to any of prettytable's defined styles

In[3]: %config = False


If you have installed ``pandas``, you can use a result set's
``.DataFrame()`` method

.. code-block:: python

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

In [4]: dataframe = result.DataFrame()

The bogus non-standard pseudo-SQL command ``PERSIST`` will create a table name
in the database from the named DataFrame.

.. code-block:: python

In [5]: %sql PERSIST dataframe

In [6]: %sql SELECT * FROM dataframe;

.. _Pandas:


If you have installed ``matplotlib``, you can use a result set's
``.plot()``, ``.pie()``, and ``.bar()`` methods for quick plotting

.. code-block:: python

In[5]: result = %sql SELECT title, totalwords FROM work WHERE genretype = 'c'

In[6]: %matplotlib inline

In[7]: result.pie()

.. image::
:alt: pie chart of word count of Shakespeare's comedies


Install the lastest release with::

pip install ipython-sql

or download from and::

cd ipython-sql
sudo python install


Result sets come with a ``.csv(filename=None)`` method. This generates
comma-separated text either as a return value (if ``filename`` is not
specified) or in a file of the given name.



- Matthias Bussonnier for help with configuration
- Olivier Le Thanh Duong for ``%config`` fixes and improvements
- Distribute_
- Buildout_
- modern-package-template_
- Mike Wilson for bind variable code
- Thomas Kluyver and Steve Holden for debugging help
- Berton Earnshaw for DSN connection syntax

.. _Distribute:
.. _Buildout:
.. _modern-package-template:



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


*Release date: 30-July-2013*

Converted from an IPython Plugin to an Extension for 1.0 compatibility

*Release date: 01-Aug-2013*

Deleted Plugin import left behind in 0.2.2


*Release date: 20-Sep-2013*

* Contributions from Olivier Le Thanh Duong:

- SQL errors reported without internal IPython error stack

- Proper handling of configuration

* Added .DataFrame(), .pie(), .plot(), and .bar() methods to
result sets


*Release date: 13-Oct-2013*

* displaylimit config parameter

* reports number of rows affected by each query

* test suite working again

* dict-style access for result sets by primary key


* Reporting of number of rows affected configurable with ``feedback``

* Local variables usable as SQL bind variables


* ``.csv(filename=None)`` method added to result sets


* Python 3 compatibility restored
* DSN access supported (thanks Berton Earnshaw)


* PERSIST pseudo-SQL command added


* Indentations visible in HTML cells
* COMMIT each SQL statement immediately - prevent locks


* Fixed issue #30, commit failures for sqlite (thanks stonebig, jandot)  
File Type Py Version Uploaded on Size
ipython-sql-0.3.6.tar.gz (md5) Source 2015-05-20 11KB
ipython_sql-0.3.6-py3.4.egg (md5) Python Egg 3.4 2015-05-20 23KB
  • Downloads (All Versions):
  • 21 downloads in the last day
  • 918 downloads in the last week
  • 3605 downloads in the last month