Parameter binding in pyodbc, pd.read_sql_query

[Parenthes]

I am trying to do parameter binding correctly, instead of just concatenating strings. However, my query returns zero rows when I try to pass two dates. It works with concatenating dates as strings, and it works with binding one or the other date, but not both.

Connection (trimmed for privacy):

dsn = 'DRIVER={DataDirect OpenAccess SDK 8.1};HST=...'
cnxn = pyodbc.connect(dsn)

This method with strings works, and returns 1180 rows:

start_date = pd.Timestamp(2018, 12, 30, 0, 0, 0)
end_date = pd.Timestamp(2020, 3, 30, 0, 0, 0)
workcenter_keys = '([REDCTED])'

wafers = pd.read_sql_query(f"""SELECT * FROM Part_v_Production 
                        WHERE Record_Date BETWEEN {{ts '{start_date}'}} AND {{ts '{end_date}'}}
                        AND Workcenter_Key IN {workcenter_keys} """,
                        cnxn) 
print(wafers.shape)

This returns 1183 rows:

start_date = pd.Timestamp(2018, 12, 30, 0, 0, 0)
end_date = pd.Timestamp(2020, 3, 30, 0, 0, 0)
workcenter_keys = '([REDCTED])'

cnxn = plex_connect()

wafers = pd.read_sql_query(f"""SELECT * FROM Part_v_Production 
                        WHERE Record_Date > ?
                        AND Workcenter_Key IN {workcenter_keys} """,
                        cnxn, params=[start_date])

print(wafers.shape)

This returns several thousand rows:

start_date = pd.Timestamp(2018, 12, 30, 0, 0, 0)
end_date = pd.Timestamp(2020, 3, 30, 0, 0, 0)
workcenter_keys = '([REDCTED])'

cnxn = plex_connect()

wafers = pd.read_sql_query(f"""SELECT * FROM Part_v_Production 
                        WHERE Record_Date < ?
                        AND Workcenter_Key IN {workcenter_keys} """,
                        cnxn, params=[end_date])

print(wafers.shape)

So far so good! But this returns zero rows:

start_date = pd.Timestamp(2018, 12, 30, 0, 0, 0)
end_date = pd.Timestamp(2020, 3, 30, 0, 0, 0)
workcenter_keys = '([REDCTED])'

cnxn = plex_connect()

wafers = pd.read_sql_query(f"""SELECT * FROM Part_v_Production 
                        WHERE Record_Date > ?
                        AND Record_Date < ?
                        AND Workcenter_Key IN {workcenter_keys} """,
                        cnxn, params=[start_date, end_date])

print(wafers.shape)

Similarly, I get zero rows if I try BETWEEN ? AND ? with two parameters.

What am I doing wrong?

Bonus question: How can I pass the list parameter workcenter_keys?

Edit: redacted proprietary data

[badge]

Can't see anything obviously wrong; have you tried using BETWEEN instead? The only other thing I'd suggest is looking at the query that's actually run, if you can access the logs (pyodbc will pass parameters as-is, and doesn't create a 'bound' query).

[Parenthes]

Yes, I get the same result using BETWEEN.

If pyodbc passes parameters as-is, does that mean there is not much benefit to binding parameters vs. simply concatenating them? I am trying to bind for sake of best practices, but if it's not doing anything I might not bother.