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
The query works fine with MYSQL
I am geting an error like this
Traceback (most recent call last):
File "model/availability.py", line 278, in set_none_state
avail=DBSession.query(AvailState).with_lockmode(cls.lockmode).\
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py", line 1300, in first
ret = list(self[0:1])
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py", line 1221, in __getitem__
return list(res)
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py", line 1361, in __iter__
return self._execute_and_instances(context)
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/query.py", line 1364, in
_execute_and_instances
result = self.session.execute(querycontext.statement,
params=self._params, mapper=self._mapper_zero_or_none())
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/orm/session.py", line 754, in execute
return self.__connection(engine, close_with_result=True).execute(
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py", line 824, in execute
return Connection.executors[c](self, object, multiparams, params)
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py", line 874, in
_execute_clauseelement
return self.__execute_context(context)
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py", line 896, in __execute_context
self._cursor_execute(context.cursor, context.statement,
context.parameters[0], context=context)
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py", line 950, in _cursor_execute
self._handle_dbapi_exception(e, statement, parameters, cursor,
context)
File "/root/tg2env/lib/python2.4/site-packages/SQLAlchemy-0.5.6-
py2.4.egg/sqlalchemy/engine/base.py", line 931, in
_handle_dbapi_exception
raise exc.DBAPIError.instance(statement, parameters, e,
connection_invalidated=is_disconnect)
DatabaseError: (DatabaseError) ORA-00907: missing right parenthesis
'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 \nFROM avail_current WHERE
avail_current.entity_id = :entity_id_1 FOR UPDATE) WHERE ROWNUM
<= :ROWNUM_1) \nWHERE ora_rn > :ora_rn_1' {'ROWNUM_1': 1,
'entity_id_1': '8893e212-536b-6006-d7a3-8058e9056ea2', 'ora_rn_1': 0}
For single query with FOR UPDATE works fine .
Any help is appreciable
Thanks
--
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.
