pymssql 库中是否正确实现了参数绑定? [英] Is parameter binding implemented correctly in pymssql library?

问题描述

我正在使用 pymsqsql 库从 Python 程序调用极其简单的查询.

 以 self.conn.cursor() 作为游标:cursor.execute('select extra_id from mytable where id = %d', id)extra_id = cursor.fetchone()[0]

请注意，参数绑定的使用方式如 pymssql 文档中所述.

参数绑定的主要目标之一是为 DBMS 引擎提供缓存查询计划的能力.我使用 Profiler 连接到 MS SQL 并检查了实际执行的查询.事实证明，每次执行唯一的语句时(具有自己的绑定 ID).我还使用此类查询检查了查询使用情况:

select * from sys.dm_exec_cached_plans ec交叉申请sys.dm_exec_sql_text(ec.plan_handle) txtwhere txt.text like '%select extra_id from mytable where id%'

并且它表明该计划没有被重用(这当然是可以预料的，因为每个查询的文本都是唯一的).这与从 C# 查询时的参数绑定有很大不同，我们可以看到查询相同但提供的参数不同.

所以我想知道我是否正确使用了 pymssql 以及这个库是否适合与 MS SQL DBMS 一起使用.

P.S.我知道 MS SQL 有一个自动参数化的特性，它适用于基本查询，但它没有保证，可能不适用于复杂查询.

解决方案

您正确使用了 pymssql.在将查询发送到服务器之前，pymssql 实际上确实将参数值替换为 SQL 文本.例如:

pymssql:

SELECT * FROM tablename WHERE id=1

pyodbc 与 Microsoft 的 SQL Server ODBC 驱动程序(不是 FreeTDS ODBC 驱动程序):

exec sp_prepexec @p1 output,N'@P1 int',N'SELECT * FROM tablename WHERE id=@P1',1

但是，请记住，pymssql 基于 FreeTDS，并且上述行为似乎是 FreeTDS 处理参数化查询方式的函数，而不是 pymssql 本身的特定功能.>

是的，如 this answer.

I'm calling extremely simple query from Python program using pymsqsql library.

 with self.conn.cursor() as cursor:
     cursor.execute('select extra_id from mytable where id = %d', id)
     extra_id = cursor.fetchone()[0]

Note that parameter binding is used as described in pymssql documentation.

One of the main goals of parameter binding is providing ability for DBMS engine to cache the query plan. I connected to MS SQL with Profiler and checked what queries are actually executed. It turned out that each time a unique statement gets executed (with its own bound ID). I also checked query usage with such query:

select * from sys.dm_exec_cached_plans ec
cross apply
sys.dm_exec_sql_text(ec.plan_handle) txt
where  txt.text like '%select extra_id from mytable where id%'

And it shown that the plan is not reused (which is expectable of course due to unique text of each query). This differs much from parameter binding when querying from C#, when we can see that queries are the same but the supplied parameters are different.

So I wonder if I am using pymssql correctly and whether this lib is appropriate for using with MS SQL DBMS.

P.S. I know that MS SQL has a feature of auto-parameterization which works for basic queries, but it is not guarantied, and may not work for complex queries.

解决方案

You are using pymssql correctly. It is true that pymssql actually does substitute the parameter values into the SQL text before sending the query to the server. For example:

pymssql:

SELECT * FROM tablename WHERE id=1

pyodbc with Microsoft's ODBC Driver for SQL Server (not the FreeTDS ODBC driver):

exec sp_prepexec @p1 output,N'@P1 int',N'SELECT * FROM tablename WHERE id=@P1',1

However, bear in mind that pymssql is based on FreeTDS and the above behaviour appears to be a function of the way FreeTDS handles parameterized queries, rather than a specific feature of pymssql per se.

And yes, it can have implications for the re-use of execution plans (and hence performance) as illustrated in this answer.

这篇关于pymssql 库中是否正确实现了参数绑定?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！