GP <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 [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.
>
> --
> 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.
