Hi,
Thanks for the quick reply.
In my query i am not using any limit()/offset()/first()
but i am using order by ascending. can "order by" cause an issue??
avail_states=DBSession.query(
AvailState).with_lockmode('update').\
filter(AvailState.entity_id.in_(input_entityids)).\
order_by(AvailState.entity_type.asc()).\
order_by(AvailState.entity_id.asc()).\
order_by(AvailState.owner.asc()).all()
the given python code is the actual code ( stack trace got modified while i
removed some package names. sorry about that.)
In mysql same code generates a single "select for update" query.
in oracle it translates into 3 nested queries.
thnx again
On Wed, May 26, 2010 at 9:53 AM, Michael Bayer <mike...@zzzcomputing.com>wrote:
>
> On May 26, 2010, at 9:16 AM, dhanil anupurath wrote:
>
> > Hi
> >
> > I am using oracle/mysql and sqlalchemy for a project I am working on.
> >
> > I am using lockmode in SA query,while generating an ORACLE query it
> > causes trouble.
> >
> > The class definition is like this:
> >
> > class AvailState(Base):
> > MONITORING = 1
> > NOT_MONITORING = 0
> >
> > __tablename__ = 'avail_current'
> >
> > entity_id = Column(Unicode(50),Sequence('entity_id_seq'),
> > primary_key = True)
> > entity_type = Column(Integer)
> > avail_state = Column(Integer)
> > monit_state = Column(Integer)
> > transient_state = Column(Unicode(255))
> > transient_state_time = Column(DateTime)
> > owner = Column(Unicode(255))
> > timestamp = Column(DateTime)
> > description = Column(Unicode(256)
> >
> >
> > I have the table generated:
> >
> > Table Column Data
> > Type
> > AVAIL_CURRENT ENTITY_ID Varchar2
> > ENTITY_TYPE
> Number
> > AVAIL_STATE
> Number
> > MONIT_STATE
> Number
> > TRANSIENT_STATE Varchar2
> > TRANSIENT_STATE_TIME Date
> > OWNER
> Varchar2
> > TIMESTAMP
> Date
> > DESCRIPTION
> Varchar2
> >
> >
> > The query I have written in SA is:
> >
> > avail_states=DBSession.query(AvailState).with_lockmode(cls.lockmode).\
> >
> > filter(AvailState.entity_id.in_(input_entityids)).\
> >
> > order_by(AvailState.entity_type.asc()).\
> >
> > order_by(AvailState.entity_id.asc()).\
> >
> > order_by(AvailState.owner.asc()).all()
> >
> >
> > The SA-generated query is :
> >
> > SELECT avail_current_entity_id, avail_current_entity_type,
> > avail_current_avail_state, avail_current_monit_state,
> > avail_current_transient_state, avail_current_transient__1,
> > avail_current_owner, avail_current_timestamp,
> > avail_current_description FROM (SELECT avail_current_entity_id,
> > avail_current_entity_type, avail_current_avail_state,
> > avail_current_monit_state, avail_current_transient_state,
> > avail_current_transient__1, avail_current_owner,
> > avail_current_timestamp, avail_current_description, ROWNUM AS ora_rn
> > FROM (SELECT avail_current.entity_id AS avail_current_entity_id,
> > avail_current.entity_type AS avail_current_entity_type,
> > avail_current.avail_state AS avail_current_avail_state,
> > avail_current.monit_state AS avail_current_monit_state,
> > avail_current.transient_state AS avail_current_transient_state,
> > avail_current.transient_state_time AS avail_current_transient__1,
> > avail_current.owner AS avail_current_owner, avail_current.timestamp AS
> > avail_current_timestamp, avail_current.description AS
> > avail_current_description FROM avail_current WHERE
> > avail_current.entity_id = "bc3e8724-d282-08a9-272a-458a22e93f74 FOR
> > UPDATE) WHERE ROWNUM <=1) WHERE ora_rn > 0
>
> Oracle apparently cannot nest FOR UPDATE inside a nested query (ticket 1815
> is added for this issue). You will have to forego the usage of
> limit()/offset()/first() in your query, and only use "all()". (Your example
> python code is not the actual code since it doesn't match your stacktrace).
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To post to this group, send email to sqlalch...@googlegroups.com.
> To unsubscribe from this group, send email to
> sqlalchemy+unsubscr...@googlegroups.com<sqlalchemy%2bunsubscr...@googlegroups.com>
> .
> For more options, visit this group at
> http://groups.google.com/group/sqlalchemy?hl=en.
>
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalch...@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.
