Is parameter binding implemented correctly in pyms

Question

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.

Answer 1

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.