Sqlalchemy 简明教程

SQLAlchemy ORM - Declaring Mapping

SQLAlchemy 的对象关系映射器 API 的主要目标是促进将用户定义的 Python 类与数据库表相关联，并将这些类的对象与相应表中的行相关联。对象的 state 和行的 state 的变化彼此同步匹配。SQLAlchemy 能够根据用户定义的类及其定义的关系来表达数据库查询。

The main objective of the Object Relational Mapper API of SQLAlchemy is to facilitate associating user-defined Python classes with database tables, and objects of those classes with rows in their corresponding tables. Changes in states of objects and rows are synchronously matched with each other. SQLAlchemy enables expressing database queries in terms of user defined classes and their defined relationships.

ORM 基于 SQL 表达式语言构建。它是一种高级抽象的用法模式。事实上，ORM 是表达式语言的一个应用用法。

The ORM is constructed on top of the SQL Expression Language. It is a high level and abstracted pattern of usage. In fact, ORM is an applied usage of the Expression Language.

虽然可以单独使用对象关系映射器构建成功的应用程序，但有时使用 ORM 构建的应用程序可能在需要特定数据库交互时直接使用表达式语言。

Although a successful application may be constructed using the Object Relational Mapper exclusively, sometimes an application constructed with the ORM may use the Expression Language directly where specific database interactions are required.

Declare Mapping

首先，调用 create_engine() 函数来设置引擎对象，随后将其用于执行 SQL 操作。该函数有两个参数，一个是数据库名称，另一个是回显参数，当设置为 True 时将生成活动日志。如果数据库不存在，将创建该数据库。在以下示例中，创建了 SQLite 数据库。

First of all, create_engine() function is called to set up an engine object which is subsequently used to perform SQL operations. The function has two arguments, one is the name of database and other is an echo parameter when set to True will generate the activity log. If it doesn’t exist, the database will be created. In the following example, a SQLite database is created.

from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)

当调用 Engine.execute() 或 Engine.connect() 等方法时，引擎将建立与数据库的真实 DBAPI 连接。然后用它来 发 射 SQLORM，它不直接使用引擎；而是由 ORM 在后台使用。

The Engine establishes a real DBAPI connection to the database when a method like Engine.execute() or Engine.connect() is called. It is then used to emit the SQLORM which does not use the Engine directly; instead, it is used behind the scenes by the ORM.

在 ORM 的情况下，配置过程首先描述数据库表，然后定义将映射到这些表的类。在 SQLAlchemy 中，这两个任务是同时执行的。这是通过使用声明系统完成的；创建的类包括描述实际数据库表的指令，这些表被映射到。

In case of ORM, the configurational process starts by describing the database tables and then by defining classes which will be mapped to those tables. In SQLAlchemy, these two tasks are performed together. This is done by using Declarative system; the classes created include directives to describe the actual database table they are mapped to.

基本类存储 Declarative 系统中类的目录和映射表。这称为声明基本类。在通常导入的模块中通常只有一个此基类的实例。declarative_base() 函数用于创建基本类。此函数在 sqlalchemy.ext.declarative 模块中定义。

A base class stores a catlog of classes and mapped tables in the Declarative system. This is called as the declarative base class. There will be usually just one instance of this base in a commonly imported module. The declarative_base() function is used to create base class. This function is defined in sqlalchemy.ext.declarative module.

from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

声明基本类后，可以基于它定义任意数量的映射类。以下代码定义了 Customer 的类。它包含要映射到的表，以及其中的列的名称和数据类型。

Once base classis declared, any number of mapped classes can be defined in terms of it. Following code defines a Customer’s class. It contains the table to be mapped to, and names and datatypes of columns in it.

class Customers(Base):
   __tablename__ = 'customers'

   id = Column(Integer, primary_key = True)
   name = Column(String)
   address = Column(String)
   email = Column(String)

在声明性中的类必须具有 tablename 属性，以及至少一个 Column 作为主键的一部分。声明性用称为 descriptors 的特殊 Python 访问器替换所有 Column 对象。此过程称为仪器化，它提供了在 SQL 上下文中引用表的途径，并能够持久保存和加载数据库中列的值。

A class in Declarative must have a tablename attribute, and at least one Column which is part of a primary key. Declarative replaces all the Column objects with special Python accessors known as descriptors. This process is known as instrumentation which provides the means to refer to the table in a SQL context and enables persisting and loading the values of columns from the database.

这种映射类像一个普通 Python 类，根据要求具有属性和方法。

This mapped class like a normal Python class has attributes and methods as per the requirement.

声明性系统中有关类的信息称为表元数据。SQLAlchemy 使用 Table 对象来表示由声明性创建的特定表此信息。根据规范创建 Table 对象，并通过构建 Mapper 对象将其与类关联。此映射器对象不会直接使用，而是作为映射类和表之间的内部接口使用。

The information about class in Declarative system, is called as table metadata. SQLAlchemy uses Table object to represent this information for a specific table created by Declarative. The Table object is created according to the specifications, and is associated with the class by constructing a Mapper object. This mapper object is not directly used but is used internally as interface between mapped class and table.

每个 Table 对象都是名为 MetaData 的更大集合的成员，并且此对象可通过声明性基本类的 .metadata 属性获得。 MetaData.create_all() 方法是将我们的引擎作为数据库连接的源传递进去。对于尚未创建的所有表，它会向数据库发出 CREATE TABLE 语句。

Each Table object is a member of larger collection known as MetaData and this object is available using the .metadata attribute of declarative base class. The MetaData.create_all() method is, passing in our Engine as a source of database connectivity. For all tables that haven’t been created yet, it issues CREATE TABLE statements to the database.

Base.metadata.create_all(engine)

下面给出了创建数据库和表以及映射 Python 类的完整脚本 −

The complete script to create a database and a table, and to map Python class is given below −

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
engine = create_engine('sqlite:///sales.db', echo = True)
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()

class Customers(Base):
   __tablename__ = 'customers'
   id = Column(Integer, primary_key=True)

   name = Column(String)
   address = Column(String)
   email = Column(String)
Base.metadata.create_all(engine)

执行后，Python 控制台将回显正在执行的 SQL 表达式 −

When executed, Python console will echo following SQL expression being executed −

CREATE TABLE customers (
   id INTEGER NOT NULL,
   name VARCHAR,
   address VARCHAR,
   email VARCHAR,
   PRIMARY KEY (id)
)

如果我们使用 SQLiteStudio 图形工具打开 Sales.db，它将在其中显示具有上述结构的客户表。

If we open the Sales.db using SQLiteStudio graphic tool, it shows customers table inside it with above mentioned structure.

customers table