ipython-sql

Introduces a %sql / %%sql magic.

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

Examples:

In [1]: %load_ext sql

In [2]: %%sql postgres://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]: []

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

Configuration

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 ipython_config.py 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):

c.SqlMagic.style = ‘PLAIN_COLUMNS’

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

ipython profile create
ipython locate profile

See http://ipython.org/ipython-doc/stable/config/overview.html#configuration-objects-and-files for more details on IPython configuration.

Development

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

Credits

Matthias Bussonnier for help with configuration
Distribute
Buildout
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

Development Status
- 3 - Alpha
Environment
- Console
License
- OSI Approved :: MIT License
Topic
- Database

Release files for ipython-sql 0.1.2.1

For a detailed explanation of source distributions (sdists) and built distributions (wheels), please see the package formats documentation.

Source distribution (sdist)

Source distribution for ipython-sql 0.1.2.1
File	Size	Uploaded
ipython-sql-0.1.2.1.tar.gz	5.5 kB	Mar 29, 2013	Details

Built distribution (wheel)

Table of built distributions (wheels) for ipython-sql 0.1.2.1
File	Interpreter	ABI	Platform	Reset
ipython_sql-0.1.2.1-py3.2.egg 10.3 kB Mar 29, 2013	Legacy Egg format	-	-	Details

Total release size: 15.8 kB

Release files / ipython-sql-0.1.2.1.tar.gz

Download URL	ipython-sql-0.1.2.1.tar.gz
Size	5.5 kB
Tags	Source
SHA-256 checksum How to use checksums	`5aa385966de08f91fb436a5797d1870b23b31a70cf555dc0213a2c1cf9ee6d97`
BLAKE2b-256 checksum How to use checksums	`91d77f6e891b3fbf4c7988b8a57f0cfec13c3b5f8a006ccacdd6742c1a90d6fd`
Upload date	Mar 29, 2013
Uploaded using Trusted Publishing? What is trusted publishing?	No

Release files / ipython_sql-0.1.2.1-py3.2.egg

Download URL	ipython_sql-0.1.2.1-py3.2.egg
Size	10.3 kB
Tags	Egg
SHA-256 checksum How to use checksums	`1ab3c9517118e79c80196bc9aec689696b6f7c64bcc1779db137c207292d21ce`
BLAKE2b-256 checksum How to use checksums	`fbef64635e4091fb34e0fba6f106fb080f5af6a083c11b00e50e2c17d73b13fa`
Upload date	Mar 29, 2013
Uploaded using Trusted Publishing? What is trusted publishing?	No