CODEWITHSUNDEEP
pythonsqlsql-serverpyodbc
Shaikh
Shaikh

Reputation: 1

Python pyodbc- invalid syntax with SQL

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

Upvotes: 0

Views: 676

Answers (1)

Thom A
Thom A

Reputation: 96015

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)

Upvotes: 1

Related Questions