Advanced Python: Working with Databases (2020)

Course details

To create functional and useful Python applications, you need a database. Databases allow you to store data from user sessions, track inventory, make recommendations, and more. However, Python is compatible with many options: SQLite, MySQL, and PostgreSQL, among others. Selecting the right database is a skill that advanced developers are expected to master. This course provides an excellent primer, comparing the different types of databases that can be connected through the Python Database API. Instructor Kathryn Hodge teaches the differences between SQLite, MySQL, and PostgreSQL and shows how to use the ORM tool SQLAlchemy to query a database. The final chapters put your knowledge to practical use in two hands-on projects: developing a full-stack application with Python, PostgreSQL, and Flask and creating a data analysis app with pandas and Jupyter Notebook. By the end, you should feel comfortable creating and using databases and be able to decide which Python database is right for you.
Topics include:
What is a database?
Relational vs. nonrelational databases
Creating a SQLite database
Editing records in SQLite
Creating a MySQL database
Encapsulating database operations
Creating a PostgreSQL database
Interacting with databases using SQLAlchemy
Creating a stored procedure
Developing full-stack apps with Python and Flask
Developing analysis apps with pandas and SQLAlchemy

Concepts

0. Introduction

01 - Using databases to level up your Python applications
02 - What you need to know

1. Introduction to Databases in Python

03 - What is a database
04 - Relational databases
05 - Non-relational databases
06 - Python Database API

2. Using SQLite in Python

07 - What is SQLite
08 - Creating an SQLite database
09 - Manipulating records in a SQLite database
10 - What is SQLAlchemy
11 - Setting up a virtual environment for SQLAlchemy
12 - Using SQLAlchemy Core with an SQLite database
13 - Challenge - Create an SQLite database
14 - Solution - Create an SQLite database

3. Using MySQL in Python

15 - What is MySQL
16 - Creating a MySQL database
17 - Building tables in a MySQL database
18 - Connecting a Python application to a MySQL database
19 - Encaspulating database operations to make better applications
20 - Developing Pythonic applications with SQLAlchemy ORM
21 - Using SQLAlchemy Sessions to transact on a MySQL database
22 - Using SQL to import CSV data
23 - Leveraging SQLAlchemy and pandas to import CSV data
24 - Challenge - Create a MySQL database
25 - Solution - Create a MySQL database

4. Using PostgreSQL in Python

26 - What is PostgreSQL
27 - Creating a PostgreSQL database
28 - Creating a table in Postgres using Python
29 - Inserting data into a Postgres database
30 - Interacting with a Postgres database using Python
31 - Pythonic Postgres interactions with SQLAlchemy Core
32 - Pythonic Postgres interactions with SQLAlchemy ORM
33 - Grouping SQL statements with stored procedures
34 - Creating a stored procedure in PostgreSQL
35 - Using Postgres stored procedures and functions in Python
36 - Challenge - Create a Postgres database
37 - Solution - Create a Postgres database

5. Developing Full-Stack Applications

38 - Setting up Flask in a Python application
39 - Creating a webpage with Flask
40 - Developing additional routes to enhance your application
41 - Instantiating a Postgres database using Python
42 - Connecting a database to a Pythonic Flask project
43 - Feeding data from a database into a Flask application
44 - Develop add functionality to a Flask application
45 - Challenge - Develop delete functionality
46 - Solution - Develop delete functionality

6. Developing Analysis Applications

47 - Introduction to pandas
48 - Setting up pandas and Jupyter Notebook
49 - Analyzing data with pandas
50 - Integrating SQLAlchemy with pandas

Conclusion

51 - Next steps