SQLAlchemy0.6.5文档-----用元数据定义数据库

版权所有,转载注明.
注:这篇文章一开始申请了百度博客并放在上面,但是不会编辑代码.又放在这里了.
[size=large]用元数据定义数据库[/size]
数据库元数据为SQLAlchemy 的查询和对象映射提供支持，这些元数据主要由描述数据库表以及其它描述架构层次的Python对象组成。这些对象主要提供三种核心操作--创建CREATE和DROP语句（称作DDL),构建SQL查询，以及传递关于数据库结构的信息。数据库元数据可以明确地用各种元素以及他们的属性来描述，比如用Table,Column,ForeignKey以及Sequence，他们都从sqlalchemy.schema中导入。元数据也可以让SQLAlchemy通过一个叫做reflection的操作自动生成，意思是你从一个单一的对象比如Table开始，命名它然后告诉SQLAlchemy从一个数据库engine加载该数据表中其它的数据和信息。

SQLAlchemy数据库元数据构建的一个重要特征是可以用类似真正的DDL那样的声明式的定义风格。

所有的元数据都存储在叫做MetaData的对象中：

from sqlalchemy import *
metadata = MetaData()

MetaData是一个容器，它集中存放一个或多个数据库的各种特征信息。声明一个数据表，用Table类。它的两个基本的参数是表名，以及数据表绑定的MetaData对象。其它可选的位置参数通常是描述每个字段的Column对象：

user = Table('user',metadata,
Column('user_id',Integer,primary_key = True),
Column('user_name',String(16),nullable = False),
Column('email_address',String(16)),
Column('password',String(20),nullable = False)
)

上面的代码段定义了一个user表，包含四个字段。表的主键由user_id字段组成。多个字段被加上primary_key=True标志形成一个多列主键，又叫复合主键。注意每个字段的数据类型用对应每一个数据类型的对象来描述，比如Integer和String。SQLAlchemy支持多种类型的数据以及自定义类型。

[size=large]访问表和字段[/size]
MetaData对象包含我们已经关联给它的所有schema constructs。他有一些访问这些表对象的方法，比如sorted_tables方法为每一个Table对象返回一个List(该表引用的所有表都按顺序放在它的前面)：

>>> for t in metadata.sorted_tables:
...            print t.name
user
user_preference
invoice
invoice_item

多数情况下，Table对象被显式地声明，而且这些对象都作为模块级的变量存在于程序中。声明过的Table对象有一整套方法可以访问该对象的属性。给出下面的表定义：

employees = Table('employees', metadata,
Column('employee_id', Integer, primary_key=True),
Column('employee_name', String(60), nullable=False),
Column('employee_dept', Integer, ForeignKey("departments.department_id"))
)

注意这里用到了ForeignKey对象，这个命令定义了一个对其它表的引用。访问该表属性的方法包括：

# access the column "EMPLOYEE_ID":
employees.columns.employee_id

# or just
employees.c.employee_id

# via string
employees.c['employee_id']

# iterate through all columns
for c in employees.c:
print c

# get the table's primary key columns
for primary_key in employees.primary_key:
print primary_key

# get the table's foreign key objects:
for fkey in employees.foreign_keys:
print fkey

# access the table's MetaData:
employees.metadata

# access the table's bound Engine or Connection, if its MetaData is bound:
employees.bind

# access a column's name, type, nullable, primary key, foreign key
employees.c.employee_id.name
employees.c.employee_id.type
employees.c.employee_id.nullable
employees.c.employee_id.primary_key
employees.c.employee_dept.foreign_keys

# get the "key" of a column, which defaults to its name, but can
# be any user-defined string:
employees.c.employee_name.key

# access a column's table:
employees.c.employee_id.table is employees

# get the table related by a foreign key
list(employees.c.employee_dept.foreign_keys)[0].column.table# access the column "EMPLOYEE_ID":
employees.columns.employee_id

# or just
employees.c.employee_id

# via string
employees.c['employee_id']

# iterate through all columns
for c in employees.c:
print c

# get the table's primary key columns
for primary_key in employees.primary_key:
print primary_key

# get the table's foreign key objects:
for fkey in employees.foreign_keys:
print fkey

# access the table's MetaData:
employees.metadata

# access the table's bound Engine or Connection, if its MetaData is bound:
employees.bind

# access a column's name, type, nullable, primary key, foreign key
employees.c.employee_id.name
employees.c.employee_id.type
employees.c.employee_id.nullable
employees.c.employee_id.primary_key
employees.c.employee_dept.foreign_keys

# get the "key" of a column, which defaults to its name, but can
# be any user-defined string:
employees.c.employee_name.key

# access a column's table:
employees.c.employee_id.table is employees

# get the table related by a foreign key
list(employees.c.employee_dept.foreign_keys)[0].column.table

[size=large]创建和删除数据库表[/size]
一旦Table对象被定义，假设你在处理一个新创建的数据库，你可能想为这些表创建CREATE语句以及其它相关的定义(例外情况是，很可能你不想这么做，如果你已经有了其它的更好的方式，比如使用你的数据库自带的工具又或者已经有了现成的脚本-如果是这样，请忽略本节下面的内容-SQLAlchemy并不强制要求你创建你定义的数据表）。

创建CREATE语句的一个常见方式是使用MetaData对象的create_all()方法。这个方法先查询每个要创建的Table是否存在，如果不存在才会创建
CREATE语句：

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

metadata = MetaData()

user = Table('user', metadata,
Column('user_id', Integer, primary_key = True),
Column('user_name', String(16), nullable = False),
Column('email_address', String(60), key='email'),
Column('password', String(20), nullable = False)
)

user_prefs = Table('user_prefs', metadata,
Column('pref_id', Integer, primary_key=True),
Column('user_id', Integer, ForeignKey("user.user_id"), nullable=False),
Column('pref_name', String(40), nullable=False),
Column('pref_value', String(100))
)

sqlmetadata.create_all(engine)
PRAGMA table_info(user){}
CREATE TABLE user(
user_id INTEGER NOT NULL PRIMARY KEY,
user_name VARCHAR(16) NOT NULL,
email_address VARCHAR(60),
password VARCHAR(20) NOT NULL
)
PRAGMA table_info(user_prefs){}
CREATE TABLE user_prefs(
pref_id INTEGER NOT NULL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES user(user_id),
pref_name VARCHAR(40) NOT NULL,
pref_value VARCHAR(100)
)

create_all()方法通常会在表定义中创建外键约束，因为这个原因它也同时按照表之间的约束关系按顺序进行创建。可以通过ALTER_TABLE标志改变这个默认的方式。与此类似，drop_all()方法用来删除所有的表。与create_all()刚好相反-它会先检查表是否存在，然后以与创建时相反的顺序进行删除。删除及创建单个数据表通过Table对象的create()和drop()方法。这两个方法在操作前不会检查表是否存在：

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

meta = MetaData()

employees = Table('employees', meta,
Column('employee_id', Integer, primary_key=True),
Column('employee_name', String(60), nullable=False, key='name'),
Column('employee_dept', Integer, ForeignKey("departments.department_id"))
)
sqlemployees.create(engine)
CREATE TABLE employees(
employee_id SERIAL NOT NULL PRIMARY KEY,
employee_name VARCHAR(60) NOT NULL,
employee_dept INTEGER REFERENCES departments(department_id)
)

drop()方法:

employees.drop(engine)

可以通过给create()和drop()方法添加checkfirst=True参数启用“先检查表是否存在”的逻辑：

employees.create(engine, checkfirst=True)
employees.drop(engine, checkfirst=False)

Binding MetaData to an Engine or Connection
注意前面的例子中create()和drop()方法需要一个engine参数,它代表正在使用中的数据库.当一个schema construct和一个Engine对象或者一个Connection对象组合在一起,我们称之为"绑定".上面例子中与schema construc相关的绑定只在进行期间有效.然而,通过MetaData对象的bind方法可以将一个engine与schema construct进行永久绑定:

engine = create_engine('sqlite://')

# create MetaData
meta = MetaData()

# bind to an engine
meta.bind = engine

现在调用create_all()方法不用再提供Engine参数:

meta.create_all()

MetaData对象的bind方法适用于任何需要活动连接的操作,比如说自动加载数据库表的定义(叫"反射"):

# describe a table called 'users', query the database for its columns
users_table = Table('users', meta, autoload=True)

还有为MetaData对象的数据表执行SQL语句也是:

# generate a SELECT statement and execute
result = users_table.select().execute()

把MetaData和Engine进行绑定完全是可选的.上面的操作可以通过参数而不是永久绑定实现:

# describe a table called 'users', query the database for its columns
users_table = Table('users', meta, autoload=True, autoload_with=engine)

# generate a SELECT statement and execute
result = engine.execute(users_table.select())

应该还是不该使用绑定?可能最好是先不绑定,而是由特别需要的时候才这么做.绑定适用于以下情景:

[list]
[*]你没有使用ORM,经常使用"connectionless"的操作,而且你发现整个程序中经常需要指定同一个Engine对象.这种情况绑定可以提供隐含的执行.

[*]你的程序有多个schema对应不同的数据库.为每个schema创建一个MetaData对象,绑定到各自的engine,在不同的schema之间提供一个清晰的工作场所.ORM集成了这种方式,每个Session会自动使用通过Table对象的metadata对象为Table对象绑定的engine(假设Session对象自己没有配置bind).
[*]还有,MetaData的bind属性在以下情况下容易带来混乱:你的程序用同一组Table对象在不同的时间使用多个不同的数据库.只是为了在不同的engine上执行不同的操作而进行Table对象的拷贝往往会带来混乱,没有必要.例如一个程序在主数据库上进行写操作而在从数据库上进行只读操作.一个全局MetaData对象像这样在操作间切换数据库是不合适的,尽管ThreadLocalMeataData对象是适合的.
[/list]

[size=large]为架构命名[/size]
有些数据库支持多个架构.与此相应,Table对象可以指定一个schema参数:

financial_info = Table('financial_info', meta,
Column('id', Integer, primary_key=True),
Column('value', String(100), nullable=False),
schema='remote_banks'
)

在MetaData内,这个数据表通过组合financial_info和remote_banks进行指定.如果另外一个叫financial_info的表在引用时没有通过remote_banks schema,那么将会引用另外一张不同的表.ForeignKey对象可以用remote_banks.financial_info.id的形式引用这个表的字段.任何需要名字限定符的
地方都要使用schema参数,包括Oracle的"owner"属性以及其它类似的地方.比较长的schema可以使用点分式名字:schema="dbo.scott"

[size=medium]特定数据库相关的选项[/size]

Table对象支持特定数据库相关的选项.例如,MySQL有不同的数据表backend类型,包括"MyISAM"和"InnoDB".这可以在定义Table时通过使用
mysql_engine来指定:

addresses = Table('engine_email_addresses', meta,
Column('address_id', Integer, primary_key = True),
Column('remote_user_id', Integer, ForeignKey(users.c.user_id)),
Column('email_address', String(20)),
mysql_engine='InnoDB'
)

其它的backends还可能支持表一级的选项.

Schema API Constructs

(略)

[size=large]反射数据库对象[/size]
一个Table对象可以从数据库中已经存在的对应的数据库schema对象中为它自己加载信息.这个过程叫做"反射".很简单,你只需要指定表名,一个MetaData对象,以及autoload=True参数.如果MetaData是非永久绑定的,同时加上autoload_with参数:

>>> messages = Table('messages', meta, autoload=True, autoload_with=engine)
>>> [c.name for c in messages.columns]
['message_id', 'message_name', 'date']

上述操作会用给定的engine在数据库中查询messages表的信息,而且会创建与这些信息对应的Column,ForeignKey,以及其它对象,就好像这个Table对象是在Python中创建的一样.当数据表被映射后,如果一个给定的表通过外键引用了其它表,被引用的表也会在MetaData对象内创建,来表达这种连接关系.如下,假定shopping_cart_items表引用了shopping_cars表.反射shopping_cart_items表会把shopping_cart表也装载进来:

>>> shopping_cart_items = Table('shopping_cart_items', meta, autoload=True, autoload_with=engine)
>>> 'shopping_carts' in meta.tables:
True

MetaData对象一个有趣的"singleton-like"行为方式是,如果你分别请求了两个表,MetaData会保证只为唯一的表名创建一个Table对象.Table构造器实际上为你返回那个已经存在的Table对象如果给定名字的表对象已经被创建了.如下所示,我们可以为已经创建的shopping_carts表起个名字并访问它:

shopping_carts = Table('shopping_carts', meta)

当然,不管上面的表使用autoload=True是个好主意,这样一来,如果该表的属性没有装载进来将会执行一次装载操作.autoload操作在表还没有加载时只会进行一次,一旦加载,用同样的表名再调用Table不会进行任何反射查询.

[size=medium]覆盖反射的字段[/size]
在进行表反射时可以对字段用明确的值进行独立的覆盖;这对指定自定义类型及增加约束很有用,比如说数据库没有配置主键字段,等等:

>>> mytable = Table('mytable', meta,
... Column('id', Integer, primary_key=True),   # override reflected 'id' to have primary key
... Column('mydata', Unicode(50)),    # override reflected 'mydata' to be Unicode
... autoload=True)

[size=medium]反射视图[/size]
反射系统也可以反射views.基本用法与反射表一样:
my_view = Table("some_view", metadata, autoload=True)
上面的代码中,my_view是一个Table对象,拥有与"some_view"视图相同的名字以及每一个类型的字段.通常,反射视图时需要至少一个主键约束,if not foreign keys as well.视图反射不会推断这些约束条件.通过"覆盖"技术,明确指定这些字段哪个是主键或者哪个有外键约束:

my_view = Table("some_view", metadata,
Column("view_id", Integer, primary_key=True),
Column("related_thing", Integer, ForeignKey("othertable.thing_id")),
autoload=True
)

[size=medium]一次反射所有表[/size]
MetaData对象也可以反射整个集合并得到一个数据表的列表.这是通过调用reflect()方法进行的.调用之后,所有被加载的表都存在于MetaData对象的的数据表字典中:

meta = MetaData()
meta.reflect(bind=someengine)
users_table = meta.tables['users']
addresses_table = meta.tables['addresses']

metadata.relect()# 也提供了一个方便的方式清除或删除一个数据库的所有行:
meta = MetaData()
meta.reflect(bind=someengine)
for table in reversed(meta.sorted_tables):
someengine.execute(table.delete())

[size=medium]小粒度反射并检查[/size]
有个低层接口提供一个backend-agnostic系统,可以从一个给定数据库中加载schema,table,column以及约束条件描述的等的列表.称为:"Inspector"

from sqlalchemy import create_engine
from sqlalchemy.engine import reflection
engine = create_engine('...')
insp = reflection.Inspector.from_engine(engine)
print insp.get_table_names()

Column Insert/Update Defaults(插入和更新的默认值)
SQLAlchemy 提供了丰富的字段级事件的feature set,在INSERT和UPDATE语句执行期间触发.这些选项包括:
[list]
[*]INSERT和UPDATE操作期间默认使用标量值
[*]在INSERT和UPDATE操作之上执行Python函数
[*]INSERT语句中嵌套了SQL表达式(or in some caseses execute beforehand)
[*]UPDATE语句中嵌套了SQL表达式
[*]服务器端在INSERT时使用了默认值
[*]UPDATE时使用了Markers for server-side triggers
[/list]

插入/更新的默认行为的总的原则是,它们只在没有为特定字段提供execute()参数时起作用;否则,使用给定的值.

[size=large]Scalar Defaults(标量默认值)[/size]
The simplest kind of default is a scalar value used as the default value of a column:
最简单的标量默认值是用作字段默认值的情况:

Table("mytable", meta,
    Column("somecolumn", Integer, default=12)
)

Above, the value “12” will be bound as the column value during an INSERT if no other value is supplied.
上面的代码中,如果在INSERT操作期间没有提供字段值,那么"12"将被绑定为该字段的默认值.
A scalar value may also be associated with an UPDATE statement, though this is not very common (as UPDATE statements are usually looking for dynamic defaults):
尽管不常见,但是标量值也可以在UPDATE操作中使用(因为UPDATE语句通常会查找动态默认值)

Table("mytable", meta,
    Column("somecolumn", Integer, onupdate=25)
)

[size=medium]Python-Executed Functions[/size]

The default and onupdate keyword arguments also accept Python functions. These functions are invoked at the time of insert or update if no other value for that column is supplied, and the value returned is used for the column’s value. Below illustrates a crude “sequence” that assigns an incrementing counter to a primary key column:
Python 函数也可以用作更新数据时的默认关键字参数.如果在插入或更新时没有提供其他值,python函数就会被调用而且其返回值会作为字段值.以下例子演示了用原始序列给主键字段进行赋值:

# a function which counts upwards
i = 0
def mydefault():
    global i
    i += 1
    return i

t = Table("mytable", meta,
    Column('id', Integer, primary_key=True, default=mydefault),
)

It should be noted that for real “incrementing sequence” behavior, the built-in capabilities of the database should normally be used, which may include sequence objects or other autoincrementing capabilities. For primary key columns, SQLAlchemy will in most cases use these capabilities automatically. See the API documentation for Column including the autoincrement flag, as well as the section on Sequence later in this chapter for background on standard primary key generation techniques.
应该注意,对于现实中真正的增序序列,应该使用数据库内置的功能,通常包含对象序列或其它自增对象.SQLChemy大多数情况下都会为主键提供自增能力.关于主键创建技术中字段,自增标志的背景信息请参考API文档,也可参考本章稍后关于序列的部分.
To illustrate onupdate, we assign the Python datetime function now to the onupdate attribute:
为了演示更新操作,我们把datetime函数now赋值给了onupdate属性:

import datetime

t = Table("mytable", meta,
    Column('id', Integer, primary_key=True),

    # define 'last_updated' to be populated with datetime.now()
    Column('last_updated', DateTime, onupdate=datetime.datetime.now),
)

When an update statement executes and no value is passed for last_updated, the datetime.datetime.now() Python function is executed and its return value used as the value for last_updated. Notice that we provide now as the function itself without calling it (i.e. there are no parenthesis following) - SQLAlchemy will execute the function at the time the statement executes.

当update语句执行时发现没有为last_updated字段提供参数值,datetime.datetime.now()函数将被执行并将返回值作为该字段的值,注意我们把now函数本身作为参数但没有调用它(也就是说,没有紧跟其后的括号)---SQLAlchemy 会在语句执行的当时执行该函数.