English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 153 lectures (13h 39m) | 2.57 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.
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 Access the code for this section here
6 Variables in Python
7 String formatting in Python
8 Getting user input
9 Writing our first Python app
10 Lists, tuples and sets
11 Advanced set operations
12 Booleans in Python
13 If statements
14 The in keyword in Python
15 If statements with the in keyword
16 Loops in Python
17 List comprehensions in Python
18 Dictionaries
19 Destructuring variables
20 Functions in Python
21 Function arguments and parameters
22 Default parameter values
23 Functions returning values
24 Lambda functions in Python
25 Dictionary comprehensions
26 Unpacking arguments
27 Unpacking keyword arguments
28 Object-Oriented Programming in Python
29 Magic methods _str_ and _repr_
30 @classmethod and @staticmethod
31 Class inheritance
32 Class composition
33 Type hinting in Python 3.5+
34 Imports in Python
35 Relative imports in Python
36 Errors in Python
37 Custom error classes
38 First-class functions
39 Simple decorators in Python
40 The ‘at’ syntax for decorators
41 Decorating functions with parameters
42 Decorators with parameters
43 Mutability in Python
44 Mutable default parameters (and why they’re a bad idea)
Build a Programming Journal with Python & SQL
45 Overview of the project
46 Creating our user menu
47 What is SQL
48 Using Python lists as an in-memory database
49 A SQLite data viewer
50 CREATE TABLE new tables with SQL
51 How to write comments in SQL
52 CREATE TABLE exercises
53 How to connect to a SQLite database with Python
54 Connecting to SQLite in our app
55 What is a cursor
56 INSERT INTO add data to a table
57 INSERT INTO exercises
58 How to insert data into SQLite with Python
59 SELECT retrieve data from a table
60 SELECT exercises
61 Retrieving results from a cursor
62 WHERE search with SQL
63 WHERE exercises
64 DROP TABLE deleting entire tables
65 DROP TABLE exercise
66 What is a SQL injection attack
A Movie Watchlist App with Python & SQL
67 Overview of the project
68 Three development stages of our project
69 Our starting code for this project
70 Queries we’ll need for the project to begin with
71 Write the database.py file
72 UPDATE changing data with SQL
73 UPDATE exercises
74 Write our user menu and functions
75 Watched movies second approach
76 DELETE FROM removing rows with SQL
77 DELETE FROM exercises
78 Stage 2 watching movies
79 Relational data primary and foreign keys
80 Relational data exercise
81 Watched movies final approach
82 Stage 3 adding new watched movies
83 Auto-incrementing row IDs
84 Auto-incrementing exercise
85 JOIN access two tables at once with SQL
86 Use JOINs to retrieve the movies a user has watched
87 Types of JOINs with examples
88 ORDER BY sort the returned table
89 LIMIT getting a certain number of rows
90 LIKE flexible searching
91 What is an index in SQL
92 Adding an index to our table for more efficient searching
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 What is ACID
103 Essential queries we’ll need for this project
104 RETURNING data from modified rows
105 Nested queries getting the latest poll
106 SQL built-in functions
107 GROUP BY and calculating vote percentages
108 PostgreSQL window functions
109 PostgreSQL window functions (part 2)
110 How to use ORDER BY with window functions
111 How to use PARTITION with window functions
112 SQL DISTINCT and DISTINCT ON
113 The SQL HAVING clause
114 SQL VIEW virtual tables
115 How to read the PostgreSQL documentation
116 Adding type hinting to our application
Working with dates and times
117 How to separate our database entities into models
118 Creating our Option model class
119 Changes needed in database.py
120 Changes needed in app.py
121 What is connection pooling
122 How to create a connection pool with psycopg2
123 Reduce pooling duplication with context managers
124 Reduce cursor creation duplication with context managers
125 The Python datetime module
126 How to calculate new dates with timedelta
127 How to handle timezones with pytz
128 How to save dates to PostgreSQL
129 Add the vote date to our polling app
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 install matplotlib
140 How to draw a line graph with matplotlib
141 Matplotlib backends
142 Matplotlib figures, axes, and plots
143 The Object-Oriented Approach with matplotlib
144 How to add multiple subplots to a figure
145 How to draw a pie chart with matplotlib
146 How to draw a bar chart with matplotlib
147 How to adjust the size of a matplotlib plot
148 How to adjust the x axis tick labels so they fit in the screen
149 How to draw a stacked bar chart with matplotlib
150 How to create a legend from your graphed data
151 How to export an image with matplotlib
152 How to create one document with multiple matplotlib plots
153 How to create a custom legend with matplotlib
Resolve the captcha to access the links!