pyodbc sql statement does not accept nvarchar

I am connecting to SQL database using python. Using pyodbc driver for this. The connection is successful and working fine. My problem is when I write any SQL statement which involves any condition involving nvarchar data type I am getting errors. For example

cursor.execute('SELECT * FROM master.sys.databases where name = 'my_database_name'')

I get following error:

pyodbc.ProgrammingError: (’42S22′, “[42S22] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Invalid column name ‘PDX_database_name’. (207) (SQLExecDirectW)”)

probably some syntax mistake, can anyone help?

Answer

Python allows literal strings with ' or ", so the easiest solution is to use " in Python and ' in SQL. And use the N prefix for NVARCHAR literals eg

cursor.execute("SELECT * FROM master.sys.databases where name = N'my_database_name'")

Another really good practice in Python is to use multi-line string literals for SQL queries. EG

sql = '''
select *
from sys.databases
where name = 'my_database_name'
'''

In which you don’t have to escape ', and you can cut-and-paste properly-formatted SQL statements of any length.

To us a local variable, replace the SQL literal string with a parameter marker ?, and pass the variable to the second argument of cursor.execute

cursor.execute("SELECT * FROM master.sys.databases where name = ?'", dbname)