Python pyodbc- invalid syntax with SQL

Question

I am using Jupyter Notebook.And I am using pyodbc to fetch data from SQL Server 2018, but when I try to execute this it give me an error. how to solve it?

import pyodbc
import datetime
conn = pyodbc.connect('Driver={SQL Server};'
                      'Server=DESKTOP-MBJNNJR;'
                      'Database=master;'
                      'Trusted_Connection=yes;')
cursor = conn.cursor()

cursor.execute("SELECT Employee_Name, Hire_Date FROM Employee WHERE Hire_Date BETWEEN [{%s}] AND [{%s}]");
hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)
cursor.execute(query, (hire_start, hire_end))
for (Employee_Name, Hire_Date) in cursor:    
    print("{}, {} was hired on {:%d %m %Y}".format(Employee_Name, Hire_Date))
cursor.close()

Error:

ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '{%s}'. (207) (SQLExecDirectW); [42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name '{%s}'. (207)")

Nimantha · Accepted Answer · 2021-11-12 13:47:35Z

1

This looks like a typographical error, alongside the incorrect use of parameter markers.

Presumably the first line with cursor.execute should actually be query =.... For the actual cursor.execute you don't pass parameters in further parenthesis and the parameter markers in PyODBC is ? not %s (to represent a string).

query = "SELECT Employee_Name, Hire_Date FROM Employee WHERE Hire_Date BETWEEN ? AND ?;"
hire_start = datetime.date(1999, 1, 1)
hire_end = datetime.date(1999, 12, 31)
cursor.execute(query, hire_start, hire_end)

edited Nov 12, 2021 at 13:47

Nimantha

6,5276 gold badges32 silver badges78 bronze badges

answered Nov 2, 2021 at 12:45

Thom A♦

97.7k12 gold badges67 silver badges102 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Shaikh Over a year ago

when i try this again it is giving me an error. Error: ('HYC00', '[HYC00] [Microsoft][ODBC SQL Server Driver]Optional feature not implemented (0) (SQLBindParameter)')

Collectives™ on Stack Overflow

Python pyodbc- invalid syntax with SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related