Python Data Persistence 简明教程

Python Data Persistence - SQLAlchemy

任何关系数据库中的数据都存储在表中。表结构定义了基本数据类型的属性数据类型，这些数据类型通常仅映射到 Python 的内置数据类型。但是，Python 的用户定义对象不能持久地存储到/检索自 SQL 表。

Any relational database holds data in tables. The table structure defines data type of attributes which are basically of primary data types only which are mapped to corresponding built-in data types of Python. However, Python’s user-defined objects can’t be persistently stored and retrieved to/from SQL tables.

这是 SQL 类型与面向对象编程语言（如 Python）之间的差异。SQL 对其他对象（如 dict、元组、列表或任何用户定义的类）没有等效的数据类型。

This is a disparity between SQL types and object oriented programming languages such as Python. SQL doesn’t have equivalent data type for others such as dict, tuple, list, or any user defined class.

如果你必须将对象存储在关系数据库中，则必须先将其实例属性解构为 SQL 数据类型，然后再执行 INSERT 查询。另一方面，从 SQL 表中检索到的数据为原始类型。必须使用所需类型的 Python 对象，才能在 Python 脚本中使用。这是对象关系映射工具发挥作用的地方。

If you have to store an object in a relational database, it’s instance attributes should be deconstructed into SQL data types first, before executing INSERT query. On the other hand, data retrieved from a SQL table is in primary types. A Python object of desired type will have to be constructed by using for use in Python script. This is where Object Relational Mappers are useful.

Object Relation Mapper (ORM)

Object Relation Mapper (ORM) 是类与 SQL 表之间的接口。Python 类映射到数据库中的某个表中，这样对象和 SQL 类型之间的转换将自动执行。

An Object Relation Mapper (ORM) is an interface between a class and a SQL table. A Python class is mapped to a certain table in database, so that conversion between object and SQL types is automatically performed.

用 Python 代码编写的 Students 类映射到数据库中的 Students 表中。因此，所有 CRUD 操作都是通过调用类的各个方法来完成的。这样消除了在 Python 脚本中执行硬编码 SQL 查询的需要。

The Students class written in Python code is mapped to Students table in the database. As a result, all CRUD operations are done by calling respective methods of the class. This eliminates need to execute hard coded SQL queries in Python script.

因此，ORM 库充当对原始 SQL 查询的抽象层，有助于快速应用程序开发。 SQLAlchemy 是 Python 的流行对象关系映射器。对模型对象状态的任何操作都将与其在数据库表中的相关行同步。

ORM library thus acts as an abstraction layer over the raw SQL queries and can be of help in rapid application development. SQLAlchemy is a popular object relational mapper for Python. Any manipulation of state of model object is synchronized with its related row in the database table.

SQLALchemy 库包含 ORM API 和 SQL 表达式语言（ SQLAlchemy Core ）。表达式语言直接执行关系数据库的基本构造。

SQLALchemy library includes ORM API and SQL Expression Language (SQLAlchemy Core). Expression language executes primitive constructs of the relational database directly.

ORM 是构建在 SQL 表达式语言之上的高级抽象化使用模式。可以说，ORM 是表达语言的应用。在该主题中，我们将讨论 SQLAlchemy ORM API 并使用 SQLite 数据库。

ORM is a high level and abstracted pattern of usage constructed on top of the SQL Expression Language. It can be said that ORM is an applied usage of the Expression Language. We shall discuss SQLAlchemy ORM API and use SQLite database in this topic.

SQLAlchemy 通过使用方言系统使用它们各自的 DBAPI 实现与各种类型的数据库通信。所有方言都要求安装适当的 DBAPI 驱动程序。以下类型的数据库方言包括：

SQLAlchemy communicates with various types of databases through their respective DBAPI implementations using a dialect system. All dialects require that an appropriate DBAPI driver is installed. Dialects for following type of databases are included −

Firebird
Microsoft SQL Server
MySQL
Oracle
PostgreSQL
SQLite
Sybase

安装 SQLAlchemy 非常简单，使用 pip 程序包管理器。

Installation of SQLAlchemy is easy and straightforward, using pip utility.

pip install sqlalchemy

要检查 SQLalchemy 是否正确安装及其版本，请在 Python 提示符下输入以下命令：

To check if SQLalchemy is properly installed and its version, enter following on Python prompt −

>>> import sqlalchemy
>>>sqlalchemy.__version__
'1.3.11'

与数据库的交互通过作为 create_engine() 函数返回值获取的引擎对象来完成。

Interactions with database are done through Engine object obtained as a return value of create_engine() function.

engine =create_engine('sqlite:///mydb.sqlite')

SQLite 允许创建内存中的数据库。内存中数据库的 SQLAlchemy 引擎创建如下：

SQLite allows creation of in-memory database. SQLAlchemy engine for in-memory database is created as follows −

from sqlalchemy import create_engine
engine=create_engine('sqlite:///:memory:')

如果你打算使用 MySQL 数据库，请使用其 DB-API 模块 – pymysql 和相关方言驱动程序。

If you intend to use MySQL database instead, use its DB-API module – pymysql and respective dialect driver.

engine = create_engine('mysql+pymydsql://root@localhost/mydb')

create_engine 有一个可选的 echo 参数。如果设置为 true，引擎生成的 SQL 查询将在终端上显示。

The create_engine has an optional echo argument. If set to true, the SQL queries generated by engine will be echoed on the terminal.

SQLAlchemy 包含 declarative base 类。它作为模型类和映射表的目录。

SQLAlchemy contains declarative base class. It acts as a catalog of model classes and mapped tables.

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

下一步是定义模型类。它必须派生自基本对象 – 如上所述的 declarative_base 类。

Next step is to define a model class. It must be derived from base – object of declarative_base class as above.

将 *tablename* 属性设置为要创建的表在数据库中的名称。其他属性对应于字段。每个属性都是 SQLAlchemy 中的一个 Column 对象，并且其数据类型来自以下列表之一：

Set *tablename* property to name of the table you want to be created in the database. Other attributes correspond to the fields. Each one is a Column object in SQLAlchemy and its data type is from one of the list below −

BigInteger
Boolean
Date
DateTime
Float
Integer
Numeric
SmallInteger
String
Text
Time

以下代码是一个名为 Student 的模型类，它映射到 Students 表。

Following code is the model class named as Student that is mapped to Students table.

#myclasses.py
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String, Numeric
base=declarative_base()
class Student(base):
   __tablename__='Students'
   StudentID=Column(Integer, primary_key=True)
   name=Column(String)
   age=Column(Integer)
   marks=Column(Numeric)

要创建一个具有对应结构的 Students 表，请执行为基本类定义的 create_all() 方法。

To create a Students table that has a corresponding structure, execute create_all() method defined for base class.

base.metadata.create_all(engine)

我们现在必须声明一个 Student 类的对象。所有数据库事务（比如添加、删除或从数据库中检索数据等）都由 Session 对象处理。

We now have to declare an object of our Student class. All database transactions such as add, delete or retrieve data from database, etc., are handled by a Session object.

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
sessionobj = Session()

存储在 Student 对象中的数据通过会话的 add() 方法在底层表中物理添加。

Data stored in Student object is physically added in underlying table by session’s add() method.

s1 = Student(name='Juhi', age=25, marks=200)
sessionobj.add(s1)
sessionobj.commit()

以下是向 students 表中添加记录的整个代码。在执行该代码时，将在控制台上显示相应的 SQL 语句日志。

Here, is the entire code for adding record in students table. As it is executed, corresponding SQL statement log is displayed on console.

from sqlalchemy import Column, Integer, String
from sqlalchemy import create_engine
from myclasses import Student, base
engine = create_engine('sqlite:///college.db', echo=True)
base.metadata.create_all(engine)

from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
sessionobj = Session()
s1 = Student(name='Juhi', age=25, marks=200)
sessionobj.add(s1)
sessionobj.commit()

Console output

CREATE TABLE "Students" (
   "StudentID" INTEGER NOT NULL,
   name VARCHAR,
   age INTEGER,
   marks NUMERIC,
   PRIMARY KEY ("StudentID")
)
INFO sqlalchemy.engine.base.Engine ()
INFO sqlalchemy.engine.base.Engine COMMIT
INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
INFO sqlalchemy.engine.base.Engine INSERT INTO "Students" (name, age, marks) VALUES (?, ?, ?)
INFO sqlalchemy.engine.base.Engine ('Juhi', 25, 200.0)
INFO sqlalchemy.engine.base.Engine COMMIT

session 对象还提供 add_all() 方法，以在单个事务中插入多个对象。

The session object also provides add_all() method to insert more than one objects in a single transaction.

sessionobj.add_all([s2,s3,s4,s5])
sessionobj.commit()

既然已在表中添加了记录，我们想要像 SELECT 查询一样从中提取记录。会话对象有 query() 方法来执行该任务。在我们的 Student 模型上，query() 方法会返回查询对象。

Now that, records are added in the table, we would like to fetch from it just as SELECT query does. The session object has query() method to perform the task. Query object is returned by query() method on our Student model.

qry=seesionobj.query(Student)

使用此 Query 对象的 get() 方法提取与给定主键对应的对象。

Use the get() method of this Query object fetches object corresponding to given primary key.

S1=qry.get(1)

在执行此语句时，它在控制台上回显的相应 SQL 语句如下所示：

While this statement is executed, its corresponding SQL statement echoed on the console will be as follows −

BEGIN (implicit)
SELECT "Students"."StudentID" AS "Students_StudentID", "Students".name AS
   "Students_name", "Students".age AS "Students_age",
   "Students".marks AS "Students_marks"
FROM "Students"
WHERE "Products"."Students" = ?
sqlalchemy.engine.base.Engine (1,)

query.all() 方法返回所有对象的列表，该列表可以使用循环来遍历。

The query.all() method returns a list of all objects which can be traversed using a loop.

from sqlalchemy import Column, Integer, String, Numeric
from sqlalchemy import create_engine
from myclasses import Student,base
engine = create_engine('sqlite:///college.db', echo=True)
base.metadata.create_all(engine)
from sqlalchemy.orm import sessionmaker
Session = sessionmaker(bind=engine)
sessionobj = Session()
qry=sessionobj.query(Students)
rows=qry.all()
for row in rows:
   print (row)

更新映射表中的记录非常容易。您所要做的就是使用 get() 方法提取记录，为所需的属性分配一个新值，然后使用会话对象提交更改。在下面，我们将 Juhi 学生的分数更改为 100。

Updating a record in the mapped table is very easy. All you have to do is fetch a record using get() method, assign a new value to desired attribute and then commit the changes using session object. Below we change marks of Juhi student to 100.

S1=qry.get(1)
S1.marks=100
sessionobj.commit()

删除记录同样容易，只需从会话中删除所需的对象即可。

Deleting a record is just as easy, by deleting desired object from the session.

S1=qry.get(1)
Sessionobj.delete(S1)
sessionobj.commit()