Ah! I will keep track of it - for now, I will just ignore AttributeError
exception.
I am loving sqlalchemy, thanks for creating and maintaining it!
-GP
On Monday, March 16, 2015 at 11:37:36 PM UTC-4, Michael Bayer wrote:
>
>
>
> GP <pandit...@gmail.com <javascript:>> 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+...@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.
