Python Database Mastery: Dive into SQLAlchemy & Alembic

Execute and fetch data with SQLAlchemy by creating a sample telegram users table, inserting records, and retrieving results with cursor, all, and scalar methods; compare textual SQL and ORM usage.

Learn to create tables with SQLAlchemy's ORM by mapping Python classes to database tables using a declarative base, mapped columns, primary keys, constraints, and Alembic migrations.

Learn to define a user table by mapping a Python class to SQLAlchemy columns, specifying mapped types, nullable constraints, created at timestamp with server default, and a self-referencing foreign key.

Explains mixins in SQLAlchemy to reuse column definitions, including a TimestampMaxin with createdAt and updatedAt, and uses declaredAttributeDirective to auto name tables and prevent naming mistakes.

Learn how to reuse column declarations with annotated types and mapped columns in SQLAlchemy, and model primary keys, foreign keys, and many-to-many relations for orders and products.

Learn to create tables with SQLAlchemy using a declarative base and metadata, configure an engine, use create_all and drop_all, and prepare for Alembic migrations.

Master database migrations with lambic: install, configure an environment, import models to align base metadata, and generate offline or online migrations that alter tables and data using SQLAlchemy.

Learn how to create your first Alembic migration for a SQLAlchemy project by configuring metadata, using auto generate, and applying upgrade head to tables such as products, users, and orders.

Learn how to modify a database using Alembic migrations, adjusting column lengths, adding and renaming columns, and applying upgrades to reflect product, language, and price schemas.

Learn to insert data into a users table using the SQLAlchemy ORM, via a repository and session, with insert statements, handling nulls, and committing.

Learn advanced select queries with SQLAlchemy ORM, including get user by telegram id, selecting specific columns, using where clauses, scalars, and ordering and limiting results.

Learn how to combine insert, select, and update in a single Postgres query using on conflict do nothing or update, returning the full user record and committing the transaction.

Seed initial data in tables using Faker to generate users, orders, and products, including referrer IDs, and save results for testing queries, then migrate with Alembic.

Establish one-to-many and bidirectional relationships in SQLAlchemy by mapping parent and child tables, using back_populates and foreign keys, and configure many-to-many via an association table for orders, products, and users.

Explore inner and outer joins on a self-referencing user table, using an alias to distinguish parent and referral users, and apply on clause to fetch invited users.

Learn to join users, orders, and products using SQLAlchemy ORM relationships, leveraging the orders products association table to run a single efficient joined query and access specific fields.

Master aggregated queries in sqlalchemy, using count, sum, average, min, and max; join users and orders, apply group by, labeling, and having for precise insights.

Update queries with ORM demonstrate updating a user row using SQLAlchemy's update function, applying a where clause, setting new values via values, and committing, with referer constraints highlighted.

Delete records with an ORM by specifying a where clause on the userTelegramId and committing the transaction to persist changes.

Learn how to perform bulk inserts with orm using bindParam in SQLAlchemy to add multiple products to an order by inserting productId and quantity from dictionaries, then commit.

Learn to migrate SQLAlchemy to asynchronous mode by using an async engine, adapting sessions, and enabling async Alembic migrations for apps like Telegram bots and FastAPI.