Advanced Python: Working with Databases

Introduction

• Using databases to level up your Python applications
• What you need to know

1. Introduction to Databases in Python

• What is a database?
• Relational databases
• Non-relational databases
• Python Database API

2. Using SQLite in Python

• What is SQLite?
• Creating an SQLite database
• Manipulating records in a SQLite database
• What is SQLAlchemy?
• Setting up a virtual environment for SQLAlchemy
• Using SQLAlchemy Core with an SQLite database
• Challenge: Create an SQLite database
• Solution: Create an SQLite database

3. Using MySQL in Python

• What is MySQL?
• Creating a MySQL database
• Building tables in a MySQL database
• Connecting a Python application to a MySQL database
• Encaspulating database operations to make better applications
• Developing Pythonic applications with SQLAlchemy ORM
• Using SQLAlchemy Sessions to transact on a MySQL database
• Using SQL to import CSV data
• Leveraging SQLAlchemy and pandas to import CSV data
• Challenge: Create a MySQL database
• Solution: Create a MySQL database

4. Using PostgreSQL in Python

• What is PostgreSQL?
• Creating a PostgreSQL database
• Creating a table in Postgres using Python
• Inserting data into a Postgres database
• Interacting with a Postgres database using Python
• Pythonic Postgres interactions with SQLAlchemy Core
• Pythonic Postgres interactions with SQLAlchemy ORM
• Grouping SQL statements with stored procedures
• Creating a stored procedure in PostgreSQL
• Using Postgres stored procedures and functions in Python
• Challenge: Create a Postgres database
• Solution: Create a Postgres database

5. Developing Full-Stack Applications

• Setting up Flask in a Python application
• Creating a webpage with Flask
• Developing additional routes to enhance your application
• Instantiating a Postgres database using Python
• Connecting a database to a Pythonic Flask project
• Feeding data from a database into a Flask application
• Develop add functionality to a Flask application
• Challenge: Develop delete functionality
• Solution: Develop delete functionality

6. Developing Analysis Applications

• Introduction to pandas
• Setting up pandas and Jupyter Notebook
• Analyzing data with pandas
• Integrating SQLAlchemy with pandas

Conclusion

• Next steps