Kiến thức nền tảng về truy vấn với SQLAlchemy ORM

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 Outcomes

By the end of this course, learners will be able to:

1. Introductions

• Understand the course coverage, aims, and objectives.

• Explain the purpose of SQLAlchemy ORM and its advantages in database management.

2. Preparing for Development

• Set 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 Models

• Set 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 Data

• Insert 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 Database

• Populate 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 - Filtering

• Apply 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 - Joins

• Perform inner joins for:

  • Foreign key relationships

  • One-to-one relationships

  • Many-to-many relationships

• Perform left joins for:

  • Foreign key relationships

  • One-to-one relationships

  • Many-to-many relationships

• Execute full outer joins and exclude specific results.

9. Fundamentals - Aggregation and Grouping

• Perform aggregate calculations using:

  • count(), sum(), avg(), min(), and max().

• Group results using group_by().

• Filter grouped results using having().