unhandled data type cx_Oracle.LOB

[GP]

Hello,

While trying to insert into an Oracle table with one column defined as CLOB, I get the following error:

File "/home/xxxxx/.local/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 442, in do_execute
    cursor.execute(statement, parameters)
sqlalchemy.exc.NotSupportedError: (NotSupportedError) Variable_TypeByValue(): unhandled data type cx_Oracle.LOB

Statement: 'INSERT INTO contract_cancellation_test_s ( contract_id, cancel_dt, cancellation_obj) VALUES ( :contract_id, :cancel_dt, :cancellation_obj) '
Parameters: {'contract_id': 23.0, 'cancel_dt': datetime.datetime(2015, 1, 14, 0, 0),'cancellation_obj' : <cx_Oracle.LOB object at 0x7f0a427be4f0> }

Versions:

Python: 3.4
SQLAlchemy: 0.9.9
cx_Oracle: 5.1.3

Here is code snippet: I am selecting records from one table and inserting into another (both source and target are different schema - as handled by source_conn, target_conn)

    # Sample query: SELECT CONTRACT_ID, CANCEL_DT, CANCELLATION_OBJ from SOURCE_TABLE
    query_rs = source_conn.execute(select_query)
    while True:
        row = query_rs.fetchone()
        if not row:
            query_rs.close()
            break
        row_dict = dict(row)
        insert_target_stmt = l_target_table.insert()
        insert_target_stmt.execute(row_dict)

(My original code was using fetchmany() instead of fetchone(), but I simplified it to first make it work on row by row.)


Both the tables (source and target) are defined as :

CONTRACT_ID       NUMBER(19,0)
CANCEL_DT         TIMESTAMP(6)
CANCELLATION_OBJ  CLOB

I have read the relevant parts of sqlalechmy documentation - and have played with following parameters, but the error remains : auto_convert_lobs, auto_setinputsizes, arraysize


I can't figure out what I am doing wrong here.

Any help?

Thanks!
GP

[Michael Bayer]

GP <pandit...@gmail.com> wrote:

> Hello,
>
> While trying to insert into an Oracle table with one column defined as CLOB, I get the following error:
> File "/home/xxxxx/.local/lib/python3.4/site-packages/sqlalchemy/engine/default.py", line 442, in do_execute
> cursor.execute(statement, parameters)
> sqlalchemy.exc.NotSupportedError: (NotSupportedError) Variable_TypeByValue(): unhandled data type cx_Oracle.LOB
>
> Statement: 'INSERT INTO contract_cancellation_test_s ( contract_id, cancel_dt, cancellation_obj) VALUES ( :contract_id, :cancel_dt, :cancellation_obj) '
> Parameters: {'contract_id': 23.0, 'cancel_dt': datetime.datetime(2015, 1, 14, 0, 0),'cancellation_obj' : <cx_Oracle.LOB object at 0x7f0a427be4f0> }

It’s a little odd you’re pulling the LOB object from the row directly;
SQLAlchemy should be converting this to a string. Are you setting
auto_convert_lobs to False? Or more likely, is the original query a plain
string and not a Core SQL expression ? If it’s a string, it is possible,
though shouldn’t be happening, that SQLAlchemy won’t be told that this is a
CLOB column and it doesn’t know to do any conversion and you’ll get
cx_oracle’s LOB back; you need to convert that to string.

I’ve checked the code and if OCI is reporting as CLOB, it should be
converted. But call value() on the LOB to resolve.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+...@googlegroups.com.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.

[GP]

Thank you Michael.

auto_covert_lobs : I ran with all three possible values: True, False, and without supplying it. The results are the same.

The original query is a bit more complicated than the example I gave, and is built dynamically. But I am using sqlalchemy select, and not a plain string. Query is of object type "sqlalchemy.select.sql.selectable.Select" (Or "sqlalchemy.sql.expression.Select"?), if it helps.

Here is what the query object value looks like:

SELECT CAST(contract_id AS FLOAT) AS contract_id, cancel_dt AS cancel_dt, cancellation_obj AS cancellation_obj FROM contract_cancellation WHERE updated_ts BETWEEN :updated_ts_1 AND :updated_ts_2

Let me try calling value().


Thanks
GP

[GP]

I think now I (probably) know where this may be coming from.

You asked

> is the original query a plain string and not a Core SQL expression

The way I am forming the query is by using select , append_column, append_whereclause and finally append_from('my_table'). I think this pretty much generates a plain string query and not the one that's tied to a sqlalchemy table type object. And this may be why sqlalchemy is not applying necessary conversion because it doesn't really know the data types of the columns I am selecting?

Apologies if I am simplifying this too much and/or talking nonsense.

Thanks
GP

[GP]

So that's what was happening:

This select construct fails:

select_query = select()
select_query.append_column(contract_id)
select_query.append_column(cancel_dt)
select_query.append_column(cancellation_obj)
select_query.append_from(source_table_name)

But this select construct works:

select_query = select([source_table.c.contract_id, source_table.c.cancel_dt, source_table.c.cancellation_quote_obj])

So it's just matter of rewriting select query in the 'right' way.

Thanks for pointing in the right direction!
GP

[Michael Bayer]

GP <pandit...@gmail.com> wrote:

> So that's what was happening:
>
> This select construct fails:
> select_query = select()
> select_query.append_column(contract_id)
> select_query.append_column(cancel_dt)
> select_query.append_column(cancellation_obj)
> select_query.append_from(source_table_name)
>
>
> But this select construct works:
> select_query = select([source_table.c.contract_id, source_table.c.cancel_dt, source_table.c.cancellation_quote_obj])
>
> So it's just matter of rewriting select query in the 'right' way.
>
> Thanks for pointing in the right direction!

OK, is “cancellation_obj” a column object with CLOB as the datatype ?

even if you just made it this:

from sqlalchemy.sql import column
append_column(column(‘cancellation_obj’, CLOB))

that should work.


otherwise, what’s interesting here is to add a “column” without a datatype both bypasses the usual Table metadata feature, but also, bypasses if it was totally a plain text SQL string there’s logic in place to intercept the CLOB in that case also. the recipe above managed to avoid both.

> GP
>
> On Monday, March 16, 2015 at 4:57:28 PM UTC-4, GP wrote:
> I think now I (probably) know where this may be coming from.
>
> You asked
> > is the original query a plain string and not a Core SQL expression
>
> The way I am forming the query is by using select , append_column, append_whereclause and finally append_from('my_table'). I think this pretty much generates a plain string query and not the one that's tied to a sqlalchemy table type object. And this may be why sqlalchemy is not applying necessary conversion because it doesn't really know the data types of the columns I am selecting?
>
> Apologies if I am simplifying this too much and/or talking nonsense.
>
> Thanks
> GP
>
> On Monday, March 16, 2015 at 3:49:32 PM UTC-4, GP wrote:
> Thank you Michael.
>
> auto_covert_lobs : I ran with all three possible values: True, False, and without supplying it. The results are the same.
>
> The original query is a bit more complicated than the example I gave, and is built dynamically. But I am using sqlalchemy select, and not a plain string. Query is of object type "sqlalchemy.select.sql.selectable.Select" (Or "sqlalchemy.sql.expression.Select"?), if it helps.
>
> Here is what the query object value looks like:
> SELECT CAST(contract_id AS FLOAT) AS contract_id, cancel_dt AS cancel_dt, cancellation_obj AS cancellation_obj FROM contract_cancellation WHERE updated_ts BETWEEN :updated_ts_1 AND :updated_ts_2
>
> Let me try calling value().
>
>
> Thanks
> GP
>

[GP]

OK, is “cancellation_obj” a column object with CLOB as the datatype ?

Yes, that's how it's defined in the database.

Because of dynamic nature of the code, I was using append_column without specifying column type. I made changes to define column in table.c.<column_name> format rather than just using Column('column name'). This way, I can make sure column data types are included with column definitions, without me having to specify the data type explicitly with each column.

It's interesting that I used that one way (out of three possible ways) that wasn't 'right', but it's all good now :)

Now onto changing from fetchmany() to fetchone() - since LOBs are pretty much forcing me to use fetchone().

Thank you for your help!
GP

[Michael Bayer]

GP <pandit...@gmail.com> wrote:

> OK, is “cancellation_obj” a column object with CLOB as the datatype ?
>
> Yes, that's how it's defined in the database.
>
> Because of dynamic nature of the code, I was using append_column without specifying column type. I made changes to define column in table.c.<column_name> format rather than just using Column('column name'). This way, I can make sure column data types are included with column definitions, without me having to specify the data type explicitly with each column.
>
> It's interesting that I used that one way (out of three possible ways) that wasn't 'right', but it's all good now :)
>
> Now onto changing from fetchmany() to fetchone() - since LOBs are pretty much forcing me to use fetchone().

OK, if you were to get the CLOB types working correctly, SQLAlchemy’s result proxy works around that issue also, by fetching rows in chunks and converting the LOB objects to strings while they are still readable, so you could keep with the fetchmany() calls.

[GP]

That's what I thought, and it works, but there seems to be a difference in how resultset is handled when you select LOB column.

Here is a basic script, that selects record from a source table which has 36 rows. It fetches 10 records at a time.

from sqlalchemy import Table, select, create_engine, MetaData

engine = create_engine('oracle+cx_oracle://xxx:yyy@zzz')
conn = engine.connect()
metadata = MetaData()
metadata.bind = conn

source_table = Table('contract_cancellation_test', metadata, autoload=True)
target_table = Table('contract_cancellation_test_s', metadata, autoload=True)

# Query 1 : without selecting LOB  : Works fine
#select_query = select([source_table.c.contract_id, source_table.c.cancel_dt])

# Query 2 : selecting canellation_quote LOB column : Fails in last fetchmany because query_rs is closed
select_query = select([source_table.c.contract_id, source_table.c.cancel_dt, source_table.c.cancellation_obj])

query_rs = conn.execute(select_query)
print("executing select")

loop_count = 1
while True:
    rows = query_rs.fetchmany(size=10)
    if not rows:  # we are done if result set list is empty
        query_rs.close()
        break
    row_dict = [dict(l_row) for l_row in rows]
    insert_target_stmt = target_table.insert()
    print("inserting for loop = {}".format(str(loop_count)))
    insert_target_stmt.execute(row_dict)
    loop_count += 1

print("done")
conn.close()

Query 1 does not have LOB type column, and it works fine. Query 2 has LOB type column in and it fails in fetchmany() call after last set is retrieved.

Here is the output:

----- results query 1 -----

executing select
inserting for loop = 1
inserting for loop = 2
inserting for loop = 3
inserting for loop = 4
done


----- results query 1 -----

executing select
inserting for loop = 1
inserting for loop = 2
inserting for loop = 3
inserting for loop = 4
Traceback (most recent call last):
  File "/home/xxx/.local/lib/python3.4/site-packages/sqlalchemy/engine/result.py", line 733, in _fetchone_impl
    return self.cursor.fetchone()
AttributeError: 'NoneType' object has no attribute 'fetchone'

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/xxx/myprojects/python/sync/test_lob_1.py", line 23, in <module>
    rows = query_rs.fetchmany(size=10)
  ...
  ...
  File "/home/xxx/.local/lib/python3.4/site-packages/sqlalchemy/engine/result.py", line 759, in _non_result
    raise exc.ResourceClosedError("This result object is closed.")
sqlalchemy.exc.ResourceClosedError: This result object is closed.

As long as I can check that resultset is empty and break from the loop, I am fine. Any better way of handling this?

Thanks
GP

[Michael Bayer]

That’s a bug in the oracle-specific result proxy. I’ve created https://bitbucket.org/zzzeek/sqlalchemy/issue/3329/fetchmany-fails-on-bufferedcolproxy-on for that.

[GP]

Ah! I will keep track of it - for now, I will just ignore AttributeError exception.

I am loving sqlalchemy, thanks for creating and maintaining it!

-GP