English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 201 lectures (17h 1m) | 6.26 GB
Build 3 Python Projects In 3 Days Using SQLite, MySQL, and PostgreSQL
Welcome to the best resource online for learning to work with SQL in python.
Python and SQL are two of the most in-demand skills in any data-related or data-adjacent role today.
In this course, we’ll use SQLite, MySQL and PostreSQL to build three projects of increasing complexity that will give you a solid foundation in using SQL in python applications.
Over 17 hours and 50 coding assignments, you will gain practical mastery of, not only SQL and python, but also tens of programming and computer science concepts.
In building these projects, you won’t be copy/pasting code. Instead, we will be writing code from scratch, and we will be writing lots of it.
This “forced” practice will help solidify your understanding of the concepts and techniques we cover. Each assignment will be followed by a detailed solution and explanation.
By the end of the course, after 3 immersive days, SQL and python will be on your resume!
Structure & Curriculum
Day 1: SQLite
- understanding connections, cursors, transactions in sqlite3
- parameterizing and executing queries
- understanding and preventing SQL injections
- introduction to DBAPI
- building the Freight Manager (9 coding assignments)
Day 2: MySQL
- revisiting connections, cursors, transactions with mysql-connector
- building prepared statements
- exploring dictionary, buffered and namedtuple cursors
- building the School Registrar (19 coding assignments)
Day 3: PostgreSQL
- revisiting connections, cursors, transactions with psycopg2
- generating dynamic SQL from python code
- building the Guestbook API (22 coding assignments)
- with object oriented programming, password hashing, and more
This is the ultimate, immersive introduction to two of the most valuable skills today.
What you’ll learn
- Work with SQL databases confidently in python applications
- Build and deploy a web-based API using FastAPI and PostgreSQL
- Understand cursors, transactions, connections, and query execution in depth
- Explore more advanced topics around dynamic SQL generation, prepared statements, and non-tuple cursors
Table of Contents
Day 0 – Two Minute Welcome
1 min Introduction
2 All Course Resources – Lectures + Project Challenges
Day 1 – Freight Manager
3 Section Overview
4 Lecture Resources
5 Introduction to SQLIite
6 sqlite3 In Python
7 DBAPI 2.0
8 Connections And Cursors
9 Inserts
10 DML vs DDL (and DCL and DQL)
11 Fetching
12 Column Names Would Be Nice
13 The Transaction Lifecycle
14 Parameterized Statements
15 A Different Flavor
16 Various Ways To Execute
17 Database Dump
18 Context Managed Connections
19 Let’s Build!
20 Project Resources – All Solution Challenges
21 Creating A Virtual Environment
22 Challenge 1 – Interface
23 The Interface
24 Sketching Out The Basics
25 Challenge 2 – DDL
26 Executing the DDL
27 Foreign Key Gotcha
28 The Add Box Menu
29 Challenge 3 – Add Box To Database
30 Add Box Helper
31 Challenge 4 – Displaying Box Types
32 Displaying All Boxes
33 A Prettier Display
34 Challenge 5 – Loading Boxes With Validation
35 Load Box Menu
36 The Containers Database View
37 Container Constraints
38 The Final Stretch
39 Quick Note
40 Challenge 6 – Seed Data
41 Seeding Some Boxes
42 Challenge 7 – Containers
43 Displaying Containers
44 Business Summary
45 The Bottom Line
46 Challenge 9 – Configurable Constants
47 Refactoring Configuration
Day 2 – Course Registrar
48 Section Overview
49 Lecture Resources
50 Welcome To MySQL
51 Local Vs Cloud MySQL
52 Connecting From Python
53 URL Parsing
54 Cursors Again
55 Context Managers Revisited
56 Parameterized Inserts
57 Prepared Statements
58 Let’s Fetch
59 Buffering
60 Dict Cursors
61 A Named Alternative
62 executescript() Please
63 Best Multi With Autocommit
64 Let’s Build!
65 Project Resources – All Challenge Solutions
66 Setting Up
67 The Interface
68 Challenge 1 – Connection Helper
69 Writing The Helper
70 Challenge 2 – Prepare The DDL
71 Wiring Up The DDL
72 Challenge 3 – The Reset Command
73 Resetting The Database
74 Challenge 4 – Add Student
75 Adding New Students
76 Challenge 5 – Add Course
77 Adding New Courses
78 Challenge 6 – Prerequisites
79 Prereq DDL And Command
80 Addressing A Small Inconsistency
81 Challenge 7 – Querying
82 The Query Helper
83 Challenge 8 – Seeding Data
84 Initializing Data
85 —no-with-data
86 Challenge 9 – Show Prerequisites For Course
87 Displaying Prereqs
88 Pretty Tables
89 Challenge 10 – Show Students And Courses
90 Displaying Students And Courses
91 Challenge 11 – Course Enrollment Part I
92 Naive Enrollment
93 Challenge 12 – Grading
94 The Grade Command
95 Challenge 13 – Course Enrollment Part II
96 Prereq-aware Enrollment
97 Hiding Internals, Colorizing Externals
98 Verbose Query Execution
99 Challenge 14 – Unenroll
100 Unenrolling Students
101 Challenge 15 – Courses By Student
102 Current Enrollment Report
103 Challenge 16 – Transcripts
104 Generating Transcripts
105 Challenge 17 – Letter Grades
106 Approach
107 Implementation
108 Challenge 18 – Most Popular Courses
109 Most Enrolled Courses
110 Challenge 19 – Top Students
111 Top Performers
Day 3 – Guestbook API
112 Section Overview
113 Lecture Resources
114 PostgreSQL
115 The Precompiled Alternative
116 Database Server
117 Connecting
118 Query Execution
119 Parametrization
120 Dynamic Query Generation
121 Dynamic Queries Part II
122 Quick Refactor
123 Let’s Build!
124 Project Resources – All Challenge Solutions
125 New Virtual Env
126 The Web-based API
127 Automatic Documentation
128 Our Database
129 Object-Oriented Helper Part I
130 Object-Oriented Helper Part II
131 Connection URL As Env Variable
132 Dependency Injection
133 Challenge 1 – DDL
134 Creating Our First Tables
135 The Register Endpoint
136 Data Validation With Pydantic
137 User Registration Data Validation
138 Hashing Passwords
139 Registration With Hashed Passwords
140 Challenge 2 – Persisting To Database
141 Inserting Users
142 Adding The UNIQUE Constraint
143 HTTPExceptions And Status Codes
144 Challenge 3 – Object-Oriented Refactor
145 OOP Write Returning Id
146 Challenge 4 – Registration Tokens
147 Saving Tokens
148 The Activate Endpoint + Cleanup
149 Challenge 5 – Get One
150 Implementing Database get_one()
151 Challenge 6 – Get One As Special Case Of Get
152 Implementing A Generalized Get
153 Challenge 7 – Update
154 Implementing Database .update()
155 Challenge 8 – Endpoint Integration
156 The Activate Flow
157 Challenge 9 – Activate Once
158 Enforcing One Time Activation
159 Messages Prep
160 Form vs Query Params
161 Code Reorg Using Routers
162 Challenge 10 – Messages DDL + Path
163 Persisting Messages
164 Fixing A Typo
165 Authentication
166 Authenticated POST messages
167 Fixing An Old Bug
168 Challenge 11 – Updating Messages
169 PATCH messages
170 Challenge 12 – Get Message By Id
171 Getting Public + Own Messages
172 Challenge 13 – Get All Messages
173 Getting Public And Own Messages
174 or_where Refactor
175 Challenge 14 – Database Delete
176 Implementing .delete()
177 Refactoring Database Methods
178 Challenge 15 – Integrating Delete
179 Deleting Messages From The Interface
180 Challenge 16 – New Get Contains
181 Implementing Get With Like Operator
182 Challenge 17 – Get With Contains
183 Refactoring Get
184 Challenge 18 – Search Messages
185 Implementing Message Search
186 A More Coherent Database .get()
187 The Final .get() Refactor
188 Challenge 19 – Upvotes
189 Upvotes DDL
190 Challenge 20 – Upvoting
191 The Upvote Interface
192 Quick Bug Fix
193 Challenge 21 – Most Popular Messages
194 Most Upvoted
195 Preparing To Send Emails
196 Sending Generated Tokens
197 Two Little Tweaks
198 Preparing To Deploy
199 Deployment
Thank you!
200 Outro + Sqlalchemy Musings
201 My Other Courses
Resolve the captcha to access the links!