the length of the variable hour is 792 and the uitid is 15, so both under 1000, so I don't know why I recieve this error, I tried it two ways as it can be seen below:
first way:
def generate_sql_in_binds(
name: str,
size: int,
start: int = 1,
max_binds: int = 1000,
) -> str:
in_clauses = (
"{name} IN ({binds})".format(
name=name,
binds=",".join(
(
f":{b+start}"
for b in range(i, min(i+max_binds,size))
)
)
)
for i in range(0, size, max_binds)
)
return "(" + (" OR ".join(in_clauses)) + ")"
and the error is
[![enter image description here][1]][1]
You need to put TO_DATE in the iterator that generates the bind parameters so that it wraps each bind parameter (rather than trying to wrap the entire list):
query = """SELECT HOUR, UNITSCHEDULEID, VERSIONID, MINRUNTIME
FROM int_Stg.UnitScheduleOfferHourly
WHERE Hour in ({hour_binds})
AND UnitScheduleId in ({id_binds})""".format(
hour_binds=",".join(
(f"TO_DATE(:{idx}, 'dd-mon-yyyy hh24')" for idx in range(1, len(hour) + 1))
),
id_binds=",".join(
(f":{idx}" for idx in range(len(hour) + 1, len(unitid) + len(hour) + 1))
),
)
res = connection.cursor().execute(query, (*hour, *unitid)).fetchall()
print(res)
However, you would probably be better to leave the query as it is and convert the list of strings to a list of datetimes.
from datetime import datetime
hour_dt = tuple(datetime.strptime(value, "%d-%b-%Y %H") for value in hour)
query = """SELECT HOUR, UNITSCHEDULEID, VERSIONID, MINRUNTIME
FROM int_Stg.UnitScheduleOfferHourly
WHERE Hour in ({hour_binds})
AND UnitScheduleId in ({id_binds})""".format(
hour_binds=",".join(
(f":{idx}" for idx in range(1, len(hour_dt) + 1)),
),
id_binds=",".join(
(f":{idx}" for idx in range(len(hour_dt) + 1, len(unitid) + len(hour) + 1)),
),
)
res = connection.cursor().execute(query, (*hour_dt, *unitid)).fetchall()
print(res)