starter-code-17.ipynb

Kernel: Python 2

import pandas as pd
from pandas.io import sql

Pandas can be used to connect to most relational databases. In this demonstration, we will create and connect to a SQLite database. SQLite creates portable SQL databases saved in a single file. These databases are stored in a very efficient manner and allow fast querying, making them ideal for small databases or databases that need to be moved across machines.

If you are looking to start using a database without the setup of mysql or postgres, SQLite is a good start.

import sqlite3

conn = sqlite3.connect('dat-test.db')

Let's return to the Rossmann sales data and load that into the database.

Data is moved to the database through the to_sql command, similar to the to_csv command.

to_sql takes as arugments: - name, the table name to create - con, a connection to a database - index, whether to input the index column - schema, if we want to write a custom schema for the new table - if_exists, what to do if the table already exists. We can overwrite it, add to it, or fail

data = pd.read_csv('../../../lesson-15/assets/dataset/rossmann.csv', low_memory=False)
data.head()

data.to_sql('rossmann_sales',
            con=conn,
            if_exists='replace',
            index=False)

sql.read_sql('select * from rossmann_sales limit 10', con=conn)

CHECK: Load the Rossmann Store metadata in rossmann-stores.csv and create a table into the database from it

# TODO

CHECK: (SELECT) Have the students write a query that returns the Store, Date and Customers

# TODO

CHECK: (WHERE) Have the students write a query that returns the Store, Date and Customers for when the stores were open and running a promotion

# TODO

CHECK: (GROUP BY) Have the students write a query that returns the total sales on promotion days.

# TODO

Exercises

1. Load the Walmart sales and store features data

2. Create a table for each of those datasets

3. Select the store, date and fuel price on days it was over 90 degrees

4. Select the store, date and weekly sales and temperature

5. What were average sales on holiday vs. non-holiday sales

6. What were average sales on holiday vs. non-holiday sales when the temperature was below 32 degrees