In this module, you’ll receive an introduction to three new tools: SQLite, SQLAlchemy, and Flask. You’ll use these tools to build on your knowledge of SQL database structures and querying methods. You’ll also write and run Python code in a Jupyter notebook and create graphs by using Python.

9.1 Introduction to SQLAlchemy

Overview

This lesson will combine what we learned last week about SQL with our favorite programming language: Python. Before getting started, you’ll need to install SQLite. SQLite is a SQL dialect that shares much of its syntax with PostgreSQL, but it’s entirely serverless.The lesson will first introduce you to SQLAlchemy, a Python library that’s designed to work with SQL databases. You’ll then complete an activity in which you’ll analyze weather data by using SQLAlchemy. After another activity in which you’ll create new DataFrames based on United States census data, your instructor will lead you through a crash course in object-oriented programming (OOP).

What You’ll Learn

By the end of this lesson, you will be able to:

  • Connect to a SQL database by using SQLAlchemy.

  • Perform basic SQL queries by using engine.execute().

  • Create Python classes and objects.

  • Perform create, read, update, delete (CRUD) operations on data in a SQL database by using the SQLAlchemy object-relational mapper (ORM).

9.1.1 SQL Alchemy

SQLAlchemy is a Python library that allows users to access and manage SQL databases. SQLAlchemy provides a powerful interface to interact with relational databases.

SQLAlchemy bridges the differences amon various SQL dialects. A single script that uses SQLAlchemy can perform the same query across the different SQL dialects such as, PostgreSQL, SQLite, MySQL, etc.

To help us begin to connect to a database we will use the function create_engine() from sqlalchemy to connect to the database.

Code 
from sqlalchemy import create_engine, text

# Path too sqlite file
database_path = "../data/sql-files/Census_Data.sqlite"

# Create engine to talk to the database
engine = create_engine(f"sqlite:///{database_path}")

The engine above creates a Dialect object that is tailored towards SQLite and Pool object that will establish a DBAPI connection. So far we have not established the connection to the database. The connection happens when we make a function call to engine.connect() or engine.begin().

First, let’s use engine.execute() to perform a SQL query. Inside of engine.execute() we can pass a SQL query in the form of a string

Code 
# Query the the first five records the databse
data = engine.execute("SELECT * FROM Census_Data LIMIT 5")

# Print the results
for i in data:
    print(i)
('HOUSTON, TX', 'HOUSTON', 'TX', 3061887, 1775897, 684416, 11586, 230549, 1368287, 54180, 387082, 62520, 100014, 349920, 138882, 42491, 27737, 633609, 1593803, 122895, 67484, 22637, 33.43958333, 32.55, 34.36354167, 56206.5, 32239.52083, 956.7083333, 178233.6842, 29.77573444, -95.41454828)
('CHICAGO, IL', 'CHICAGO', 'IL', 2702091, 1318869, 843633, 7554, 161478, 785374, 32800, 370569, 50202, 100972, 385664, 178511, 54636, 26956, 588639, 1439118, 173087, 45864, 18209, 34.52678571, 33.79821429, 35.14107143, 57735.96429, 38730.83929, 1119.928571, 264739.2857, 41.86783754, -87.67343993)
('BROOKLYN, NY', 'BROOKLYN', 'NY', 2595259, 1126111, 870465, 8744, 297890, 509243, 48934, 389177, 65899, 107313, 348413, 164826, 39328, 17446, 597404, 1297832, 129667, 33644, 14845, 35.17567568, 33.36756757, 36.57837838, 51469.18919, 28309.67568, 1261.783784, 605743.2432, 40.65280511, -73.9565277)
('LOS ANGELES, CA', 'LOS ANGELES', 'CA', 2426413, 1068202, 324842, 15949, 273829, 1292382, 62684, 280325, 22924, 83153, 312270, 104024, 40600, 19436, 626981, 1273305, 141105, 26989, 12329, 35.33548387, 34.53548387, 36.06129032, 47494.58333, 30073.19355, 1201.766667, 557115.0, 34.04220912, -118.3034679)
('MIAMI, FL', 'MIAMI', 'FL', 1820704, 1361009, 363514, 2250, 33144, 1162711, 27137, 322521, 30827, 113990, 231241, 79888, 36586, 15913, 338765, 935326, 91584, 20714, 6969, 38.74074074, 37.12037037, 40.26296296, 51232.90741, 25949.35185, 1260.833333, 243279.6296, 25.7602677, -80.2985105)

Now let’s establish a connection to get our data and we can also use the pandas library to work with our data

Code 
import pandas as pd

connection = engine.connect()

df = pd.read_sql("SELECT * FROM Census_Data", connection)

df.head()
CityState city state Population White Population Black Population Native American Population Asian Population Hispanic Population Education None ... Employment Female Computer Engineering Median Age Median Male Age Median Female Age Household Income Income Per Capita Median Gross Rent Median Home Value lat lng
0 HOUSTON, TX HOUSTON TX 3061887 1775897 684416 11586 230549 1368287 54180 ... 22637 33.439583 32.550000 34.363542 56206.50000 32239.52083 956.708333 178233.6842 29.775734 -95.414548
1 CHICAGO, IL CHICAGO IL 2702091 1318869 843633 7554 161478 785374 32800 ... 18209 34.526786 33.798214 35.141071 57735.96429 38730.83929 1119.928571 264739.2857 41.867838 -87.673440
2 BROOKLYN, NY BROOKLYN NY 2595259 1126111 870465 8744 297890 509243 48934 ... 14845 35.175676 33.367568 36.578378 51469.18919 28309.67568 1261.783784 605743.2432 40.652805 -73.956528
3 LOS ANGELES, CA LOS ANGELES CA 2426413 1068202 324842 15949 273829 1292382 62684 ... 12329 35.335484 34.535484 36.061290 47494.58333 30073.19355 1201.766667 557115.0000 34.042209 -118.303468
4 MIAMI, FL MIAMI FL 1820704 1361009 363514 2250 33144 1162711 27137 ... 6969 38.740741 37.120370 40.262963 51232.90741 25949.35185 1260.833333 243279.6296 25.760268 -80.298511

5 rows × 31 columns

9.1.2 Python Classes

Another useful aspect of SQLAlchemy is that it can also update a SQL database using Python classes.

Classes are essentially blueprints for Python objects; they allow developers to create organized variables with keys, values, and methods on the fly.

For example we can have the following

Code 
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Float

# Sets an object to use the default declarative base in SQLAlchemy
Base = declarative_base()

# Creates Classes which will serve as the anchor points for our tables
class Dog(Base):
    __tablename__ = "dog"
    id = Column(Integer, primary_key = True)
    name = Column(String(255))
    color = Column(String(255))
    age = Column(Integer)

class Cat(Base):
    __tablename__ = "cat"
    id = Column(Integer, primary_key = True)
    name = Column(String(255))
    color = Column(String(255))
    age = Column(Integer)

# Create a specific instance of the Dog and Cat classes
dog = Dog(name = "Charles", color = "brown", age = 4)
cat = Cat(name = "Lucy", color = "gray", age = 7)

print(f"The dog's name is {dog.name}. He's a {dog.color} dog and he's {dog.age} years old.")
print(f"The cat's name is {cat.name}. She's a {cat.color} cat and she's {cat.age} years old.")
The dog's name is Charles. He's a brown dog and he's 4 years old.
The cat's name is Lucy. She's a gray cat and she's 7 years old.

9.1.3 SQLAlchemy’s Object-Relational Mapper (ORM)

9.2 Advanced Usage of the SQLAlchemy ORM

Overview

This lesson will introduce you to the finer details of working with the SQLAlchemy ORM. You’ll learn how to create complex queries, update rows, perform joins, and use ORM methods to run queries.

What You’ll Learn

By the end of this lesson, you will be able to:

  • Create classes that model tables by using the SQLAlchemy ORM.

  • Perform CRUD operations on databases by using the SQLAlchemy ORM.

  • Reflect existing databases.

  • Review the table names in a database by using SQLAlchemy inspection.

  • Plot query results that are retrieved by using SQLAlchemy.

  • Run a t-test to validate differences in means.

9.3 Introduction to Flask and Serving Data with APIs

Overview

In today’s lesson, you’ll learn the fundamentals of both the web and client-server architecture. This will include using Flask to create a database-backed server and to design and implement API endpoints.

What You’ll Learn

By the end of this lesson, you will be able to:

  • Create and run a server by using Flask.

  • Define endpoints by using a Flask decorator.

  • Extract query-variable path values from GET requests.

  • Run database queries on behalf of the client by using variable paths.

  • Return JSONified query results from API endpoints.