Skip to main content

PostgreSQL for Python Programmers

Executive Summary

This course teaches programming professionals how to master PostgreSQL for modern data applications. Learn SQL from basics to advanced queries, model and optimize databases, and implement secure, concurrent transaction logic. Integrate PostgreSQL with Python using Psycopg2, SQLAlchemy, and Pandas. Extend functionality with procedural languages and popular extensions. Ideal for Python developers working with relational data at scale.

Programming Professionals Collaborating

Description

  • This comprehensive course is designed for computer programming professionals seeking to master PostgreSQL administration.
  • It covers everything from the basics of PostgreSQL architecture to advanced topics like replication, high availability, and deploying PostgreSQL in the cloud.
  • The course provides practical, hands-on experience with installing, configuring, and securing PostgreSQL, as well as performance tuning and routine maintenance.
  • It also delves into advanced topics like containerizing PostgreSQL and deploying it on major cloud platforms.
  • By the end of the course, participants will have a deep understanding of PostgreSQL administration and be equipped with the skills to manage PostgreSQL in a variety of environments.

Objectives

  • Gain a comprehensive understanding of PostgreSQL, its architecture, and key features.
  • Master the installation, setup, and configuration of PostgreSQL, including understanding configuration files and settings.
  • Understand and implement security measures in PostgreSQL, including user authentication, role management, and data encryption.
  • Learn how to tune PostgreSQL for performance, including monitoring and optimizing queries, managing resource consumption, and using indexes effectively.
  • Master the techniques for backup and restore in PostgreSQL, including SQL Dump, File System Level Backup, and Continuous Archiving.
  • Understand and implement replication and high availability strategies in PostgreSQL, including configuring streaming replication and managing failover procedures.
  • Learn how to carry out routine maintenance and monitoring tasks in PostgreSQL, including vacuuming, reindexing, and log file management.
  • Understand how to deploy and manage PostgreSQL in containerized and cloud environments, including Docker, Kubernetes, and major cloud platforms like AWS, Google Cloud, and Azure.

Duration

16 hours of intensive training with live instruction delivered over three or four days to accommodate varied scheduling needs. 

Request Information

🇺🇸

Course Outline

Introduction to PostgreSQL for Python Programming
  • Overview of PostgreSQL
  • Key Features and Benefits
  • Use Cases in the Industry
Setting Up PostgreSQL
  • Installing PostgreSQL on Different Operating Systems
  • Basic Configuration and Post-Installation Setup
PostgreSQL Architecture
  • System Architecture and Process Model
  • Data Storage and Tablespaces
Basic SQL with PostgreSQL
  • Basic SQL Syntax and Data Types
  • Creating, Altering, and Dropping Tables
  • CRUD Operations (Create, Read, Update, Delete)
Advanced SQL Techniques
  • Complex Queries: Joins, Subqueries, and Common Table Expressions (CTEs)
  • Functions and Operators: Built-in and User-Defined
  • Aggregate Functions and Grouping
Database Design and Modeling
  • Normalization and Denormalization
  • Entity-Relationship Modeling and Schema Design
Performance Tuning and Optimization
  • Using EXPLAIN and ANALYZE for Query Optimization
  • Indexing Strategies and Index Maintenance
  • Performance Tips and Configuration Tuning
Concurrency and Transactions
  • Transactions, ACID Properties, and Isolation Levels
  • Locking Mechanisms and Concurrency Control
  • Multi-Version Concurrency Control (MVCC)
Security and Authentication
  • User Management and Role-Based Access Control
  • Authentication Methods and Configuring pg_hba.conf
  • Data Encryption Techniques and SSL/TLS Configuration
Python Programming with PostgreSQL
  • Setting Up Psycopg2
  • Connecting to PostgreSQL from Python
  • Performing CRUD Operations with Python
  • Using SQLAlchemy with PostgreSQL
  • Handling Transactions in Python
  • Working with JSON Data
  • Working with Vectors
Loading Data from PostgreSQL into Pandas
  • Writing SQL Queries to Retrieve Data
  • Using pandas.read_sql() to Load Data
  • Handling Large Datasets Efficiently
Writing Data from Pandas to PostgreSQL
  • Creating New Tables from DataFrames
  • Inserting and Updating Data in Existing Tables
  • Using pandas.to_sql() for Efficient Data Transfers
Handling Data Types and Indexes
  • Mapping Data Types Between Pandas and PostgreSQL
  • Ensuring Data Integrity and Consistency
  • Managing Indexes for Optimized Performance
Extending PostgreSQL
  • Using Procedural Languages: PL/pgSQL, PL/Python, PL/Perl
  • Working with Extensions and Modules
  • Popular Extensions (PostGIS, hstore, etc.)
Conclusion
  • Summary of Key Concepts
  • Q&A
  • Further Resources and Next Steps

Prerequisites

  • Experience with Python programming is required.
  • No prior experience with PostgreSQL is necessary.
  • Students should have a basic understanding of SQL and relational databases.

Training Materials

All students receive comprehensive courseware covering all topics in the course. Courseware is distributed via GitHub in the form of documentation and extensive code samples. Students practice the topics covered through challenging hands-on lab exercises. Students will need a free, personal GitHub account to access the courseware. Student will need permission to install Docker Desktop, Visual Studio Code, and Visual Studio Code Extensions on their computers. If students are unable to configure a local environment, a cloud-based environment can be provided.