<p>根据对我的问题的评论,我设法让存储过程使用表值参数运行(并从SP获取返回值)
最后的脚本如下:</p>
<pre><code>import pandas as pd
import pytds
from pytds import login
import sqlalchemy as sa
from sqlalchemy import create_engine
import sqlalchemy_pytds
def connect():
return pytds.connect(dsn='ServerName',database='DBName',autocommit=True, auth=login.SspiAuth())
engine = sa.create_engine('mssql+pytds://[ServerName]', creator=connect)
conn = engine.raw_connection()
with conn.cursor() as cur:
arg = [["foo.ExternalInput","bar.ExternalInput"]]
tvp = pytds.TableValuedParam(type_name="core.MatchColumnTable", rows=arg)
cur.execute("EXEC test_proc @Target = N'[dbname].[tablename1]', @Source = N'[dbname].[table2]', @CleanTarget = 0, @UseColumnsFromTarget = 0, @MergeOnColumn = %s", (tvp,))
result = cur.fetchall()
print(result)
</code></pre>
<p>autocommit被添加到连接中(在游标中提交事务),表值参数(marchcolumntable)需要2列,因此修改arg以适合2列。在</p>
<p>除了tvp之外还需要的参数包含在exec字符串中。execute字符串中的最后一个参数是用tvp填充的tvp参数(mergeoncolumn)的名称。在</p>
<p>您可以选择添加pytds文档中描述的结果状态或行数:
<a href="https://python-tds.readthedocs.io/en/latest/index.html" rel="nofollow noreferrer">https://python-tds.readthedocs.io/en/latest/index.html</a></p>
<p><strong>注意!</strong>:在存储过程中,必须确保
将添加setnocount ON,否则将无法将任何结果返回到Python</p>