1

I am passing about 80k parameters but while executing the query, it seems like pyodbc or SQL is truncating the total number of parameters. It works when I pass around 2k parameters but fails anything above 2k. Below is my queries and I will highly appreciate your help.

contact_lst = df_attempt_filtered['Contact ID'].unique()
len(contact_lst) ### Prints: 80871

query_contact = """
SELECT DISTINCT
    con.CONTACT_ID,
    con.JOB_ROLE,
    con.JOB_FUNCTION,
    con.MAIN_PHONE_NUM,
    con.WORK_PHONE_NUM,
    con.MOBILE_PHONE_NUM,
    acc.EMPLOYEE_COUNT AS "EmpSize"
FROM
    V_S_CONTACT_w_MOBILE_NUM AS con
LEFT JOIN 
    V_S_ACCOUNT_ALL_w_others as acc
        ON CON.ACCOUNT_ID = ACC.ACCT_ID
WHERE
    con.CONTACT_ID IN ({})""".format(", ".join(["?"]*len(contact_lst)))

df_contacts = pd.read_sql(query_contact, con=conn, params=contact_lst)

I'm getting the following error:

ProgrammingError: ('The SQL contains 15335 parameter markers, but 80871 parameters were supplied', 'HY000') During handling of the above exception, another exception occurred:

Krishnang K Dalal
  • 2,322
  • 9
  • 34
  • 55
  • 4
    *"I am passing about 80k parameters"* There's your problem. Use a temporary table/table variable. An `IN` with 80,000 values it in will [probably kill your instance. – Thom A Feb 24 '20 at 17:11
  • 1
    Does this answer your question? [maximum number of parameters in sql query](https://stackoverflow.com/questions/845931/maximum-number-of-parameters-in-sql-query) – Piotr Palka Feb 24 '20 at 17:11
  • 1
    use a table valued parameter to supply 80k values - pyodbc example https://github.com/mkleehammer/pyodbc/blob/969ea2c42712036d473fab5d4db715452580c6d3/tests3/sqlservertests.py#L1647 – Martin Smith Feb 24 '20 at 17:12
  • 2
    The fact that you need 80,000 parameters seems like an XY problem unto itself, if I'm honest. – Thom A Feb 24 '20 at 17:14

1 Answers1

0

Simple workaround:
You can create a XML or JSON string in python and then pass it as a single parameter.
Then you will shred this JSON/XML in the query.

Piotr Palka
  • 3,086
  • 1
  • 9
  • 17