Sqlalchemy 简明教程

SQLAlchemy - Introduction

SQLAlchemy 是一个流行的 SQL 工具包和 Object Relational Mapper 。它用 Python 编写，为应用程序开发人员提供了 SQL 的全部功能和灵活性。它是一个 open source 和 cross-platform software ，在 MIT 许可证下发布。

SQLAlchemy is a popular SQL toolkit and Object Relational Mapper. It is written in Python and gives full power and flexibility of SQL to an application developer. It is an open source and cross-platform software released under MIT license.

SQLAlchemy 以其对象关系映射器 (ORM) 而闻名，可以使用它，将类映射到数据库，从而允许对象模型和数据库模式从一开始就以一种干净解耦的方式开发。

SQLAlchemy is famous for its object-relational mapper (ORM), using which, classes can be mapped to the database, thereby allowing the object model and database schema to develop in a cleanly decoupled way from the beginning.

随着 SQL 数据库的规模和性能开始变得重要，它们的行为不像对象集合。另一方面，随着对象集合中的抽象开始变得重要，它们的行为不像表和行。SQLAlchemy 旨在适应这两个原则。

As size and performance of SQL databases start to matter, they behave less like object collections. On the other hand, as abstraction in object collections starts to matter, they behave less like tables and rows. SQLAlchemy aims to accommodate both of these principles.

出于这个原因，它采用了 data mapper pattern (like Hibernate) rather than the active record pattern used by a number of other ORMs 。使用 SQLAlchemy，将从不同的角度看待数据库和 SQL。

For this reason, it has adopted the data mapper pattern (like Hibernate) rather than the active record pattern used by a number of other ORMs. Databases and SQL will be viewed in a different perspective using SQLAlchemy.

Michael Bayer 是 SQLAlchemy 的原始作者。它的初始版本发布于 2006 年 2 月。最新版本编号为 1.2.7，最近于 2018 年 4 月发布。

Michael Bayer is the original author of SQLAlchemy. Its initial version was released in February 2006. Latest version is numbered as 1.2.7, released as recently as in April 2018.

What is ORM?

ORM（对象关系映射）是一种编程技术，用于在面向对象编程语言的不兼容类型系统之间转换数据。通常，在面向对象 (OO) 语言（如 Python）中使用的类型系统包含非标量类型。这些不能表示为整数和字符串之类的基元类型。因此，OO 编程器必须将标量数据中的对象转换为与后端数据库交互。然而，在大多数数据库产品（如 Oracle、MySQL 等）中的数据类型是主要的。

ORM (Object Relational Mapping) is a programming technique for converting data between incompatible type systems in object-oriented programming languages. Usually, the type system used in an Object Oriented (OO) language like Python contains non-scalar types. These cannot be expressed as primitive types such as integers and strings. Hence, the OO programmer has to convert objects in scalar data to interact with backend database. However, data types in most of the database products such as Oracle, MySQL, etc., are primary.

在 ORM 系统中，每个类都映射到底层数据库中的一个表。ORM 负责处理这些问题，让你专注于对系统逻辑进行编程，而不再需要自己编写乏味的数据库接口代码。

In an ORM system, each class maps to a table in the underlying database. Instead of writing tedious database interfacing code yourself, an ORM takes care of these issues for you while you can focus on programming the logics of the system.

SQLAlchemy - Environment setup

让我们讨论使用 SQLAlchemy 所需的环境设置。

Let us discuss the environmental setup required to use SQLAlchemy.

必须使用高于 2.7 的任何 Python 版本才能安装 SQLAlchemy。最简单的安装方法是使用 Python 包管理器 pip 。此实用程序与 Python 的标准发行版捆绑在一起。

Any version of Python higher than 2.7 is necessary to install SQLAlchemy. The easiest way to install is by using Python Package Manager, pip. This utility is bundled with standard distribution of Python.

pip install sqlalchemy

使用上述命令，我们可以从 python.org 下载 SQLAlchemy 的 latest released version ，并将其安装到系统中。

Using the above command, we can download the latest released version of SQLAlchemy from python.org and install it to your system.

对于 Python 的 conda 发行版，可以使用 conda terminal 通过以下命令安装 SQLAlchemy：

In case of anaconda distribution of Python, SQLAlchemy can be installed from conda terminal using the below command −

conda install -c anaconda sqlalchemy

也可以从下列源代码安装 SQLAlchemy：

It is also possible to install SQLAlchemy from below source code −

python setup.py install

SQLAlchemy 被设计为使用专为某个特定数据库而构建的 DBAPI 实现来运行。它使用方言系统与各种类型的 DBAPI 实现和数据库通信。所有方言都要求已安装相应的 DBAPI 驱动程序。

SQLAlchemy is designed to operate with a DBAPI implementation built for a particular database. It uses dialect system to communicate with various types of DBAPI implementations and databases. All dialects require that an appropriate DBAPI driver is installed.

以下是包含的方言：

The following are the dialects included −

  1. Firebird

  2. Microsoft SQL Server

  3. MySQL

  4. Oracle

  5. PostgreSQL

  6. SQLite

  7. Sybase

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

To check if SQLAlchemy is properly installed and to know its version, enter the following command in the Python prompt −

>>> import sqlalchemy
>>>sqlalchemy.__version__
'1.2.7'