jaydebeapi executemany() method doesn't work with one parameter

jaydebeapi executemany() method doesn't work using sql query containing only one parameter, but at the same time two parameters works fine.

For example given query doesn't executes:

cursor.executemany("DELETE FROM table1 WHERE col1 = ?", tup_df)

And this one works fine

cursor.executemany("DELETE FROM table1 WHERE col1 = ? AND col2 = ?", tup_df)

assuming that the expression going after AND manipulates with an existing column in the db (using some workarounds like 1 = ? or so didn't work out)

Test code using first case

df = pandas.DataFrame(data={'col1': [3563412]})

conn = jaydebeapi.connect(jar_class, url, {'user': user, 'password': password}, jar_path)

cursor = conn.cursor()

row_count = 0

for _, df in df.groupby(np.arange(len(df)) // 2000):
    tup_df = [tuple(x) for x in df.values]
    cursor.executemany("DELETE FROM table1 WHERE col1 = ?", tup_df)
    row_count = row_count + cursor.rowcount

print(row_count + ' row(s) processed')

Test code for the second case

df = pandas.DataFrame(data={'col1': [3563412]})

conn = jaydebeapi.connect(jar_class, url, {'user': user, 'password': password}, jar_path)

cursor = conn.cursor()

row_count = 0

for _, df in df.groupby(np.arange(len(df)) // 2000):
    tup_df = [tuple(x) for x in df.values]
    cursor.executemany("DELETE FROM table1 WHERE col1 = ? AND col2 = ?", tup_df)
    row_count = row_count + cursor.rowcount

print(row_count + ' row(s) processed')

Throwing error:

Traceback (most recent call last):
  File "test.py", line 21, in <module>
    cursor.executemany("DELETE FROM table1 WHERE col1 = ?", tup_df)
  File "/home/ray/.local/lib/python3.6/site-packages/jaydebeapi/__init__.py", line 516, in executemany
    self._set_stmt_parms(self._prep, parameters)
  File "/home/ray/.local/lib/python3.6/site-packages/jaydebeapi/__init__.py", line 490, in _set_stmt_parms
    prep_stmt.setObject(i + 1, parameters[i])
RuntimeError: No matching overloads found for setObject in find. at native/common/jp_method.cpp:127

Current db is db2, jaydebeapi - 1.1.1, jpype - 0.6.3, python - 3.6. Changing DB didn't help. Any help will be much thankful.

Solution

I think the problem is being caused by attempting to insert NumPy values into the database, and these values are not being handled by jaydebeapi nor JPype.

If you look at the source code of the jaydebeapi module, in /home/ray/.local/lib/python3.6/site-packages/jaydebeapi/__init__.py according to the traceback in your question, you will probably see a commented-out line

        # print (i, parameters[i], type(parameters[i]))

within _set_stmt_params. It's line 489 on my machine. I uncommented this line, ran your code and I got the following output:

0 3563412 <class 'numpy.int64'>

This tells me that the parameter value 3563412 is a NumPy 64-bit integer value.

My best guess is that the error arises because neither jaydebeapi nor JPype know how to convert this to a Java value.

It looks to me as if you will have to convert the values each row of your dataframe into ints (or some other Python type that can be converted into Java), using something like the following:

tup_df = [tuple(int(y) for y in x) for x in df.values]

I made this change to your code and it no longer generated the error in your question.

If there is a built-in function in NumPy or Pandas to do this for you, feel free to use this instead.

Incidentally, I got the same error with either one or two parameters: things did not work as normal for me in the two-parameter case as they appear to have done with you.