The Complete Python/PostgreSQL Course 2.0

Explore string formatting in Python 3.6 and later by using f strings to embed variables and using format templates with placeholders to create reusable greetings.

Learn how to collect user data in Python using input with prompts, convert strings to numbers for calculations, and format results with f-strings and two decimal places.

Build your first Python app that asks for age, converts input to int, computes months by 12, and prints a readable message with an f-string; explore extending to seconds.

Explore booleans in python to drive decisions using boolean logic and comparisons, and learn how ==, !=, >, <, >=, <= differ from is in identity checks.

Explore the Python in keyword for membership tests across lists, tuples, sets, and strings, with practical examples like checking a name in a list and a movie in a set.

Learn to use the in keyword in if statements with a set of movie titles to check user input, and build a magic number game with play prompts.

Master Python destructuring by unpacking tuples into variables, iterating over dictionaries and lists, ignoring values with underscores, and using head and star tail to separate elements.

Explore Python function arguments and parameters, showing how to pass positional and keyword arguments, define functions with x and y, and handle calls and errors.

Learn how to define default parameter values in Python, making y optional while x remains required, and understand defaults last, plus keyword versus positional calls.

Explore how functions return values in Python, including default None returns, printing versus returning, and using return to pass results to callers.

Define lambda functions in Python as unnamed, single-line helpers that take inputs and return outputs. Use them with map or assign a name, but prefer normal functions for clarity.

Learn how dictionary comprehensions create a username-to-user information mapping by turning a list of user tuples into a dictionary, enabling efficient login checks and data retrieval.

Explore unpacking keyword arguments with **kwargs, pack and unpack dictionaries into named arguments, and pass positional and keyword arguments between functions in Python.

Explore Python's magic methods str and repr, and learn how __str__ provides a user-friendly string representation while __repr__ offers an unambiguous form to recreate objects.

Learn how class composition lets a class use other classes to reduce complexity in Python. See why a bookshelf has many books, and why composition is more common than inheritance.

Learn how type hinting in Python 3.5+ uses list annotations, return types, and typing imports to catch type errors with editors and linters.

Learn how to import code between files in Python, using from module import function or import module, how __name__ varies, and how sys.path and PYTHONPATH influence imports.

Master relative imports in Python and compare them with absolute imports. The lecture demonstrates from .mymodule and from ..mylib usage, reveals the folder-based rules, and advocates using absolute imports.

Explore how Python errors work, using raise to signal conditions, and catch them with try-except blocks. Learn to use else, finally, and traceback for robust error handling and debugging.

Create custom error classes in Python and raise them to enforce page limits. Explore a book-like class with init, repr, and a read method that enforces page limits.

Learn how the at syntax for decorators in Python creates wrapped functions, and use functools.wraps to preserve name and documentation while customizing access control.

Learn to decorate functions with parameters in Python by making decorators accept unlimited arguments with *args and **kwargs, and pass them through to the original function.

Explore mutability in Python by showing how a and b reference the same list object through id, how append alters both, and how tuples, strings, and integers behave as immutable.

Learn why mutable default parameters in Python cause shared state across instances when using a default empty list, and apply the None pattern to safely initialize lists inside constructors.

Learn how to build a programming journal app with sqlite, featuring a menu to add entries, view entries, and exit, storing date and content prompts for Python and SQL learning.

Create a Python user menu using a while loop and input, with welcome message and options to add or view, using the walrus operator and a list as SQLite database.

Discover how sql, or structured query language, interacts with relational databases (rdbms) to query and insert data across tables, linking users and accounts with unique identifiers.

Learn to use Python lists as an in-memory database by storing dictionaries with content and date, with add and get entries functions and a refactor toward SQL Lite.

Learn to set up a local sqlite development environment with db browser for sqlite, create a data.db database and a users table, and run basic sql queries.

Learn to write create table commands in Postgres, defining table names and column data types. Use the if not exists clause and semicolon to prevent errors when creating tables.

Connect to a sqlite database with Python's sqlite3 module, manage data files and connections, and use transactions with commit, rollback, and context managers.

Connect to SQLite with Python, create data.db, and switch to a database-backed approach. Create table entries with content and date, and implement add and get operations with commits.

Learn what a cursor is, distinguish database cursors from SQLite cursors, and use them to traverse results in chunks and iterate rows with for loops.

Master inserting data into a table with insert into, optionally naming columns and providing values, and use single quotation marks in PostgreSQL to avoid errors.

Learn to insert data into SQLite with Python using connection.execute and parameterized queries to commit changes and prevent SQL injection, including creating tables if not exists.

Learn how the select statement retrieves data from the users table by listing columns like first_name and surname. Explore filtering with where and joining tables for relational data.

Learn to retrieve data from a sqlite database using python by creating and using a cursor, iterating results with for-loops, and optionally returning rows with sqlite3.row_factory.

Learn how to filter sql query results with the where clause, understand comparison operators, and/or group conditions for clearer, multi-line queries.

Learn to delete an entire table with the drop table command, include if exists to avoid errors, and practice deleting tables in sqlite db browser.

Learn what SQL injection attacks are, how unsafe string formatting enables them, and how to prevent them with parameterized queries using placeholders like question marks in Python and PostgreSQL libraries.

Explore building a movie watchlist app that tracks movies, release dates, and watched status, with user management, a menu-driven interface, and incremental development in three stages.

Explore the three development stages of a movie database project, from a single movies table with a watched flag and timestamps to a normalized, multi-user design with joins.

Explore the starter code for a menu-driven text-based app using Python and PostgreSQL, including importing database.py, creating tables, and implementing movie-related queries and functions.

Develop the stage one feature by creating a movies table with title, release timestamp, and watched, and implementing create, insert, select all, upcoming, and watched queries.

Write and organize the database.py file to connect to data.db with sqlite3, implementing five core functions for creating tables, adding, retrieving (all and upcoming), and updating movies using datetime timestamps.

Learn how to use the SQL update statement to modify data in a table, applying the SET clause to assign values to multiple columns and using WHERE to target rows.

Learn to complete the user menu in a Python app by adding new movies with parsed release dates, converting to timestamps, and updating or retrieving movies via database functions.

Move the watched status from the movies table to a dedicated watched table to support multiple users and associate each entry with a user and a movie.

Learn to delete rows from a database with delete from and a where clause, avoiding deleting everything, by deleting from users and from movies where title equal 'The Matrix'.

Update database.py to create watch list and watched tables and move from movies to watched, implementing insert, delete, and a print function for a user's watched movies.

Explore relational data by mapping users to accounts with primary and foreign keys, and learn how referential constraints in PostgreSQL and SQLite prevent orphaned records and inconsistent data.

Explore stage three by modeling a many-to-many relationship between users and movies with a watched junction table, featuring primary and foreign keys and use of joins to fetch related data.

Implement stage three database changes by adding movies, users, and watched tables with primary and foreign keys, update app logic for IDs, and prepare joins to show titles.

Discover how autoincrementing ids work in SQLite and PostgreSQL, using integer primary keys as an alias for rowid, and learn when the autoincrement keyword is unnecessary.

Learn how to extract data from two tables at once with SQL join, map the left id to holder_id via the ON clause, and select columns from users and accounts.

Learn how to build and run join queries to fetch movies watched by a user, combining the movies, watched, and users tables with filtering and display logic.

Use the order by clause in select statements to sort results after where by columns, such as years_experience, in descending or ascending order; desc for high values, asc is default.

Learn to limit query results using the limit keyword, ordering by years of experience to fetch the top 10 most experienced employees, with or without a where clause.

Explore the SQL like keyword and wildcards percent and underscore to perform flexible string searches, including starts with and contains, with notes on case sensitivity in SQLite.

Learn how SQL indexes speed up filtering and sorting on a specific column, using B-trees to enable faster reads and how writes slow down as the index updates.

Learn how to create an SQL index on the movies table, using release timestamp to speed up searches, with if not exists and naming conventions.

Compare SQLite and PostgreSQL by contrasting setup, size, and concurrency; explain SQLite's single-file design limits writes while PostgreSQL handles writes and enforces foreign keys with on delete options.

Learn to use a cloud PostgreSQL provider to access a database for the course, including creating an ElephantSQL instance, selecting a region near you, and running queries in the browser.

Interact with PostgreSQL via ElephantSQL in the browser to create tables, insert data, and run queries in the public schema, with notes on quotation marks and using schemas.

Compare psycopg2 and psycopg2-binary for PostgreSQL access from Python, explain installation hurdles, and guide beginners to use psycopg2-binary with PyCharm and virtual environments.

Learn to protect sensitive data by using environment variables and a .env file, loading them with python-dotenv, and using database URL for psycopg2 connections.

Migrate from sqlite to Postgres and use psycopg2 cursors with a context manager. Replace question marks with percent s, use serial for auto-increment, and keep app.py unchanged.

Explore autoincrementing columns in Postgres with sequences and serial. Create and manipulate sequences with nextval and currval, and use serial for automatic primary keys that always increment by one.

Learn how code diffs show changes during migrating a movie watchlist app from sqlite to postgres, including replacing sqlite3 with psycopg2, using environment variables and cursors, and %s placeholders.

Discover how to design polls and options in relational tables, enable voting and view results with percentage calculations, and select a random winner from votes using a database.

Explore the ACID properties—atomicity, consistency, isolation, and durability—and how committing transactions ensures data integrity and durable storage in PostgreSQL databases.

Explore using starter Python and PostgreSQL code to manage polls with tables for polls, options, and votes, perform joins, inserts, and implement functions like get_latest_poll and get_poll_and_vote_results.

Learn how to create polls and use the returning keyword to fetch auto-generated IDs during insert, eliminating extra selects and enabling batch inserts with psycopg2 extras execute_values.

explains nested queries and subqueries to retrieve the latest poll and its options by joining polls and options, using inner and outer queries and the with keyword.

Discover built-in PostgreSQL functions, including mathematical and aggregate functions like random(), count(expr), avg(expr), and max(expr), plus using order by random with limit 1 to fetch a random vote.

Learn how to use group by with aggregate functions to count votes per user and compute per‑option vote percentages, and introduce window functions to calculate totals across groups.

Explore PostgreSQL window functions and the over clause to compute the salary average across all rows beside each employee's salary, with examples and edge cases.

Learn how PostgreSQL window functions operate on calculated aggregates, using a polling data example to show how over computes sums and percentages after counts.

Learn to use the order by clause with a window function to rank polls by vote counts using rank over and a descending order by votes.

use partition by polls.title with window functions to subdivide data by poll titles, count votes per option, and rank options within each poll.

Explore how to use distinct and distinct on in Postgres to return the top vote option per poll, emphasizing ordering by poll id and first-row grouping.

Apply the having clause to filter after aggregation, while where filters before aggregation. Group by poll_id and count(id), then use having count(id) > 2; aliases can't be used in having.

Explore SQL views as virtual tables that run the underlying query, covering simple and updatable views, local and cascaded checks, generated columns, and materialised views.

Explore the PostgreSQL documentation, a comprehensive, up-to-date resource that explains how Postgres works, SQL syntax, and key topics like tutorials, server administration, and client interfaces.

Add type hinting to your application by annotating parameters with int and str, returning lists, and defining custom types like Poll, PollWithOption, and PollResults to guide PyCharm.

Separate our code into models by creating poll and option classes, improving readability and enabling straightforward database interactions, such as saving polls and managing options.

Create the option class to manage option data, votes, and retrieval. Explore saving, getting, and voting via the connections module and discuss the trade-off between models and direct database access.

Refactor the database layer to add get polls, get poll, get poll options, add option, and get votes for option, with updated queries and ready for poll and option objects.

Refactor app.py to use model classes for polls and options, removing direct database calls. Explain the ORM-driven flow and the trade-off between performance and simplicity.

Discover how to implement Python connection pooling with psycopg2 to reuse connections, manage pool size, avoid RAM limits, and handle multiple simultaneous users.

Learn to replace repeated connection creation with a psycopg2 simple connection pool, configure minconn and maxconn, and use pool.getconn and pool.putconn to manage connections efficiently.

Learn to reduce duplication by introducing a context manager to get a connection from the pool and return it automatically using yield and a finally block.

Create a reusable get_cursor context manager to reduce cursor creation duplication and streamline cursor handling with connection pooling. Yield the cursor and rely on context managers to clean up.

Master the Python datetime module to handle dates and times with datetime objects, now and today, and format with strftime and strptime, and convert to timestamps.

Master Python date arithmetic with timedelta by adding days or weeks to datetime objects and safely comparing datetimes to timedeltas, including parsing with strptime and checking against next week.

Learn to handle time zones with pytz by converting between UTC and local times, localizing naive datetimes, and storing UTC in databases for reliable datetime management.

Save dates to PostgreSQL by converting local times to UTC with pytz, storing UTC timestamps, and displaying them in the user's timezone.

Add a vote date to the polling app by updating the votes table with a utc timestamp, updating insert and display logic, and handling timezone conversion for logs.

Discover how composite primary keys use two columns to form a unique, indexed key, enforce a composite unique constraint, and apply composite foreign keys across related tables.

Learn how to define and use user-defined functions in PostgreSQL, including creating functions, passing arguments, returning values, and using sql language with examples like delete_inactive.

Learn how to use composite types as function inputs by passing an entire email_opens row, computing how long ago an open occurred, and applying it alongside joined user data.

Explore stored procedures in PostgreSQL, how they differ from functions in transaction control and return values, and how to create and call them with plpgsql or sql.

Explore the difference between connections and transactions in psycopg2: connections handle one query at a time, while transactions run linear, commit-or-rollback operations within a connection.

Explore PostgreSQL locking, including table- and row-level locks, advisory locking, and how conflicting locks, deadlocks, and manual locks affect transactions.

Understand psycopg2's thread safety and its lack of built-in async support, and explore using the SELECT module or switching to AIOPG for true async PostgreSQL.

Learn to construct safe sql strings with psycopg2 using the sql module for identifiers and literals. Use sql placeholders and format to prevent injection and build dynamic queries.

Explore how to create line, pie, and bar charts with Matplotlib, generate multiple plots, customize colors and layouts, and export images from poll data for web and reports.

Learn to create line graphs with Matplotlib by plotting data on figures and axes, customizing markers and colors, and adding axis labels and a title with plt.show.

Learn how a figure is the top-level container that holds axes and plots, how multiple axes and plots can exist, and how pyplot automatically selects and shows the current figure.

Learn the object-oriented approach to matplotlib by creating a figure, adding axes with figure.add_subplot, and plotting with axes.plot, then use axes.set_title and labels for clearer, cleaner code.

Learn how to add multiple subplots to a single matplotlib figure using add_subplot or plt.subplots, arranging axes in a grid and plotting separate data on each.

Learn to build pie charts with matplotlib using poll data, including labeling the segments, exploding the first slice, and displaying percentages, while retrieving options and votes from a database.

Learn to draw a bar chart with matplotlib from poll data, showing each poll's title and its total votes, rotate labels, and adjust the plotting area for readability.

Adjust the matplotlib plot size with plt.figure and set fig size to 10 by 10 inches to create room for axis labels, then set figure.subplots_adjust bottom to 0.35.

Rotate x axis tick labels to fit the screen with rotation 30 using plt.x_ticks, and compare the simple functional approach to the object-oriented method (axes.set_x_tick_labels and axes.set_x_ticks).

Learn to draw a stacked bar chart with matplotlib using a polls dataset, stacking women on top of men and adjusting xticks and subplot margins.

Learn to add and customize legends in pyplot charts, using labels or explicit plot references to map colors to categories like men and women.

Learn to export a matplotlib figure as an image file with bbox_inches tight, controlling subplot adjustments, padding, and color options to preserve labels and readability.

Export a single matplotlib figure containing two subplots and learn to adjust spacing with wspace and hspace, then save with bbox_inches='tight' for clean borders.

Create a custom legend in matplotlib by making color patches with Patch, using hex colors 5c44fd and ff5566 for tech and clothing, and passing them as handles to plt.legend.