Turn your IPython console into a cross-database SQL client
ipydb is an IPython plugin for running SQL queries and viewing their results.
Some demonstration videos are available in the documentation
$ ipython In  : %load_ext ipydb In  : %automagic on Automagic is ON, % prefix IS NOT needed for line magics. In  : connecturl mysql://user:pass@localhost/employees In  localhost/employees: tables departments dept_emp dept_manager employees salaries titles In  localhost/employees: fields departments departments ----------- dept_name VARCHAR(40) dept_no CHAR(4) In  localhost/employees: select * from departments order by dept_name +---------+--------------------+ | dept_no | dept_name | +---------+--------------------+ | d009 | Customer Service | | d005 | Development | | d002 | Finance | | d003 | Human Resources | | d001 | Marketing | | d004 | Production | | d006 | Quality Management | | d008 | Research | | d007 | Sales |
- Tab-completion of table names, fields and joins
- View query results in ascii-table format piped through less
- Single-line or multi-line query editing
- Tab-completion metadata is read in the background and persisted across sessions
- Cross-database support, thanks to SqlAlchemy: supported databases
To install ipydb:
$ pip install ipydb
You will need a python driver for your database of choice. For example:
$ pip install mysql-python
Start ipython and load the ipydb plugin:
$ ipython In : load_ext ipydb
Documentation is available at: http://ipydb.readthedocs.org
Connecting to Databases
There are two ways to connect to a database with ipydb. Directly via a connection url, using the connecturl magic function, or, using a connection ‘nickname’ with the connect magic function.
1. Using connecturl
You can connect to a database using an SqlAlchemy style url as follows:
In  : connecturl mysql://myuser:mypass@localhost/mydatabase In  : connecturl sqlite:///path/to/mydb.sqlite In  : connecturl sqlite:///:memory:
See the SqlAlchemy Documentation for further information.
2. Using connect and a .db-connections configuration file
For this to work, you need to create a file called .db-connections located in your home directory. .db-connections is an “ini” formatted file, parsable by python’s ConfigParser module.
Here’s an example of what ~/.db-connections might look like:
[mydb] type = mysql username = root password = xxxx host = localhost database = employees [myotherdb] type = sqlite database = /path/to/file.sqlite
Each database connection defined in ~/.db-connections is then referenceable via its [section heading]. So with the above .db-connections file, the following examples would work:
In  : connect mydb In  mydb : connect myotherdb