是否可以将表值参数传递到PYODBC查询?

Question

我知道可以将TVP传递给存储过程(这是我以前在这里问过的问题)。我现在想知道，如果这是我可以在Python做的事情。我总是得到这个错误。它说有一个无效的类型，那么参数需要知道我传入的是什么类型吗?

pyodbc。ProgrammingError: ('42000'， '[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]列，参数或变量#1:无法找到数据类型READONLY。(2715) (SQLExecDirectW);[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]必须声明表变量"@P1"(1087);[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]无法准备语句。(8180);[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]参数或变量'@P1'的数据类型无效。(2724)")

from src.data.dbaccess import con
queryString2 = """ 
SET NOCOUNT ON
DECLARE @t As Table(
PersonName VARCHAR(50)
)
INSERT INTO @t
SELECT *
FROM ?
SELECT * FROM @t
"""
data = ([('Jim', ), ('Bob', )],)
with pyodbc.connect(con.conString) as testCon:
crsr = testCon.execute(queryString2, data)
for row in crsr:
print(row)

**编辑澄清。在我去年的问题中，我问我是否可以将TVP传递给SQL Server存储过程。这一次，这个过程是我自己的Python代码。这就是我现在要解决的问题。

Answer 1

正如在对这个问题的评论中提到的，可以使用System.Data.SqlClient从c#传递TVP到匿名代码块，如下所示:

var tvpData = new DataTable();
tvpData.Columns.Add(new DataColumn("id", Type.GetType("System.Int32")));
tvpData.Rows.Add(new object[] { 1 });
tvpData.Rows.Add(new object[] { 2 });
using (var con = new SqlConnection(@"Server=127.0.0.1,49242;Database=mydb;Trusted_Connection=True;"))
{
con.Open();
using (var cmd = new SqlCommand("SELECT * FROM @p1", con))
{
SqlParameter tvpParam = cmd.Parameters.AddWithValue("@p1", tvpData);
tvpParam.SqlDbType = SqlDbType.Structured;
tvpParam.TypeName = "dbo.dboListInt";  // an existing user-defined table type
SqlDataReader rdr = cmd.ExecuteReader();
Console.WriteLine("rows returned:");
while (rdr.Read())
{
Console.WriteLine(rdr[0]);
}
}
}

控制台输出:

rows returned:
1
2

然而，这将不能与使用System.Data.Odbc的c# ODBC连接一起工作，因为代码依赖于SqlClient特定的SqlDbType.Structured类型。

pyodbc没有类似的机制来指定"结构化"(TVP)参数类型。相反，它通过"shape"来推断是否存在表值参数。它接收的参数(例如，由元素元组表示的一行，其中一个元素本身是元组列表)。

此外，pyodbc和ODBC驱动程序之间的对话目前没有完全考虑用户定义类型作为除普通存储过程调用之外的任何参数。(如果可以使用临时存储过程CREATE PROCEDURE #myTempSP …就好了，但是测试表明临时存储过程不能处理用户定义的表类型。)

TL;DR - tvp可以从c#传递给匿名代码块，但(目前)不能通过pyodbc从Python传递给匿名代码块。