Pyodbc:插入时出现 SQLExecDirectW 错误 [英] Pyodbc : SQLExecDirectW error while insert
问题描述
出于某种原因,我使用 pyodbc 以带单引号的文本形式将以下数组完全存储在 SQL 服务器中.
<块引用>['Sachin', 'Yuvraj']
我使用下面的代码插入上面的值
tes_table= SQLCURSOR.execute('''INSERT INTO Test_Table(test_name) VALUES ('{}')'''.format(arr))
我收到以下错误.
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]'Sachin' 附近的语法不正确.(102) (SQLExecDirectW)";)[13/Oct/2020 23:54:53] POST/api/save HTTP/1.1"500 77431
这是另一个示例,说明为什么使用字符串格式将数据值嵌入到 SQL 命令文本中是个坏主意.在这种情况下,呈现的字符串文字会产生语法错误,因为单引号未正确转义.
<预><代码>>>>arr = ['Sachin', 'Yuvraj']>>>"... VALUES ('{}')".format(arr)... VALUES ('['Sachin', 'Yuvraj']')"相反,您应该使用适当的参数化查询
sql = """\INSERT INTO Test_Table (test_name) VALUES (?)"tes_table = SQLCURSOR.execute(sql, str(arr))
For some reason, I am storing the below array completely in the SQL server using pyodbc in the form of text with single quotes.
['Sachin', 'Yuvraj']
I am inserting the above value using below code
tes_table= SQLCURSOR.execute('''INSERT INTO Test_Table(test_name) VALUES ('{}')
'''.format(arr))
I am getting the below error.
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near 'Sachin'. (102) (SQLExecDirectW)")
[13/Oct/2020 23:54:53] "POST /api/save HTTP/1.1" 500 77431
This is another example of why using string formatting to embed data values into SQL command text is a bad idea. In this case the rendered string literal creates a syntax error because the single quotes are not properly escaped.
>>> arr = ['Sachin', 'Yuvraj']
>>> "... VALUES ('{}')".format(arr)
"... VALUES ('['Sachin', 'Yuvraj']')"
Instead, you should be using a proper parameterized query
sql = """\
INSERT INTO Test_Table (test_name) VALUES (?)
"""
tes_table = SQLCURSOR.execute(sql, str(arr))
这篇关于Pyodbc:插入时出现 SQLExecDirectW 错误的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!