时间戳

首页 > 解决方案 > SQLAlchemy INSERT stmt with OUTPUT 返回多行

python - SQLAlchemy INSERT stmt with OUTPUT 返回多行

问题描述

当我使用 sqlalchemy 插入多行时，我没有得到插入的 id 值。

例如，当我在 MS SQL Server 中执行普通 SQL 时，它会在结果集中显示 2 行（我插入的记录 ...1,2）。

INSERT INTO dbo.emp (id, fname, lname, dob)    
OUTPUT inserted.id     
VALUES (1, 'Test1','Rec1','01/01/1990'), (2, 'Test2','Rec2','01/01/1990')

当我使用下面的代码在 sqlalchemy 中执行相同的查询时，打印语句只显示值 2（不是 1）。看起来结果集已插入最后一行。下面的代码有什么问题吗？

try:    
    sql = """\    
    INSERT INTO dbo.emp (id, fname, lname, dob)   
    OUTPUT inserted.id    
    VALUES (:id, :fname, :lname, :dob)    
    """    
    stmt = text(sql)    
    data = [    
         {'id': 1, 'fname':'Test1', 'lname':'rec1', 'dob':'05/05/2000'}    
        ,{'id': 2, 'fname': 'Test2', 'lname': 'rec2', 'dob': '05/05/2001'}    
    ]    
    result = conn.execute(stmt, data)    
    for id in result:    
        print(id)    
    
    conn.commit()    
except exc.IntegrityError as e:    
    print('Code3 - Integrity error raised',  e)    
    conn.rollback()    
except exc.SQLAlchemyError as e:    
    print('Code3 - Something else went wrong', e)     
    conn.rollback()

标签： pythonsql-serversqlalchemypyodbc

解决方案

对于像字典列表这样的多行参数数据，SQLAlchemy 使用 pyodbc 的.executemany()方法，该方法不适合从 OUTPUT 子句返回多个结果。

如果您使用的是 SQL Server 2016+，则可以使用OPENJSON并传递表示多行数据的单个字符串来获取结果：

import json

import sqlalchemy as sa

engine = sa.create_engine("mssql+pyodbc://@mssqlLocal")

with engine.begin() as conn:
    conn.exec_driver_sql("DROP TABLE IF EXISTS emp")
    conn.exec_driver_sql(
        "CREATE TABLE emp ("
            "id int identity primary key, "
            "fname nvarchar(50), "
            "lname nvarchar(50)"
        ")"
    )

sql = """\
INSERT INTO emp (fname, lname)
OUTPUT inserted.id
SELECT fname, lname FROM 
OPENJSON(:json_str)
    WITH (
        fname nvarchar(50) '$.fname',
        lname nvarchar(50) '$.lname'
    )
"""
data = [
    {"fname": "Homer", "lname": "Simpson"},
    {"fname": "Ned", "lname": "Flanders"},
]
with engine.begin() as conn:
    results = conn.execute(
        sa.text(sql), {"json_str": json.dumps(data)}
    ).fetchall()
    print(results)  # [(1,), (2,)]

对于旧版本的 SQL Server，您可以将行数据上传到临时表，然后使用

INSERT INTO emp (fname, lname)
OUTPUT inserted.id
SELECT fname, lname FROM #temp_table

推荐阅读