SQLAlchemy學(xué)習(xí)筆記（二）

SQLAlchemy Core

現(xiàn)在我們可以連接到數(shù)據(jù)庫了，接下來讓我們看看如何使用SQLAlchemy Core為我們的應(yīng)用程序提供數(shù)據(jù)庫服務(wù)。SQLAlchemy Core是一種python式的表示SQL命令和數(shù)據(jù)結(jié)構(gòu)元素的方法，稱為SQL表達式語言。SQLAlchemy Core可以與Django或SQLAlchemy ORM一起使用，也可以作為獨立的解決方案使用。

Schema and Types

我們必須做的第一件事是定義表中保存的數(shù)據(jù)、這些數(shù)據(jù)如何相互關(guān)聯(lián)以及對這些數(shù)據(jù)的任何約束。

為了提供對底層數(shù)據(jù)庫的訪問，SQLAlchemy需要表示數(shù)據(jù)庫中應(yīng)該存在的表。我們可以用三種方法中的一種來做這件事:

• 使用用戶定義的表對象

• 使用表示表的聲明類

• 從數(shù)據(jù)庫中推斷

本章主要討論第一個問題，因為這是SQLAlchemy Core使用的方法;在掌握基本知識之后，我們將在后面的章節(jié)中介紹其他兩個選項。表對象包含一個類型化列及其屬性的列表，這些列與一個公共元數(shù)據(jù)容器相關(guān)聯(lián)。我們將通過查看SQLAlchemy中可用來構(gòu)建表的類型來開始模式定義的探索。

Types

有四種我們可以在SQLAlchemy中使用的常見類型：

• Generic
• SQL Standard
• Vendor specific
• User defined

SQLAlchemy定義了大量從每個后端數(shù)據(jù)庫支持的實際SQL類型抽象出來的泛型類型。這些類型在sqlalchemy中都是可用的。為方便起見，還可以在sqlalchemy模塊中使用它們。所以讓我們想想這些泛型類型是如何有用的。

布爾泛型類型通常使用布爾SQL類型，在Python端處理true或false;但是，它也在不支持布爾類型的后端數(shù)據(jù)庫上使用SMALLINT。由于SQLAlchemy，這個小細節(jié)對您是隱藏的，而且您可以相信，您構(gòu)建的任何查詢或語句都會對該類型的字段正確操作，而不管使用的是哪種數(shù)據(jù)庫類型。您只需要在Python代碼中處理true或false。這種行為使得泛型類型非常強大，在數(shù)據(jù)庫轉(zhuǎn)換或分割后端系統(tǒng)(其中數(shù)據(jù)倉庫是一種數(shù)據(jù)庫類型，事務(wù)性是另一種)期間非常有用。表1-1顯示了Python和SQL中的泛型類型及其相關(guān)類型表示。

提示：學(xué)習(xí)了解這些類型很重要，因為以后會經(jīng)常用到他們來定義你的數(shù)據(jù)。

我們可能會發(fā)現(xiàn)，如果你之前已經(jīng)定義（創(chuàng)建）好了你的數(shù)據(jù)庫，可能會有些類型無法與你創(chuàng)建的數(shù)據(jù)庫精確的匹配，例如MySQL中的CHAR、VARCHAR類型，別急，SQLAlchemy考慮到了這一情況，在sqlalchemy.dialects中針對這些特定數(shù)據(jù)庫精確匹配的類型，但是需要知道的是，你針對某廠商特定數(shù)據(jù)庫定義的精確匹配的數(shù)據(jù)類型，如果從一種數(shù)據(jù)庫遷移到另一種數(shù)據(jù)庫，那么你的定義可能變得無效，因此，如果除非特別的場景下，推薦使用sqlalchemy中定義好的通用類型，而不是使用某一廠商數(shù)據(jù)庫的類型。

針對特定數(shù)據(jù)庫中的類型的支持，全部定義在sqlalchemy.dialects中。
例如：我們需要使用MySQL中的CHAR,VARCHAR類型，則可以按照下面的方式導(dǎo)入：

from sqlalchemy.dialects.mysql import CHAR, VARCHAR

Metadata

元數(shù)據(jù)用于將數(shù)據(jù)庫結(jié)構(gòu)連接在一起，以便可以在SQLAlchemy中快速訪問它。將元數(shù)據(jù)看作表對象的一種目錄，其中包含關(guān)于引擎和連接的可選信息，這通常很有用?？梢酝ㄟ^字典metada .tables訪問這些表。讀操作是線程級安全的，然而，表結(jié)構(gòu)并不是線程級安全的。元數(shù)據(jù)在綁定到對象之前，需要先被導(dǎo)入并且初始化。我們來初始化一個元數(shù)據(jù)類的實例，以便我們在后續(xù)的學(xué)習(xí)過程中使用.

from sqlalchemy import MetaData
metadata = MetaData()

一旦我們有了保存數(shù)據(jù)庫結(jié)構(gòu)的方法，就可以開始定義表了。

Tables

通過使用表名和元數(shù)據(jù)調(diào)用表構(gòu)造函數(shù)，在提供的元數(shù)據(jù)對象中的SQLAlchemy Core中初始化表對象;任何附加參數(shù)都假定為列對象。另外還有一些關(guān)鍵字參數(shù)支持我們稍后將討論的特性。列對象表示表中的每個字段。這些列是通過使用名稱、類型和參數(shù)調(diào)用列來構(gòu)造的，這些參數(shù)表示任何額外的SQL結(jié)構(gòu)和約束。在本章的其余部分中，我們將構(gòu)建一組表，我們將在第1部分中使用這些表。在示例1-1中，我們將創(chuàng)建一個表，用于存儲在線cookie交付服務(wù)的cookie庫存。

示例1-1：實例化數(shù)據(jù)表和列

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定義為了索引，提升我們查詢操作的性能
  • 什么是數(shù)據(jù)庫中的索引？
• Numeric(12, 2)定義了一列數(shù)據(jù)，包括長度和精度，可以存儲一個最長包含11位數(shù)字和2位小數(shù)部分的數(shù)據(jù)。

在我們進一步學(xué)習(xí)數(shù)據(jù)表之前，我們需要先了解基礎(chǔ)的Columns。

Columns

列定義表中存在的字段，它們提供了主要的方法，通過這些字段的關(guān)鍵字參數(shù)定義其他約束。不同類型的列具有不同的主參數(shù)。例如，字符串類型列的主參數(shù)是length，而具有小數(shù)部分的數(shù)字將具有精度和長度。大多數(shù)其他類型沒有主參數(shù)。

提示：有時您會看到一些示例，它們只顯示沒有長度的字符串列，這是主要參數(shù)。這種行為不是普遍支持的——例如，MySQL和其他幾個數(shù)據(jù)庫后端不支持它。

列還可以有一些額外的關(guān)鍵字參數(shù)，這些參數(shù)有助于進一步塑造它們的行為。我們可以根據(jù)需要標記列和/或強制它們是唯一的。我們還可以設(shè)置默認的初始值，并在記錄更新時更改值。用于此目的的一個常見用例是字段，這些字段指示何時為日志記錄或?qū)徲嬆康膭?chuàng)建或更新記錄。讓我們在示例1-2中查看這些關(guān)鍵字參數(shù)的作用。

示例1-2：一個具有多個列控制參數(shù)的表

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設(shè)置為可調(diào)用日期時間。現(xiàn)在，不再調(diào)用函數(shù)本身，而是datetime.now()。如果我們使用函數(shù)調(diào)用本身，它就會將默認值設(shè)置為表首次實例化的時間。通過使用callable，我們可以獲得實例化和更新每個記錄的時間。

我們一直在使用列關(guān)鍵字參數(shù)來定義表結(jié)構(gòu)和約束;但是，也可以在列對象之外聲明它們。當您使用現(xiàn)有數(shù)據(jù)庫時，這一點非常關(guān)鍵，因為您必須告訴SQLAlchemy數(shù)據(jù)庫中存在的模式、構(gòu)造和約束。例如，如果數(shù)據(jù)庫中現(xiàn)有索引與SQLAlchemy使用的默認索引命名模式不匹配，則必須手動定義該索引。下面兩個部分將向您展示如何做到這一點。

提示：“鍵和約束”和“索引”中的所有命令都包含在表構(gòu)造函數(shù)中，或通過特殊方法添加到表中。它們將作為獨立語句持久化或附加到元數(shù)據(jù)。

Keys and Constraints

鍵和約束用于確保我們的數(shù)據(jù)在存儲到數(shù)據(jù)庫之前滿足某些需求。表示鍵和約束的對象可以在基本SQLAlchemy模塊中找到，可以導(dǎo)入三個更常見的對象，如下所示:

from sqlalchemy import \
    PrimaryKeyConstraint, \
    UniqueConstraint, \
    CheckConstraint

最常見的鍵類型是主鍵，它用作數(shù)據(jù)庫表中每個記錄的惟一標識符，并用于確保不同表中兩個相關(guān)數(shù)據(jù)之間的適當關(guān)系。正如您在前面的示例1-1和示例1-2中看到的，可以通過使用primary_key關(guān)鍵字參數(shù)使列成為主鍵。您還可以通過在多個列上將設(shè)置primary_key設(shè)置為True來定義組合主鍵。鍵將被視為一個元組，其中標記為鍵的列將按它們在表中定義的順序出現(xiàn)。主鍵也可以在表構(gòu)造函數(shù)中的列之后定義，如下面的代碼片段所示。您可以添加由逗號分隔的多個列來創(chuàng)建組合鍵。如果我們想顯式地定義這個鍵，如例1-2所示，它看起來是這樣的:

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

另一個常見的約束是惟一約束，它用于確保給定字段中沒有兩個值重復(fù)。例如，對于我們的在線cookie交付服務(wù)，我們希望確保每個客戶都有惟一的用戶名來登錄我們的系統(tǒng)。我們還可以為列指定唯一的約束，如之前在username列中所示，或者我們可以手動定義它們，如下所示:

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

示例1-2中沒有顯示檢查約束類型。這種類型的約束用于確保為列提供的數(shù)據(jù)與用戶定義的一組標準匹配。在下面的例子中，我們確保unit_cost永遠不允許小于0.00，因為每個cookie都要花費一定的成本(請記住經(jīng)濟學(xué)101法則:TINSTAAFC—也就是說，沒有免費的cookie這回事!)：

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

除了鍵和約束之外，我們可能還希望提高某些字段的查找效率。這就是索引的作用。

Indexes

索引用于加速字段值的查找，在示例1-1中，我們在cookie_name列上創(chuàng)建了一個索引，因為我們知道我們將經(jīng)常這樣搜索。如該示例所示創(chuàng)建索引時，您將擁有一個名為ix_cookies_cookie_name的索引。我們還可以使用顯式構(gòu)造類型定義索引。可以用逗號分隔多個列。您還可以添加一個關(guān)鍵字參數(shù)unique=True，以要求索引也是惟一的。當顯式創(chuàng)建索引時，它們被傳遞到列之后的表構(gòu)造函數(shù)。為了模擬示例1-1中創(chuàng)建的索引，我們可以顯式地這樣做:

from sqlalchemy import Index 

Index('ix_cookies_cookie_name', 'cookie_name')

我們還可以創(chuàng)建因所使用的后端數(shù)據(jù)庫而略有不同的函數(shù)索引。這允許您為經(jīng)常需要基于某些不尋常上下文進行查詢的情況創(chuàng)建索引。例如，如果我們想通過cookie SKU進行選擇，并將其命名為一個連接項，例如SKU0001巧克力片，該怎么辦?我們可以定義這樣的索引來優(yōu)化查找:

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

現(xiàn)在是深入研究關(guān)系數(shù)據(jù)庫最重要的部分的時候了:表關(guān)系以及如何定義它們。

Relationships and ForeignKeyConstraints

現(xiàn)在我們已經(jīng)有了一個列，其中包含了所有正確的約束和索引，接下來讓我們看看如何在表之間創(chuàng)建關(guān)系。我們需要一種跟蹤訂單的方法，包括表示每個cookie和訂單數(shù)量的行項目。為了幫助可視化這些表之間的關(guān)系，請參見圖1-1。

圖1-1 表關(guān)系圖

實現(xiàn)關(guān)系的一種方法如示例1-3所示，在order_id列上的line_items表中;這將導(dǎo)致一個ForeignKeyConstraint來定義兩個表之間的關(guān)系。在這種情況下，可以為單個訂單提供多個行項目。但是，如果深入研究line_items表，您將看到我們還通過cookie_id ForeignKey與cookies表建立了關(guān)系。這是因為line_items實際上是訂單和cookie之間的關(guān)聯(lián)表，其中包含一些額外的數(shù)據(jù)。關(guān)聯(lián)表用于在其他兩個表之間啟用多對多關(guān)系。表上的單個洋鍵通常是一對多關(guān)系的標志;但是，如果表上有多個foreign - key關(guān)系，那么它很可能是一個關(guān)聯(lián)表。

示例1-3：有聯(lián)系的多個表

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中，注意，我們使用了一個字符串，而不是對列的實際引用。

使用字符串而不是實際的列允許我們跨多個模塊分隔表定義，并且/或不必擔(dān)心加載表的順序。這是因為SQLAlchemy只會在第一次訪問表名和列時執(zhí)行該字符串的解析。如果我們使用硬引用，例如cookie.c.cookie_id，在我們的ForeignKey定義中，它將在模塊初始化期間執(zhí)行該解析，并且可能會失敗，這取決于加載表的順序。

您還可以顯式地定義ForeignKeyConstraint，如果試圖匹配現(xiàn)有的數(shù)據(jù)庫模式，那么可以使用它與SQLAlchemy。這與以前創(chuàng)建鍵、約束和索引以匹配名稱模式等時的工作方式相同。在定義表定義之前，需要從sqlalchemy模塊導(dǎo)入ForeignKeyConstraint。下面的代碼展示了如何為line_items和orders表之間的order_id字段創(chuàng)建ForeignKeyConstraint:

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

到目前為止，我們一直在以SQLAlchemy能夠理解的方式定義表。如果您的數(shù)據(jù)庫已經(jīng)存在，并且已經(jīng)構(gòu)建了模式，那么就可以開始編寫查詢了。但是，如果需要創(chuàng)建完整的模式或添加表，則需要知道如何將它們保存在數(shù)據(jù)庫中以便永久存儲。

持久化數(shù)據(jù)表

我們所有的表和其他模式定義都與一個元數(shù)據(jù)實例相關(guān)聯(lián)。將模式持久化到數(shù)據(jù)庫只需調(diào)用元數(shù)據(jù)實例上的create_all()方法，并使用引擎在其中創(chuàng)建這些表:

metadata.create_all(bind=engine)

默認情況下，create_all不會嘗試重新創(chuàng)建數(shù)據(jù)庫中已經(jīng)存在的表，并且多次運行是安全的。使用像Alembic這樣的數(shù)據(jù)庫遷移工具來處理對現(xiàn)有表或其他模式的任何更改要比試圖在應(yīng)用程序代碼中直接手工編碼更改更為明智(我們將在后面更全面地對此進行探討)?，F(xiàn)在我們已經(jīng)在數(shù)據(jù)庫中持久化了這些表，讓我們看一下示例1-4，它顯示了我們在本章中處理的表的完整代碼。

示例1-4：內(nèi)存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)

在本節(jié)中，我們了解了SQLAlchemy如何使用元數(shù)據(jù)作為目錄來存儲表模式和其他雜項數(shù)據(jù)。我們還可以定義一個包含多個列和約束的表。我們研究了約束的類型，以及如何在列對象之外顯式地構(gòu)造它們來匹配現(xiàn)有的模式或命名模式。然后我們介紹了如何設(shè)置審計的默認值和onupdate值。最后，我們現(xiàn)在知道如何將模式持久化或保存到數(shù)據(jù)庫中以便重用。下一步是學(xué)習(xí)如何通過SQL表達式語言處理模式中的數(shù)據(jù)。