Sqlalchemy:链接表的大容量相关更新

Question

我有两个相关的表已经填充了数据（使用SQLAlchemy ORM）。但是，到目前为止还没有链接各个记录，即外键列为空。我需要批量更新外键列，基于不同列的匹配。在

举例说明：

class Location(Base):
    __tablename__ = 'locations'
    id = Column(Integer, primary_key=True)
    x = Column(Float)
    y = Column(Float)


class Stopover(Base):
    __tablename__ = 'stopovers'
    id = Column(Integer, primary_key=True)
    x = Column(Float)
    y = Column(Float)
    location_id = Column(Integer, ForeignKey("locations.id"))
    location = relationship("Location", backref=backref("stopovers"))

因此，本质上，我需要通过匹配'x'和'y'列，将20000+个'Stopover'记录与一个'Location'关联起来，即批量更新Location_id列。在

此代码正确地生成了“位置”标识：

^{pr2}$

然而，它似乎不起作用——通过Sqliteman对数据库进行研究表明，位置标识尚未更新。另外，我想肯定有一种更优雅的方式来处理这个问题。在

在文档中，我发现Correlated Updates最接近我要找的东西。但是，文档只引用SQL表达式语言，而我使用的是ORM。我是SQLAlchemy新手，我尝试将文档转换为ORM的尝试没有成功。在

如果您能帮我找到执行此批量更新的最优雅方式，我将不胜感激。提前谢谢。在

Answer 1

SQLAlchemy是分层工作的。在基础层，SQLAlchemy提供了一些东西，比如使用各种数据库驱动程序到数据库的统一接口，以及连接池实现。上面有一个SQL表达式语言，允许您使用Python对象定义数据库的表和列，然后使用这些对象使用SQLAlchemy提供的api创建SQL表达式。还有ORM。ORM构建在这些现有的层上，因此即使您使用ORM，您仍然可以下拉使用expressionapi。使用声明性模型（它构建在ORM之上），您甚至比这个级别更高。在

大多数表达式API基于SQLAlchemy表对象和列。这些表可由映射类上的__table__属性访问，列作为映射类的属性可用。因此，即使您处于声明性级别，在使用使用声明性映射的模型时，您仍然可以利用在那里可用的大部分内容。所以，example correlated query。。。在

>>> stmt = select([addresses.c.email_address]).\
...             where(addresses.c.user_id == users.c.id).\
...             limit(1)
>>> conn.execute(users.update().values(fullname=stmt)) 

…可以通过使用__table__属性和声明性列转换为声明性ORM模型。。。在

^{pr2}$

我相信你的相关查询应该是这样的。。在

stmt = select([Location.id]).\
    where(and_(Location.x==Stopover.x, Location.y==Stopover.y)).limit(1)

conn.execute(Stopover.__table__.update().values(location_id=stmt)

生成的SQL：

UPDATE stopovers SET location_id=(SELECT locations.id 
FROM locations 
WHERE locations.x = stopovers.x AND locations.y = stopovers.y
LIMIT ? OFFSET ?)