python - SQLAlchemy 的“post_update”与已从会话中删除的对象的行为不同

Question

我正在尝试将行从一个数据库实例复制到另一个在不同环境中具有相同架构的数据库。此模式中的两个表以它们产生相互依赖的行的方式链接。插入这些行post_update后，将按预期运行，但更新语句将 ID 字段的值设置为None而不是预期的 ID。

这只发生在使用已从会话中删除的对象时。使用新创建的对象时，其post_update行为完全符合预期。

例子

我有一个看起来像这样的关系：

class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    top_product_id = Column(Integer, ForeignKey('products.id'))
    products = relationship('Product', primaryjoin='Product.category_id == Category.id', back_populates='category', cascade='all', lazy='selectin')
    top_product = relationship('Product', primaryjoin='Category.top_product_id == Product.id', post_update=True, cascade='all', lazy='selectin')


class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    category_id = Column(Integer, ForeignKey('categories.id'))
    category = relationship('Category', primaryjoin='Product.category_id == Category.id', back_populates='products', cascade='all', lazy='selectin')

如果我从一个数据库查询一个类别及其相关产品并尝试将它们写入另一个数据库，则更新的top_product_id行为不会像预期的那样，None而是将值设置为。以下代码：

category = source_session.query(Category).filter(Category.id == 99).one()
source_session.expunge(category)
make_transient(category)
for products in category.products:
    make_transient(product)
# this step is necessary to prevent a foreign key error on the initial category insert
category.top_product_id = None
dest_session.add(category)

导致 SQLAlchemy 生成以下 SQL：

INSERT INTO categories (name, top_product_id) VALUES (%s, %s)
('SomeCategoryName', None)
INSERT INTO products (name, category_id) VALUES (%s, %s)
('SomeProductName', 99)
UPDATE categories SET top_product_id=%s WHERE categories.id = %s
(None, 99)

但如果我使用新创建的对象，一切都会按预期工作。

category = Category()
product = Product()
category.name = 'SomeCategoryName'
product.name = 'SomeProductName'
product.category = category
category.top_product = product
dest_session.add(category)

结果是：

INSERT INTO categories (name, top_product_id) VALUES (%s, %s)
('SomeCategoryName', None)
INSERT INTO products (name, category_id) VALUES (%s, %s)
('SomeProductName', 99)
UPDATE categories SET top_product_id=%s WHERE categories.id = %s
(1, 99)

除了这种差异之外，这两个动作之间的一切行为都相同。所有其他关系均已正确创建，ID 和外键已按预期设置。只有由top_product_id更新 子句中创建的集合post_update未能按预期运行。

作为附加的故障排除步骤，我尝试了：

1. 创建新对象
2. 将它们添加到会话
3. 将会话刷新到数据库
4. 从会话中删除对象
5. 取消设置对象上的外键 ID 字段（以避免初始插入错误）并使对象瞬态
6. 将对象重新添加到会话
7. 重新刷新到数据库

在第一次刷新到数据库时，top_product_id设置正确。在第二个，它设置为None. 因此，这证实了问题不在于会话中的差异，而是与从会话中删除对象并使它们暂时性有关。在 expunge/make 瞬态过程中必须有某些事情发生/没有发生，这会使这些对象处于根本不同的状态，并阻止post_update其表现出应有的方式。

任何关于从这里去哪里的想法都将不胜感激。

标签： pythonmysqlsqlalchemy

Answer 1

我假设您的Base班级混在name专栏中？

你的目标是让它inspect(category).committed_state看起来像新创建的对象（可能除了id属性）。每个产品对象都相同。

在您的“新创建的对象”示例中，category'committed_state在刷新会话之前看起来像这样：

{'id': symbol('NEVER_SET'),
 'name': symbol('NO_VALUE'),
 'products': [],
 'top_product': symbol('NEVER_SET')}

while看起来像这样product：committed_state

{'category': symbol('NEVER_SET'),
 'id': symbol('NEVER_SET'),
 'name': symbol('NO_VALUE')}

要获得更新后的行为，您需要同时过期category.top_product_id（以防止它包含在 中INSERT）和 fudge category.top_product（committed_state使 SQLAlchemy 相信值已更改，因此需要导致UPDATE）。

首先，category.top_product_id在制作category瞬态之前过期：

source_session.expire(category, ["top_product_id"])

然后 fudge category.top_product（这可能发生在瞬态committed_state之前或之后）：category

from sqlalchemy import inspect
from sqlalchemy.orm.base import NEVER_SET
inspect(category).committed_state.update(top_product=NEVER_SET)

完整示例：

from sqlalchemy import Column, ForeignKey, Integer, String, create_engine, inspect
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import Session, make_transient, relationship
from sqlalchemy.orm.base import NEVER_SET

class Base(object):
    name = Column(String(50), nullable=False)


Base = declarative_base(cls=Base)


class Category(Base):
    __tablename__ = 'categories'
    id = Column(Integer, primary_key=True)
    top_product_id = Column(Integer, ForeignKey('products.id'))
    products = relationship('Product', primaryjoin='Product.category_id == Category.id', back_populates='category', cascade='all', lazy='selectin')
    top_product = relationship('Product', primaryjoin='Category.top_product_id == Product.id', post_update=True, cascade='all', lazy='selectin')


class Product(Base):
    __tablename__ = 'products'
    id = Column(Integer, primary_key=True)
    category_id = Column(Integer, ForeignKey('categories.id'), nullable=False)
    category = relationship('Category', primaryjoin='Product.category_id == Category.id', back_populates='products', cascade='all', lazy='selectin')


source_engine = create_engine('sqlite:///')
dest_engine = create_engine('sqlite:///', echo=True)

def fk_pragma_on_connect(dbapi_con, con_record):
    dbapi_con.execute('pragma foreign_keys=ON')

from sqlalchemy import event
for engine in [source_engine, dest_engine]:
    event.listen(engine, 'connect', fk_pragma_on_connect)

Base.metadata.create_all(bind=source_engine)
Base.metadata.create_all(bind=dest_engine)

source_session = Session(bind=source_engine)
dest_session = Session(bind=dest_engine)

source_category = Category(id=99, name='SomeCategoryName')
source_product = Product(category=source_category, id=100, name='SomeProductName')
source_category.top_product = source_product
source_session.add(source_category)
source_session.commit()
source_session.close()

# If you want to test UPSERTs in dest_session.
# dest_category = Category(id=99, name='PrevCategoryName')
# dest_product = Product(category=dest_category, id=100, name='PrevProductName')
# dest_category.top_product = dest_product
# dest_session.add(dest_category)
# dest_session.commit()
# dest_session.close()

category = source_session.query(Category).filter(Category.id == 99).one()
# Ensure relationship attributes are initialized before we make objects transient.
_ = category.top_product
# source_session.expire(category, ['id'])  # only if you want new IDs in dest_session
source_session.expire(category, ['top_product_id'])
for product in category.products:
    # Ensure relationship attributes are initialized before we make objects transient.
    _ = product.category
    # source_session.expire(product, ['id'])  # only if you want new IDs in dest_session
    # Not strictly needed as long as Product.category is not a post-update relationship.
    source_session.expire(product, ['category_id'])

make_transient(category)
inspect(category).committed_state.update(top_product=NEVER_SET)

for product in category.products:
    make_transient(product)
    # Not strictly needed as long as Product.category is not a post-update relationship.
    inspect(product).committed_state.update(category=NEVER_SET)

dest_session.add(category)
# Or, if you want UPSERT (must retain original IDs in this case)
# dest_session.merge(category)
dest_session.flush()

它在以下位置生成此 DML dest_session：

INSERT INTO categories (name, id, top_product_id) VALUES (?, ?, ?)
('SomeCategoryName', 99, None)
INSERT INTO products (name, id, category_id) VALUES (?, ?, ?)
('SomeProductName', 100, 99)
UPDATE categories SET top_product_id=? WHERE categories.id = ?
(100, 99)

似乎make_transient应该重置committed_state为好像它是一个新对象，但我想不是。