I am trying to fetch SDO_GEOMETRY typed columns from an Oracle database using Python (3.11) and the oracledb library (1.3.0). I want to use an outputtypehandler to convert the SDO_GEOMETRY instances into pickle encoded bytes. This works fine for NUMBER columns, if I try to set the typ parameter in cursor.var to typ=str, but fails for typ=bytes and typ=oracledb.DB_TYPE_RAW for all kinds of column types. The SDO_GEOMETRY columns always produce errors regardless of the typ parameter value. The outconverter is not even called as shown below.
Here is my example code:
import oracledb
import pickle
def output_type_handler(cursor, name, default_type, size, precision, scale):
def pickle_converter(obj) -> bytes:
print(f"Converter called for {name}.")
return pickle.dumps(obj)
if default_type == oracledb.DB_TYPE_OBJECT:
return cursor.var(
typ=oracledb.DB_TYPE_RAW,
size=size,
arraysize=cursor.arraysize,
outconverter=pickle_converter
)
# Switch to thick mode
oracledb.init_oracle_client()
ora_connection = oracledb.connect(
dsn=oracledb.makedsn("ora.local", 1521, "TST"),
user="test",
password="test"
)
ora_connection.outputtypehandler = output_type_handler
with ora_connection.cursor() as cursor:
# GEOMETRIE is an SDO_GEOMETRY column
recs = cursor.execute("SELECT GEOMETRIE FROM MV_CS_STWG1KP").fetchmany(5)
print(recs)
Output (note, that the line Converter called for ... is not even printed so the converter was never called):
Traceback (most recent call last):
File "/home/jannis/.config/JetBrains/PyCharmCE2023.1/scratches/tmp.py", line 28, in <module>
num_recs = cursor.execute("SELECT GEOMETRIE FROM MV_CS_STWG1KP").fetchmany(5)
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
File "/home/jannis/PycharmProjects/etl_engine/venv/lib/python3.11/site-packages/oracledb/cursor.py", line 492, in fetchmany
row = fetch_next_row(self)
^^^^^^^^^^^^^^^^^^^^
File "src/oracledb/impl/base/cursor.pyx", line 397, in oracledb.base_impl.BaseCursorImpl.fetch_next_row
File "src/oracledb/impl/thick/cursor.pyx", line 132, in oracledb.thick_impl.ThickCursorImpl._fetch_rows
File "src/oracledb/impl/thick/utils.pyx", line 413, in oracledb.thick_impl._raise_from_odpi
File "src/oracledb/impl/thick/utils.pyx", line 403, in oracledb.thick_impl._raise_from_info
oracledb.exceptions.DatabaseError: ORA-00932: inconsistent datatypes: expected BINARY got ADT
I must use thick mode to connect to an older Oracle database. How can I solve this?
You need to convert to a Python object before serializing it. Even removing your output handler and pickling explicitly gives an error:
cur.execute("select geometry from testgeometry")
r, = cur.fetchone()
p = pickle.dumps(r) # fails with error "KeyError: '__getstate__'"
Instead try the following. It uses a type converter to convert to a Python object, and then a row factory to pickle this.
class mySDO(object):
def __init__(self, gtype, elemInfo, ordinates):
self.gtype = gtype
self.elemInfo = elemInfo
self.ordinates = ordinates
obj_type = con.gettype("MDSYS.SDO_GEOMETRY")
def SDOOutputTypeHandler(cursor, name, default_type, size, precision, scale):
def SDOOutConverter(DBobj):
return mySDO(int(DBobj.SDO_GTYPE), DBobj.SDO_ELEM_INFO.aslist(), DBobj.SDO_ORDINATES.aslist())
if default_type == oracledb.DB_TYPE_OBJECT:
return cursor.var(obj_type, arraysize=cursor.arraysize, outconverter=SDOOutConverter)
cur.outputtypehandler = SDOOutputTypeHandler
cur.execute("select geometry from testgeometry")
cur.rowfactory = lambda *args: pickle.dumps(args)
p = cur.fetchone()
print(p)
However, would it be better to use the 'well known binary' (WKB) format for spatial objects? You can get this directly from the DB without needing output converters or row factories:
oracledb.defaults.fetch_lobs = False
cur = con.cursor()
cur.execute("select sdo_util.to_wkbgeometry(geometry) from testgeometry")
b = cur.fetchone()
print(b)