Sqlalchemy Orm Fundamentals

Master the power of SQLAlchemy ORM with the fundamentals of database interaction using Python.

What you'll learn

Set up the development environment, including Python, VSCode, and virtual environments for SQLAlchemy.

Learn to define database tables as SQLAlchemy ORM models with relationships, constraints, and event listeners.

Generate and manage database tables using SQLAlchemy ORM and PostgreSQL.

Perform data insertion, updates, deletions, and manage transactions using SQLAlchemy ORM.

Retrieve and manipulate database records efficiently using SQLAlchemy query utilities.

Apply filtering techniques to refine database queries using SQLAlchemy ORM.

Utilize different types of SQL joins to combine data from multiple tables.

Perform data aggregation, grouping, and filtering to analyze database records.

Requirements

No programming experience needed, everything should be provided.

Description

Master the power of SQLAlchemy ORM with this in-depth course designed to teach you the fundamentals of database interaction using Python. Whether you're a developer, data professional, or aspiring backend engineer, this course will equip you with the skills to define, query, and manage databases efficiently.Starting with setting up your development environment, you'll learn how to define database models, establish relationships, and enforce constraints using SQLAlchemy’s declarative ORM. You’ll then dive into generating tables, inserting and updating records, and executing complex queries with filtering, joins, and aggregations.By the end of this course, you'll be able to integrate SQLAlchemy ORM into real-world projects, optimize data retrieval, and leverage the full power of Python for database management.This course is perfect for Python developers, backend engineers, data analysts, and anyone looking to gain expertise in ORM-based database operations.Learning OutcomesBy the end of this course, learners will be able to:1. IntroductionsUnderstand the course coverage, aims, and objectives.Explain the purpose of SQLAlchemy ORM and its advantages in database management.2. Preparing for DevelopmentSet up a development environment for SQLAlchemy on both Windows and macOS.Install Python and configure it for SQLAlchemy development.Set up VSCode for SQLAlchemy development on Windows and macOS.Create and manage virtual environments for dependency management.3. Fundamentals - Defining Database Tables (Models)Understand the structure of the database through an ERD.Create a new SQLAlchemy project with a declarative base.Define database tables using SQLAlchemy ORM models.Identify and apply different field types, including DateTime fields.Implement required, nullable, and default values in table columns.Enforce uniqueness constraints and define primary and foreign keys.Establish self-referencing relationships in tables.Implement on-delete behaviors for foreign key constraints.Define and manage many-to-many and one-to-one relationships.Introduce database-level constraints and event listeners.Utilize database-level event listeners (triggers) for automation.Convert models to Python type hinting for improved readability and maintainability.4. Fundamentals - Generating Tables from ModelsSet up PostgreSQL using Docker.Create a database engine using SQLAlchemy.Establish and manage ORM sessions for interacting with the database.Generate tables from ORM models.Drop and recreate tables as needed.5. Fundamentals - Inserting, Updating, and Deleting DataInsert records using add() and commit().Perform bulk inserts using add_all(), bulk_save_objects(), and bulk_insert_mappings().Update existing records with SQLAlchemy ORM.Track changes to ORM-managed objects.Insert records into tables with foreign keys and relationships.Use flush() to manage transactions effectively.Implement record deletion, including handling relationships.Secure sensitive fields through encryption techniques.Set and manage server-side default values.Use PostgreSQL and DataGrip to insert and update data efficiently.6. Fundamentals - Querying the DatabasePopulate the database with seed data.Retrieve records using SELECT.Filter records using WHERE conditions.Inspect raw SQL generated by SQLAlchemy ORM.Utilize common query utilities:first(), count(), limit(), exists(), and order_by().Implement @classmethod to define reusable query logic in models.7. Fundamentals - FilteringApply the where() filtering method.Combine filters using AND and OR logic.Use basic comparison operators for queries.Implement filtering functions such as like(), in_(), and between().Retrieve distinct records using distinct().Construct composable queries for better query optimization.8. Fundamentals - JoinsPerform inner joins for:Foreign key relationshipsOne-to-one relationshipsMany-to-many relationshipsPerform left joins for:Foreign key relationshipsOne-to-one relationshipsMany-to-many relationshipsExecute full outer joins and exclude specific results.9. Fundamentals - Aggregation and GroupingPerform aggregate calculations using:count(), sum(), avg(), min(), and max().Group results using group_by().Filter grouped results using having().

Overview

Section 1: Introductions

Lecture 1 Course Overview

Lecture 2 SQLAlchemy

Section 2: Preparing For Development

Lecture 3 Module Primer

Lecture 4 Windows: Python Installation

Lecture 5 MacOS: Python Installation

Lecture 6 Quick Start Guide: VS Code for Windows

Lecture 7 Quick Start Guide: VS Code for MacOS

Lecture 8 Quick Start Guide: Creating Virtual Environments on Windows

Lecture 9 Quick Start Guide: Creating Virtual Environments on MacOS

Section 3: Fundamentals: Defining Database Tables (Models)

Lecture 10 Module Primer

Lecture 11 The Databases ERD

Lecture 12 Starting a new SQLAlchemy Project

Lecture 13 Declarative Base

Lecture 14 Defining the Database Tables as Models

Lecture 15 Common Field Types

Lecture 16 Date and Time Fields

Lecture 17 Required, Null and Blank Fields

Lecture 18 Default Values

Lecture 19 Unique Values

Lecture 20 Creating Primary Keys

Lecture 21 Creating Foreign Keys

Lecture 22 Self Referencing Relationships

Lecture 23 On-Delete Behaviour

Lecture 24 Defining Many-To-Many Relationships

Lecture 25 Creating a one-to-one Relationship

Lecture 26 Introducing to Database Level Constraints

Lecture 27 Introducing SQLAlchemy Event Listeners

Lecture 28 Introducing to Database Level Event Listeners (Triggers)

Lecture 29 Source Code

Section 4: Fundamentals - Generating Tables from Models

Lecture 30 Module Primer

Lecture 31 PostgreSQL Docker Setup

Lecture 32 Creating an Engine

Lecture 33 ORM Sessions

Lecture 34 Creating and Removing Tables in the Database

Lecture 35 Source Code + Setup Guide

Section 5: Fundamentals - Inserting, Updating and Deleting Data

Lecture 36 Module Primer

Lecture 37 Inserting using add() and commit()

Lecture 38 Bulk Inserts add_all()

Lecture 39 Bulk Inserts bulk_insert_mappings

Lecture 40 Bulk Inserts bulk_save_objects

Lecture 41 Updating Existing Records

Lecture 42 ORM Tracking

Lecture 43 Inserts with Foreign Keys Tables

Lecture 44 Using Flush in SQLAlchemy

Lecture 45 Inserts with One-to-One Tables

Lecture 46 Inserts with Many-to-Many Tables

Lecture 47 Deleting Records

Lecture 48 Deleting Records with Relationships

Lecture 49 Handling and Encrypting Sensitive Fields

Lecture 50 Source Code + Setup Guide

Section 6: Fundamentals: Querying the Database

Lecture 51 Module Primer

Lecture 52 Seeding the Database

Lecture 53 Using SELECT to Retrieve Records

Lecture 54 Selecting Specific Columns with WHERE

Lecture 55 Inspecting Raw SQL

Lecture 56 Using Common Utility first()

Lecture 57 Using Common Utility count()

Lecture 58 Using Common Utility limit()

Lecture 59 Using Common Utility exists()

Lecture 60 Using Common Utility order_by()

Lecture 61 Using Model @classmethod

Lecture 62 Source Code + Setup Guide

Section 7: Fundamentals: Filtering

Lecture 63 Module Primer

Lecture 64 Where() General Filtering Method

Lecture 65 AND OR Logic in Filtering

Lecture 66 Function like()

Lecture 67 Basic Comparison Operators

Lecture 68 Function between

Lecture 69 Function in_()

Lecture 70 Utility distinct

Lecture 71 Composable Queries

Lecture 72 Source Code + Setup Guide

Section 8: Fundamentals: Joins

Lecture 73 Module Primer

Lecture 74 Inner joins Foreign Key Relationships

Lecture 75 Inner joins One-to-One

Lecture 76 Inner joins Many-to-Many

Lecture 77 Left Joins Foreign Key Relationships

Lecture 78 Left Joins One-to-One Relationships

Lecture 79 Left Joins Many-to-Many Relationships

Lecture 80 Full Outer Join

Lecture 81 Full Outer Join Excluding

Lecture 82 Source Code + Setup Guide

Section 9: Fundamentals: Aggregation and Grouping

Lecture 83 Module Primer

Lecture 84 Count

Lecture 85 Sum

Lecture 86 Average

Lecture 87 Grouping results using group_by()

Lecture 88 Filtering grouped results using having()

Lecture 89 Min and Max

Lecture 90 Source Code + Setup Guide

Beginner to Intermediate Python Developers – Those looking to integrate databases into their applications using SQLAlchemy ORM.,Data Analysts & Engineers – Professionals who need to interact with SQL databases programmatically.,Web Developers – Especially those working with Flask or FastAPI who need an ORM for database operations.,Software Engineers – Anyone building applications requiring efficient database querying and management.,Students & Computer Science Learners – Individuals learning about relational databases and ORM concepts.,Technical Enthusiasts – Anyone curious about SQLAlchemy and ORM-based database operations.