Section Overview

Introduction to SQLIite

sqlite3 In Python

DBAPI 2.0

Connections And Cursors

Inserts

DML vs DDL (and DCL and DQL)

Fetching

The Transaction Lifecycle

Parameterized Statements

A Different Flavor

Various Ways To Execute

Database Dump

Column Names Would Be Nice

Context Managed Connections

Let's Build!

Creating A Virtual Environment

Challenge 1 - Interface

Sketching Out The Basics

The Interface

Challenge 2 - DDL

Executing the DDL

Challenge 3 - Add Box To Database

The Add Box Menu

Foreign Key Gotcha

Challenge 4 - Displaying Box Types

Add Box Helper

A Prettier Display

Challenge 5 - Loading Boxes With Validation

Displaying All Boxes

The Containers Database View

Load Box Menu

Container Constraints

The Final Stretch

Challenge 6 - Seed Data

Challenge 7 - Containers

Seeding Some Boxes

Displaying Containers

Challenge 9 - Configurable Constants

Business Summary

The Bottom Line

Refactoring Configuration

min Introduction

Welcome To MySQL

Local Vs Cloud MySQL

Connecting From Python

URL Parsing

Cursors Again

Parameterized Inserts

Let's Fetch

Buffering

Context Managers Revisited

Dict Cursors

Prepared Statements

A Named Alternative

Setting Up

executescript() Please

Best Multi With Autocommit

Challenge 1 - Connection Helper

The Interface

Writing The Helper

Challenge 2 - Prepare The DDL

Challenge 3 - The Reset Command

Wiring Up The DDL

Challenge 4 - Add Student

Resetting The Database

Challenge 5 - Add Course

Adding New Students

Adding New Courses

Challenge 6 - Prerequisites

Challenge 7 - Querying

Addressing A Small Inconsistency

Challenge 8 - Seeding Data

The Query Helper

Challenge 9 - Show Prerequisites For Course

—no-with-data

Displaying Prereqs

Initializing Data

Prereq DDL And Command

Pretty Tables

Challenge 10 - Show Students And Courses

Challenge 11 - Course Enrollment Part I

Displaying Students And Courses

Challenge 12 - Grading

The Grade Command

Naive Enrollment

Challenge 13 - Course Enrollment Part II

Verbose Query Execution

Challenge 14 - Unenroll

Hiding Internals, Colorizing Externals

Challenge 15 - Courses By Student

Unenrolling Students

Current Enrollment Report

Challenge 16 - Transcripts

Challenge 17 - Letter Grades

Approach

Implementation

Challenge 18 - Most Popular Courses

Prereq-aware Enrollment

Generating Transcripts

Challenge 19 - Top Students

Top Performers

Most Enrolled Courses

The Precompiled Alternative

PostgreSQL

Database Server

Connecting

Query Execution

Parametrization

Dynamic Query Generation

Let's Build!

Quick Refactor

Dynamic Queries Part II

New Virtual Env

The Web-based API

Automatic Documentation

Object-Oriented Helper Part I

Our Database

Object-Oriented Helper Part II

Challenge 1 - DDL

Dependency Injection

Creating Our First Tables

The Register Endpoint

User Registration Data Validation

Data Validation With Pydantic

Registration With Hashed Passwords

Challenge 2 - Persisting To Database

Inserting Users

Adding The UNIQUE Constraint

Hashing Passwords

HTTPExceptions And Status Codes

Challenge 3 - Object-Oriented Refactor

Challenge 4 - Registration Tokens

Saving Tokens

OOP Write Returning Id

The Activate Endpoint + Cleanup

Challenge 5 - Get One

Challenge 6 - Get One As Special Case Of Get

Challenge 7 - Update

Implementing A Generalized Get

Challenge 8 - Endpoint Integration

Implementing Database get one()

Challenge 9 - Activate Once

Implementing Database .update()

The Activate Flow

Enforcing One Time Activation

Form vs Query Params

Challenge 10 - Messages DDL + Path

Messages Prep

Persisting Messages

Fixing A Typo

Code Reorg Using Routers

Challenge 11 - Updating Messages

Fixing An Old Bug

Authenticated POST messages

Challenge 12 - Get Message By Id

Authentication

PATCH messages

Challenge 13 - Get All Messages

Getting Public + Own Messages

Getting Public And Own Messages

Challenge 14 - Database Delete

Implementing .delete()

Challenge 15 - Integrating Delete

or where Refactor

Refactoring Database Methods

Challenge 16 - New Get Contains

Deleting Messages From The Interface

Implementing Get With Like Operator

Challenge 18 - Search Messages

Challenge 17 - Get With Contains

Refactoring Get

Challenge 19 - Upvotes

A More Coherent Database .get()

Implementing Message Search

The Final .get() Refactor

Upvotes DDL

Challenge 20 - Upvoting

Quick Bug Fix

Challenge 21 - Most Popular Messages

The Upvote Interface

Most Upvoted

Preparing To Send Emails

Two Little Tweaks

Preparing To Deploy

Sending Generated Tokens

Deployment

Outro + Sqlalchemy Musings