Unicode string cannot be passed to SQL query in form of parameter with pymssql

Question

I'm having trouble passing a Unicode string to an SQL query via parameter.

Initiate the connection and execute the query:

conn = pymssql.connect(server='serverName', port=1433, user='userName', password='password', database='databaseName')
cursor = conn.cursor()

hebrew_string = "הצפת מחסנית"
cursor.execute("SELECT userID FROM users WHERE userName IN %s", hebrew_string)

This will give me the below error:

MSSQLDatabaseException: (102, b"Incorrect syntax near '\xd7\x94\xd7\xa6\xd7\xa4\xd7\xaa \xd7\x9e\xd7\x97\xd7\xa1\xd7\xa0\xd7\x99\xd7\xaa'.DB-Lib error message 20018, severity 15:\nGeneral SQL Server error: Check messages from the SQL Server\n")

I know that this can be solved easily using N'<hebrew_string>', but as I want to make a dynamic query, this is not what I'm looking for.

cursor.execute("SELECT userID FROM users WHERE userName IN (N'הצפת מחסנית')")

And these methods did not work based on my experiment:

cursor.execute("SELECT userID FROM users WHERE userName IN N'%s'", hebrew_string)
cursor.execute("SELECT userID FROM users WHERE userName IN (N'%s')", hebrew_string)
cursor.execute("SELECT userID FROM users WHERE userName IN N%s", hebrew_string)

I'm using python3.8

Any answer would be appreciated. Thanks!

Answer 1

try this :

cursor.execute("SELECT userID FROM users WHERE userName IN (%s)", hebrew_string)