时间戳

首页 > 解决方案 > 如何使用 SQLAlchemy ORM 插入子查询（将数据从一个表移动到另一个表）

python - 如何使用 SQLAlchemy ORM 插入子查询（将数据从一个表移动到另一个表）

问题描述

以下简单示例描述了我的 postgres DB 问题（尽管我的问题更多是关于 sqlalchemy 而不是 postgres）：

我有一个名为detection列的表：

我有另一个名为item以下列的表：

我想将某个商店的整个数据集从一个表移动到另一个detection表，item同时还执行将美分转换为美元的操作（这个例子是理论上的，我的实际问题与美分到美元的操作不同）。

在原始 SQL 中，我可以使用以下查询：

INSERT INTO item (detection_id, price_in_dollar) 
    SELECT id AS detection_id, 
    price_in_cent / 100 AS price_in_dollar 
    FROM detection 
    WHERE shop_id = {shop_id}

是否可以使用 SQLAlchemy 复制此查询？由于数据量很大（可能是数百万行），我不想先下载数据进行操作，然后再上传。我的例子是：

q = session.query(Detection).filter(Detection.shop_id == shop_id)
for detection_record in q:
    session.add(Item(detection_id=detection_record.id,
                     price_in_dollar=detection_record.price_in_cent / 100))
session.commit()

然而，这将首先将所有数据下载到机器上，而不是在数据库本身中完成所有工作，因此具有与我的示例查询不同的行为。

标签： pythonpython-3.xpostgresqlsqlalchemy

解决方案

仅仅因为您在项目中使用 ORM 并不意味着您必须对所有事情都使用 ORM 。SQLAlchemy ORM 非常适合将关系“事物”作为 Python 对象下拉并使用它们。对于服务器端操作，SQLAlchemy Core 是使用的工具。

假设您已经使用声明式声明了您的 ORM 对象，例如，

import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class Detection(Base):
    __tablename__ = "detection"
    # ...

然后您可以使用 Core 使用如下代码创建服务器端操作：

meta = Base.metadata
item_t = meta.tables[Item.__tablename__]
detection_t = meta.tables[Detection.__tablename__]

target_shop_id = 1  # test value

ins = item_t.insert().from_select(
    ["detection_id", "price_in_dollar"],
    sa.select(
        [
            detection_t.c.id.label("detection_id"),
            (detection_t.c.price_in_cents / sa.text("100")).label(
                "price_in_dollar"
            ),
        ]
    ).where(detection_t.c.shop_id == target_shop_id),
)

with engine.begin() as conn:
    conn.execute(ins)

生成的 SQL 文本是

INSERT INTO item (detection_id, price_in_dollar) SELECT detection.id AS detection_id, detection.price_in_cents / 100 AS price_in_dollar 
FROM detection 
WHERE detection.shop_id = ?

推荐阅读