Executemany uses first row as template for the following rows.

[TsunekiR]

If the type for a column in the first row is different from another row it will throw an exception inside the executemany.

for instance the following parameters:

[ {'f1' : 1}, {'f1' : 1.0} ]
[ {'f1' : None}, {'f1' : 1.0} ]

will fail.
but these:

[ {'f1' : 1}, {'f1' : 1} ]
[ {'f1' : 1.0}, {'f1' : None} ]

will work.

1. What versions are you using?

platform.platform: Linux-5.14.0-1045-oem-x86_64-with-glibc2.29
sys.maxsize > 2**32: True
platform.python_version: 3.8.10

oracledb.__version__: 1.0.1

2. Is it an error or a hang or a crash?
   Error

3. What error(s) or behavior you are seeing?

  File "/home/tsuneki/tok/repos/tks-simulacao/src/core/interface/database/test_executemany.py", line 119, in <module>
    cursor.executemany(statement = query, parameters = dict)
  File "/home/tsuneki/.local/lib/python3.8/site-packages/oracledb/cursor.py", line 438, in executemany
    self._impl.bind_many(self, parameters)
  File "src/oracledb/impl/base/cursor.pyx", line 339, in oracledb.base_impl.BaseCursorImpl.bind_many
  File "src/oracledb/impl/base/cursor.pyx", line 54, in oracledb.base_impl.BaseCursorImpl._bind_values
  File "src/oracledb/impl/base/cursor.pyx", line 95, in oracledb.base_impl.BaseCursorImpl._bind_values_by_name
  File "src/oracledb/impl/base/bind_var.pyx", line 129, in oracledb.base_impl.BindVar._set_by_value
  File "src/oracledb/impl/base/var.pyx", line 176, in oracledb.base_impl.BaseVarImpl._check_and_set_value
  File "src/oracledb/impl/base/var.pyx", line 158, in oracledb.base_impl.BaseVarImpl._check_and_set_scalar_value
  File "/home/tsuneki/.local/lib/python3.8/site-packages/oracledb/errors.py", line 103, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.NotSupportedError: DPY-3013: unsupported Python type float for variable DB_TYPE_VARCHAR

4. Does your application call init_oracle_client()?

yes
oracledb.init_oracle_client(lib_dir='/usr/lib/oracle/19.8/client64/lib')

5. Include a runnable Python script that shows the problem.

CREATE TABLE "S1"."T1" 
   ("float_number" NUMBER(18,9));

import oracledb

oracledb.init_oracle_client(lib_dir='/usr/lib/oracle/19.8/client64/lib')

dictList = [{
    "float_number"   : None
},{
    "float_number"   : 2.0
}]

conn = oracledb.connect(dsn='db:1521/db', user='user', password='password')

query = '''INSERT INTO S1.T1 (float_number)
VALUES (:float_number)'''

with conn.cursor() as cursor:
    cursor.executemany(statement=query, parameters=dictList)
    cursor.commit()

[anthony-tuininga]

I can confirm this issue. The defer type assignment logic was precisely backwards! And clearly we don't have a test case for that situation yet. I'll address that right away.

[anthony-tuininga]

I've created a patch for this and added a test. Thanks for reporting this issue!

[TsunekiR]

Could you make this into a new release?

I use the oracledb module in a production environment and only official releases can be used in that enviroment

[anthony-tuininga]

Yes, of course. This patch just missed the cutoff for python-oracledb 1.0.2, though, so it will be some time before that can happen. Sometime in the next few weeks, though, at the latest.

[anthony-tuininga]

This fix is included in python-oracledb 1.0.3, which was just released.