pyodbc send parameter as nvarchar

user_15609849 0
Viewed 397

I have a query with multiple parameters that I need to run using pyodbc and I have an issue. This is a simplified version of the query, with only one parameter (I actually have 6)

declare @alertTypeName nvarchar(255)
set @alertTypeName = ?
SELECT TOP 1 Id FROM AlertType WHERE Name = @alertTypeName

@alertTypeName can have special characters, for example 'Boli şi Dăunători'. Naturally, if I were to run this query directly in SQL Server I would set @alertTypeName = N'Boli şi Dăunători'. The SELECT does not return anything without adding N in front of the text. My problem is, how do I send the value as parameter to the query in cursor.execute() so it will be correctly encoded?

This is python my code at the moment (simplified version):

query = textwrap.dedent(""" 
declare @alertTypeName nvarchar(255)
set @alertTypeName = ?
SELECT TOP 1 Id FROM AlertType WHERE Name = @alertTypeName
""")

[...]
alertTypeName = "Boli şi Dăunători"

[...]
    
paramValues = [syswinAlertId, code, alertTypeName, alertMessage, createdBy, modifiedBy]
cursor.execute(query, paramValues)

alertTypeName can have different values depending on some conditions, so I cannot just add it directly to the query text.

pyodbc parameter-passing sql-server sql python
2 Answers

In Python 3 all strings are Unicode, so if you pass a string as a parameter value then pyodbc will relay it to the ODBC Driver Manager (DM) in the appropriate format to be recognized as Unicode. For example, if we start a SQL Server Profiler trace and then run this code

cnxn = pyodbc.connect("DSN=mssqlLocal")
crsr = cnxn.cursor()
sql = "SELECT ? AS thing"
params = ("Boli şi Dăunători",)
result = crsr.execute(sql, params).fetchval()

we'll see that SQL Server is interpreting the parameter value as an N'string'

declare @p1 int
set @p1=1
exec sp_prepexec @p1 output,N'@P1 nvarchar(34)',N'SELECT @P1 AS thing',N'Boli şi Dăunători'
select @p1

Note that we do not need to mess with .setencoding() and .setdecoding(); the pyodbc defaults are the correct ones for SQL Server

user_21443900

It seems my problem was actually caused by a faulty if condition in the code, and not by SQL. Thanks everyone for helping.

user_156098490
Related