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 On Monday, March 16, 2015 at 10:08:47 PM UTC-4, Michael Bayer wrote: > > > > GP <pandit...@gmail.com <javascript:>> 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. > > > > > > > > Thank you for your help! > > GP > > > > On Monday, March 16, 2015 at 5:54:54 PM UTC-4, Michael Bayer wrote: > > > > > > 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 > > > > > > -- > > > 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. > > > > -- > > 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 <javascript:>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:>. > > 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.