SQLAlchemy ORM Query Fundamentals

Set up your development environment for SQLAlchemy, covering Windows, Mac, and Linux. Install Python, explore Visual Studio Code, and master virtual environments to manage dependencies.

Install python on Windows by using Microsoft Store or Python.org, customize the setup, set the install location, add python to the path, and verify the installation with the proper command.

Install python on macOS via the official python website, download the latest macOS version, run the installation wizard, and verify the installation in a terminal.

Install visual studio code on windows, install the official python extension with debugger and pylance, optionally ruff, then create and open a project folder and explore the terminal.

Install visual studio code on mac, install the official python extension, explore night owl theme and prettier tools, and set up a project folder, terminal, and settings.

Create and activate a Windows virtual environment with venv, isolating dependencies and enabling activation by adjusting PowerShell execution policy to remote signed, and manage the interpreter from the virtual environment.

Create and manage python virtual environments on macOS using the Visual Studio Code terminal, activate and deactivate venvs, and ensure the project uses the venv interpreter.

Explore the module primer on defining database tables with SQLAlchemy, building models that interact with databases via Python objects, and preparing to migrate, query, and design scalable databases.

Explore an ERD-based approach to modeling a stock management system, using a pre-created ERD to learn 1-to-1, 1-to-many, and many-to-many relationships and basic draw.io workflow.

Create a Mac virtual environment, install SQL alchemy, and use pip freeze plus requirements.txt to prepare the environment for exploring module source code.

Define a base class with decorative base, build Python classes as database tables, and map these ORM models to actual tables to interact with the relational database using SQLAlchemy 2.0.

Define the database tables as SQLAlchemy models by inheriting from the decorative base, converting entities like category into Python classes with snake_case table names.

Explore common field types in SQLAlchemy, including string, text, integer, boolean, and numeric, and learn how SQLAlchemy translates these to database types for PostgreSQL.

Learn to implement date and time fields in SQLAlchemy models, using createdat and updatedat with default and onupdate, including time zone considerations and server vs Python timestamp generation.

Learn to enforce required, null, and blank fields in SQLAlchemy ORM by setting nullable to false for essential columns and understanding primary keys and foreign keys.

Learn how to apply default values in SQLAlchemy ORM queries, defining Python-level defaults and server defaults, with examples for boolean, integer, datetime, and stock quantity fields.

Apply the unique true constraint in SQLAlchemy ORM to enforce unique values. Consider multi-column uniqueness and index performance across category name and slug, promotions, products, usernames, and emails.

Define integer primary keys with auto increment in SQLAlchemy models to ensure unique rows, support composite keys with table args, and consider big integers or uuids for distributed databases.

Define a foreign key to enforce referential integrity between category and product, enabling a one-to-many relationship. Use SQLAlchemy's relationship and back_populates to simplify cross-table queries.

Implement a self-referencing foreign key to create recursive relationships within the same table for hierarchical data. Use category IDs to link parents and children, enabling multi-level categories and subcategories.

Define on delete behavior for foreign keys in a one-to-many relationship, exploring cascade, set null, set default, restrict, and no action, then apply rules in SQLAlchemy to protect data integrity.

Define and implement many-to-many relationships in SQLAlchemy by creating a link (through) table with a surrogate primary key and foreign keys, plus unique constraints and bidirectional back_populates.

Identify the one-to-one relationship between product and stock management, place a foreign key on stock management, enforce uniqueness, and configure SQLAlchemy relationships with back_populates and single_parent to enable bidirectional queries.

Explore database level constraints and check constraints in SQLAlchemy, including unique constraints and slug format checks, to enforce data integrity across applications.

SQLAlchemy event listeners let you run custom logic during ORM and engine lifecycles, such as before insert or before update, including formatting fields to lowercase via decorators.

Explore database level triggers in Postgres with SQLAlchemy, and learn to enforce business rules and automate tasks by using a trigger that lowercases name and slug on insert or update.

Access the module's source code via this tutorial, giving practical insight into SQLAlchemy ORM query fundamentals.

Explore SQLAlchemy and PostgreSQL as you set up a Docker Postgres database, create an engine, manage ORM sessions, and generate tables from models, with PgAdmin and DataGrip.

Learn to set up a PostgreSQL database in Docker using docker-compose, pull the official postgres image, expose port 5432, initialize the inventory database, and run containers in detached mode.

Learn to create a SQLAlchemy engine to connect a Python app to a PostgreSQL database, using a database URL, driver like psycopg2, and optional settings such as echo and SSL.

Explore how SQLAlchemy ORM sessions manage database interactions, including engine setup, sessionmaker usage, auto commit and auto flush behavior, and a Python context manager for safe commit, rollback, and close.

Migrate your SQLAlchemy ORM models to a Postgres database using an engine and sessions. Drop all and create all tables from base metadata to sync schema.

Download the module source code, set up Visual Studio Code and a Python virtual environment, install requirements, and launch Postgres and Pgadmin with Docker Compose to run the tutorials.

Master modifying data with the SQLAlchemy ORM, covering insert, update, delete, and transaction management with context managers. Handle relationships and foreign keys across 1-to-1 and many-to-many models with bulk operations.

Insert data into the category table using add and commit with SQLAlchemy ORM, utilizing a session context manager, models, and a reset database baseline.

Learn how to perform bulk inserts with SQLAlchemy's session.add_all by inserting multiple model instances in one call, committing to persist changes, and comparing with session.add.

Learn how bulk save objects in SQLAlchemy bypasses the identity map for faster bulk inserts, avoiding object tracking and automatic relationship handling, triggers, and cascading during data seeding.

Use bulk insert mappings to insert large datasets quickly by supplying a list of dictionaries, bypassing ORM object creation and identity tracking, ideal for data from API or CSV.

Learn three methods to update existing records with the SQLAlchemy ORM, including in-session updates with identity tracking, using merge for detached data, and bulk updates for large datasets.

Explore SQLAlchemy ORM tracking, a high-level mechanism that watches object states in a session. Track creation, modification, and deletion, translating changes into updates on commit.

Learn how to insert into a table with a foreign key by creating a related category first, then insert a product using the category's primary key, enforcing referential integrity.

Flush sends pending inserts, updates, and deletes to the database for inspection before committing, enabling dependent queries and multi-operation transactions without permanent writes.

Learn how to insert data for a one-to-one product and stock management relationship, using flush to generate the product ID and SQLAlchemy to link records in order.

Learn how to insert into a many-to-many relationship with SQLAlchemy ORM by creating a category, product, and promotion event, then populating the through table via relationships.

Create and commit new categories, then select from the category table by id, name, or slug and delete single or multiple records with session.delete followed by a commit.

Learn how SQLAlchemy handles deleting records with related data, including on delete restrict vs cascade, passive deletes, and cascading behaviors to avoid orphans in category and product relationships.

Learn how to securely handle sensitive data by distinguishing hashing from encryption, use bcrypt to hash passwords with salt, and validate passwords in SQLAlchemy models.

Set up your development environment for SQLAlchemy ORM tutorials by downloading module source code, creating a virtual environment, installing requirements, and running PostgreSQL via Docker Compose with pgAdmin.

Learn the basics of querying databases with the SQLAlchemy ORM, from seeding data to using the select statement, where clause, count, and limit for efficient results.

Seed the database with JSON fixtures using SQLAlchemy ORM, load data into each table via bulk save objects, validate and hash passwords, and commit parent data before linking tables.

Learn how to build and execute select queries with SQLAlchemy ORM 2.0 using the expression language to retrieve records, return results, and use scalars for flat values.

Use the SQLAlchemy ORM where clause to filter select queries, chain methods to target specific rows and columns, and understand scalar vs scalars when handling single versus multiple results.

Learn how SQLAlchemy's expression language translates Python queries into SQL, and how to inspect the generated raw SQL using engine echo or logging to understand performance, with practical examples.

Explore common utility functions to tailor queries: use first for a single row, scalars to map rows to ORM objects, and all to fetch all results, with optional sql logging.

Learn how to count rows with SQLAlchemy 2.0 using the count function, including importing func, building a select from a table, and evaluating the result at the database level.

Limit the number of rows returned by a SQLAlchemy 2.0 query to five from the category table, and demonstrate how offset selects the starting point in the database SQL.

Learn how to use the exists function in SQLAlchemy to efficiently check for a record’s existence, with practical examples using category data and where filters.

Demonstrates how to use order_by to sort query results by one or more columns, including ascending and descending order, and multi-criteria ordering by createdat and name.

Define class methods in SQLAlchemy models with the @classmethod decorator to query, create entries, or bulk update at the class level, enabling reuse and the fat model thin controller approach.

Download the module’s source code, set up a Python virtual environment, and run docker compose up -d to start postgres and pgadmin for SQLAlchemy tutorials.

Explore sqlalchemy filtering from basic comparisons to advanced query utilities, using the select statement and various operators. Master composable queries with distinct, like, between, and range filters through targeted exercises.

Learn to filter SQLAlchemy 2.0 queries with where, applying conditions, booleans, and chained clauses to category data, with examples using session.execute and scalars.

Explore and or logic in SQL filtering with SQLAlchemy, showing how to combine conditions in a where clause using and_ and or to filter category records by is_active and name.

Learn how the like function enables pattern matching in SQLAlchemy ORM queries, using % and _ wildcards to filter text and perform case-insensitive searches with ilike.

Explore SQLAlchemy comparison operators, equals, not equals, greater than, less than, and greater-than-or-equal-to and less-than-or-equal-to variants, used in where clauses to filter data.

Explore SQL joins to combine and analyze data across multiple tables, mastering inner joins, left joins, full outer joins, and the excluding full outer join to work with relational databases.

Learn how to use SQLAlchemy's in_() to filter a column by multiple values, like electronics or clothing, including not in and other data types, for advanced queries.

Explore the distinct function in SQLAlchemy ORM, returning only unique rows and filtering duplicates, with examples about product names and descriptions.

Learn to build modular, composable queries by returning query parts from class methods and chaining where clauses, using an active flag to filter products.

Download the module's source code and set up your environment for SQLAlchemy ORM tutorials with Docker Compose, Postgres, pgAdmin, Python, and a virtual environment.

Explore how SQL joins combine data across tables, mastering inner joins, left joins, full outer joins, and the excluding full outer join.

Learn how to model one-to-many relationships with foreign keys in SQLAlchemy, and use inner joins to query related product and category data across tables.

Explore inner joins for one-to-one relationships between product and stock management, returning matching records and selecting product and stock data, with notes on foreign keys, unique constraints, and left joins.

Master inner joins across a many-to-many relation using a link table to fetch product and promotion event data, with two joins and optional where filtering.

Master left outer joins in SQLAlchemy, returning all records from the left table and any matching right data, with nulls when the foreign key is unavailable.

Master left joins in a one-to-one relationship to return all products with optional stock management data, and learn how to query for matches and nonmatches.

Master left joins in a many-to-many setup using SQLAlchemy with the product and promotion event tables and their link table, returning all products with related promotion data when present.

Learn how to perform a full outer join in SQLAlchemy to return all records from product and stock management, identify missing data, and spot orphaned records for data cleaning.

Master the full outer join excluding to pull all data from product and stock management in a 1-to-1 setup, then filter with a where clause to return unconnected records.

Download module source code and set up your environment with Python, Visual Studio Code, and docker desktop. Create a virtual environment, install requirements, and run tutorials with docker compose.

Learn to perform aggregation and grouping to summarize data, calculating totals, averages, counts, and minimum and maximum values for insights such as revenue per store or salaries per department.

Learn to count rows with SQLAlchemy by using the count function in a select, with session scalar, joins, and is none filters to summarize products and stock data.

Learn to use SQLAlchemy's sum function to calculate total values from product prices and stock quantities through inner joins and group by category.

Learn how to compute averages with SQLAlchemy ORM queries, including average price and stock, using scalar results, inner joins on product and promotion events, and grouping when necessary.

Learn how group by groups rows by specified columns to compute per-group aggregates like average and sum, with promotions and products as examples.

Apply the having clause to filter groups created by group by after aggregation. Use sum and aggregates to limit results, such as total sales over 5000 in promotions in SQLAlchemy.

Explore how to use the max and min functions in SQLAlchemy ORM to aggregate data by category, with inner joins, group by, and having filters for prices more than 100.

Learn how to download module source code, set up a Python virtual environment, install dependencies, and run tutorials with Docker Compose using Postgres and PgAdmin.