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 <[email protected] <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 <[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] <javascript:>.
> > To post to this group, send email to [email protected]
> <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 [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.
