'Issues with SQL Server and PYODBC
I'm getting an error at this statement:
cursor.execute("SELECT * FROM dbo.User")
Error:
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC Driver 11 for SQL Server][SQL Server]Incorrect syntax near the keyword 'User'. (156) (SQLExecDirectW)")
The code is below. I'm assuming the connection is fine because nothing happens unless I execute the query? Am I doing something wrong?
SERVER = 'LAPTOP-1E7UL24T\SQLEXPRESS02'
DATABASE = 'PT'
DRIVER='{ODBC Driver 17 for SQL Server}'
DATABASE_CONNECTION=f'Driver={DRIVER};SERVER={SERVER};Database={DATABASE};Trusted_Connection=yes;'
print(DATABASE_CONNECTION)
cnxn=pyodbc.connect(DATABASE_CONNECTION)
cursor=cnxn.cursor()
cursor.execute("SELECT * FROM dbo.User")
Solution 1:[1]
User is a reserved word and needs to be escaped, commonly by using square brackets e.g.
SELECT * FROM dbo.[User]
But double quotes also work:
SELECT * FROM dbo."User"
Although it will make your life (and any developers who follow) a lot easier if you just avoid using reserved words.
Solution 2:[2]
Most likely you are running into the deprecated data types: IMAGE, TEXT and NVARCHAR. They need to be handled differently.
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Dale K |
| Solution 2 | not2qubit |