Detailed explanation of the ORM framework SQLAlchemy in Python

SQLAlchemy is a powerful Python SQL library that provides a high-level abstraction for operating on the database. By using SQLAlchemy's ORM (Object Relational Mapping) framework, we can conveniently operate the database in an object-oriented manner in the program without writing complex SQL statements and dealing with underlying details such as database connections and transactions. In this article, we will introduce SQLAlchemy’s ORM framework in depth and explore how to use it to complete various database operations.

1. Install and configure SQLAlchemy

Before we start using SQLAlchemy, we need to install it first. You can use a package manager such as pip or conda to install SQLAlchemy:

pip install sqlalchemy

After the installation is complete, we can use the SQLAlchemy library in Python programs. Before starting to use it, we need to initialize the SQLAlchemy engine and metadata in the program. This can be achieved using the following code:

from sqlalchemy import create_engine, MetaData

engine = create_engine('数据库连接字符串')
metadata = MetaData(bind=engine)

Among them, the 'database connection string' needs to be replaced with the actual database connection string, for example:

engine = create_engine('mysql+pymysql://root:password@localhost/test')

MySQL database is used here, and the user name is root. The password is password, and the connected database is named test.

2. Define database tables and ORM mapping

Before using SQLAlchemy’s ORM framework to operate, we need to define the structure of the database table first. This can be achieved by defining a Python class. For example:

from sqlalchemy import Column, Integer, String

class User(Base):
    __tablename__ = 'users'

    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    age = Column(Integer)
    email = Column(String(120), unique=True)

    def __repr__(self):
        return f"<User(name='{self.name}', age={self.age}, email='{self.email}')>"

In this example, we define a User class, which corresponds to a database table named 'users'. This table contains four fields: id, name, age and email. By defining these attributes in the Python class, we establish a mapping relationship, and each column in the table corresponds to an attribute in the Python class. You can use the __repr__ function to conveniently output the properties of an object.

In order to map the User class and the database table, we also need to define a Metadata object of the data table. This object contains some information describing the structure of the data table, such as representation, column names, data types, constraints, etc. At the same time, we also need to use the sessionmaker function to create a session factory for creating database session objects:

from sqlalchemy.orm import sessionmaker

Base.metadata.create_all(engine)
Session = sessionmaker(bind=engine)

Here, we use the create_all() method to map all classes that inherit from the Base class. The Session object is a factory function used to create a database session, which needs to be bound to a database engine.

3. Use ORM for database operations

After defining the classes and metadata objects of the data table, we can start using ORM for database operations. SQLAlchemy's ORM provides a series of APIs that can be used for CRUD operations and query operations. We will introduce these operations separately below.

1. Add data

One of the most commonly used operations in ORM is to add data. This can be achieved by creating a data table object and adding it to the session.

from sqlalchemy.orm import Session

user = User(name='Alice', age=20, email='alice@example.com')
session = Session()
session.add(user)
session.commit()

In this example, we create a User object and add it to the session. Finally, the data is submitted to the database by calling the commit() method.

2. Modify data

Use the ORM framework to modify data. You can use the rollback() and commit() methods to implement transaction operations.

session = Session()
user = session.query(User).filter_by(name='Alice').first()
if user:
    user.age = 21
    session.rollback()
    session.commit()

In this example, we first use the query() method of the session object to obtain a user record named 'Alice' from the database. Then the user's age attribute is modified and the rollback() method is called. This operation will undo all update operations that occurred in the database after the modification. Finally, calling the commit() method will submit the modified data to the database.

3. Delete data

It is also very simple to use the ORM framework to delete data. You can directly delete the data to be deleted from the session.

session = Session()
user = session.query(User).filter_by(name='Alice').first()
if user:
    session.delete(user)
    session.commit()

In this example, we first use the query() method to obtain a user record named 'Alice' from the database. Then delete this record from the session and submit the deletion operation to the database through the commit() method.

4. Query data

You can use the query() method to perform query operations, and use the filter_by() method to specify query conditions. After the query is completed, you can use the all() method to get all results, or you can use the first() method to get the first result.

session = Session()
users = session.query(User).all()
for user in users:
    print(user)

In this example, we first use the query() method to obtain all records from the User table. Then iterate through all results and output the attributes.

In addition to simple queries, we can also use some advanced query methods, such as using the order_by() method to sort by a certain column, or using the limit() method to limit the number of returned results.

users = session.query(User).order_by(User.age.desc()).limit(10).all()

Here, we get the top 10 records from the User table in descending order by the age column.

4. Summary

SQLAlchemy is a powerful Python SQL library, and its ORM framework provides a high-level abstraction for operating databases. By defining classes, metadata objects and session factories, we can easily implement various database operations. SQLAlchemy provides many rich APIs that can be used for CRUD operations, query operations, transaction operations, etc. At the same time, it also supports a variety of database engines and data types to meet various needs.

The above is the detailed content of Detailed explanation of the ORM framework SQLAlchemy in Python. For more information, please follow other related articles on the PHP Chinese website!