I am working on pyodbc in Eclipse (4.3.2v20140221-1852) with PyDev on Win 7. My python is 3.2.5.
At my code:
cursor.execute("select top " + str(1) + " a.my_id, a.mycode" +
"from my_table as a where a.mycode = ?", aGivenCode)
I got an error :
pyodbc.ProgrammingError: ('42000', "[42000] [Microsoft][ODBC SQL Server Driver]
[SQL Server]Incorrect syntax near the keyword 'as'. (156) (SQLExecDirectW)")
Why I got error at " as a " ?
thanks !
A couple suggestions for future development:
Use multi-line string notation """ to allow better identification of syntax errors (like the missing space between column and table in the original query).
Instead of string concatenation, use a parameter for TOP values. The value must be wrapped in parentheses.
With these guidelines, your original code would be updated to:
top = 1
cursor.execute("""select top (?) a.my_id, a.mycode
from my_table as a
where a.mycode = ?""",
(top, aGivenCode))
Try this? I think you're missing a space in there:
cursor.execute("select top " + str(1) + " a.my_id, a.mycode from my_table as a where a.mycode = ?", aGivenCode)
See how there is no space in between 'a.mycode' and 'from my_table'? I'm not 100% sure that is right, but give it a try.
These things are also easier to debug like this:
sql = "select top " + str(1) + " a.my_id, a.mycode from my_table as a where a.mycode = ?"
print sql
cursor.execute(sql, aGivenCode)
