Duration
5 days
What you'll learn
- How to leverage the power of databases (in-memory, SQLite, and PostgreSQL) to upgrade your Python applications
- Prevent common pitfalls: avoid SQL injection attacks, store database credentials securely, and optimize the performance of your applications
- Understand how databases work and how to take advantage of their features from the ground up, by building multiple projects
- This is a no-nonsense, no-frills course on fully mastering PostgreSQL and how to use it effective within and outside of your Python apps
- Learn how to work with dates and times effectively in Python applications
- Produce engaging charts, graphs, and reports using database data
Target Audience
- Beginner Python developers who want to take the next step in upgrading their applications by leveraging the power of databases.
- Any Python developer who is not familiar with databases and wants to fully understand their choices before using one in their applications
- Business analysis and data scientists who want to create larger applications or interact with larger data sets through a database
Introduction
A Full Python Refresher
- Variables in Python
- String formatting in Python
- Getting user input
- Writing our first Python app
- Lists, tuples and sets
- Advanced set operations
- Booleans in Python
- If statements
- The "in" keyword in Python
- If statements with the "in" keyword
- Loops in Python
- List comprehensions in Python
- Dictionaries
- Destructuring variables
- Functions in Python
- Function arguments and parameters
- Default parameter values
- Functions returning values
- Lambda functions in Python
- Dictionary comprehensions
- Unpacking arguments
- Unpacking keyword arguments
- Object-Oriented Programming in Python
- Magic methods: _str_ and _repr_
- @classmethod and @staticmethod
- Class inheritance
- Class composition
- Type hinting in Python 3.5+
- Imports in Python
- Relative imports in Python
- Errors in Python
- Custom error classes
- First-class functions
- Simple decorators in Python
- The 'at' syntax for decorators
- Decorating functions with parameters
- Decorators with parameters
- Mutability in Python
- Mutable default parameters (and why they're a bad idea)
Build a Programming Journal with Python & SQL
- Overview of the project
- Creating our user menu
- What is SQL
- Using Python lists as an in-memory database
- A SQLite data viewer
- CREATE TABLE: new tables with SQL
- How to write comments in SQL
- CREATE TABLE exercises
- How to connect to a SQLite database with Python
- Connecting to SQLite in our app
- What is a cursor?
- INSERT INTO: add data to a table
- INSERT INTO exercises
- How to insert data into SQLite with Python
- SELECT: retrieve data from a table
- SELECT exercises
- Retrieving results from a cursor
- WHERE: search with SQL
- WHERE exercises
- DROP TABLE: deleting entire tables
- DROP TABLE exercise
- What is a SQL injection attack?
A Movie Watchlist App with Python & SQL
- Overview of the project
- Three development stages of our project
- Our starting code for this project
- Queries we'll need for the project to begin with
- Write the database.py file
- UPDATE: changing data with SQL
- UPDATE exercises
- Write our user menu and functions
- Watched movies: second approach
- DELETE FROM: removing rows with SQL
- DELETE FROM exercises
- Stage 2: watching movies
- Relational data: primary and foreign keys
- Relational data exercise
- Watched movies: final approach
- Stage 3: adding new watched movies
- Auto-incrementing row IDs
- Auto-incrementing exercise
- JOIN: access two tables at once with SQL
- Use JOINs to retrieve the movies a user has watched
- Types of JOINs with examples
- ORDER BY: sort the returned table
- LIMIT: getting a certain number of rows
- LIKE: flexible searching
- What is an index in SQL?
- Adding an index to our table for more efficient searching
Introduction to PostgreSQL: Migrating our App
- SQLite vs. PostgreSQL
- How to install PostgreSQL
- How to run and access PostgreSQL
- psycopg2 vs psycopg2-binary
- How to store (and not store!) sensitive information in your code
- Psycopg2 cursors and query parameters
- Auto-incrementing columns: SEQUENCE and SERIAL in PostgreSQL
- Our changed code, and finding differences between files
- Overview of the project
- What is ACID?
- Essential queries we'll need for this project
- RETURNING data from modified rows
- Nested queries: getting the latest poll
- SQL built-in functions
- GROUP BY and calculating vote percentages
- PostgreSQL window functions
- PostgreSQL window functions (part 2)
- How to use ORDER BY with window functions
- How to use PARTITION with window functions
- SQL DISTINCT and DISTINCT ON
- The SQL HAVING clause
- SQL VIEW: virtual tables
- How to read the PostgreSQL documentation
- Adding type hinting to our application
Working with dates and times
- How to separate our database entities into models
- Creating our Option model class
- Changes needed in database.py
- Changes needed in app.py
- What is connection pooling?
- How to create a connection pool with psycopg2
- Reduce pooling duplication with context managers
- Reduce cursor creation duplication with context managers
- The Python datetime module
- How to calculate new dates with timedelta
- How to handle timezones with pytz
- How to save dates to PostgreSQL
- Add the vote date to our polling app
Python and Advanced PostgreSQL with psycopg2
- Composite primary keys
- User-defined functions in PostgreSQL
- Composite types and sets in functions
- Stored procedures in PostgreSQL
- Connections vs transactions in psycopg2
- Locking in PostgreSQL
- Asynchronous psycopg2
- SQL string composition with psycopg2
Charting data from our tables using matplotlib
- Overview: creating graphs from poll data
- How to install matplotlib
- How to draw a line graph with matplotlib
- Matplotlib backends
- Matplotlib figures, axes, and plots
- The Object-Oriented Approach with matplotlib
- How to add multiple subplots to a figure
- How to draw a pie chart with matplotlib
- How to draw a bar chart with matplotlib
- How to adjust the size of a matplotlib plot
- How to adjust the x axis tick labels so they fit in the screen
- How to draw a stacked bar chart with matplotlib
- How to create a legend from your graphed data
- How to export an image with matplotlib
- How to create one document with multiple matplotlib plots
- How to create a custom legend with matplotlib
Programme scope, delivery arrangements and suitability are confirmed during enquiry.