GP <pandit.gau...@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. > 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 sqlalchemy+unsubscr...@googlegroups.com. > To post to this group, send email to sqlalchemy@googlegroups.com. > 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 sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.