Re: [sqlalchemy] unhandled data type cx_Oracle.LOB
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
Re: [sqlalchemy] unhandled data type cx_Oracle.LOB
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
Re: [sqlalchemy] unhandled data type cx_Oracle.LOB
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+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.
Re: [sqlalchemy] unhandled data type cx_Oracle.LOB
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!
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+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.
[sqlalchemy] unhandled data type cx_Oracle.LOB
Hello,
While trying to insert into an Oracle table with one column defined as
CLOB, I get the following error:
File
/home/x/.local/lib/python3.4/site-packages/sqlalchemy/engine/default.py
, line 442, in do_execute
cursor.execute(statement, parameters)
sqlalchemy.exc.NotSupportedError: (NotSupportedError) Variable_TypeByValue
(): unhandled data type cx_Oracle.LOB
Statement: 'INSERT INTO contract_cancellation_test_s ( contract_id,
cancel_dt, cancellation_obj) VALUES ( :contract_id, :cancel_dt,
:cancellation_obj) '
Parameters: {'contract_id': 23.0, 'cancel_dt': datetime.datetime(2015, 1, 14
, 0, 0),'cancellation_obj' : cx_Oracle.LOB object at 0x7f0a427be4f0 }
Versions:
Python: 3.4
SQLAlchemy: 0.9.9
cx_Oracle: 5.1.3
Here is code snippet: I am selecting records from one table and inserting
into another (both source and target are different schema - as handled by
source_conn, target_conn)
# Sample query: SELECT CONTRACT_ID, CANCEL_DT, CANCELLATION_OBJ from
SOURCE_TABLE
query_rs = source_conn.execute(select_query)
while True:
row = query_rs.fetchone()
if not row:
query_rs.close()
break
row_dict = dict(row)
insert_target_stmt = l_target_table.insert()
insert_target_stmt.execute(row_dict)
(My original code was using fetchmany() instead of fetchone(), but I
simplified it to first make it work on row by row.)
Both the tables (source and target) are defined as :
CONTRACT_ID NUMBER(19,0)
CANCEL_DT TIMESTAMP(6)
CANCELLATION_OBJ CLOB
I have read the relevant parts of sqlalechmy documentation - and have
played with following parameters, but the error remains : auto_convert_lobs,
auto_setinputsizes, arraysize
I can't figure out what I am doing wrong here.
Any help?
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+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.
[sqlalchemy] Auto-loading With Relationships
We have sets of databases with several hundred tables per database. Most of these tables are linked using various foreign key relationships. Is there any sort of automatic SQL Alchemy generation? Either SQL Soup like loading or a script that gets run once that generates code for use. -Gp --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Auto-loading With Relationships
According to Autocode's readme it doesn't support relationships. The major reason that Soup won't work for me is it doesn't support relationships between tables. I'm still going to take a closer look at Autocode. Thanks for the suggestion. -Paul On Feb 17, 4:09 pm, Michael Trier mtr...@gmail.com wrote: Hi, On Tue, Feb 17, 2009 at 2:50 PM, Gp gpm...@gmail.com wrote: We have sets of databases with several hundred tables per database. Most of these tables are linked using various foreign key relationships. Is there any sort of automatic SQL Alchemy generation? Either SQL Soup like loading or a script that gets run once that generates code for use. - There's this:http://www.sqlalchemy.org/trac/wiki/UsageRecipes/AutoCode -- Michael Trierhttp://blog.michaeltrier.com/http://thisweekindjango.com/ --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
