There are many ways to browse through a SQL database, throughout this Appendix we are only going to be focusing on learning about SQL queries using a combination of SQLite,Python,pandas, and SQLAlchemy. Please note that this is a pretty specific way of operating with a SQL Database, and may or may not fit other general needs. The primary goal of this section is to teach you how to use SQL queries to grab information and set it as a pandas DataFrame. We will not be going over more general topics of relational databases, MySQL, or using a SQL console directly.
To fully understand the content of this Appendix, I suggest you complete the course up to at least Lecture 28, although I really recommend completing up to Lecture 46 to get the most out of this Appendix!
To start this appendix, download SQLAlchemy. You can do this by either downloading it here
Or - by typing pip install sqlalchemy in your command line.
Or - by typing conda install sqlalchemy if you are using the Anaconda installation of Python. (recommended)
Next up we will download a sql browser. We will be using SQLite Browser because it is lightweight and free to use. There are many alternatives you can use, check out a list of 10 free ones here
Download SQLite Browser here: http://sqlitebrowser.org/
You can download the fully constructed database here
Or - you can download the .sql file to construct the database yourself: http://dev.mysql.com/doc/index-other.html Then use SQLite Browser to construct the database by running the .sql
Either way, make sure to save it in the same directory as your iPython notebooks, or remember the file path for later so we can tell pandas exactly where to look for it.
Check out the database either by opening it up using SQLite Browser or by checking out the diagram at this link: Diagram
I've posted it below as well inside this notebook, but fair warning, the picture is huge!
# Note: The picture is really big, I suggest you check out the link directly!
from IPython.display import Image
Image(url='http://www.dbquanti.eu/css/images/database.png')
Now that we have seen an overview of what the database looks like, let's go ahead and learn how to communicate with it with Python and pandas.
Python comes with SQLite3, which provides a lightweight disk-based database that doesn't require a seperate server process. It's useful to prototyp with SQLite and then port the code to a larger database system, like MySQL. Python comes with a pretty awesome module to connect to a SQL database with SQLite. The module is SQLite3, let's go ahead and import it (and pandas as well).
# imports!
import sqlite3
import pandas as pd
To use the module, you must first create a Connection object that represents the database. If the database name already exists SQLite3 will automatically connect to it, if it does not exsist, SQLite3 will automatically create.
For experienced users: You can also supply the special name :memory: to create a database in RAM.
Let's make the connection!
# Connect to the database (again, downloaded from here: https://www.dropbox.com/s/t049qmjzycrakro/sakila.db?dl=0
con = sqlite3.connect("sakila.db")
Now we can run a basic SQL query, pass it with pandas, and display the output as a DataFrame! Don't worry if you don't understand the query completely yet, this is just a usage example for connecting to the database, other lectures will dive deeper into SQL queries.
# Set SQL query as a comment
sql_query = ''' SELECT * FROM customer '''
# Use pandas to pass sql query using connection form SQLite3
df = pd.read_sql(sql_query, con)
# Show the resulting DataFrame
df
customer_id | store_id | first_name | last_name | address_id | active | create_date | last_update | ||
---|---|---|---|---|---|---|---|---|---|
0 | 1 | 1 | MARY | SMITH | MARY.SMITH@sakilacustomer.org | 5 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
1 | 2 | 1 | PATRICIA | JOHNSON | PATRICIA.JOHNSON@sakilacustomer.org | 6 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
2 | 3 | 1 | LINDA | WILLIAMS | LINDA.WILLIAMS@sakilacustomer.org | 7 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
3 | 4 | 2 | BARBARA | JONES | BARBARA.JONES@sakilacustomer.org | 8 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
4 | 5 | 1 | ELIZABETH | BROWN | ELIZABETH.BROWN@sakilacustomer.org | 9 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
5 | 6 | 2 | JENNIFER | DAVIS | JENNIFER.DAVIS@sakilacustomer.org | 10 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
6 | 7 | 1 | MARIA | MILLER | MARIA.MILLER@sakilacustomer.org | 11 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
7 | 8 | 2 | SUSAN | WILSON | SUSAN.WILSON@sakilacustomer.org | 12 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
8 | 9 | 2 | MARGARET | MOORE | MARGARET.MOORE@sakilacustomer.org | 13 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
9 | 10 | 1 | DOROTHY | TAYLOR | DOROTHY.TAYLOR@sakilacustomer.org | 14 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
10 | 11 | 2 | LISA | ANDERSON | LISA.ANDERSON@sakilacustomer.org | 15 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
11 | 12 | 1 | NANCY | THOMAS | NANCY.THOMAS@sakilacustomer.org | 16 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
12 | 13 | 2 | KAREN | JACKSON | KAREN.JACKSON@sakilacustomer.org | 17 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
13 | 14 | 2 | BETTY | WHITE | BETTY.WHITE@sakilacustomer.org | 18 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
14 | 15 | 1 | HELEN | HARRIS | HELEN.HARRIS@sakilacustomer.org | 19 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
15 | 16 | 2 | SANDRA | MARTIN | SANDRA.MARTIN@sakilacustomer.org | 20 | 0 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
16 | 17 | 1 | DONNA | THOMPSON | DONNA.THOMPSON@sakilacustomer.org | 21 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
17 | 18 | 2 | CAROL | GARCIA | CAROL.GARCIA@sakilacustomer.org | 22 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
18 | 19 | 1 | RUTH | MARTINEZ | RUTH.MARTINEZ@sakilacustomer.org | 23 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
19 | 20 | 2 | SHARON | ROBINSON | SHARON.ROBINSON@sakilacustomer.org | 24 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
20 | 21 | 1 | MICHELLE | CLARK | MICHELLE.CLARK@sakilacustomer.org | 25 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
21 | 22 | 1 | LAURA | RODRIGUEZ | LAURA.RODRIGUEZ@sakilacustomer.org | 26 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
22 | 23 | 2 | SARAH | LEWIS | SARAH.LEWIS@sakilacustomer.org | 27 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
23 | 24 | 2 | KIMBERLY | LEE | KIMBERLY.LEE@sakilacustomer.org | 28 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:28 |
24 | 25 | 1 | DEBORAH | WALKER | DEBORAH.WALKER@sakilacustomer.org | 29 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:29 |
25 | 26 | 2 | JESSICA | HALL | JESSICA.HALL@sakilacustomer.org | 30 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:29 |
26 | 27 | 2 | SHIRLEY | ALLEN | SHIRLEY.ALLEN@sakilacustomer.org | 31 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:29 |
27 | 28 | 1 | CYNTHIA | YOUNG | CYNTHIA.YOUNG@sakilacustomer.org | 32 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:29 |
28 | 29 | 2 | ANGELA | HERNANDEZ | ANGELA.HERNANDEZ@sakilacustomer.org | 33 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:29 |
29 | 30 | 1 | MELISSA | KING | MELISSA.KING@sakilacustomer.org | 34 | 1 | 2006-02-14 22:04:36.000 | 2011-09-14 18:10:29 |
... | ... | ... | ... | ... | ... | ... | ... | ... | ... |
569 | 570 | 2 | IVAN | CROMWELL | IVAN.CROMWELL@sakilacustomer.org | 576 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
570 | 571 | 2 | JOHNNIE | CHISHOLM | JOHNNIE.CHISHOLM@sakilacustomer.org | 577 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
571 | 572 | 1 | SIDNEY | BURLESON | SIDNEY.BURLESON@sakilacustomer.org | 578 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
572 | 573 | 1 | BYRON | BOX | BYRON.BOX@sakilacustomer.org | 579 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
573 | 574 | 2 | JULIAN | VEST | JULIAN.VEST@sakilacustomer.org | 580 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
574 | 575 | 2 | ISAAC | OGLESBY | ISAAC.OGLESBY@sakilacustomer.org | 581 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
575 | 576 | 2 | MORRIS | MCCARTER | MORRIS.MCCARTER@sakilacustomer.org | 582 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
576 | 577 | 2 | CLIFTON | MALCOLM | CLIFTON.MALCOLM@sakilacustomer.org | 583 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
577 | 578 | 2 | WILLARD | LUMPKIN | WILLARD.LUMPKIN@sakilacustomer.org | 584 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
578 | 579 | 2 | DARYL | LARUE | DARYL.LARUE@sakilacustomer.org | 585 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
579 | 580 | 1 | ROSS | GREY | ROSS.GREY@sakilacustomer.org | 586 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
580 | 581 | 1 | VIRGIL | WOFFORD | VIRGIL.WOFFORD@sakilacustomer.org | 587 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
581 | 582 | 2 | ANDY | VANHORN | ANDY.VANHORN@sakilacustomer.org | 588 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
582 | 583 | 1 | MARSHALL | THORN | MARSHALL.THORN@sakilacustomer.org | 589 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
583 | 584 | 2 | SALVADOR | TEEL | SALVADOR.TEEL@sakilacustomer.org | 590 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
584 | 585 | 1 | PERRY | SWAFFORD | PERRY.SWAFFORD@sakilacustomer.org | 591 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
585 | 586 | 1 | KIRK | STCLAIR | KIRK.STCLAIR@sakilacustomer.org | 592 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
586 | 587 | 1 | SERGIO | STANFIELD | SERGIO.STANFIELD@sakilacustomer.org | 593 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
587 | 588 | 1 | MARION | OCAMPO | MARION.OCAMPO@sakilacustomer.org | 594 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
588 | 589 | 1 | TRACY | HERRMANN | TRACY.HERRMANN@sakilacustomer.org | 595 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
589 | 590 | 2 | SETH | HANNON | SETH.HANNON@sakilacustomer.org | 596 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
590 | 591 | 1 | KENT | ARSENAULT | KENT.ARSENAULT@sakilacustomer.org | 597 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
591 | 592 | 1 | TERRANCE | ROUSH | TERRANCE.ROUSH@sakilacustomer.org | 598 | 0 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
592 | 593 | 2 | RENE | MCALISTER | RENE.MCALISTER@sakilacustomer.org | 599 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
593 | 594 | 1 | EDUARDO | HIATT | EDUARDO.HIATT@sakilacustomer.org | 600 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
594 | 595 | 1 | TERRENCE | GUNDERSON | TERRENCE.GUNDERSON@sakilacustomer.org | 601 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
595 | 596 | 1 | ENRIQUE | FORSYTHE | ENRIQUE.FORSYTHE@sakilacustomer.org | 602 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
596 | 597 | 1 | FREDDIE | DUGGAN | FREDDIE.DUGGAN@sakilacustomer.org | 603 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
597 | 598 | 1 | WADE | DELVALLE | WADE.DELVALLE@sakilacustomer.org | 604 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
598 | 599 | 2 | AUSTIN | CINTRON | AUSTIN.CINTRON@sakilacustomer.org | 605 | 1 | 2006-02-14 22:04:37.000 | 2011-09-14 18:10:44 |
599 rows × 9 columns
Congratulations! You just passed a SQL Query using pandas and Python! You're amazing! Subsequent lectures will go further into how to query with SQL, but if you already know SQL, you're good to go!