===========

ipython-sql

:Author: Catherine Devlin, http://catherinedevlin.blogspot.com

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

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

.. image:: https://raw.github.com/catherinedevlin/ipython-sql/master/examples/writers.png :width: 600px :alt: screenshot of ipython-sql in the Notebook

Examples

.. 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

For secure access, you may dynamically access your credentials (e.g. from your system environment or getpass.getpass) to avoid storing your password in the notebook itself. Use the $ before any variable to access it in your %sql command.

.. code-block:: python

In [11]: user = os.getenv('SOME_USER')
   ....: password = os.getenv('SOME_PASSWORD')
   ....: connection_string = "postgresql://{user}:{password}@localhost/some_database".format(user=user, password=password)
   ....: %sql $connection_string
Out[11]: u'Connected: some_user@some_database'

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)

Connecting

Connection strings are SQLAlchemy_ standard.

Some example connection strings::

mysql+pymysql://scott:tiger@localhost/foo
oracle://scott:tiger@127.0.0.1:1521/sidname
sqlite://
sqlite:///foo.db

.. _SQLAlchemy: http://docs.sqlalchemy.org/en/latest/core/engines.html#database-urls

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::

mysql+pymysql://scott:tiger@localhost/foo?charset=utf8

Configuration

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
--------------
SqlMagic.autolimit=<Int>
    Current: 0
    Automatically limit the size of the returned result sets
SqlMagic.autopandas=<Bool>
    Current: False
    Return Pandas DataFrames instead of regular result sets
SqlMagic.displaylimit=<Int>
    Current: 0
    Automatically limit the number of rows displayed (full result set is still
    stored)
SqlMagic.feedback=<Bool>
    Current: True
    Print number of rows affected by DML
SqlMagic.short_errors=<Bool>
    Current: True
    Don't display the full traceback on SQL Programming Error
SqlMagic.style=<Unicode>
    Current: 'DEFAULT'
    Set the table printing style to any of prettytable's defined styles
    (currently DEFAULT, MSWORD_FRIENDLY, PLAIN_COLUMNS, RANDOM)

In[3]: %config SqlMagic.feedback = False

Pandas

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: http://pandas.pydata.org/

Graphing

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:: https://raw.github.com/catherinedevlin/ipython-sql/master/examples/wordcount.png :alt: pie chart of word count of Shakespeare's comedies

Installing

Install the lastest release with::

pip install ipython-sql

or download from https://github.com/catherinedevlin/ipython-sql and::

cd ipython-sql
sudo python setup.py install

Dumping

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.

Development

https://github.com/catherinedevlin/ipython-sql

Credits

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
Andrés Celis for SQL Server bugfix

.. _Distribute: http://pypi.python.org/pypi/distribute .. _Buildout: http://www.buildout.org/ .. _modern-package-template: http://pypi.python.org/pypi/modern-package-template

News

0.1

Release date: 21-Mar-2013

Initial release

0.1.1

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

0.1.2

Release date: 29-Mar-2013

Python 3 compatibility
use prettyprint package
allow multiple SQL per cell

0.2.0

Release date: 30-May-2013

Accept bind variables (Thanks Mike Wilson!)

0.2.1

Release date: 15-June-2013

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

0.2.2

Release date: 30-July-2013

Converted from an IPython Plugin to an Extension for 1.0 compatibility

0.2.2.1

Release date: 01-Aug-2013

Deleted Plugin import left behind in 0.2.2

0.2.3

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

0.3.0

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

0.3.1

Reporting of number of rows affected configurable with feedback
Local variables usable as SQL bind variables

0.3.2

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

0.3.3

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

0.3.4

PERSIST pseudo-SQL command added

0.3.5

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

0.3.6

Fixed issue #30, commit failures for sqlite (thanks stonebig, jandot)

0.3.7

New column_local_vars config option submitted by darikg
Avoid contaminating user namespace from locals (thanks alope107)

0.3.7.1

Avoid "connection busy" error for SQL Server (thanks Andrés Celis)

0.3.8

Stop warnings for deprecated use of IPython 3 traitlets in IPython 4 (thanks graphaelli; also stonebig, aebrahim, mccahill)
README update for keeping connection info private, from eshilts