Sqlite3 and MySQL demo

With the excellent ipython-sql jupyter extension installed, it becomes very easy to connect to SQL database backends. This notebook demonstrates how to do this.

Note that this is a Python 2 notebook.

First, we need to activate the extension:



In [46]:

    
%load_ext sql









    



The sql extension is already loaded. To reload it, use:
  %reload_ext sql

There are warnings, but that's okay - this happens a lot these days due to the whole ipython/jupyter renaming process. You can ignore them.

Get a database

Using the bash shell (not a notebook!), follow the instructions at the SW Carpentry db lessons discussion page to get the survey.db file. This is a sqlite3 database.

I recommend following up with the rest of the instructions on that page to explore sqlite3.

Connecting to a Sqlite3 database

This part is easy, just connect like so (assuming the survey.db file is in the same directory as this notebook):



In [47]:

    
%sql sqlite:///survey.db









    Out[47]:





u'Connected: None@survey.db'



In [48]:

    
%sql SELECT * FROM Person;









    



Done.






    Out[48]:





    
        ident
        personal
        family
    
    
        dyer
        William
        Dyer
    
    
        pb
        Frank
        Pabodie
    
    
        lake
        Anderson
        Lake
    
    
        roe
        Valentina
        Roerich
    
    
        danforth
        Frank
        Danforth

You should be able to execute all the standard SQL queries from the lesson here now. Note that you can also do this on the command line.

Note specialized sqlite3 commands like ".schema" might not work.

Connecting to a MySQL database

Now that you've explored the survey.db sample database with sqlite3, let's try working with mysql:



In [49]:

    
%sql mysql://mysqluser:mysqlpass@localhost/









    Out[49]:





u'Connected: mysqluser@'

note if you get an error about MySQLdb not being installed here, enter this back in your bash shell:

% sudo pip install mysql-python

If it asks for your password, it's "vagrant".

After doing this, try executing the above cell again. You should see:

u'Connected: mysqluser@'

...if it works.

Creating a database

Now that we're connected, let's create a database.



In [50]:

    
%sql CREATE DATABASE week3demo;









    



1 rows affected.






    Out[50]:





[]

Now that we've created the database week3demo, we need to tell MySQL that we want to use it:



In [51]:

    
%sql USE week3demo;









    



0 rows affected.






    Out[51]:





[]

But there's nothing in it:



In [52]:

    
%sql SHOW TABLES;









    



0 rows affected.






    Out[52]:





    
        Tables_in_week3demo

Creating a table

From here we need to create a first table. Let's recreate the Person table from the SW Carpentry db lesson, topic 1.



In [53]:

    
%%sql 
CREATE TABLE Person
(ident CHAR(10),
 personal CHAR(25),
 family CHAR(25));









    



0 rows affected.






    Out[53]:





[]



In [54]:

    
%sql SHOW TABLES;









    



1 rows affected.






    Out[54]:





    
        Tables_in_week3demo
    
    
        Person



In [55]:

    
%sql DESCRIBE Person;









    



3 rows affected.






    Out[55]:





    
        Field
        Type
        Null
        Key
        Default
        Extra
    
    
        ident
        char(10)
        YES
        
        None
        
    
    
        personal
        char(25)
        YES
        
        None
        
    
    
        family
        char(25)
        YES
        
        None

Inserting data

Okay then, let's insert the sample data:



In [56]:

    
%%sql
INSERT INTO Person VALUES
("dyer", "William", "Dyer"),
("pb", "Frank", "Pabodie"),
("lake", "Anderson", "Lake"),
("roe", "Valentina", "Roerich"),
("danforth", "Frank", "Danforth")
;









    



5 rows affected.






    Out[56]:





[]

Selecting data

Okay, now we're cooking. There's data in the Person table, so we can start to SELECT it.



In [57]:

    
%sql SELECT * FROM Person;









    



5 rows affected.






    Out[57]:





    
        ident
        personal
        family
    
    
        dyer
        William
        Dyer
    
    
        pb
        Frank
        Pabodie
    
    
        lake
        Anderson
        Lake
    
    
        roe
        Valentina
        Roerich
    
    
        danforth
        Frank
        Danforth



In [58]:

    
%sql SELECT * FROM Person WHERE personal = "Frank";









    



2 rows affected.






    Out[58]:





    
        ident
        personal
        family
    
    
        pb
        Frank
        Pabodie
    
    
        danforth
        Frank
        Danforth

Accessing data from Python

One of the great things about ipython-sql is it marshalls all the data into Python objects for you. For example, to get the result data into a Python object, grab it from _:



In [59]:

    
result = _
print result









    



+----------+----------+----------+
|  ident   | personal |  family  |
+----------+----------+----------+
|    pb    |  Frank   | Pabodie  |
| danforth |  Frank   | Danforth |
+----------+----------+----------+

You can even assign it to a Pandas dataframe:



In [60]:

    
df = result.DataFrame()



In [61]:

    
df









    Out[61]:






  
    
      
      ident
      personal
      family
    
  
  
    
      0
      pb
      Frank
      Pabodie
    
    
      1
      danforth
      Frank
      Danforth

Cleaning up

If you were just doing a little exploring and wish to clean up, it's easy to get rid of tables and databases.

NOTE: these are permanent actions. Only do them if you know you don't need them any longer.

To get rid of a table, use DROP TABLE:



In [62]:

    
%sql DROP TABLE Person;









    



0 rows affected.






    Out[62]:





[]



In [63]:

    
%sql SHOW TABLES;









    



0 rows affected.






    Out[63]:





    
        Tables_in_week3demo

And to get rid of a whole database, use DROP DATABASE:



In [64]:

    
%sql DROP DATABASE week3demo;









    



0 rows affected.






    Out[64]:





[]



In [65]:

    
%sql SHOW DATABASES;









    



3 rows affected.






    Out[65]:





    
        Database
    
    
        information_schema
    
    
        mysql
    
    
        performance_schema

ident	personal	family
dyer	William	Dyer
pb	Frank	Pabodie
lake	Anderson	Lake
roe	Valentina	Roerich
danforth	Frank	Danforth

Field	Type	Null	Default
ident	char(10)	YES	None
personal	char(25)	YES	None
family	char(25)	YES	None