Can not update field of type Number(1, 0) sometimes

[yangmijun]

1. Software version:

   Oracle 21c Express Python 3.9 cx-Oracle 8.3.0 SQLAlchemy 2.0.20

2. Create a simple table with a column of type Number(1, 0), then input a few records into it.

CREATE TABLE "MODELS"."TEST_TABLE" ( "NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE, "VALUE" NUMBER(1, 0) )

3. I use SQLAlchemy + cx-Oracle to connect the db. I can update the "VALUE" column directly.

   this python code works:

   value_to_update = 0 up = (tb_info.update() .where(tb_info.c.name == "Value1") .values(value=value_to_update) )

4. But if I try to get value from a DataFrame, it will throw an exception: cx_Oracle.NotSupportedError: Python value of type numpy.int32 not supported.

   this python code will throw an exception:

   value_to_update = df1.loc[df1['name'] == 'Value1']['value'].squeeze().astype('int')

   up = (tb_info.update() .where(tb_info.c.name == "Value1") .values(value=value_to_update) )

5. The whole python code:

import pandas as pd from sqlalchemy import MetaData, Table, create_engine

engine=create_engine("oracle+cx_oracle://MODELS:models@localhost/?service_name=xepdb1", echo=True)

meta = MetaData() conn = engine.connect()

tb_info = Table('test_table', meta, autoload_with=engine)

---# Work value_to_update = 0 up = (tb_info.update() .where(tb_info.c.name == "Value1") .values(value=value_to_update) )

--- # Not Work df1 = pd.DataFrame({ "name":["Value1","Value2","Value3"], "value":[0,1,0] })

value_to_update = df1.loc[df1['name'] == 'Value1']['value'].squeeze().astype('int')

up = (tb_info.update() .where(tb_info.c.name == "Value1") .values(value=value_to_update) )

---# update conn.execute(up) conn.commit()

[anthony-tuininga]

This is not a bug! The list of types directly supported by the driver can be found in this table. Although this is for python-oracledb, the new version of the driver, the contents should be the same. If you want to bind other values you need to use an input type handler as shown in the documentation.

Because you are using SQLAlchemy you have to establish a hook first as is shown in the following code and then use that hook to create an input type handler:

import numpy
import oracledb
import sqlalchemy

pool = oracledb.create_pool(user="my_user", password="my_password", dsn="host_name:port/service_name",
                                                   min=1, max=5, increment=1)

def input_type_handler(cursor, value, arraysize):
    if isinstance(value, numpy.int64):
        return cursor.var(int, arraysize=arraysize, inconverter=lambda v: v.item())

def pool_acquire():
    conn = pool.acquire()
    conn.inputtypehandler = input_type_handler
    return conn

engine = sqlalcehmy.create_engine("oracle+oracledb://", creator=pool_acquire, poolclass=sqlalchemy.NullPool)

The remainder of your code can be used unchanged. The example uses SQLAlchemy 2.0 and the python-oracledb driver but this should also work for cx_Oracle if you are unable to ugprade.

Another option for you is to call item() yourself on the value to update instead of binding the value directly. In that case you don't need the code above!

[yangmijun]

@anthony-tuininga Thank you very much, I just add a "item()" to the "value_to_update", and everything is fine now.