TypeError: (‘Params must be in a list, tuple, or Row’, ‘HY000’) Error on pyodbc

Question

I am trying to fetch all the duplicates from tokens and load into tokn_Duplicates table. Getting Type error as below, if the sequence/format of the values passed to the insert query is causing this. Tried different options from the forum to pass the values as tuple/list, havent been successful

Code snippet below and error

 with conn.cursor() as cur:

cur.execute("""
           select case when tokn_type_cd IS NULL Then 'Address'  else tokn_type_cd end as tokn_type_cd,case when tokn_val_tx is null then '0' else tokn_val_tx end tokn_val_tx,case when pg_duplicate_ct is null then 0 else pg_duplicate_ct end  pg_duplicate_ct  from ((SELECT 0 DUMMY ) DM LEFT OUTER JOIN (SELECT tokn_type_cd, tokn_val_tx, COUNT(*) AS pg_duplicate_ct
      FROM   devt.tokens
      GROUP BY tokn_type_cd, tokn_val_tx
      HAVING COUNT(*) > 1) on 1=1)
        """)
        rows2 = cur.fetchall()

cnxn = pyodbc.connect(server='connect to server')

crsr = cnxn.cursor()

if len(rows2):
    crsr.executemany("INSERT INTO duplicates (tokn_type_cd, tokn_val_tx, pg_duplicate_ct) VALUES (?, ?, ?)", [str(r) for r in rows2])
cnxn.commit()

print("... Successfully completed...")

Error received below

     
crsr.executemany(“INSERT INTO duplicates (tokn_type_cd, tokn_val_tx, pg_duplicate_ct) VALUES (?, ?, ?)“, [str(r) for r in rows2])
TypeError: (‘Params must be in a list, tuple, or Row’, ‘HY000’)

Tried the below change from the forum:

crsr.executemany(“INSERT INTO duplicates (tokn_type_cd, tokn_val_tx, pg_duplicate_ct) VALUES (?, ?, ?)“, [str(tokn_type_cd), str(tokn_val_tx) ,pg_duplicate_ct])

received the error below after the change

crsr.executemany(“INSERT INTO secret_tokn_duplicates (tokn_type_cd, tokn_val_tx, pg_duplicate_ct) VALUES (?, ?, ?)“, [str(tokn_type_cd), str(tokn_val_tx) ,pg_duplicate_ct])
NameError: name ‘tokn_type_cd’ is not defined

Any help would be appreciated.

Thanks

Answer 1

rows2 is the result of a .fetchall() which means that it is a list of pyodbc.Row objects. Such a list is suitable without modification for use as parameters for a subsequent .executemany() operation. In other words, don't mess with rows2; just pass it to the .executemany() for your INSERT:

if len(rows2):
    crsr.executemany(
        "INSERT INTO duplicates (tokn_type_cd, tokn_val_tx, pg_duplicate_ct) VALUES (?, ?, ?)",
        rows2
    )
cnxn.commit()

Your initial attempt failed because [str(r) for r in rows2] returns a list of string objects (scalars) instead of a list of Row objects.

Answer 2

Try

crsr.executemany(
    "INSERT INTO secret_tokn_duplicates (tokn_type_cd, tokn_val_tx, pg_duplicate_ct) VALUES (?, ?, ?)",
    [(str(row.tokn_type_cd), str(row.tokn_val_tx), row.pg_duplicate_ct) for row in rows2])

or if that doesn't work:

crsr.executemany(
    "INSERT INTO secret_tokn_duplicates (tokn_type_cd, tokn_val_tx, pg_duplicate_ct) VALUES (?, ?, ?)",
    [(str(row[0]), str(row[1]), row[2]) for row in rows2])

The list [(str(row.tokn_type_cd), str(row.tokn_val_tx), row.pg_duplicate_ct) for row in rows2] creates a list of 3-tuples from the rows in rows2. I don't have access to your database, so I can't test that it works, but it's my best guess at will work given what's in your question.

Your first attempt

crsr.executemany("INSERT INTO duplicates (tokn_type_cd, tokn_val_tx, pg_duplicate_ct) VALUES (?, ?, ?)", [str(r) for r in rows2])

didn't work because the list you passed to crsr.executemany contained strings, which were what you got if you attempted to convert each row read from the cursor to a string. The elements in the list passed to crsr.executemany must be lists, tuples or Rows: you need to pass it a list of lists, a list of tuples or a list of Rows.

Your second attempt

crsr.executemany("INSERT INTO duplicates (tokn_type_cd, tokn_val_tx, pg_duplicate_ct) VALUES (?, ?, ?)", [str(tokn_type_cd), str(tokn_val_tx) ,pg_duplicate_ct])

didn't work because tokn_type_cd, tokn_val_tx and pg_duplicate_ct aren't variables that Python knows about. However, even if you had defined them, you would again end up passing a list of strings to crsr.executemany and would get the first error again.

---

However, having said all the above, most RDBMSs support INSERT INTO ... SELECT ... statements, which will copy the data directly, so why not just execute the following?

cur.execute("""
           INSERT INTO duplicates (tokn_type_cd, tokn_val_tx, pg_duplicate_ct)
           select case when tokn_type_cd IS NULL Then 'Address'  else tokn_type_cd end as tokn_type_cd,case when tokn_val_tx is null then '0' else tokn_val_tx end tokn_val_tx,case when pg_duplicate_ct is null then 0 else pg_duplicate_ct end  pg_duplicate_ct  from ((SELECT 0 DUMMY ) DM LEFT OUTER JOIN (SELECT tokn_type_cd, tokn_val_tx, COUNT(*) AS pg_duplicate_ct
      FROM   devt.tokens
      GROUP BY tokn_type_cd, tokn_val_tx
      HAVING COUNT(*) > 1) on 1=1)
        """)