พื้นฐาน ORM ของ SQLAlchemy

Welcome to SQLAlchemy ORM! You'll learn how to connect your Python code to a database in a powerful, object-oriented way.

An Object Relational Mapper (ORM) is a tool that helps you interact with a database using objects from your programming language, instead of writing raw SQL.

• It maps database tables to Python classes.
• It maps database rows to Python objects.
• It maps database columns to Python attributes.

This makes database operations feel more like working with regular Python objects.

SQLAlchemy is a comprehensive and powerful ORM for Python. It provides a full suite of well-known persistence patterns for efficient and high-performing database access.

We'll focus on its ORM capabilities, which allow you to define your database structure (schema) using Python classes and interact with data using instances of those classes.

It supports many databases, including SQLite, PostgreSQL, MySQL, and more!

To start defining our database models, we need a special base class. SQLAlchemy's Declarative Base provides this foundation.

It's essentially a factory that generates a base class which your ORM models will inherit from. This base class connects your Python classes to the underlying database tables.

Here's how you get it:

Once you have your Base, you can define your database tables as Python classes. Each class will represent a table, and its attributes will represent the columns.

Let's create a simple User model. It will have an id and a name.

In our User model, id and name are defined using Column objects. The Column function lets you specify details about each database column:

• Data Type: Integer for whole numbers, String for text. SQLAlchemy has many more!
• Primary Key: primary_key=True marks a column as the unique identifier for each row.
• Length: String(50) sets a maximum length for text fields.
• Nullable: By default, columns are nullable. You can set nullable=False to require a value.

The __tablename__ attribute is crucial; it tells SQLAlchemy the actual name of the table in your database.

Before we can create tables or interact with the database, SQLAlchemy needs to know where it is! This is where the Engine comes in.

An Engine is the starting point for any SQLAlchemy application. It connects your application to a specific database using a connection string.

For simplicity, we'll use an in-memory SQLite database, which is great for testing as it disappears when the program ends:

With our Base, defined models, and engine, we can now create the actual database tables!

The Base.metadata.create_all(engine) method inspects all classes that inherit from Base and creates the corresponding tables in the database connected by the engine.

If the tables already exist, SQLAlchemy won't try to recreate them, preventing errors.

Let's put it all together! Run this code to see how to define a model and create its table in an in-memory SQLite database.

Notice how we import everything needed, define Base, create our User model, set up the engine, and finally, create the tables.

Defining models and creating tables are just the first steps. To actually interact with the data (add, query, update, delete), you need a Session.

A Session is like a temporary workspace for your database operations. It holds all the objects you've loaded or created and keeps track of changes.

You create a Session using sessionmaker and bind it to your engine:

Great job! You've taken your first steps into the world of SQLAlchemy ORM.

Here's what we covered:

• What is an ORM: Maps Python objects to database tables.
• SQLAlchemy: A powerful Python ORM.
• Declarative Base: The foundation (Base = declarative_base()) for your models.
• Defining Models: Creating Python classes (like User) that inherit from Base.
• Columns: Using Column with data types (Integer, String) and attributes (primary_key).
• Engine: Connecting to your database (create_engine).
• Table Creation: Bringing models to life in the database (Base.metadata.create_all(engine)).
• Session: Your workspace for database interactions (sessionmaker).

Next, we'll learn how to add, query, update, and delete data using these concepts!