带OUTPUT的SQLAlchemy INSERT stmt返回多行

Question

当我使用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中执行相同的查询时，print语句只显示值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()

Answer 1

对于像dicts列表这样的多行参数数据，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

Answer 2

我确信conn.execute会运行两次insert命令。看，您的sql命令是一个数据行的插入，因此它不会转换为以下内容：

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

相反，insert的两个命令是连续运行的，sqlalchemy似乎只保留第二个结果。(我试图深入研究sqlalchemy代码，但找不到它发生的地方(。

如果你真的想使用sqlalchemy的优势，我建议你至少使用sqlalcohy查询语言，让sqlalchemi构造你的sql命令。查阅SQL表达式语言教程开始学习。

Answer 3

除了@gord Thompson使用JSON给出的答案外，通常还可以使用临时表先上传数据，然后使用"插入。。。输出。。。选择"将其移动到目标表。这避免了所有的转换和格式问题，并且在概念上接近原始方法。