SQLAlchemy的“post\u update”对于从会话中删除的对象的行为不同

2024-03-29 07:48:08 发布

您现在位置:Python中文网/ 问答频道 /正文

我正在尝试将行从一个DB实例复制到另一个DB实例,在不同的环境中使用相同的模式。此架构中的两个表的链接方式使它们产生相互依赖的行。插入这些行后,post_update将按预期运行,但是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和外键按预期设置。只有在由post_update创建的update子句中设置的top_product_id无法按预期的方式工作。你知道吗

作为额外的故障排除步骤,我尝试了:

  1. 创建新对象
  2. 将它们添加到会话中
  3. 将会话刷新到数据库
  4. 从会话中删除对象
  5. 取消设置对象上的外键ID字段(以避免初始插入错误)并使对象成为临时对象
  6. 将对象重新添加到会话
  7. 重新冲洗至DB

在第一次刷新DB时,正确设置top_product_id。第二,它被设置为None。因此,这证实了问题不在于会话中的差异,而是与从会话中删除对象并使其成为临时对象有关。在“删除/生成”瞬态过程中,必须有某种事情发生/不发生,使这些对象处于一种根本不同的状态,并阻止post_update以应有的方式运行。你知道吗

如果您有任何想法,我们将不胜感激。你知道吗


Tags: 对象namenoneiddbtopupdateproduct
1条回答
网友
1楼 · 发布于 2024-03-29 07:48:08

我假设你的Base类混合在name列中?你知道吗

您的目标是使inspect(category).committed_state看起来与新创建的对象类似(除了id属性)。每个产品对象都一样。你知道吗

在“新创建的对象”示例中,categorycommitted_state在刷新会话之前如下所示:

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

productcommitted_state看起来是这样的:

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

要获得更新后的行为,您需要同时终止category.top_product_id(以防止它包含在INSERT)和伪造category.top_productcommitted_state(以使SQLAlchemy相信值已更改,因此需要引起UPDATE)。你知道吗

首先,在使category变为瞬态之前使category.top_product_id过期:

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

然后是fudge category.top_productcommitted_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()

它在dest_session中生成这个DML:

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重置为一个新对象,但我猜不是。你知道吗

相关问题 更多 >