GP <pandit.gau...@gmail.com> wrote:
> 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? 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. > > Thanks > GP > > > > On Monday, March 16, 2015 at 10:08:47 PM UTC-4, Michael Bayer wrote: > > > 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. > > > > > > > > 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. > > 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+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.