How to solve for pyodbc.ProgrammingError: The second parameter to executemany must not be empty

Question

Hi i'm having an issue with the transfer of data from one database to another. I created a list using field in a table on a msql db, used that list to query and oracle db table (using the initial list in the where statement to filter results) I then load the query results back into the msql db.

The program runs for the first few iterations but then errors out, with the following error ( Traceback (most recent call last): File "C:/Users/1/PycharmProjects/DataExtracts/BuyerGroup.py", line 67, in insertIntoMSDatabase(idString) File "C:/Users/1/PycharmProjects/DataExtracts/BuyerGroup.py", line 48, in insertIntoMSDatabase mycursor.executemany(sql, val) pyodbc.ProgrammingError: The second parameter to executemany must not be empty.)

I can't seem to find and guidance online to troubleshoot this error message. I feel it may be a simple solution but I just can't get there...

# import libraries
import cx_Oracle
import pyodbc
import logging
import time
import re
import math
import numpy as np

logging.basicConfig(level=logging.DEBUG)

conn = pyodbc.connect('''Driver={SQL Server Native Client 11.0};
                         Server='servername';
                         Database='dbname';
                         Trusted_connection=yes;''')
b = conn.cursor()
dsn_tns = cx_Oracle.makedsn('Hostname', 'port', service_name='name')
conn1 = cx_Oracle.connect(user=r'uid', password='pwd', dsn=dsn_tns)
c = conn1.cursor()

beginTime = time.time()

bind = (b.execute('''select distinct field1
                     from [server].[db].[dbo].[table]'''))
print('MSQL table(s) queried, List Generated')

# formats ids for sql string
def surroundWithQuotes(id):
    return "'" + re.sub(",|\s$", "", str(id)) + "'"

def insertIntoMSDatabase(idString):
    osql = '''SELECT distinct field1, field2
                FROM Database.Table
                WHERE field2 is not null and field3 IN ({})'''.format(idString)
    c.execute(osql)
    claimsdata = c.fetchall()
    print('Oracle table(s) queried, Data Pulled')

    mycursor = conn.cursor()
    sql = '''INSERT INTO [dbo].[tablename] 
                (
                 [fields1]
                ,[field2]
                )
            VALUES (?,?)'''

    val = claimsdata
    mycursor.executemany(sql, val)
    conn.commit()

ids = []
formattedIdStrings = []

# adds all the ids found in bind to an iterable array
for row in bind:
    ids.append(row[0])

# splits the ids[] array into multiple arrays < 1000 in length
batchedIds = np.array_split(ids, math.ceil(len(ids) / 1000))

# formats the value inside each batchedId to be a string
for batchedId in batchedIds:
    formattedIdStrings.append(",".join(map(surroundWithQuotes, batchedId)))

# runs insert into MS database for each batch of IDs
for idString in formattedIdStrings:
    insertIntoMSDatabase(idString)

print("MSQL table loaded, Data inserted into destination")

endTime = time.time()

print("Program Time Elapsed: ",endTime-beginTime)

conn.close()
conn1.close()

Answer 1

mycursor.executemany(sql, val)

pyodbc.ProgrammingError: The second parameter to executemany must not be empty.

Before calling .executemany() you need to verify that val is not an empty list (as would be the case if .fetchall() is called on a SELECT statement that returns no rows) , e.g.,

if val:
    mycursor.executemany(sql, val)