Is parameter binding implemented correctly in pyms

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.