The Complete Python/PostgreSQL Course 2.0

The Complete Python/PostgreSQL Course 2.0
The Complete Python/PostgreSQL Course 2.0
English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 13.5 Hours | 4.82 GB

Take the next step in your Python development by leveraging the power of PostgreSQL and databases!

Master PostgreSQL and use it in your Python apps with this course!

Python and PostgreSQL are two of the most in-demand skills in the world. After completing this course, you’ll be confident in adding both to your resume/CV.

In addition, all the database knowledge you’ll gain in this course is easily transferable to other databases such as MySQL, Microsoft SQL Server, Amazon Redshift, Oracle, and others!

The focus of this course is on fully understanding PostgreSQL, and effectively integrating it with your Python applications. If you’re a Python developer or you use Python at all, you’re going to love this course!

You will:

  • Add data storage to your Python apps with in-memory databases, SQLite, and PostgreSQL.
  • Understand when and why you might want to use different types of databases in your applications.
  • Build a programming journal project to learn how to add a SQLite database to your application.
  • Create a movie watch-list app to explore how to improve your database design over time, as well as how to model different types of relationships.
  • Migrate the movie watch-list app to PostgreSQL to identify the differences between PostgreSQL and SQLite.
  • Build a polling app to learn about advanced data analysis with GROUP BY, PostgreSQL window functions, and nested queries.
  • Extend the polling app to work with dates and times, including how to avoid common timezone pitfalls.
  • Learn how to structure Python apps professionally, to make development easy when working with databases.
  • Explore advanced SQL and PostgreSQL concepts such as user-defined functions, stored procedures, locking, and async database connections with Python.
  • Take all your knowledge and apply it to produce data analysis reports and charts using matplotlib.

As you can see, there’s a lot to do!

But this course is a no-nonsense, no-frills course. Every lecture has been planned, crafted, and edited to be perfectly concise and to the point. That way, you can take the most from this course, quickly and easily.

Throughout the course, we have animated diagrams to explain database concepts, as well as code-along videos when we’re working with Python. But you won’t just be watching me code! I’ve carefully planned and mapped out every project and feature we add, and I encourage you to try them on your own first, before following along with me!

Also, throughout the course I provide data sets with challenges and exercises for you to practice what you’ve learned.

I’ve been teaching and helping students online for over 7 years, and this course is the culmination of my teaching experience. I know how to help you understand concepts fully and quickly, in the best way for you.

Upon completing this course you’ll be able to easily extend your Python applications with database functionality. You’ll know how to answer questions using data, and you’ll definitely be able to list PostgreSQL as one of your strongest skills.

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
Table of Contents

Introduction
1 Welcome to this course!
2 Initial setup (for newer Pythonistas)
3 Get the complete e-book here

A Full Python Refresher
4 Introduction to this section
5 If statements
6 The in keyword in Python
7 If statements with the in keyword
8 Loops in Python
9 List comprehensions in Python
10 Dictionaries
11 Destructuring variables
12 Functions in Python
13 Function arguments and parameters
14 Default parameter values
15 Access the code for this section here
16 Functions returning values
17 Lambda functions in Python
18 Dictionary comprehensions
19 Unpacking arguments
20 Unpacking keyword arguments
21 Object-Oriented Programming in Python
22 Magic methods str and repr
23 @classmethod and @staticmethod
24 Class inheritance
25 Class composition
26 Variables in Python
27 Type hinting in Python 3.5+
28 Imports in Python
29 Relative imports in Python
30 Errors in Python
31 Custom error classes
32 First-class functions
33 Simple decorators in Python
34 The ‘at’ syntax for decorators
35 Decorating functions with parameters
36 Decorators with parameters
37 String formatting in Python
38 Mutability in Python
39 Mutable default parameters (and why they’re a bad idea)
40 Getting user input
41 Writing our first Python app
42 Lists, tuples and sets
43 Advanced set operations
44 Booleans in Python

Build a Programming Journal with Python & SQL
45 Overview of the project
46 Connecting to SQLite in our app
47 What is a cursor
48 INSERT INTO add data to a table
49 INSERT INTO exercises
50 How to insert data into SQLite with Python
51 SELECT retrieve data from a table
52 SELECT exercises
53 Retrieving results from a cursor
54 WHERE search with SQL
55 WHERE exercises
56 Creating our user menu
57 DROP TABLE deleting entire tables
58 DROP TABLE exercise
59 What is a SQL injection attack
60 What is SQL
61 Using Python lists as an in-memory database
62 A SQLite data viewer
63 CREATE TABLE new tables with SQL
64 How to write comments in SQL
65 CREATE TABLE exercises
66 How to connect to a SQLite database with Python

A Movie Watchlist App with Python & SQL
67 Overview of the project
68 DELETE FROM removing rows with SQL
69 DELETE FROM exercises
70 Stage 2 watching movies
71 Relational data primary and foreign keys
72 Relational data exercise
73 Watched movies final approach
74 Stage 3 adding new watched movies
75 Auto-incrementing row IDs
76 Auto-incrementing exercise
77 JOIN access two tables at once with SQL
78 Three development stages of our project
79 Use JOINs to retrieve the movies a user has watched
80 Types of JOINs with examples
81 ORDER BY sort the returned table
82 LIMIT getting a certain number of rows
83 LIKE flexible searching
84 What is an index in SQL
85 Adding an index to our table for more efficient searching
86 Our starting code for this project
87 Queries we’ll need for the project to begin with
88 Write the database.py file
89 UPDATE changing data with SQL
90 UPDATE exercises
91 Write our user menu and functions
92 Watched movies second approach

Introduction to PostgreSQL Migrating our App
93 SQLite vs. PostgreSQL
94 How to install PostgreSQL
95 How to run and access PostgreSQL
96 psycopg2 vs psycopg2-binary
97 How to store (and not store!) sensitive information in your code
98 Psycopg2 cursors and query parameters
99 Auto-incrementing columns SEQUENCE and SERIAL in PostgreSQL
100 Our changed code, and finding differences between files

Building a Poll App & Advanced SQL
101 Overview of the project
102 How to use ORDER BY with window functions
103 How to use PARTITION with window functions
104 SQL DISTINCT and DISTINCT ON
105 The SQL HAVING clause
106 SQL VIEW virtual tables
107 How to read the PostgreSQL documentation
108 Adding type hinting to our application
109 What is ACID
110 Essential queries we’ll need for this project
111 RETURNING data from modified rows
112 Nested queries getting the latest poll
113 SQL built-in functions
114 GROUP BY and calculating vote percentages
115 PostgreSQL window functions
116 PostgreSQL window functions (part 2)

Working with dates and times
117 How to separate our database entities into models
118 How to calculate new dates with timedelta
119 How to handle timezones with pytz
120 How to save dates to PostgreSQL
121 Add the vote date to our polling app
122 Creating our Option model class
123 Changes needed in database.py
124 Changes needed in app.py
125 What is connection pooling
126 How to create a connection pool with psycopg2
127 Reduce pooling duplication with context managers
128 Reduce cursor creation duplication with context managers
129 The Python datetime module

Python and Advanced PostgreSQL with psycopg2
130 Composite primary keys
131 User-defined functions in PostgreSQL
132 Composite types and sets in functions
133 Stored procedures in PostgreSQL
134 Connections vs transactions in psycopg2
135 Locking in PostgreSQL
136 Asynchronous psycopg2
137 SQL string composition with psycopg2

Charting data from our tables using matplotlib
138 Overview creating graphs from poll data
139 How to adjust the size of a matplotlib plot
140 How to adjust the x axis tick labels so they fit in the screen
141 How to draw a stacked bar chart with matplotlib
142 How to create a legend from your graphed data
143 How to export an image with matplotlib
144 How to create one document with multiple matplotlib plots
145 How to create a custom legend with matplotlib
146 How to install matplotlib
147 How to draw a line graph with matplotlib
148 Matplotlib backends
149 Matplotlib figures, axes, and plots
150 The Object-Oriented Approach with matplotlib
151 How to add multiple subplots to a figure
152 How to draw a pie chart with matplotlib
153 How to draw a bar chart with matplotlib