SQLAlchemy学习笔记（二）

SQLAlchemy Core

现在我们可以连接到数据库了，接下来让我们看看如何使用SQLAlchemy Core为我们的应用程序提供数据库服务。SQLAlchemy Core是一种python式的表示SQL命令和数据结构元素的方法，称为SQL表达式语言。SQLAlchemy Core可以与Django或SQLAlchemy ORM一起使用，也可以作为独立的解决方案使用。

Schema and Types

我们必须做的第一件事是定义表中保存的数据、这些数据如何相互关联以及对这些数据的任何约束。

为了提供对底层数据库的访问，SQLAlchemy需要表示数据库中应该存在的表。我们可以用三种方法中的一种来做这件事:

• 使用用户定义的表对象

• 使用表示表的声明类

• 从数据库中推断

本章主要讨论第一个问题，因为这是SQLAlchemy Core使用的方法;在掌握基本知识之后，我们将在后面的章节中介绍其他两个选项。表对象包含一个类型化列及其属性的列表，这些列与一个公共元数据容器相关联。我们将通过查看SQLAlchemy中可用来构建表的类型来开始模式定义的探索。

Types

有四种我们可以在SQLAlchemy中使用的常见类型：

• Generic
• SQL Standard
• Vendor specific
• User defined

SQLAlchemy定义了大量从每个后端数据库支持的实际SQL类型抽象出来的泛型类型。这些类型在sqlalchemy中都是可用的。为方便起见，还可以在sqlalchemy模块中使用它们。所以让我们想想这些泛型类型是如何有用的。

布尔泛型类型通常使用布尔SQL类型，在Python端处理true或false;但是，它也在不支持布尔类型的后端数据库上使用SMALLINT。由于SQLAlchemy，这个小细节对您是隐藏的，而且您可以相信，您构建的任何查询或语句都会对该类型的字段正确操作，而不管使用的是哪种数据库类型。您只需要在Python代码中处理true或false。这种行为使得泛型类型非常强大，在数据库转换或分割后端系统(其中数据仓库是一种数据库类型，事务性是另一种)期间非常有用。表1-1显示了Python和SQL中的泛型类型及其相关类型表示。

SQLAlchemy	Python	SQL
BigInteger	int	BIGINT
Boolean	bool	BOOLEAN or SMALLINT
Date	datetime.date	BIGINT
Enum	str	ENUM or VARCHAR
Float	float or Decimal	FLOAT or REAL
Integer	Int	INTEGER
Interval	int	INTEGER
LargeBinary	byte	BLOB or BYTEA
Numeric	Decimal.Decimal	NUMERIC or DECIMAL
Unicode	unicode	UNICODE or VARCHAR
Text	str	CLOB or VARCHAR
Time	Datetime.time	DATETIME

提示：学习了解这些类型很重要，因为以后会经常用到他们来定义你的数据。

我们可能会发现，如果你之前已经定义（创建）好了你的数据库，可能会有些类型无法与你创建的数据库精确的匹配，例如MySQL中的CHAR、VARCHAR类型，别急，SQLAlchemy考虑到了这一情况，在sqlalchemy.dialects中针对这些特定数据库精确匹配的类型，但是需要知道的是，你针对某厂商特定数据库定义的精确匹配的数据类型，如果从一种数据库迁移到另一种数据库，那么你的定义可能变得无效，因此，如果除非特别的场景下，推荐使用sqlalchemy中定义好的通用类型，而不是使用某一厂商数据库的类型。

针对特定数据库中的类型的支持，全部定义在sqlalchemy.dialects中。
例如：我们需要使用MySQL中的CHAR,VARCHAR类型，则可以按照下面的方式导入：

from sqlalchemy.dialects.mysql import CHAR, VARCHAR

Metadata

元数据用于将数据库结构连接在一起，以便可以在SQLAlchemy中快速访问它。将元数据看作表对象的一种目录，其中包含关于引擎和连接的可选信息，这通常很有用。可以通过字典metada .tables访问这些表。读操作是线程级安全的，然而，表结构并不是线程级安全的。元数据在绑定到对象之前，需要先被导入并且初始化。我们来初始化一个元数据类的实例，以便我们在后续的学习过程中使用.

from sqlalchemy import MetaData
metadata = MetaData()

一旦我们有了保存数据库结构的方法，就可以开始定义表了。

Tables

通过使用表名和元数据调用表构造函数，在提供的元数据对象中的SQLAlchemy Core中初始化表对象;任何附加参数都假定为列对象。另外还有一些关键字参数支持我们稍后将讨论的特性。列对象表示表中的每个字段。这些列是通过使用名称、类型和参数调用列来构造的，这些参数表示任何额外的SQL结构和约束。在本章的其余部分中，我们将构建一组表，我们将在第1部分中使用这些表。在示例1-1中，我们将创建一个表，用于存储在线cookie交付服务的cookie库存。

示例1-1：实例化数据表和列

from sqlalchemy import \
    Table, \
    Column, \
    Integer, \
    Numeric, \
    String, \
    ForeignKey
    
cookies = Table('cookies', metadata, 
    Column('cookie_id', Integer(), primary_key=True),
    Column('cookie_name', String(50), index=True),
    Column('cookie_recipe_url', String(255)),
    Column('cookie_sku', String(55)), 
    Column('quantity', Integer()), 
    Column('unit_cost', Numeric(12, 2)) )

• primar_key表明了这一列是主键
• 将cookie_name定义为了索引，提升我们查询操作的性能
  • 什么是数据库中的索引？
• Numeric(12, 2)定义了一列数据，包括长度和精度，可以存储一个最长包含11位数字和2位小数部分的数据。

在我们进一步学习数据表之前，我们需要先了解基础的Columns。

Columns

列定义表中存在的字段，它们提供了主要的方法，通过这些字段的关键字参数定义其他约束。不同类型的列具有不同的主参数。例如，字符串类型列的主参数是length，而具有小数部分的数字将具有精度和长度。大多数其他类型没有主参数。

提示：有时您会看到一些示例，它们只显示没有长度的字符串列，这是主要参数。这种行为不是普遍支持的——例如，MySQL和其他几个数据库后端不支持它。

列还可以有一些额外的关键字参数，这些参数有助于进一步塑造它们的行为。我们可以根据需要标记列和/或强制它们是唯一的。我们还可以设置默认的初始值，并在记录更新时更改值。用于此目的的一个常见用例是字段，这些字段指示何时为日志记录或审计目的创建或更新记录。让我们在示例1-2中查看这些关键字参数的作用。

示例1-2：一个具有多个列控制参数的表

from datetime import datetime 
from sqlalchemy import DateTime

users = Table('users', metadata,

    Column('user_id', Integer(), primary_key=True),
    Column('username', String(15), nullable=False, unique=True), 
    Column('email_address', String(255), nullable=False),
    Column('phone', String(20), nullable=False),
    Column('password', String(25), nullable=False),
    Column('created_on', DateTime(), default=datetime.now),
    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)

• nullable指定了该列是否支持null值；
• unique决定了这一列的值在当前表中是不是必须唯一
• default指定了默认值
• onupdate使得当前记录任何字段变更时，该列的值都将更新为最后一次操作的时间。

警告：您会注意到我们将default和onupdate设置为可调用日期时间。现在，不再调用函数本身，而是datetime.now()。如果我们使用函数调用本身，它就会将默认值设置为表首次实例化的时间。通过使用callable，我们可以获得实例化和更新每个记录的时间。

我们一直在使用列关键字参数来定义表结构和约束;但是，也可以在列对象之外声明它们。当您使用现有数据库时，这一点非常关键，因为您必须告诉SQLAlchemy数据库中存在的模式、构造和约束。例如，如果数据库中现有索引与SQLAlchemy使用的默认索引命名模式不匹配，则必须手动定义该索引。下面两个部分将向您展示如何做到这一点。

提示：“键和约束”和“索引”中的所有命令都包含在表构造函数中，或通过特殊方法添加到表中。它们将作为独立语句持久化或附加到元数据。

Keys and Constraints

键和约束用于确保我们的数据在存储到数据库之前满足某些需求。表示键和约束的对象可以在基本SQLAlchemy模块中找到，可以导入三个更常见的对象，如下所示:

from sqlalchemy import \
    PrimaryKeyConstraint, \
    UniqueConstraint, \
    CheckConstraint

最常见的键类型是主键，它用作数据库表中每个记录的惟一标识符，并用于确保不同表中两个相关数据之间的适当关系。正如您在前面的示例1-1和示例1-2中看到的，可以通过使用primary_key关键字参数使列成为主键。您还可以通过在多个列上将设置primary_key设置为True来定义组合主键。键将被视为一个元组，其中标记为键的列将按它们在表中定义的顺序出现。主键也可以在表构造函数中的列之后定义，如下面的代码片段所示。您可以添加由逗号分隔的多个列来创建组合键。如果我们想显式地定义这个键，如例1-2所示，它看起来是这样的:

PrimaryKeyConstraint('user_id', name='user_pk')

另一个常见的约束是惟一约束，它用于确保给定字段中没有两个值重复。例如，对于我们的在线cookie交付服务，我们希望确保每个客户都有惟一的用户名来登录我们的系统。我们还可以为列指定唯一的约束，如之前在username列中所示，或者我们可以手动定义它们，如下所示:

UniqueConstraint('username', name='uix_username')

示例1-2中没有显示检查约束类型。这种类型的约束用于确保为列提供的数据与用户定义的一组标准匹配。在下面的例子中，我们确保unit_cost永远不允许小于0.00，因为每个cookie都要花费一定的成本(请记住经济学101法则:TINSTAAFC—也就是说，没有免费的cookie这回事!)：

CheckConstraint(
    'unit_cost >= 0.00',
    name='unit_cost_positive'
    )

除了键和约束之外，我们可能还希望提高某些字段的查找效率。这就是索引的作用。

Indexes

索引用于加速字段值的查找，在示例1-1中，我们在cookie_name列上创建了一个索引，因为我们知道我们将经常这样搜索。如该示例所示创建索引时，您将拥有一个名为ix_cookies_cookie_name的索引。我们还可以使用显式构造类型定义索引。可以用逗号分隔多个列。您还可以添加一个关键字参数unique=True，以要求索引也是惟一的。当显式创建索引时，它们被传递到列之后的表构造函数。为了模拟示例1-1中创建的索引，我们可以显式地这样做:

from sqlalchemy import Index 

Index('ix_cookies_cookie_name', 'cookie_name')

我们还可以创建因所使用的后端数据库而略有不同的函数索引。这允许您为经常需要基于某些不寻常上下文进行查询的情况创建索引。例如，如果我们想通过cookie SKU进行选择，并将其命名为一个连接项，例如SKU0001巧克力片，该怎么办?我们可以定义这样的索引来优化查找:

Index('ix_test', mytable.c.cookie_sku, mytable.c.cookie_name))

现在是深入研究关系数据库最重要的部分的时候了:表关系以及如何定义它们。

Relationships and ForeignKeyConstraints

现在我们已经有了一个列，其中包含了所有正确的约束和索引，接下来让我们看看如何在表之间创建关系。我们需要一种跟踪订单的方法，包括表示每个cookie和订单数量的行项目。为了帮助可视化这些表之间的关系，请参见图1-1。

图1-1 表关系图

实现关系的一种方法如示例1-3所示，在order_id列上的line_items表中;这将导致一个ForeignKeyConstraint来定义两个表之间的关系。在这种情况下，可以为单个订单提供多个行项目。但是，如果深入研究line_items表，您将看到我们还通过cookie_id ForeignKey与cookies表建立了关系。这是因为line_items实际上是订单和cookie之间的关联表，其中包含一些额外的数据。关联表用于在其他两个表之间启用多对多关系。表上的单个洋键通常是一对多关系的标志;但是，如果表上有多个foreign - key关系，那么它很可能是一个关联表。

示例1-3：有联系的多个表

from sqlalchemy import ForeignKey

orders = Table('orders', metadata,
    Column('order_id', Integer(), primary_key=True),
    Column('user_id', ForeignKey('users.user_id')),
    Column('shipped', Boolean(), default=False)
)

line_items = Table('line_items', metadata,
    Column('line_items_id', Integer(), primary_key=True),
    Column('order_id', ForeignKey('orders.order_id')),
    Column('cookie_id', ForeignKey('cookies.cookie_id')),
    Column('quantity', Integer()), 
    Column('extended_cost',Numeric(12, 2)) 
)

注意：在示例1-3中，注意，我们使用了一个字符串，而不是对列的实际引用。

使用字符串而不是实际的列允许我们跨多个模块分隔表定义，并且/或不必担心加载表的顺序。这是因为SQLAlchemy只会在第一次访问表名和列时执行该字符串的解析。如果我们使用硬引用，例如cookie.c.cookie_id，在我们的ForeignKey定义中，它将在模块初始化期间执行该解析，并且可能会失败，这取决于加载表的顺序。

您还可以显式地定义ForeignKeyConstraint，如果试图匹配现有的数据库模式，那么可以使用它与SQLAlchemy。这与以前创建键、约束和索引以匹配名称模式等时的工作方式相同。在定义表定义之前，需要从sqlalchemy模块导入ForeignKeyConstraint。下面的代码展示了如何为line_items和orders表之间的order_id字段创建ForeignKeyConstraint:

ForeignKeyConstraint(['order_id'], ['orders.order_id'])

到目前为止，我们一直在以SQLAlchemy能够理解的方式定义表。如果您的数据库已经存在，并且已经构建了模式，那么就可以开始编写查询了。但是，如果需要创建完整的模式或添加表，则需要知道如何将它们保存在数据库中以便永久存储。

持久化数据表

我们所有的表和其他模式定义都与一个元数据实例相关联。将模式持久化到数据库只需调用元数据实例上的create_all()方法，并使用引擎在其中创建这些表:

metadata.create_all(bind=engine)

默认情况下，create_all不会尝试重新创建数据库中已经存在的表，并且多次运行是安全的。使用像Alembic这样的数据库迁移工具来处理对现有表或其他模式的任何更改要比试图在应用程序代码中直接手工编码更改更为明智(我们将在后面更全面地对此进行探讨)。现在我们已经在数据库中持久化了这些表，让我们看一下示例1-4，它显示了我们在本章中处理的表的完整代码。

示例1-4：内存SQLite的全示例代码

from sqlalchemy import MetaData
from datetime import datetime
from sqlalchemy import (Table, Column, Integer, Numeric,
                    String, ForeignKey, DateTime)
from sqlalchemy import create_engine

metadata = MetaData()

engine = create_engine('sqlite:///:memory:')

cookies = Table('cookies', metadata,
    Column('cookie_id', Integer(), primary_key=True),
    Column('cookie_name', String(50), index=True),
    Column('cookie_recipe_url', String(255)),
    Column('cookie_sku', String(55)),
    Column('quantity', Integer()),
    Column('unit_cost', Numeric(12, 2))
)

users = Table('users', metadata,
    Column('user_id', Integer(), primary_key=True),
    Column('username', String(15), nullable=False, unique=True),
    Column('email_address', String(255), nullable=False),
    Column('phone', String(20), nullable=False),
    Column('password', String(25), nullable=False),
    Column('created_on', DateTime(), default=datetime.now),
    Column('updated_on', DateTime(), default=datetime.now, onupdate=datetime.now)
)

orders = Table('orders', metadata,
    Column('order_id', Integer(), primary_key=True),
    Column('user_id', ForeignKey('users.user_id')),
)

line_items = Table('line_items', metadata,
    Column('line_items_id', Integer(), primary_key=True),
    Column('order_id', ForeignKey('orders.order_id')),
    Column('cookie_id', ForeignKey('cookies.cookie_id')),
    Column('quantity', Integer()),
    Column('extended_cost', Numeric(12, 2))
)

metadata.create_all(engine)

在本节中，我们了解了SQLAlchemy如何使用元数据作为目录来存储表模式和其他杂项数据。我们还可以定义一个包含多个列和约束的表。我们研究了约束的类型，以及如何在列对象之外显式地构造它们来匹配现有的模式或命名模式。然后我们介绍了如何设置审计的默认值和onupdate值。最后，我们现在知道如何将模式持久化或保存到数据库中以便重用。下一步是学习如何通过SQL表达式语言处理模式中的数据。