<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 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
Step1:  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)
Step2:  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
Step3:  note if you get an error about MySQLdb not being installed here, enter this back in your bash shell
Step4:  Now that we've created the database week3demo, we need to tell MySQL that we want to use it
Step5:  But there's nothing in it
Step6:  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.
Step7:  Inserting data
Okay then, let's insert the sample data
Step8:  Selecting data
Okay, now we're cooking. There's data in the Person table, so we can start to SELECT it.
Step9:  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 _
Step10:  You can even assign it to a Pandas dataframe
Step11:  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
Step12:  And to get rid of a whole database, use DROP DATABASE
<ASSISTANT_TASK:>
Python Code:
%load_ext sql
Explanation: 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:
End of explanation
%sql sqlite:///survey.db
%sql SELECT * FROM Person;
Explanation: 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):
End of explanation
%sql mysql://mysqluser:mysqlpass@localhost/
Explanation: 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:
End of explanation
%sql CREATE DATABASE week3demo;
Explanation: 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.
End of explanation
%sql USE week3demo;
Explanation: Now that we've created the database week3demo, we need to tell MySQL that we want to use it:
End of explanation
%sql SHOW TABLES;
Explanation: But there's nothing in it:
End of explanation
%%sql 
CREATE TABLE Person
(ident CHAR(10),
 personal CHAR(25),
 family CHAR(25));
%sql SHOW TABLES;
%sql DESCRIBE Person;
Explanation: 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.
End of explanation
%%sql
INSERT INTO Person VALUES
("dyer", "William", "Dyer"),
("pb", "Frank", "Pabodie"),
("lake", "Anderson", "Lake"),
("roe", "Valentina", "Roerich"),
("danforth", "Frank", "Danforth")
;
Explanation: Inserting data
Okay then, let's insert the sample data:
End of explanation
%sql SELECT * FROM Person;
%sql SELECT * FROM Person WHERE personal = "Frank";
Explanation: Selecting data
Okay, now we're cooking. There's data in the Person table, so we can start to SELECT it.
End of explanation
result = _
print result
Explanation: 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 _:
End of explanation
df = result.DataFrame()
df
Explanation: You can even assign it to a Pandas dataframe:
End of explanation
%sql DROP TABLE Person;
%sql SHOW TABLES;
Explanation: 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:
End of explanation
%sql DROP DATABASE week3demo;
%sql SHOW DATABASES;
Explanation: And to get rid of a whole database, use DROP DATABASE:
End of explanation
<END_TASK>