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 [email protected].
To unsubscribe from this group, send email to
[email protected].
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
