Skip to main content

RDBMS access via IPython

Project description

ipython-sql

Introduces a %sql / %%sql magic.

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

ipython-sql notepad usage screenshot

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

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

Project details


Download files

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

Source Distribution

ipython-sql-0.1.2.1.tar.gz (5.5 kB view details)

Uploaded Source

Built Distribution

ipython_sql-0.1.2.1-py3.2.egg (10.3 kB view details)

Uploaded Egg

File details

Details for the file ipython-sql-0.1.2.1.tar.gz.

File metadata

  • Download URL: ipython-sql-0.1.2.1.tar.gz
  • Upload date:
  • Size: 5.5 kB
  • Tags: Source
  • Uploaded using Trusted Publishing? No

File hashes

Hashes for ipython-sql-0.1.2.1.tar.gz
Algorithm Hash digest
SHA256 5aa385966de08f91fb436a5797d1870b23b31a70cf555dc0213a2c1cf9ee6d97
MD5 41d7f1911dc0b657396dab560e696609
BLAKE2b-256 91d77f6e891b3fbf4c7988b8a57f0cfec13c3b5f8a006ccacdd6742c1a90d6fd

See more details on using hashes here.

File details

Details for the file ipython_sql-0.1.2.1-py3.2.egg.

File metadata

File hashes

Hashes for ipython_sql-0.1.2.1-py3.2.egg
Algorithm Hash digest
SHA256 1ab3c9517118e79c80196bc9aec689696b6f7c64bcc1779db137c207292d21ce
MD5 3ae3e4ba48658c89ea2b1c916e50633d
BLAKE2b-256 fbef64635e4091fb34e0fba6f106fb080f5af6a083c11b00e50e2c17d73b13fa

See more details on using hashes here.

Supported by

AWS Cloud computing and Security Sponsor Datadog Monitoring Fastly CDN Google Download Analytics Pingdom Monitoring Sentry Error logging StatusPage Status page