<jupyter_start><jupyter_text>Python and Interacting with DatabasesMany of your at home projects can probably be accomplished with a handful of data files like csvs and tsvs. Sometimes though you're data may be stored in some sort of relational database. For a nice tutorial on what a database is watch this video later after the lecture, https://www.youtube.com/watch?v=wR0jg0eQsZA. What We'll Accomplish in this Notebook- Introduce the concept of a relational database- Introduce SQL- Show off how to execute SQL commands with python- Build our own database- Extract data out of an existing databaseLet's Go!  Relational Databases and SQLHere's a brief treatment on relational databases. Relational databases are a way of storing complicated data sets in a concise but smart manner. Databases allow you to store disparate data that refer to the same objects in a related way. A common example is a marketplace. Markets have inventories of products that get sold to customers. So the market may have a csv-esque file of its product inventory and a separate file of each sale and a separate file recording each customer. These three data sets are tied together with key values that, for example, tie each sale to a specific item in the inventory table and a specific customer in the customer table.Databases are ubiquitous in industry, so knowing how to handle them is important. If you're serious you could learn how to program directly in `SQL`,  the Structured Query Language. This is the standard language for relational databases. `SQL` is how you can talk to a database and find the data you need. Since the focus of our course is `python` we're lucky that python offers a couple of ways to interact with databases both in the base python packages with `sqlite3` (`python`'s built in SQLite driver), and with an additional package `sqlalchemy` (the `python` `SQL` toolkit and Object Relational Mapper that gives application developers the full power and flexibility of `SQL`). If you installed python via the anacondas distribution you should have `sqlalchemy` installed. If not you'll need to install it before we the end of the notebook.   `sqlite3`We'll start with `sqlite3` and leave `sqlalchemy` to you as homework. Let's first import the package.<jupyter_code>## Run this block of code to check that you have 
## the required packages installed.
import sqlite3<jupyter_output><empty_output><jupyter_text>`sqlite3` is a `C` library built into base `python` that lets `python` to execute `SQLite` processes. All you'll need to know is that `SQLite` is essentially the Diet Coke to `SQL`'s Coca-Cola. To learn more about the differences check out this webpage https://www.geeksforgeeks.org/differences-between-sql-and-sqlite/. Constructing a DatabaseTo better understand the structure of a relational database we'll first construct one. Imagine we're running a cat store, we sell 3 products: cat food, cat treats, and cat toys. We'll want a database to keep track of who is buying what stuff from us.Let's go through the process step by step. Creating a Connection<jupyter_code>## The first step is creating a connection to the database
## This creates the connection and the database if it didn't
## already exist
conn = sqlite3.connect("cat_store.db")

## Now you can check the directory in your computer where
## you store this notebook. You should now see catstore.db
## in there.<jupyter_output><empty_output><jupyter_text>Creating a Cursor Object<jupyter_code>## Step 2 is to create a cursor object
## this allows us to execute SQL code chunks
## written as python strings
c = conn.cursor()<jupyter_output><empty_output><jupyter_text>Executing SQL CodeWe'll now go over some basic SQL code. Creating a TableThe first SQL we'll do is create a table, because our database is currently empty.<jupyter_code>## SQL code is written as python strings
## then placed inside cursor.execute()


## CREATE TABLE is SQL code
## it creates a table with the given name, here products
## in parantheses we list the columns of our table
## along with the SQL data type
## The PRIMARY KEY line sets the product_id as the
## primary key for this table
## Think of a primary key as being equivalent to a pandas dataframe index
## The primary key allows us to link entries across tables
c.execute("""CREATE TABLE products(
                    product_id int,
                    product text,
                    price real,
                    in_stock int,
                    PRIMARY KEY (product_id)
                )""")

## Now in order to commit the action to your database
## you run connection.commit().
conn.commit()

## Note only run this block ONCE EVER
## Running it multiple times will cause an error
## This is because we can't create a table that already exists 
## in the database<jupyter_output><empty_output><jupyter_text>Entering an Entry in a TableThe next SQL we'll see is how to insert an entry into our table.<jupyter_code>## INSERT INTO is the SQL code 
## it will insert whatever VALUES follows 
## into the specified table
## we'll add the cat food as product 1
## its 12.50 and we have 10 bags
c.execute("INSERT INTO products VALUES (1,'Cat Food',12.50,10)")

## Again commit this to the database.
conn.commit()

## DON'T Run this more than once.<jupyter_output><empty_output><jupyter_text>Retrieving Data From a TableNow that there is data in our table we can retrieve it.<jupyter_code>## SELECT * FROM is SQL code
## it will find all entries in the specified table
c.execute("SELECT * FROM products")

## We've now selected all data from products
## We can display them with the cursor.fetchall() method
print(c.fetchall())

## Feel free to run this block as much as you want.
## Run this block to add the final two products
c.execute("INSERT INTO products VALUES (2,'Cat Treats',5.25,20)")
c.execute("INSERT INTO products VALUES (3,'Cat Toy',2.50,30)")

conn.commit()

## Only Run ONCE
## Now we can see all three products
c.execute("SELECT * FROM products")

## We've now selected all data from products
## We can display them with the cursor.fetchall() method
print(c.fetchall())

## Feel free to run this block as much as you want.<jupyter_output><empty_output><jupyter_text>Getting Data With Logic ConditionsThe final thing we'll go over before you build a table is how to select all entries from a table that meet a certain condition.<jupyter_code>## When we use SELECT * FROM table_name
## We add a WHERE statement followed by a conditional
## let's find all the entries that cost more than $5
c.execute("SELECT * FROM products WHERE price > 5")

print(c.fetchall())
## In addition to fetchall() there is: 
## fetchone() - this reads the data you've queried one at a time, and
## fetchmany(n) - this reads the data in n sized chunks at a time
c.execute("SELECT * FROM products WHERE price > 5")

print(c.fetchone())

print()

print(c.fetchone())<jupyter_output><empty_output><jupyter_text>You CodeNow it's your turn to make a table!Since our cat store has opened we've had five customers. It would be good for us to keep track of them so we can provide them coupons in the future.Each unique customer should have a:    Customer ID    First Name    Last Name    email address    number of cats they ownThe Customer ID should be the primary key for the table.It's up to you to come up with the names of the customers, just be sure that each unique customer has a unique customer id, and be sure your code is correct before running. You don't want to accidentally enter the same customer twice!Again don't worry if you're unable to finish everything in the time alotted, do your best and you can always review this later :)<jupyter_code>## You Code





## You Code<jupyter_output><empty_output><jupyter_text>Now print out a list of all our customers. Then print out a list of the customers that have more than 2 cats.<jupyter_code>## You Code<jupyter_output><empty_output><jupyter_text>An Aside on KeysBoth `product_id` and `customer_id` serve as primary keys in the `product` and `customer` tables respectively. These keys can now be used to create foreign keys in a `purchases` table that tracks which customer buys what products throughout the life of our cat store. The foreign keys in the `purchases` table serve as a way for us to provide context by linking the purchase to the `product` and `customer` parent tables. Each purchase would have its own unique `purchase_id` as well. An Aside on Memory DatabasesYou may have been incredibly annoyed or frustrated that you couldn't keep rerunning certain chunks of code. This is somewhat unavoidable when running each process in a separate chunk in a jupyter notebook. However, if you were writing a database script and then running the script in your terminal there is a way to be able to run the script multiple times while you're debugging it. In the `conn = sqlite3.connect("name.db")` line of code replace your database name with `":memory:"`, this will instead create a new database in your computer's RAM so any changes that are made by your code are only stored when the code runs.<jupyter_code>## Closing the connection now that we're done
## When you're done with a database
## be sure to close the connection
conn.close()<jupyter_output><empty_output><jupyter_text>Getting Data From a Database into `pandas`So far we've gotten a better idea on how relational databases are constructed, and we've introduced some basic `SQL` code.While it may not be creating databases it's possible that you'll be pulling data from pre-existing databases. So for the remainder of the notebook we'll see how to get data from a database, and then turn that data into a `pandas` dataframe. Looking at Census DataWe'll examine reading in data with the `census.sqlite` database. Let's connect to it now.<jupyter_code>import pandas as pd
## conn will be our connection
conn = sqlite3.connect('census.sqlite')

## Make the cursor
c = conn.cursor()
## Let's print out the tables in the
## database

## Don't worry about this code, you'll typically know
## all the tables in a database before you start querying
c.execute('SELECT name FROM sqlite_master where type= "table"')

print(c.fetchall())<jupyter_output><empty_output><jupyter_text>We'll practice acessing data from these two tables to learn more about the census data.<jupyter_code>## We can read in an entire table
## Let's do that with the state_fact table

## This gets all rows from the state_fact table
c.execute('SELECT * FROM state_fact')

print(c.fetchall())
## Alright that is difficult to read!
## Let's turn it into a dataframe!
c.execute("SELECT * FROM state_fact")

pd.DataFrame(c.fetchall()).head()
## Much nicer!
## We can also get informative column titles like so
c.execute("SELECT * FROM state_fact")

## The data we selected from the table comes
## with a "description", which is the column labels
## we can access those with a quick list comprehension
state_fact = pd.DataFrame(c.fetchall(), columns = [x[0] for x in c.description])
## Let's examine the table's first ten entries
state_fact.head(10)
## Now we can query the table in two ways
## One way is to use SQL queries
## Lets find all the states in census_region 2

## The SQL way
c.execute('SELECT * FROM state_fact WHERE census_region == 2')

## view results as dataframe in state alphabetical order
pd.DataFrame(c.fetchall(), columns = [x[0] for x in c.description]).sort_values("name")
## Now the pandas way
## note the census_region was read in as a string not a numeric
state_fact.loc[state_fact.census_region == '2',].sort_values("name")<jupyter_output><empty_output><jupyter_text>You CodeNow you explore the `census` table in the `census` database. What are the columns?Subset the data to only output the Ohio rows. Do this in two ways, the `SQL` way and the `pandas` way.How do the average ages in Ohio in 2000 and 2008 compare?Now subset the data so that you only have people older than 80 (from all states). Which state has the most people 80 or above?Get as much done as you can in the breakout session, it's okay if you're unable to complete it all! :)<jupyter_code>## You Code





## You Code




## You Code




## You Code




## You Code




## You Code<jupyter_output><empty_output><jupyter_text>An Aside on `SELECT`So far we've been using `SELECT *`. We can also get specific columns by replacing `*` with specific column names.<jupyter_code>## Here we'll only get the state names in census_region 2
c.execute('SELECT name FROM state_fact WHERE census_region == 2')

## View as a dataframe sorted alphabetically
pd.DataFrame(c.fetchall(), columns = [x[0] for x in c.description]).sort_values('name')
# Close the connection because we're done
conn.close()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.06988824177553911, 'fraction_numerical': 0.0050369903982370534, 'mean_word_length': 4.565922032413491, 'pattern_counts': {'":': 1, '<': 57, '<?xml version=': 0, '>': 59, 'https://': 2, 'lorem ipsum': 0, 'www.': 2, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '14', 'dir': 'jupyter-structured-clean-dedup', 'id': '347102', 'license': 'ECL-2.0', 'n_tokens_mistral': 3526, 'n_tokens_neox': 3313, 'n_words': 1832, 'path': 'Lectures/Data Gathering/3. Python and Interacting with Databases.ipynb', 'repo_name': 'MattGraham1996/Erdos-Institute', 'size': '12706'}