Re: [sqlalchemy] unhandled data type cx_Oracle.LOB
GP pandit.gau...@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+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
Re: [sqlalchemy] unhandled data type cx_Oracle.LOB
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:
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
[sqlalchemy] Sqlalchemy, Flask-User scoped_session
Hi everyone, I can't get the following straight although I guess it's a pretty obvious thing, since I haven't found much about this topic online. I wrote a little python/flask/sqlalchemy based web-service where I want users to register themselves to post something. I also use flask-admin, LoginManager, etc. for this reason. Now as far as I understand I want to use a scoped_session, since I have a connection pool to my database and users could edit the database simultaneously. For that reason, I need my UserModel to be a subclass of Base (which is created using 'declarative_base'). If I do so however, flask-user complains that my 'UserModel' has no attribute 'query' (in db_adapters.py line 85). What am I missing? Thanks in advance, Wolf -- 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] Sqlalchemy, Flask-User scoped_session
On Mon, Mar 16, 2015 at 1:41 PM, Wolf Reitsamer wolf.reitsa...@gmail.com wrote: Hi everyone, I can't get the following straight although I guess it's a pretty obvious thing, since I haven't found much about this topic online. I wrote a little python/flask/sqlalchemy based web-service where I want users to register themselves to post something. I also use flask-admin, LoginManager, etc. for this reason. Now as far as I understand I want to use a scoped_session, since I have a connection pool to my database and users could edit the database simultaneously. For that reason, I need my UserModel to be a subclass of Base (which is created using 'declarative_base'). If I do so however, flask-user complains that my 'UserModel' has no attribute 'query' (in db_adapters.py line 85). What am I missing? (This is guesswork since I've never used Flask) In SQLAlchemy, scoped sessions and the declarative base system are orthogonal features with no dependencies between them. You can use declarative classes with non-scoped sessions, and scoped sessions with non-declarative classes. In a web application, you typically want to ensure that session aren't shared between threads, and this is often implemented by using scoped sessions. This is convenient because it allows you to refer to a global Session object. It's not the only way to do it though - if you are happy to pass the session as a parameter everywhere you need it, it's perfectly reasonable to construct a non-scoped session at the beginning of a request, and clean it up at the end of the request. However, I guess you are using the Flask-SQLAlchemy package, which makes a lot of assumptions for you. In particular, it gives you a special base class that adds a query property to every subclass, so that instead of writing: user = dbsession.query(User).get(1) ...you can write: user = User.query.get(1) https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy/__init__.py#L729 https://github.com/mitsuhiko/flask-sqlalchemy/blob/master/flask_sqlalchemy/__init__.py#L452 It sounds like flask-user assumes that you are using Flask-SQLAlchemy's special declarative_base class. I think that as long as you use Flask-SQLAlchemy's session (which is a scoped_session) and declarative_base everywhere, you will probably be fine. Hope that helps, Simon -- 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] deferred groups
Hi,
I'm using polymorphism and I set up some deferred columns at each level
of inheritance belonging to the same deferred group :
class Base(DBBASE):
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group=full
)
class Element(DBBASE):
id = Column(ForeignKey(base.id))
comments = deferred(
Column(Text()),
group=full,
)
The following query :
Element.query().options(undefer_group('full')).all()
doesn't defer the description column, is that the expected behaviour ?
Regards
--
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] deferred groups
tonthon tontho...@gmail.com wrote:
Hi,
I'm using polymorphism and I set up some deferred columns at each level
of inheritance belonging to the same deferred group :
class Base(DBBASE):
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group=full
)
class Element(DBBASE):
id = Column(ForeignKey(base.id))
comments = deferred(
Column(Text()),
group=full,
)
The following query :
Element.query().options(undefer_group('full')).all()
doesn't defer the description column, is that the expected behaviour ?
a query for Element here will not load objects of type “Base”, so I don’t see
where “description” comes into play.
If you can provide a more complete example that would help.
--
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] deferred groups
Sorry, still a few mistakes in what I wrote (first testing what you
paste is a far better way to work) :
121 from sqlalchemy.orm import
deferred
122 from sqlalchemy import
Text
123
124 class
Base(DBBASE):
125 __tablename__ =
'base'
126 __mapper_args__ = {'polymorphic_identity': 'base',
'polymorphic_on':'type_'}
127 id = Column(Integer,
primary_key=True)
128 name =
Column(String(255))
129 description =
deferred(
130
Column(Text()),
131
group=full
132
)
133 type_ = Column( String(30),
nullable=False)
134
135 class
Element(Base):
136 __tablename__ =
'element'
137 __mapper_args__ = {'polymorphic_identity':
'element'}
138 id = Column(ForeignKey(base.id),
primary_key=True)
139 comments = deferred( Column(Text()), group=full, )
print(DbSession().query(Element).options(undefer_group('full')))
SELECT element.comments AS element_comments, element.id AS element_id,
base.id AS base_id, base.name AS base_name, base.type_ AS base_type_
FROM base INNER JOIN element ON base.id = element.id
Le 16/03/2015 17:33, tonthon a écrit :
Sorry I was a bit too speed when writing that one :)
So I've got a Base model :
class Base(DBBASE):
__tablename__ =
'base'
__mapper_args__ = {'polymorphic_identity': 'base', 'polymorphic_on':
'type_'}
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group=full
)
type_ = Column( String(30), nullable=False)
and a child model
class Element(DBBASE):
__tablename__ = 'element'
__mapper_args__ = {'polymorphic_identity': 'element'}
id = Column(ForeignKey(base.id))
comments = deferred( Column(Text()), group=full, )
The following query :
Element.query().options(undefer_group('full')).all()
doesn't load the description column, is that the expected behaviour ?
Regards
Le 16/03/2015 17:21, Michael Bayer a écrit :
tonthon tontho...@gmail.com wrote:
Hi,
I'm using polymorphism and I set up some deferred columns at each level
of inheritance belonging to the same deferred group :
class Base(DBBASE):
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group=full
)
class Element(DBBASE):
id = Column(ForeignKey(base.id))
comments = deferred(
Column(Text()),
group=full,
)
The following query :
Element.query().options(undefer_group('full')).all()
doesn't defer the description column, is that the expected behaviour ?
a query for Element here will not load objects of type “Base”, so I don’t
see where “description” comes into play.
If you can provide a more complete example that would help.
--
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] deferred groups
tonthon tontho...@gmail.com wrote:
Sorry I was a bit too speed when writing that one :)
So I've got a Base model :
class Base(DBBASE):
__tablename__ =
'base'
__mapper_args__ = {'polymorphic_identity': 'base', 'polymorphic_on':
'type_'}
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group=full
)
type_ = Column( String(30), nullable=False)
and a child model
class Element(DBBASE):
__tablename__ = 'element'
__mapper_args__ = {'polymorphic_identity': 'element'}
id = Column(ForeignKey(base.id))
comments = deferred( Column(Text()), group=full, )
The following query :
Element.query().options(undefer_group('full')).all()
doesn't load the description column, is that the expected behaviour ?
When you say “a child model Element”, that’s not what I see. Element and
Base are both descending from DBBASE. If Element were a child of Base, it
would need to be declared as “class Element(Base)”.
As it is, it is expected because the “Element” class does not have a column
called “description”. If you were to load objects of type “Base”, then you’d
see “description”.
--
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] deferred groups
Thanks a lot for the time you spent on my question (and for the answer).
I'll try to make it directly clear next time.
Regards
Le 16/03/2015 17:49, Michael Bayer a écrit :
tonthon tontho...@gmail.com wrote:
Sorry, still a few mistakes in what I wrote (first testing what you
paste is a far better way to work) :
121 from sqlalchemy.orm import
deferred
122 from sqlalchemy import
Text
123
124 class
Base(DBBASE):
125 __tablename__ =
'base'
126 __mapper_args__ = {'polymorphic_identity': 'base',
'polymorphic_on':'type_'}
127 id = Column(Integer,
primary_key=True)
128 name =
Column(String(255))
129 description =
deferred(
130
Column(Text()),
131
group=full
132
)
133 type_ = Column( String(30),
nullable=False)
134
135 class
Element(Base):
136 __tablename__ =
'element'
137 __mapper_args__ = {'polymorphic_identity':
'element'}
138 id = Column(ForeignKey(base.id),
primary_key=True)
139 comments = deferred( Column(Text()), group=full, )
print(DbSession().query(Element).options(undefer_group('full')))
SELECT element.comments AS element_comments, element.id AS element_id,
base.id AS base_id, base.name AS base_name, base.type_ AS base_type_
FROM base INNER JOIN element ON base.id = element.id
looks like it was fixed in 0.9.9 in #3287. Please upgrade.
Le 16/03/2015 17:33, tonthon a écrit :
Sorry I was a bit too speed when writing that one :)
So I've got a Base model :
class Base(DBBASE):
__tablename__ =
'base'
__mapper_args__ = {'polymorphic_identity': 'base', 'polymorphic_on':
'type_'}
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group=full
)
type_ = Column( String(30), nullable=False)
and a child model
class Element(DBBASE):
__tablename__ = 'element'
__mapper_args__ = {'polymorphic_identity': 'element'}
id = Column(ForeignKey(base.id))
comments = deferred( Column(Text()), group=full, )
The following query :
Element.query().options(undefer_group('full')).all()
doesn't load the description column, is that the expected behaviour ?
Regards
Le 16/03/2015 17:21, Michael Bayer a écrit :
tonthon tontho...@gmail.com wrote:
Hi,
I'm using polymorphism and I set up some deferred columns at each level
of inheritance belonging to the same deferred group :
class Base(DBBASE):
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group=full
)
class Element(DBBASE):
id = Column(ForeignKey(base.id))
comments = deferred(
Column(Text()),
group=full,
)
The following query :
Element.query().options(undefer_group('full')).all()
doesn't defer the description column, is that the expected behaviour ?
a query for Element here will not load objects of type “Base”, so I don’t
see where “description” comes into play.
If you can provide a more complete example that would help.
--
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.
Re: [sqlalchemy] deferred groups
tonthon tontho...@gmail.com wrote:
Sorry, still a few mistakes in what I wrote (first testing what you
paste is a far better way to work) :
121 from sqlalchemy.orm import
deferred
122 from sqlalchemy import
Text
123
124 class
Base(DBBASE):
125 __tablename__ =
'base'
126 __mapper_args__ = {'polymorphic_identity': 'base',
'polymorphic_on':'type_'}
127 id = Column(Integer,
primary_key=True)
128 name =
Column(String(255))
129 description =
deferred(
130
Column(Text()),
131
group=full
132
)
133 type_ = Column( String(30),
nullable=False)
134
135 class
Element(Base):
136 __tablename__ =
'element'
137 __mapper_args__ = {'polymorphic_identity':
'element'}
138 id = Column(ForeignKey(base.id),
primary_key=True)
139 comments = deferred( Column(Text()), group=full, )
print(DbSession().query(Element).options(undefer_group('full')))
SELECT element.comments AS element_comments, element.id AS element_id,
base.id AS base_id, base.name AS base_name, base.type_ AS base_type_
FROM base INNER JOIN element ON base.id = element.id
looks like it was fixed in 0.9.9 in #3287. Please upgrade.
Le 16/03/2015 17:33, tonthon a écrit :
Sorry I was a bit too speed when writing that one :)
So I've got a Base model :
class Base(DBBASE):
__tablename__ =
'base'
__mapper_args__ = {'polymorphic_identity': 'base', 'polymorphic_on':
'type_'}
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group=full
)
type_ = Column( String(30), nullable=False)
and a child model
class Element(DBBASE):
__tablename__ = 'element'
__mapper_args__ = {'polymorphic_identity': 'element'}
id = Column(ForeignKey(base.id))
comments = deferred( Column(Text()), group=full, )
The following query :
Element.query().options(undefer_group('full')).all()
doesn't load the description column, is that the expected behaviour ?
Regards
Le 16/03/2015 17:21, Michael Bayer a écrit :
tonthon tontho...@gmail.com wrote:
Hi,
I'm using polymorphism and I set up some deferred columns at each level
of inheritance belonging to the same deferred group :
class Base(DBBASE):
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group=full
)
class Element(DBBASE):
id = Column(ForeignKey(base.id))
comments = deferred(
Column(Text()),
group=full,
)
The following query :
Element.query().options(undefer_group('full')).all()
doesn't defer the description column, is that the expected behaviour ?
a query for Element here will not load objects of type “Base”, so I don’t
see where “description” comes into play.
If you can provide a more complete example that would help.
--
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.
Re: [sqlalchemy] deferred groups
Sorry I was a bit too speed when writing that one :)
So I've got a Base model :
class Base(DBBASE):
__tablename__ =
'base'
__mapper_args__ = {'polymorphic_identity': 'base', 'polymorphic_on':
'type_'}
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group=full
)
type_ = Column( String(30), nullable=False)
and a child model
class Element(DBBASE):
__tablename__ = 'element'
__mapper_args__ = {'polymorphic_identity': 'element'}
id = Column(ForeignKey(base.id))
comments = deferred( Column(Text()), group=full, )
The following query :
Element.query().options(undefer_group('full')).all()
doesn't load the description column, is that the expected behaviour ?
Regards
Le 16/03/2015 17:21, Michael Bayer a écrit :
tonthon tontho...@gmail.com wrote:
Hi,
I'm using polymorphism and I set up some deferred columns at each level
of inheritance belonging to the same deferred group :
class Base(DBBASE):
id = Column(Integer, primary_key=True)
name = Column(String(255))
description = deferred(
Column(Text()),
group=full
)
class Element(DBBASE):
id = Column(ForeignKey(base.id))
comments = deferred(
Column(Text()),
group=full,
)
The following query :
Element.query().options(undefer_group('full')).all()
doesn't defer the description column, is that the expected behaviour ?
a query for Element here will not load objects of type “Base”, so I don’t see
where “description” comes into play.
If you can provide a more complete example that would help.
--
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] Sqlalchemy, Flask-User scoped_session
On Monday, March 16, 2015 at 10:22:27 AM UTC-4, Simon King wrote: It sounds like flask-user assumes that you are using Flask-SQLAlchemy's special declarative_base class. I think that as long as you use Flask-SQLAlchemy's session (which is a scoped_session) and declarative_base everywhere, you will probably be fine. You're correct, Simon. It's basically glossed over in the docs here -- http://flask-sqlalchemy.pocoo.org/2.0/quickstart/#road-to-enlightenment It's not as clear as it should be that: * users must inherit from Flask-Sqlalchemy's base, not regular Sqlalchemy * the model.query is flask-sqlalchemy extension. -- 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
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.
Re: [sqlalchemy] Sqlalchemy, Flask-User scoped_session
Thanks a lot, guys! Both your answers were very helpful. -- 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.
Re: [sqlalchemy] unhandled data type cx_Oracle.LOB
GP pandit.gau...@gmail.com wrote:
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 }
It’s a little odd you’re pulling the LOB object from the row directly;
SQLAlchemy should be converting this to a string. Are you setting
auto_convert_lobs to False? Or more likely, is the original query a plain
string and not a Core SQL expression ? If it’s a string, it is possible,
though shouldn’t be happening, that SQLAlchemy won’t be told that this is a
CLOB column and it doesn’t know to do any conversion and you’ll get
cx_oracle’s LOB back; you need to convert that to string.
I’ve checked the code and if OCI is reporting as CLOB, it should be
converted. But call value() on the LOB to resolve.
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.
--
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.
