CODEWITHSUNDEEP
pythonpyodbc
Sathiamoorthy
Sathiamoorthy

Reputation: 11580

Pyodbc : SQLExecDirectW error while insert

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

Upvotes: 1

Views: 7239

Answers (1)

Gord Thompson
Gord Thompson

Reputation: 123549

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

Upvotes: 2

Related Questions