版本:1.1.0b2 |发布日期:2016年7月1日

SQLAlchemy 1.1文档

更改和迁移

项目版本

SQLAlchemy 0.9中有什么新东西?

关于本文档

本文档介绍了2013年5月发布的SQLAlchemy版本0.8,2013年12月30日发布的SQLAlchemy 0.9版本之间的更改。

文件最后更新:2015年6月10日

引言¶ T0>

本指南介绍了SQLAlchemy 0.9版中的新增功能,还介绍了影响用户将其应用程序从0.8系列SQLAlchemy迁移到0.9的更改。

请仔细阅读Behavioral Changes - ORMBehavioral Changes - Core以了解可能的向后不兼容的变更。

平台支持

针对Python 2.6及更高版本,Python 3没有2to3

0.9版本的第一个成就是消除了对Python 3兼容性的2to3工具的依赖。为了更简单明了,目前最低的Python版本是2.6,它具有与Python 3的广泛兼容性。现在所有的SQLAlchemy模块和单元测试都可以同2.6版本的Python解释器解释,包括3.1和3.2解释器。

#2671 T0>

Python 3支持的C扩展

C扩展已经移植到支持Python 3,现在可以在Python 2和Python 3环境中构建。

#2161 T0>

行为改变 - ORM

当按属性查询时,复合属性现在作为对象的形式返回

Query与复合属性结合使用,现在返回由该复合维护的对象类型,而不是分解为单独的列。使用Composite Column Types

>>> session.query(Vertex.start, Vertex.end).\
...     filter(Vertex.start == Point(3, 4)).all()
[(Point(x=3, y=4), Point(x=5, y=6))]

这种改变是与代码相反的,这些代码需要将单个属性扩展为单独的列。要获得这种行为,请使用.clauses存取器:

>>> session.query(Vertex.start.clauses, Vertex.end.clauses).\
...     filter(Vertex.start == Point(3, 4)).all()
[(3, 4, 5, 6)]

#2824 T0>

Query.select_from()不再将该子句应用于相应的实体

The Query.select_from() method has been popularized in recent versions as a means of controlling the first thing that a Query object “selects from”, typically for the purposes of controlling how a JOIN will render.

考虑下面的例子,对照通常的User映​​射:

select_stmt = select([User]).where(User.id == 7).alias()

q = session.query(User).\
           join(select_stmt, User.id == select_stmt.c.id).\
           filter(User.name == 'ed')

上面的语句可预测地呈现如下的SQL:

SELECT "user".id AS user_id, "user".name AS user_name
FROM "user" JOIN (SELECT "user".id AS id, "user".name AS name
FROM "user"
WHERE "user".id = :id_1) AS anon_1 ON "user".id = anon_1.id
WHERE "user".name = :name_1

如果我们想要颠倒JOIN的左侧和右侧元素的顺序,文档将使我们相信我们可以使用Query.select_from()来执行此操作:

q = session.query(User).\
        select_from(select_stmt).\
        join(User, User.id == select_stmt.c.id).\
        filter(User.name == 'ed')

However, in version 0.8 and earlier, the above use of Query.select_from() would apply the select_stmt to replace the User entity, as it selects from the user table which is compatible with User:

-- SQLAlchemy 0.8 and earlier...
SELECT anon_1.id AS anon_1_id, anon_1.name AS anon_1_name
FROM (SELECT "user".id AS id, "user".name AS name
FROM "user"
WHERE "user".id = :id_1) AS anon_1 JOIN "user" ON anon_1.id = anon_1.id
WHERE anon_1.name = :name_1

The above statement is a mess, the ON clause refers anon_1.id = anon_1.id, our WHERE clause has been replaced with anon_1 as well.

This behavior is quite intentional, but has a different use case from that which has become popular for Query.select_from(). 上述行为现在可以通过称为Query.select_entity_from()的新方法获得。这是一个较少使用的行为,在现代SQLAlchemy中大致等同于从定制的aliased()构造中进行选择:

select_stmt = select([User]).where(User.id == 7)
user_from_stmt = aliased(User, select_stmt.alias())

q = session.query(user_from_stmt).filter(user_from_stmt.name == 'ed')

所以在SQLAlchemy 0.9中,我们从select_stmt中选择的查询产生了我们期望的SQL:

-- SQLAlchemy 0.9
SELECT "user".id AS user_id, "user".name AS user_name
FROM (SELECT "user".id AS id, "user".name AS name
FROM "user"
WHERE "user".id = :id_1) AS anon_1 JOIN "user" ON "user".id = id
WHERE "user".name = :name_1

Query.select_entity_from()方法将在SQLAlchemy 0.8.2中可用,所以依赖于旧行为的应用程序可以首先转换到此方法,确保所有测试继续功能,然后升级到0.9没有问题。

#2736 T0>

viewonly=True on relationship() prevents history from taking effect

relationship()上的viewonly标志用于防止在flush过程中对target属性的更改产生任何影响。这是通过消除冲洗期间考虑的属性来实现的。但是,直到现在,对属性的更改仍然会将父对象注册为“脏”并触发潜在的刷新。改变是,viewonly标志现在也禁止为目标属性设置历史记录。像backrefs和用户定义的事件属性事件仍然继续正常工作。

更改如下所示:

from sqlalchemy import Column, Integer, ForeignKey, create_engine
from sqlalchemy.orm import backref, relationship, Session
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import inspect

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)

class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))
    a = relationship("A", backref=backref("bs", viewonly=True))

e = create_engine("sqlite://")
Base.metadata.create_all(e)

a = A()
b = B()

sess = Session(e)
sess.add_all([a, b])
sess.commit()

b.a = a

assert b in sess.dirty

# before 0.9.0
# assert a in sess.dirty
# assert inspect(a).attrs.bs.history.has_changes()

# after 0.9.0
assert a not in sess.dirty
assert not inspect(a).attrs.bs.history.has_changes()

#2833 T0>

关联代理SQL表达式改进和修复

通过引用标量关系上的标量值的关联代理实现的==!=运算符现在生成更完整的SQL表达式,旨在考虑当比较是针对None时,“关联”行是否存在。

考虑这个映射:

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)

    b_id = Column(Integer, ForeignKey('b.id'), primary_key=True)
    b = relationship("B")
    b_value = association_proxy("b", "value")

class B(Base):
    __tablename__ = 'b'
    id = Column(Integer, primary_key=True)
    value = Column(String)

通过0.8,查询如下:

s.query(A).filter(A.b_value == None).all()

会产生:

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a
WHERE EXISTS (SELECT 1
FROM b
WHERE b.id = a.b_id AND b.value IS NULL)

在0.9中,它现在产生:

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a
WHERE (EXISTS (SELECT 1
FROM b
WHERE b.id = a.b_id AND b.value IS NULL)) OR a.b_id IS NULL

不同之处在于,它不仅检查b.value,还检查a是否根本不指向b行。这将返回不同的结果与以前的版本,对于使用这种类型的比较,其中一些父行没有关联行的系统。

更关键的是,一个正确的表达式是为.b_value != 发出的。在0.8中,对于没有bA行,这将返回True

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a
WHERE NOT (EXISTS (SELECT 1
FROM b
WHERE b.id = a.b_id AND b.value IS NULL))

现在在0.9中,检查已经被重新编译,以确保A.b_id行的存在,除了B.value是非NULL:

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a
WHERE EXISTS (SELECT 1
FROM b
WHERE b.id = a.b_id AND b.value IS NOT NULL)

另外,has()运算符已经被增强,这样你就可以在没有标准的情况下调用标量列值,并且它将生成检查关联行是否存在的标准:

s.query(A).filter(A.b_value.has()).all()

输出:

SELECT a.id AS a_id, a.b_id AS a_b_id
FROM a
WHERE EXISTS (SELECT 1
FROM b
WHERE b.id = a.b_id)

这相当于A.b.has(),但允许直接查询b_value

#2751 T0>

关联代理丢失标量返回无

如果代理对象不存在,则从标量属性到标量的关联代理现在将返回None这与SQLAlchemy中缺少多对一返回None的事实是一致的,所以应该使用代理值。例如。:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.ext.associationproxy import association_proxy

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    b = relationship("B", uselist=False)

    bname = association_proxy("b", "name")

class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))
    name = Column(String)

a1 = A()

# this is how m2o's always have worked
assert a1.b is None

# but prior to 0.9, this would raise AttributeError,
# now returns None just like the proxied value.
assert a1.bname is None

#2810 T0>

如果值不存在,attributes.get_history()将默认从数据库中查询

A bugfix regarding attributes.get_history() allows a column-based attribute to query out to the database for an unloaded value, assuming the passive flag is left at its default of PASSIVE_OFF. 以前,这个标志不会被兑现。此外,还添加了一个新的方法AttributeState.load_history()以补充AttributeState.history属性,该属性将为未加载的属性发送加载程序可调用的对象。

这是一个小小的变化,展示如下:

from sqlalchemy import Column, Integer, String, create_engine, inspect
from sqlalchemy.orm import Session, attributes
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'
    id = Column(Integer, primary_key=True)
    data = Column(String)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

sess = Session(e)

a1 = A(data='a1')
sess.add(a1)
sess.commit()  # a1 is now expired

# history doesn't emit loader callables
assert inspect(a1).attrs.data.history == (None, None, None)

# in 0.8, this would fail to load the unloaded state.
assert attributes.get_history(a1, 'data') == ((), ['a1',], ())

# load_history() is now equiavlent to get_history() with
# passive=PASSIVE_OFF ^ INIT_OK
assert inspect(a1).attrs.data.load_history() == ((), ['a1',], ())

#2787 T0>

行为改变 - 核心

类型对象不再接受被忽略的关键字参数

通过0.8系列,大多数类型的对象接受了任意的关键字参数,

from sqlalchemy import Date, Integer

# storage_format argument here has no effect on any backend;
# it needs to be on the SQLite-specific type
d = Date(storage_format="%(day)02d.%(month)02d.%(year)04d")

# display_width argument here has no effect on any backend;
# it needs to be on the MySQL-specific type
i = Integer(display_width=5)

这是一个非常古老的bug,为0.8系列增加了弃用警告,但是因为没有人用“-W”标志运行Python,所以大部分都没有看到:

$ python -W always::DeprecationWarning ~/dev/sqlalchemy/test.py
/Users/classic/dev/sqlalchemy/test.py:5: SADeprecationWarning: Passing arguments to
type object constructor <class 'sqlalchemy.types.Date'> is deprecated
  d = Date(storage_format="%(day)02d.%(month)02d.%(year)04d")
/Users/classic/dev/sqlalchemy/test.py:9: SADeprecationWarning: Passing arguments to
type object constructor <class 'sqlalchemy.types.Integer'> is deprecated
  i = Integer(display_width=5)

从0.9系列开始,“catch all”构造函数从TypeEngine中被移除,这些无意义的参数不再被接受。

使用特定于方言的参数(如storage_formatdisplay_width)的正确方法是使用适当的方言特定类型:

from sqlalchemy.dialects.sqlite import DATE
from sqlalchemy.dialects.mysql import INTEGER

d = DATE(storage_format="%(day)02d.%(month)02d.%(year)04d")

i = INTEGER(display_width=5)

那么我们想要方言不可知类型的情况呢?我们使用TypeEngine.with_variant()方法:

from sqlalchemy import Date, Integer
from sqlalchemy.dialects.sqlite import DATE
from sqlalchemy.dialects.mysql import INTEGER

d = Date().with_variant(
        DATE(storage_format="%(day)02d.%(month)02d.%(year)04d"),
        "sqlite"
    )

i = Integer().with_variant(
        INTEGER(display_width=5),
        "mysql"
    )

TypeEngine.with_variant() isn’t new, it was added in SQLAlchemy 0.7.2. 因此,在0.8系列上运行的代码可以更正为在升级到0.9之前使用此方法并进行测试。

None can no longer be used as a “partial AND” constructor

None can no longer be used as the “backstop” to form an AND condition piecemeal. 即使某些SQLAlchemy内部使用它,这种模式也不是一个文档模式:

condition = None

for cond in conditions:
    condition = condition & cond

if condition is not None:
    stmt = stmt.where(condition)

The above sequence, when conditions is non-empty, will on 0.9 produce SELECT .. WHERE <condition> AND NULL. None不再被隐式忽略,而是与其他上下文中的None解释一致。

0.8和0.9的正确代码应为:

from sqlalchemy.sql import and_

if conditions:
    stmt = stmt.where(and_(*conditions))

另一个可以在0.9上运行的后端变种,但是在0.8上只能在后端支持布尔常量:

from sqlalchemy.sql import true

condition = true()

for cond in conditions:
    condition = cond & condition

stmt = stmt.where(condition)

在0.8上,这将产生一个在WHERE子句中总是有AND true的SELECT语句,而后者不会被接受支持布尔常量(MySQL,MSSQL)。在0.9时,true常量将在and_()连接符中被删除。

create_engine()的“密码”部分不再将+标记视为编码空间

无论出于何种原因,Python函数unquote_plus()被应用于URL的“password”字段,这是RFC 1738中描述的编码规则的错误应用它作为加号逃脱了空格。现在URL的字符串只对“:​​”,“@”或“/”进行编码,并且现在也应用于usernamepassword以前它只适用于密码)。在解析时,编码字符被转换,但加号和空格按原样传递:

# password: "pass word + other:words"
dbtype://user:pass word + other%3Awords@host/dbname

# password: "apples/oranges"
dbtype://username:apples%2Foranges@hostspec/database

# password: "apples@oranges@@"
dbtype://username:apples%40oranges%40%40@hostspec/database

# password: '', username is "username@"
dbtype://username%40:@hostspec/database

#2873 T0>

COLLATE的优先规则已经改变了

以前,像下面这样的表达式:

print((column('x') == 'somevalue').collate("en_EN"))

会产生这样的表达式:

-- 0.8 behavior
(x = :x_1) COLLATE en_EN

以上是由MSSQL误解,通常不是任何数据库建议的语法。现在,该表达式将生成大多数数据库文档所示的语法:

-- 0.9 behavior
x = :x_1 COLLATE en_EN

The potentially backwards incompatible change arises if the collate() operator is being applied to the right-hand column, as follows:

print(column('x') == literal('somevalue').collate("en_EN"))

在0.8中,这产生:

x = :param_1 COLLATE en_EN

然而在0.9中,现在会产生更准确的,但可能不是你想要的,形式如下:

x = (:param_1 COLLATE en_EN)

The ColumnOperators.collate() operator now works more appropriately within an ORDER BY expression as well, as a specific precedence has been given to the ASC and DESC operators which will again ensure no parentheses are generated:

>>> # 0.8
>>> print(column('x').collate('en_EN').desc())
(x COLLATE en_EN) DESC

>>> # 0.9
>>> print(column('x').collate('en_EN').desc())
x COLLATE en_EN DESC

#2879 T0>

Postgresql CREATE TYPE AS ENUM现在将引用应用于值

现在,postgresql.ENUM类型将应用于枚举值中的单引号:

>>> from sqlalchemy.dialects import postgresql
>>> type = postgresql.ENUM('one', 'two', "three's", name="myenum")
>>> from sqlalchemy.dialects.postgresql import base
>>> print(base.CreateEnumType(type).compile(dialect=postgresql.dialect()))
CREATE TYPE myenum AS ENUM ('one','two','three''s')

现有的解决方法已经逃脱了单引号,需要修改,否则他们现在会双重转义。

#2878 T0>

新功能

事件清除API

现在可以使用新的event.remove()函数删除使用event.listen()event.listens_for()建立的事件。发送到event.remove()targetidentifierfn参数需要与发送的参数完全匹配聆听,事件将从其所在的所有地点被删除:

@event.listens_for(MyClass, "before_insert", propagate=True)
def my_before_insert(mapper, connection, target):
    """listen for before_insert"""
    # ...

event.remove(MyClass, "before_insert", my_before_insert)

在上面的例子中,设置了propagate=True标志。这意味着my_before_insert()被建立为MyClass以及MyClass的所有子类的侦听器。The system tracks everywhere that the my_before_insert() listener function had been placed as a result of this call and removes it as a result of calling event.remove().

删除系统使用注册表将传递给event.listen()的参数与事件侦听器的集合关联,事件侦听器在很多情况下都是原始用户提供的函数的封装版本。此注册表大量使用弱引用,以便允许所有包含的内容(如侦听器目标)在超出范围时进行垃圾回收。

#2268 T0>

新的查询选项API; load_only()选项

The system of loader options such as orm.joinedload(), orm.subqueryload(), orm.lazyload(), orm.defer(), etc. 都建立在一个称为Load的新系统上。Load提供了一种“方法链接”(a.k.a.generative)加载器选项的方法,以便不使用点或多个属性名称将长路径连接在一起,而是为每个路径指定一个显式加载器样式。

虽然新的方式略为冗长,但理解起来更简单一点,就是在哪些路径上应用了哪些选项没有模棱两可的问题。它简化了选项的方法签名,并提供了更大的灵活性,特别是对于基于列的选项。旧系统也将无限期地保持功能,所有样式都可以混合使用。

Old Way

要在多元素路径中的每个链接上设置一定的加载样式,必须使用_all()选项:

query(User).options(joinedload_all("orders.items.keywords"))

New Way

Loader选项现在是可链接的,所以相同的joinedload(x)方法同样适用于每个链接,而不需要在joinedload()joinedload_all()

query(User).options(joinedload("orders").joinedload("items").joinedload("keywords"))

Old Way

在基于子类的路径上设置选项要求将路径中的所有链接拼写为类绑定属性,因为需要调用PropComparator.of_type()方法:

session.query(Company).\
    options(
        subqueryload_all(
            Company.employees.of_type(Engineer),
            Engineer.machines
        )
    )

New Way

只有路径中实际需要PropComparator.of_type()的元素需要设置为类绑定属性,之后可以恢复基于字符串的名称:

session.query(Company).\
    options(
        subqueryload(Company.employees.of_type(Engineer)).
        subqueryload("machines")
        )
    )

Old Way

在长路径的最后一个链接上设置加载器选项使用的语法看起来很像应该为路径中的所有链接设置选项,导致混淆:

query(User).options(subqueryload("orders.items.keywords"))

New Way

现在可以使用defaultload()来为现有加载程序样式应该保持不变的路径中的条目拼写路径。更详细,但意图更清晰:

query(User).options(defaultload("orders").defaultload("items").subqueryload("keywords"))

虚线的风格仍然可以利用,特别是在跳过几个路径元素的情况下:

query(User).options(defaultload("orders.items").subqueryload("keywords"))

Old Way

路径上的defer()选项需要用每个列的完整路径拼写出来:

query(User).options(defer("orders.description"), defer("orders.isopen"))

New Way

到达目标路径的单个Load对象可以有Load.defer()重复调用它:

query(User).options(defaultload("orders").defer("description").defer("isopen"))

加载类

可以直接使用Load类来提供“绑定”目标,特别是当存在多个父实体时:

from sqlalchemy.orm import Load

query(User, Address).options(Load(Address).joinedload("entries"))

仅加载

一个新的选项load_only()实现了“推迟一切,但是”的加载风格,只加载给定的列,并推迟其余的:

from sqlalchemy.orm import load_only

query(User).options(load_only("name", "fullname"))

# specify explicit parent entity
query(User, Address).options(Load(User).load_only("name", "fullname"))

# specify path
query(User).options(joinedload(User.addresses).load_only("email_address"))

特定于类的通配符

使用Load,可以使用通配符为给定实体上的所有关系(或可能是列)设置加载,而不会影响其他任何关系:

# lazyload all User relationships
query(User).options(Load(User).lazyload("*"))

# undefer all User columns
query(User).options(Load(User).undefer("*"))

# lazyload all Address relationships
query(User).options(defaultload(User.addresses).lazyload("*"))

# undefer all Address columns
query(User).options(defaultload(User.addresses).undefer("*"))

#1418 T0>

text()功能

text()结构获得了新的方法:

  • TextClause.bindparams() allows bound parameter types and values to be set flexibly:

    # setup values
    stmt = text("SELECT id, name FROM user "
          "WHERE name=:name AND timestamp=:timestamp").\
          bindparams(name="ed", timestamp=datetime(2012, 11, 10, 15, 12, 35))
    
    # setup types and/or values
    stmt = text("SELECT id, name FROM user "
          "WHERE name=:name AND timestamp=:timestamp").\
          bindparams(
              bindparam("name", value="ed"),
              bindparam("timestamp", type_=DateTime()
          ).bindparam(timestamp=datetime(2012, 11, 10, 15, 12, 35))
  • TextClause.columns() supersedes the typemap option of text(), returning a new construct TextAsFrom:

    # turn a text() into an alias(), with a .c. collection:
    stmt = text("SELECT id, name FROM user").columns(id=Integer, name=String)
    stmt = stmt.alias()
    
    stmt = select([addresses]).select_from(
                  addresses.join(stmt), addresses.c.user_id == stmt.c.id)
    
    
    # or into a cte():
    stmt = text("SELECT id, name FROM user").columns(id=Integer, name=String)
    stmt = stmt.cte("x")
    
    stmt = select([addresses]).select_from(
                  addresses.join(stmt), addresses.c.user_id == stmt.c.id)

#2877 T0>

从SELECT 插入

经过几年的毫无意义的拖延之后,这个相对较小的语法特征已经被添加,并且也被支持到0.8.3,所以在技术上0.9不是“新”。A select() construct or other compatible construct can be passed to the new method Insert.from_select() where it will be used to render an INSERT .. SELECT construct:

>>> from sqlalchemy.sql import table, column
>>> t1 = table('t1', column('a'), column('b'))
>>> t2 = table('t2', column('x'), column('y'))
>>> print(t1.insert().from_select(['a', 'b'], t2.select().where(t2.c.y == 5)))
INSERT INTO t1 (a, b) SELECT t2.x, t2.y
FROM t2
WHERE t2.y = :y_1

该构造足够聪明,可以容纳诸如类和Query对象之类的ORM对象:

s = Session()
q = s.query(User.id, User.name).filter_by(name='ed')
ins = insert(Address).from_select((Address.id, Address.email_address), q)

渲染:

INSERT INTO addresses (id, email_address)
SELECT users.id AS users_id, users.name AS users_name
FROM users WHERE users.name = :name_1

#722 T0>

select()Query()

An attempt is made to simplify the specification of the FOR UPDATE clause on SELECT statements made within Core and ORM, and support is added for the FOR UPDATE OF SQL supported by Postgresql and Oracle.

使用核心GenerativeSelect.with_for_update(),像FOR SHARENOWAIT可以单独指定,而不是链接到任意字符串代码:

stmt = select([table]).with_for_update(read=True, nowait=True, of=table)

在Posgtresql上面的语句可能呈现如下:

SELECT table.a, table.b FROM table FOR SHARE OF table NOWAIT

The Query object gains a similar method Query.with_for_update() which behaves in the same way. 此方法取代了使用不同系统翻译FOR UPDATE子句的现有的Query.with_lockmode()方法。目前,“lockmode”字符串参数仍被Session.refresh()方法接受。

浮点数字符串转换精度可配置为本地浮点类型

The conversion which SQLAlchemy does whenever a DBAPI returns a Python floating point type which is to be converted into a Python Decimal() necessarily involves an intermediary step which converts the floating point value to a string. 用于此字符串转换的比例先前被硬编码为10,现在可以配置。该设置可以在NumericFloat类型以及所有SQL和特定于方言的后代类型上使用参数decimal_return_scaleIf the type supports a .scale parameter, as is the case with Numeric and some float types such as mysql.DOUBLE, the value of .scale is used as the default for .decimal_return_scale if it is not otherwise specified. 如果.scale.decimal_return_scale都不存在,则默认值为10。例如。:

from sqlalchemy.dialects.mysql import DOUBLE
import decimal

data = Table('data', metadata,
    Column('double_value',
                mysql.DOUBLE(decimal_return_scale=12, asdecimal=True))
)

conn.execute(
    data.insert(),
    double_value=45.768392065789,
)
result = conn.scalar(select([data.c.double_value]))

# previously, this would typically be Decimal("45.7683920658"),
# e.g. trimmed to 10 decimal places

# now we get 12, as requested, as MySQL can support this
# much precision for DOUBLE
assert result == decimal.Decimal("45.768392065789")

#2867 T0>

用于ORM查询的列包

Bundle允许查询一组列,然后按照查询返回的元组将其分组到一个名称中。Bundle的初始目的是1.允许“合成”ORM列作为基于列的结果集中的单个值返回,而不是将它们展开到单个列中,并且2.允许在ORM中创建自定义结果集构造,使用ad-hoc列和返回类型,而不涉及映射类的更重量级的机制。

#2824 T0>

服务器端版本计数

现在,ORM的版本控制功能(现在还在Configuring a Version Counter中有记录)现在可以使用服务器端版本计数方案,例如由触发器或数据库系统列产生的方案,以及条件在version_id_counter函数本身之外的编程方案。通过向version_id_generator参数提供False值,ORM将使用已经设置的版本标识符,或者同时从每行中获取版本标识符INSERT或更新被发射。当使用服务器生成的版本标识符时,强烈建议仅在具有强大的RETURNING支持(Postgresql,SQL Server; Oracle也支持RETURNING,但cx_oracle驱动程序只有有限的支持)的后端使用此功能,否则额外的SELECT语句会增加显着的性能开销。Server Side Version Counters中提供的示例说明了Postgresql xmin系统列的使用情况,以便将其与ORM的版本控制功能集成在一起。

#2793 T0>

include_backrefs=False选项用于@validates

validates()函数现在接受一个选项include_backrefs=True,这个选项会绕过启动事件的验证器,

from sqlalchemy import Column, Integer, ForeignKey
from sqlalchemy.orm import relationship, validates
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()

class A(Base):
    __tablename__ = 'a'

    id = Column(Integer, primary_key=True)
    bs = relationship("B", backref="a")

    @validates("bs")
    def validate_bs(self, key, item):
        print("A.bs validator")
        return item

class B(Base):
    __tablename__ = 'b'

    id = Column(Integer, primary_key=True)
    a_id = Column(Integer, ForeignKey('a.id'))

    @validates("a", include_backrefs=False)
    def validate_a(self, key, item):
        print("B.a validator")
        return item

a1 = A()
a1.bs.append(B())  # prints only "A.bs validator"

#1535 T0>

Postgresql JSON类型

Postgresql方言现在使用postgresql.JSON类型来补充postgresql.HSTORE类型。

也可以看看

postgresql.JSON

#2581 T0>

Automap扩展

0.9.1中添加一个新的扩展,称为sqlalchemy.ext.automapThis is an experimental extension which expands upon the functionality of Declarative as well as the DeferredReflection class. Essentially, the extension provides a base class AutomapBase which automatically generates mapped classes and relationships between them based on given table metadata.

通常使用的MetaData可能是通过反射产生的,但是不要求使用反射。The most basic usage illustrates how sqlalchemy.ext.automap is able to deliver mapped classes, including relationships, based on a reflected schema:

from sqlalchemy.ext.automap import automap_base
from sqlalchemy.orm import Session
from sqlalchemy import create_engine

Base = automap_base()

# engine, suppose it has two tables 'user' and 'address' set up
engine = create_engine("sqlite:///mydatabase.db")

# reflect the tables
Base.prepare(engine, reflect=True)

# mapped classes are now created with names matching that of the table
# name.
User = Base.classes.user
Address = Base.classes.address

session = Session(engine)

# rudimentary relationships are produced
session.add(Address(email_address="foo@bar.com", user=User(name="foo")))
session.commit()

# collection-based relationships are by default named "<classname>_collection"
print(u1.address_collection)

除此之外,AutomapBase类是声明性的基础,并且支持所有声明性的特性。“自动映射”功能可以与现有的显式声明的模式一起使用,以仅生成关系和丢失的类。命名方案和关系生产例程可以使用可调用函数来放弃。

It is hoped that the AutomapBase system provides a quick and modernized solution to the problem that the very famous SQLSoup also tries to solve, that of generating a quick and rudimentary object model from an existing database on the fly. By addressing the issue strictly at the mapper configuration level, and integrating fully with existing Declarative class techniques, AutomapBase seeks to provide a well-integrated approach to the issue of expediently auto-generating ad-hoc mappings.

也可以看看

Automap

行为改进

除非是非常罕见和不寻常的假设情况,否则应该不会产生兼容性问题,但如果出现意外问题,则需要注意。

许多JOIN和LEFT OUTER JOIN表达式将不再包含在(SELECT * FROM ..)AS ANON_1

多年以来,SQLAlchemy ORM已经被阻止在一个现有的JOIN的右侧嵌套一个JOIN(通常是LEFT OUTER JOIN,因为INNER JOIN总是可以被展平):

SELECT a.*, b.*, c.* FROM a LEFT OUTER JOIN (b JOIN c ON b.id = c.id) ON a.id

这是由于直到版本3.7.16的SQLite无法解析上述格式的语句:

SQLite version 3.7.15.2 2013-01-09 11:53:05
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> create table a(id integer);
sqlite> create table b(id integer);
sqlite> create table c(id integer);
sqlite> select a.id, b.id, c.id from a left outer join (b join c on b.id=c.id) on b.id=a.id;
Error: no such column: b.id

右外连接当然是解决右括号的另一种方法;这将是非常复杂和视觉上不愉快的实现,但幸运的是SQLite不支持RIGHT OUTER JOIN :):

sqlite> select a.id, b.id, c.id from b join c on b.id=c.id
   ...> right outer join a on b.id=a.id;
Error: RIGHT and FULL OUTER JOINs are not currently supported

早在2005年,还不清楚其他数据库是否存在这种形式的问题,但是现在看来每个测试数据库除了SQLite之外,现在都支持它了(Oracle 8是一个非常古老的数据库,根本不支持JOIN关键字,但是SQLAlchemy对Oracle的语法总是有一个简单的重写方案)。更糟糕的是,应用SELECT的SQLAlchemy通常的解决方法通常会降低Postgresql和MySQL等平台的性能:

SELECT a.*, anon_1.* FROM a LEFT OUTER JOIN (
                SELECT b.id AS b_id, c.id AS c_id
                FROM b JOIN c ON b.id = c.id
            ) AS anon_1 ON a.id=anon_1.b_id

与上面的表单一样的JOIN在使用连接表继承结构时是司空见惯的;任何时候Query.join()用于从父节点连接到连接表子类,或者当类似地使用joinedload()时,SQLAlchemy的ORM将始终确保一个嵌套的JOIN永远不会呈现,以免查询无法在SQLite上运行。尽管Core一直支持更加紧凑的形式,但ORM必须避免它。

在ON子句中存在特殊标准的多对多关系中产生连接时,会出现另外一个问题。考虑一个像下面这样的加载连接:

session.query(Order).outerjoin(Order.items)

假设从OrderItem的多对多实际上是指像Subitem这样的子类,那么上面的SQL将如下所示:

SELECT order.id, order.name
FROM order LEFT OUTER JOIN order_item ON order.id = order_item.order_id
LEFT OUTER JOIN item ON order_item.item_id = item.id AND item.type = 'subitem'

上述查询有什么问题?基本上,它会加载很多order / order_item行,其中item.type == t6 > “子项目”不正确。

从SQLAlchemy 0.9开始,我们采用了一种全新的方法。ORM不再担心在一个封闭的JOIN的右侧嵌套JOIN,现在它将尽可能经常地渲染这些JOG,同时仍然返回正确的结果。When the SQL statement is passed to be compiled, the dialect compiler will rewrite the join to suit the target backend, if that backend is known to not support a right-nested JOIN (which currently is only SQLite - if other backends have this issue please let us know! )。

所以常规的query(Parent).join(Subclass)通常会产生一个更简单的表达式:

SELECT parent.id AS parent_id
FROM parent JOIN (
        base_table JOIN subclass_table
        ON base_table.id = subclass_table.id) ON parent.id = base_table.parent_id

加入像query(Parent).options(joinedload(Parent.subclasses))这样的预先加载将替换个别表而不是包装在ANON_1中:

SELECT parent.*, base_table_1.*, subclass_table_1.* FROM parent
    LEFT OUTER JOIN (
        base_table AS base_table_1 JOIN subclass_table AS subclass_table_1
        ON base_table_1.id = subclass_table_1.id)
        ON parent.id = base_table_1.parent_id

多对多连接和eagerloads将嵌套“二级”和“右”表:

SELECT order.id, order.name
FROM order LEFT OUTER JOIN
(order_item JOIN item ON order_item.item_id = item.id AND item.type = 'subitem')
ON order_item.order_id = order.id

所有这些连接,当用特别指定use_labels=TrueSelect语句呈现时,对于ORM发出的所有查询都是正确的,这些连接是“连接重写” ,这是将所有这些右嵌套连接重写为嵌套的SELECT语句的过程,同时保持由Select使用的相同标签。因此,SQLite即使在2013年也不会支持这种非常常见的SQL语法的一个数据库本身就具有额外的复杂性,上面的查询被重写为:

-- sqlite only!
SELECT parent.id AS parent_id
    FROM parent JOIN (
        SELECT base_table.id AS base_table_id,
                base_table.parent_id AS base_table_parent_id,
                subclass_table.id AS subclass_table_id
        FROM base_table JOIN subclass_table ON base_table.id = subclass_table.id
    ) AS anon_1 ON parent.id = anon_1.base_table_parent_id

-- sqlite only!
SELECT parent.id AS parent_id, anon_1.subclass_table_1_id AS subclass_table_1_id,
        anon_1.base_table_1_id AS base_table_1_id,
        anon_1.base_table_1_parent_id AS base_table_1_parent_id
FROM parent LEFT OUTER JOIN (
    SELECT base_table_1.id AS base_table_1_id,
        base_table_1.parent_id AS base_table_1_parent_id,
        subclass_table_1.id AS subclass_table_1_id
    FROM base_table AS base_table_1
    JOIN subclass_table AS subclass_table_1 ON base_table_1.id = subclass_table_1.id
) AS anon_1 ON parent.id = anon_1.base_table_1_parent_id

-- sqlite only!
SELECT "order".id AS order_id
FROM "order" LEFT OUTER JOIN (
        SELECT order_item_1.order_id AS order_item_1_order_id,
            order_item_1.item_id AS order_item_1_item_id,
            item.id AS item_id, item.type AS item_type
FROM order_item AS order_item_1
    JOIN item ON item.id = order_item_1.item_id AND item.type IN (?)
) AS anon_1 ON "order".id = anon_1.order_item_1_order_id

注意

从SQLAlchemy 1.1开始,当SQLite版本3.7.16或更高版本被检测到时,SQLite的这个特性中的解决方法将自动禁用,因为SQLite修复了对右嵌套连接的支持。

The Join.alias(), aliased() and with_polymorphic() functions now support a new argument, flat=True, which is used to construct aliases of joined-table entities without embedding into a SELECT. 默认情况下,这个标志没有打开,以帮助向后兼容 - 但是现在可以选择一个“polymorhpic”作为目标而不生成任何子查询:

employee_alias = with_polymorphic(Person, [Engineer, Manager], flat=True)

session.query(Company).join(
                    Company.employees.of_type(employee_alias)
                ).filter(
                    or_(
                        Engineer.primary_language == 'python',
                        Manager.manager_name == 'dilbert'
                    )
                )

生成(除了SQLite以外):

SELECT companies.company_id AS companies_company_id, companies.name AS companies_name
FROM companies JOIN (
    people AS people_1
    LEFT OUTER JOIN engineers AS engineers_1 ON people_1.person_id = engineers_1.person_id
    LEFT OUTER JOIN managers AS managers_1 ON people_1.person_id = managers_1.person_id
) ON companies.company_id = people_1.company_id
WHERE engineers.primary_language = %(primary_language_1)s
    OR managers.manager_name = %(manager_name_1)s

#2369 #2587

右连接的内连接可用于连接的预载

从版本0.9.4开始,上面提到的右嵌套连接可以在加入的热切加载情况下启用,其中“外部”连接与右侧的“内部”连接。

通常情况下,像下面这样加入一个加载链:

query(User).options(joinedload("orders", innerjoin=False).joinedload("items", innerjoin=True))

不会产生内连接;因为用户 - >顺序中的LEFT OUTER JOIN,所以加入的加载无法使用从order-> items的INNER连接,而不改变返回的用户行,而是忽略“chained”innerjoin=True0.9.0应该如何实现这将是,而不是:

FROM users LEFT OUTER JOIN orders ON <onclause> LEFT OUTER JOIN items ON <onclause>

新的“右嵌套连接就OK了”的逻辑会踢,我们会得到:

FROM users LEFT OUTER JOIN (orders JOIN items ON <onclause>) ON <onclause>

由于我们错过了这一点,为了避免进一步的回归,我们通过指定字符串"nested"joinedload.innerjoin添加了上述功能:

query(User).options(joinedload("orders", innerjoin=False).joinedload("items", innerjoin="nested"))

这个特性在0.9.4中是新的。

#2976 T0>

ORM可以使用RETURNING 高效地获取刚生成的INSERT / UPDATE默认值

Mapper长期支持一个名为eager_defaults=True的未公开标志。这个标志的作用是,当一个INSERT或UPDATE进行,并且该行已知具有服务器生成的默认值时,SELECT将立即跟随它,以便“急切地”加载这些新值。通常情况下,服务器生成的列在对象上标记为“过期”,除非应用程序在刷新后实际访问这些列,否则不会产生开销。因此,eager_defaults标志没有多大用处,因为它只能降低性能,并且仅用于支持用户需要在刷新过程中立即可用的默认值的外来事件方案。

In 0.9, as a result of the version id enhancements, eager_defaults can now emit a RETURNING clause for these values, so on a backend with strong RETURNING support in particular Postgresql, the ORM can fetch newly generated default and SQL expression values inline with the INSERT or UPDATE. eager_defaults, when enabled, makes use of RETURNING automatically when the target backend and Table supports “implicit returning”.

子查询Eager Loading将DISTINCT应用于某些查询的最内层的SELECT

为了减少在涉及多对一关系的情况下通过子查询加载可以生成的重复行的数量,当连接将目标列定位到不包含该对象的列时,DISTINCT关键字将被应用于最内层的SELECT主键,就像在加载时一对一一样。

也就是说,当从A-> B进行多对一的子查询时,

SELECT b.id AS b_id, b.name AS b_name, anon_1.b_id AS a_b_id
FROM (SELECT DISTINCT a_b_id FROM a) AS anon_1
JOIN b ON b.id = anon_1.a_b_id

由于a.b_id是非不同的外键,因此应用DISTINCT,以消除多余的a.b_id可以使用标记distinct_target_key为特定relationship()无条件地打开或关闭行为,将值设置为True False表示无条件关闭,而None表示当目标SELECT针对不包含完整主键的列时,功能才会生效。在0.9中,None是默认值。

该选项也被反向移至0.8,其中distinct_target_key选项默认为False

虽然此处的功能旨在通过消除重复行来帮助提高性能,但SQL本身中的DISTINCT关键字可能会对性能产生负面影响。如果SELECT中的列没有被索引,那么DISTINCT可能在行集上执行一个ORDER BY昂贵。通过保持功能仅限于希望在任何情况下索引的外键,预计新的默认值是合理的。

该功能也并不能消除每一个可能的重复行情;如果在连接链的其他地方存在多对一的情况,则可能仍然存在重复行。

#2836 T0>

Backref处理程序现在可以传播多个级别的

属性事件沿其“发起者”传递的机制,即与事件开始相关联的对象已经被改变;而不是传递一个AttributeImpl,而是传递一个新的对象attributes.Event;此对象引用AttributeImpl以及“操作标记”,表示操作是附加,删除或替换操作。

属性事件系统不再查看这个“启动器”对象,以便停止递归的一系列属性事件。相反,由于相互依赖的backref处理程序而阻止无限递归的系统已经被转移到ORM backref事件处理程序中,该处理程序现在承担确保一系列相互依赖的事件(如追加到集合A的作用)的角色。 bs,在响应中设置多对一的属性Ba)并没有进入无尽的递归流。这里的基本原理是,给定更多的事件传播的细节和控制的backref系统最终可以允许发生多于一个级别的操作;典型的情况是集合追加导致多对一的替换操作,这又会导致项目从以前的集合中被删除:

class Parent(Base):
    __tablename__ = 'parent'

    id = Column(Integer, primary_key=True)
    children = relationship("Child", backref="parent")

class Child(Base):
    __tablename__ = 'child'

    id = Column(Integer, primary_key=True)
    parent_id = Column(ForeignKey('parent.id'))

p1 = Parent()
p2 = Parent()
c1 = Child()

p1.children.append(c1)

assert c1.parent is p1  # backref event establishes c1.parent as p1

p2.children.append(c1)

assert c1.parent is p2  # backref event establishes c1.parent as p2
assert c1 not in p1.children  # second backref event removes c1 from p1.children

Above, prior to this change, the c1 object would still have been present in p1.children, even though it is also present in p2.children at the same time; the backref handlers would have stopped at replacing c1.parent with p2 instead of p1. In 0.9, using the more detailed Event object as well as letting the backref handlers make more detailed decisions about these objects, the propagation can continue onto removing c1 from p1.children while maintaining a check against the propagation from going into an endless recursive loop.

最终用户的代码是哪一个。使用AttributeEvents.set()AttributeEvents.append()AttributeEvents.remove()事件。由于这些事件可能需要修改以防止递归循环,所以启动进一步的属性修改操作,因为在没有backref事件处理程序的情况下,属性系统不再阻止事件链不断传播。此外,取决于initiator值的代码将需要根据新的API进行调整,并且必须准备好initiator的值以从其原始值因为backref处理程序现在可以在新的initiator值中交换某些操作的值。

#2789 T0>

输入系统现在处理渲染“文字绑定”值的任务

A new method is added to TypeEngine TypeEngine.literal_processor() as well as TypeDecorator.process_literal_param() for TypeDecorator which take on the task of rendering so-called “inline literal paramters” - parameters that normally render as “bound” values, but are instead being rendered inline into the SQL statement due to the compiler configuration. 当为诸如CheckConstraint的结构生成DDL时,以及在使用诸如op.inline_literal()的结构时由Alembic使用该特征。以前,对一些基本类型检查了一个简单的“isinstance”检查,并且无条件地使用了“绑定处理器”,从而导致字符串过早编码为utf-8等问题。

Custom types written with TypeDecorator should continue to work in “inline literal” scenarios, as the TypeDecorator.process_literal_param() falls back to TypeDecorator.process_bind_param() by default, as these methods usually handle a data manipulation, not as much how the data is presented to the database. TypeDecorator.process_literal_param() can be specified to specifically produce a string representing how a value should be rendered into an inline DDL statement.

#2838 T0>

模式标识符现在携带自己的引用信息

This change simplifies the Core’s usage of so-called “quote” flags, such as the quote flag passed to Table and Column. 该标志现在在字符串名称本身内部化了,现在它被表示为一个字符串子类quoted_name的一个实例。现在,IdentifierPreparer完全依赖于由quoted_name对象报告的引用偏好,而不是在大多数情况下检查任何明确的quote标志。这里解决的问题包括各种区分大小写的方法(如Engine.has_table())以及方言中的类似方法现在可以使用明确引用的名称,而不需要复杂化或引入向后不兼容的更改到这些API(其中许多是第三方),并带有引用标志的详细信息 - 特别是更广泛的标识符现在可以正确地与所谓的“大写”后端(如Oracle,Firebird和DB2)根据大小写不区分大小写的名称使用全部大写表格和列名称报告)。

quoted_name对象根据需要在内部使用;但是,如果其他关键字需要固定的引用偏好,则该类可公开使用。

#2812 T0>

改进了布尔常量,NULL常量,连词的渲染

新的功能已被添加到true()false()常量中,特别是与and_()or_()函数以及WHERE / HAVING子句与这些类型,整体布尔类型以及null()常量的行为。

从这样的表格开始:

from sqlalchemy import Table, Boolean, Integer, Column, MetaData

t1 = Table('t', MetaData(), Column('x', Boolean()), Column('y', Integer))

select结构现在将布尔列作为二进制表达式在不具有true / false常量beahvior的后端渲染:

>>> from sqlalchemy import select, and_, false, true
>>> from sqlalchemy.dialects import mysql, postgresql

>>> print(select([t1]).where(t1.c.x).compile(dialect=mysql.dialect()))
SELECT t.x, t.y  FROM t WHERE t.x = 1

The and_() and or_() constructs will now exhibit quasi “short circuit” behavior, that is truncating a rendered expression, when a true() or false() constant is present:

>>> print(select([t1]).where(and_(t1.c.y > 5, false())).compile(
...     dialect=postgresql.dialect()))
SELECT t.x, t.y FROM t WHERE false

true() can be used as the base to build up an expression:

>>> expr = true()
>>> expr = expr & (t1.c.y > 5)
>>> print(select([t1]).where(expr))
SELECT t.x, t.y FROM t WHERE t.y > :y_1

The boolean constants true() and false() themselves render as 0 = 1 and 1 = 1 for a backend with no boolean constants:

>>> print(select([t1]).where(and_(t1.c.y > 5, false())).compile(
...     dialect=mysql.dialect()))
SELECT t.x, t.y FROM t WHERE 0 = 1

None的解释,虽然不是特别有效的SQL,但至少现在是一致的:

>>> print(select([t1.c.x]).where(None))
SELECT t.x FROM t WHERE NULL

>>> print(select([t1.c.x]).where(None).where(None))
SELECT t.x FROM t WHERE NULL AND NULL

>>> print(select([t1.c.x]).where(and_(None, None)))
SELECT t.x FROM t WHERE NULL AND NULL

#2804 T0>

现在,标签构造可以在ORDER BY 中作为它们的名字进行渲染

对于在column子句和SELECT的ORDER BY子句中都使用Label的情况,标签将在ORDER BY子句中作为其名称呈现,假设底层方言报告支持这个功能。

例如。例如:

from sqlalchemy.sql import table, column, select, func

t = table('t', column('c1'), column('c2'))
expr = (func.foo(t.c.c1) + t.c.c2).label("expr")

stmt = select([expr]).order_by(expr)

print(stmt)

0.9之前会呈现为:

SELECT foo(t.c1) + t.c2 AS expr
FROM t ORDER BY foo(t.c1) + t.c2

现在呈现为:

SELECT foo(t.c1) + t.c2 AS expr
FROM t ORDER BY expr

如果除了简单的ASCDESC之外的标签没有进一步嵌入到ORDER BY中的表达式中,那么ORDER BY将只呈现标签。

上述格式适用于所有测试过的数据库,但可能与旧数据库版本(MySQL 4?Oracle 8?等等。)。根据用户报告,我们可以添加将禁用基于数据库版本检测的功能的规则。

#1068 T0>

RowProxy now has tuple-sorting behavior

The RowProxy object acts much like a tuple, but up until now would not sort as a tuple if a list of them were sorted using sorted(). __eq__()方法现在将两边都作为元组进行比较,并且还添加了__lt__()方法:

users.insert().execute(
        dict(user_id=1, user_name='foo'),
        dict(user_id=2, user_name='bar'),
        dict(user_id=3, user_name='def'),
    )

rows = users.select().order_by(users.c.user_name).execute().fetchall()

eq_(rows, [(2, 'bar'), (3, 'def'), (1, 'foo')])

eq_(sorted(rows), [(1, 'foo'), (2, 'bar'), (3, 'def')])

#2848 T0>

当类型可用时,不带类型的bindparam()构造会通过复制进行升级

“升级”一个bindparam()构造以承担封闭表达式的类型的逻辑已经以两种方式得到了改进。First, the bindparam() object is copied before the new type is assigned, so that the given bindparam() is not mutated in place. 其次,在编译InsertUpdate结构时,同样的操作发生在关于通过ValuesBase.values()方法。

如果给定一个无类型的bindparam()

bp = bindparam("some_col")

如果我们使用这个参数如下:

expr = mytable.c.col == bp

The type for bp remains as NullType, however if mytable.c.col is of type String, then expr.right, that is the right side of the binary expression, will take on the String type. 以前,bp本身已经被更改为具有String作为其类型。

同样,这个操作发生在一个InsertUpdate中:

stmt = mytable.update().values(col=bp)

上面,bp保持不变,但执行语句时将使用String类型,通过检查binds字典可以看到:

>>> compiled = stmt.compile()
>>> compiled.binds['some_col'].type
String

该功能允许自定义类型在INSERT / UPDATE语句中发挥其预期效果,而无需在每个bindparam()表达式中明确指定这些类型。

潜在的向后兼容的变化涉及两个不太可能的情况。Since the bound parameter is cloned, users should not be relying upon making in-place changes to a bindparam() construct once created. 另外,在InsertUpdate语句中使用bindparam()的代码依赖于bindparam()

#2850 T0>

列可以可靠地从通过ForeignKey 引用的列中获取它们的类型

有一个长期以来的行为,只要Column被一个ForeignKeyConstraint引用,就可以声明Column而被引用的列的类型将被复制到这个。问题是,这个功能从来没有工作得很好,没有得到维护。The core issue was that the ForeignKey object doesn’t know what target Column it refers to until it is asked, typically the first time the foreign key is used to construct a Join. 因此,直到那个时候,父Column不会有类型,或者更具体地说,它会有一个默认类型的NullType

虽然需要很长时间,但是重新组织ForeignKey对象初始化的工作已经完成,使得该特征最终可以工作。At the core of the change is that the ForeignKey.column attribute no longer lazily initializes the location of the target Column; the issue with this system was that the owning Column would be stuck with NullType as its type until the ForeignKey happened to be used.

In the new version, the ForeignKey coordinates with the eventual Column it will refer to using internal attachment events, so that the moment the referencing Column is associated with the MetaData, all ForeignKey objects that refer to it will be sent a message that they need to initialize their parent column. This system is more complicated but works more solidly; as a bonus, there are now tests in place for a wide variety of Column / ForeignKey configuration scenarios and error messages have been improved to be very specific to no less than seven different error conditions.

现在可以正常工作的情景包括:

  1. 只要目标Column与相同的MetaData关联,Column上的类型立即出现。这个工作不管哪一边配置第一:

    >>> from sqlalchemy import Table, MetaData, Column, Integer, ForeignKey
    >>> metadata = MetaData()
    >>> t2 = Table('t2', metadata, Column('t1id', ForeignKey('t1.id')))
    >>> t2.c.t1id.type
    NullType()
    >>> t1 = Table('t1', metadata, Column('id', Integer, primary_key=True))
    >>> t2.c.t1id.type
    Integer()
  2. 系统现在也可以使用ForeignKeyConstraint

    >>> from sqlalchemy import Table, MetaData, Column, Integer, ForeignKeyConstraint
    >>> metadata = MetaData()
    >>> t2 = Table('t2', metadata,
    ...     Column('t1a'), Column('t1b'),
    ...     ForeignKeyConstraint(['t1a', 't1b'], ['t1.a', 't1.b']))
    >>> t2.c.t1a.type
    NullType()
    >>> t2.c.t1b.type
    NullType()
    >>> t1 = Table('t1', metadata,
    ...     Column('a', Integer, primary_key=True),
    ...     Column('b', Integer, primary_key=True))
    >>> t2.c.t1a.type
    Integer()
    >>> t2.c.t1b.type
    Integer()
  3. 它甚至适用于“多跳” - 也就是引用另一个ColumnColumnForeignKey

    >>> from sqlalchemy import Table, MetaData, Column, Integer, ForeignKey
    >>> metadata = MetaData()
    >>> t2 = Table('t2', metadata, Column('t1id', ForeignKey('t1.id')))
    >>> t3 = Table('t3', metadata, Column('t2t1id', ForeignKey('t2.t1id')))
    >>> t2.c.t1id.type
    NullType()
    >>> t3.c.t2t1id.type
    NullType()
    >>> t1 = Table('t1', metadata, Column('id', Integer, primary_key=True))
    >>> t2.c.t1id.type
    Integer()
    >>> t3.c.t2t1id.type
    Integer()

#1765 T0>

方言改变

Firebird fdb现在是默认的Firebird方言。

如果没有方言说明符(即firebird://)创建引擎,则现在使用fdb方言。fdb is a kinterbasdb compatible DBAPI which per the Firebird project is now their official Python driver.

#2504 T0>

Firebird fdbkinterbasdb set retaining=False默认

Both the fdb and kinterbasdb DBAPIs support a flag retaining=True which can be passed to the commit() and rollback() methods of its connection. 该标志的记录理由是为了提高性能,DBAPI可以为后续事务重新使用内部事务状态。However, newer documentation refers to analyses of Firebird’s “garbage collection” which expresses that this flag can have a negative effect on the database’s ability to process cleanup tasks, and has been reported as lowering performance as a result.

目前还不清楚这个标志在给定这个信息的情况下是如何实际使用的,而且由于它似乎只是一个性能增强功能,现在默认是False可以通过将标志retaining=True传递给create_engine()调用来控制该值。这是从0.8.2开始添加的新标志,因此0.8.2上的应用程序可以根据需要开始将其设置为TrueFalse

#2763 T0>