pyodbc sql 语句不接受 nvarchar [英] pyodbc sql statement does not accept nvarchar

问题描述

我正在使用 python 连接到 SQL 数据库.为此使用 pyodbc 驱动程序.连接成功并且工作正常.我的问题是，当我编写任何涉及涉及 nvarchar 数据类型的任何条件的 SQL 语句时，都会出现错误.例如

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'')

我收到以下错误:

pyodbc.ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC Driver 17对于 SQL Server][SQL Server]无效的列名PDX_database_name".(207) (SQLExecDirectW)")

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?

推荐答案

Python 允许带有 ' 或 " 的文字字符串，所以最简单的解决方案是使用 " 在 Python 中，' 在 SQL 中.并对 NVARCHAR 文字使用 N 前缀，例如

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'")

Python 中另一个非常好的做法是对 SQL 查询使用多行字符串文字.EG

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'
'''

在其中您不必转义 '、和，您可以剪切并粘贴任何长度的正确格式的 SQL 语句.

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

对于我们一个局部变量，用参数标记?替换SQL文字字符串，并将变量传递给cursor.execute

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)

这篇关于pyodbc sql 语句不接受 nvarchar的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！