GP <[email protected]> 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.
> 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
>
> --
> 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 [email protected].
> To post to this group, send email to [email protected].
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
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 [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
