[sqlalchemy] lockmode-SA-error
Hi I am using oracle/mysql and sqlalchemy for a project I am working on. I have a query executed like this SELECT table_entity_id, table_entity_type, table_avail_state, table_monit_state, table_transient_state, table_transient__1, table_owner, table_timestamp, table_description FROM (SELECT table_entity_id, table_entity_type, table_avail_state, table_monit_state, table_transient_state, table_transient__1, table_owner, table_timestamp, table_description, ROWNUM AS ora_rn FROM (SELECT table.entity_id AS table_entity_id, table.entity_type AS table_entity_type, table.avail_state AS table_avail_state, table.monit_state AS table_monit_state, table.transient_state AS table_transient_state, table.transient_state_time AS table_transient__1, table.owner AS table_owner, table.timestamp AS table_timestamp, table.description AS table_description FROM table WHERE table.entity_id = d282-08a9-272a-458a22e93f74 FOR UPDATE) WHERE ROWNUM =1) WHERE ora_rn 0 This query uses FOR UPDATE in one of the select statements,where there am using a lockmode to a row in SA The query works fine with mysql but it fails in oracle with errorMissing right parenthesis. While I am excuting the single select command with FOR UPDATE it works fine for the given nested query it gives error,But with out the FOR UPDTAE syntax the query works fine with ORACLE I thing this an error for Lockmode using in a nested query. The error i have like this: DatabaseError: (DatabaseError) ORA-00907: missing right parenthesis 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.
[sqlalchemy] SA-Lockmode-oracle
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 ColumnData
Type
AVAIL_CURRENT ENTITY_ID Varchar2
ENTITY_TYPE Number
AVAIL_STATE Number
MONIT_STATE Number
TRANSIENT_STATEVarchar2
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-
[sqlalchemy] Acting on creation of model instances
I've only found partial answers to this problem so far, so I'd like to
expand on it here.
I have a site in which users post stories, and their friends are
notified. In order to decouple different parts of the business logic,
I would like to use a publish/subscribe mechanism that raises an event
for any new model instance, so that a news feed item referring to that
story can be created for each of the author's friends. I believe that
I want to create the news feed item only when the story has been
successfully committed, so that failure in creating the news feed item
doesn't cause the story to be rolled back too.
My current code looks like this:
class EventExtension(SessionExtension):
def __init__(self):
self.new = []
def after_flush(self, session, flush_context):
self.new = session.new
return EXT_CONTINUE
def after_commit(self, session):
for instance in self.new:
fire_event('model/create/%s' %
instance.__class__.__name__, instance)
self.new = []
return EXT_CONTINUE
The reason for the __init__ method is that after_commit was being
called without after_flush having been called, because I was calling
session.commit() after processing every request, or session.rollback()
if an exception was thrown. Perhaps autocommit would suit me better.
The problem I have is that the newly created Story doesn't have its
relationship attributes populated. For example, author_id==1, but
author==None. This particularly confuses me because the docs say that
relations are lazily loaded by default.
Can someone explain to me what is going on, and clue me in to the
correct way to do this? Thank you.
--
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.
Re: [sqlalchemy] Acting on creation of model instances
On May 26, 2010, at 12:18 AM, Dan Ellis wrote:
class EventExtension(SessionExtension):
def __init__(self):
self.new = []
def after_flush(self, session, flush_context):
self.new = session.new
return EXT_CONTINUE
def after_commit(self, session):
for instance in self.new:
fire_event('model/create/%s' %
instance.__class__.__name__, instance)
self.new = []
return EXT_CONTINUE
The reason for the __init__ method is that after_commit was being
called without after_flush having been called, because I was calling
session.commit() after processing every request, or session.rollback()
if an exception was thrown. Perhaps autocommit would suit me better.
but if after_commit() is called and after_flush() is not called, that means
nothing was flushed.
Usually the way to go about this is to populate a distinct collection of events
when objects are created:
foo = MyObject()
Session.add(foo)
add_commit_event(some_callable)
where add_commit_event:
def add_commit_event(task):
session = Session()
if not hasattr(session, '_after_commit_tasks'):
tasks = session._after_commit_tasks = []
else:
tasks = session._after_commit_tasks
tasks.append(task)
the session extension then pops from _after_commit_tasks.
The problem I have is that the newly created Story doesn't have its
relationship attributes populated. For example, author_id==1, but
author==None. This particularly confuses me because the docs say that
relations are lazily loaded by default.
the ORM is not going to help you much if you directly populate foreign key
identifiers instead of the corresponding relationship() attributes (this is in
the FAQ). Once the commit is complete, all objects are expired by default, so
that when you go to reach foo.author it will reload from the DB.You can
expire the attribute manually ahead of time if you want it to reload its value
(should be fine within after_commit).
--
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.
Re: [sqlalchemy] SA-Lockmode-oracle
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:
TableColumnData
Type
AVAIL_CURRENT ENTITY_ID Varchar2
ENTITY_TYPE Number
AVAIL_STATE Number
MONIT_STATE Number
TRANSIENT_STATEVarchar2
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.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
Re: [sqlalchemy] SA-Lockmode-oracle
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.comwrote:
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:
TableColumnData
Type
AVAIL_CURRENT ENTITY_ID Varchar2
ENTITY_TYPE
Number
AVAIL_STATE
Number
MONIT_STATE
Number
TRANSIENT_STATEVarchar2
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.comsqlalchemy%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.
[sqlalchemy] complex in clause
What is the best practice for this case? select * from my_tbl where (a,b) in ((1,1),(2,2)) It seems to me that in clause is a column attribute so i don't figure how to sqlalchemyfy this where condition Thank you in advance Gla -- Glauco Uri Prometeia SpA Via G. Marconi, 43 - 40122 Bologna Via Gonzaga, 7 - 20123 Milano Via Tirso, 26 - 00198 Roma Italia e-mail : glauco@prometeia.it phone : +39 051 6480911 --- Il contenuto e gli allegati di questo messaggio sono strettamente confidenziali, e ne sono vietati la diffusione, la riproduzione e l'uso non autorizzato. Il suo contenuto non costituisce impegno da parte della Società salvo accordo scritto tra quest'ultima ed il destinatario. Qualora il presente messaggio Le fosse pervenuto per errore, La preghiamo di comunicare immediatamente al mittente l'errata ricezione e di distruggere quanto ricevuto (compresi i file allegati) senza farne copia. Qualsivoglia utilizzo non autorizzato del contenuto di questo messaggio costituisce violazione dell'obbligo di non rivelare il contenuto della corrispondenza tra altri soggetti, salvo più grave illecito, ed espone il responsabile alle relative conseguenze. This e-mail (and any attachment(s)) is strictly confidential and for use only by intended recipient(s). Any use, distribution, reproduction or disclosure by any other person is strictly prohibited. The content of this e-mail does not constitute a commitment by the Company except where provided for in a written agreement between this e-mail addressee and the Company. If you are not an intended recipient(s), please notify the sender promptly and destroy this message and its attachments without reading or saving it in any manner. Any non authorized use of the content of this message constitutes a violation of the obligation to abstain from learning of the correspondence among other subjects, except for more serious offence, and exposes the person responsible to the relevant consequences. --- -- 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.
Re: [sqlalchemy] SA-Lockmode-oracle
On May 26, 2010, at 10:10 AM, dhanil anupurath wrote: 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?? your stacktrace says you are using first(): 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]) also SQLA 0.5 has inferior support for Oracle so I would suggest upgrading to 0.6 (the bug fix will only be in 0.6 in any case). the nested queries are because Oracle does not support LIMIT/OFFSET directly. A scheme using ROW NUMBER must be used. That scheme needs to be adjusted to move FOR UPDATE to the outside of the query. -- 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.
Re: [sqlalchemy] complex in clause
search the docs for tuple_. On May 26, 2010, at 10:35 AM, Glauco Uri wrote: What is the best practice for this case? select * from my_tbl where (a,b) in ((1,1),(2,2)) It seems to me that in clause is a column attribute so i don't figure how to sqlalchemyfy this where condition Thank you in advance Gla -- Glauco Uri Prometeia SpA Via G. Marconi, 43 - 40122 Bologna Via Gonzaga, 7 - 20123 Milano Via Tirso, 26 - 00198 Roma Italia e-mail : glauco@prometeia.it phone : +39 051 6480911 --- Il contenuto e gli allegati di questo messaggio sono strettamente confidenziali, e ne sono vietati la diffusione, la riproduzione e l'uso non autorizzato. Il suo contenuto non costituisce impegno da parte della Società salvo accordo scritto tra quest'ultima ed il destinatario. Qualora il presente messaggio Le fosse pervenuto per errore, La preghiamo di comunicare immediatamente al mittente l'errata ricezione e di distruggere quanto ricevuto (compresi i file allegati) senza farne copia. Qualsivoglia utilizzo non autorizzato del contenuto di questo messaggio costituisce violazione dell'obbligo di non rivelare il contenuto della corrispondenza tra altri soggetti, salvo più grave illecito, ed espone il responsabile alle relative conseguenze. This e-mail (and any attachment(s)) is strictly confidential and for use only by intended recipient(s). Any use, distribution, reproduction or disclosure by any other person is strictly prohibited. The content of this e-mail does not constitute a commitment by the Company except where provided for in a written agreement between this e-mail addressee and the Company. If you are not an intended recipient(s), please notify the sender promptly and destroy this message and its attachments without reading or saving it in any manner. Any non authorized use of the content of this message constitutes a violation of the obligation to abstain from learning of the correspondence among other subjects, except for more serious offence, and exposes the person responsible to the relevant consequences. --- -- 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. -- 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.
[sqlalchemy] scoped_session in a single threaded environment?
Hi all, Is there any harm in using scoped_session in a single threaded environment? cheers, Chris -- 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.
[sqlalchemy] Hierarchical queries with pgsql = 8.4.0
Hi everyone. I have been working in a little class that brings support
for with recursive idiom in my project. Actually the part it supports
for the moment are the necessary bits to generate hierarchical data (I
thought somebody might find it useful too so I added it as a recipe in
the wiki[1]).
The basic idea is that you submit a select expression like (see the wiki
for the whole example):
select([category.c.id, category.c.name])
and you will get a query like
with recursive rec as (SELECT category.id, category.name, 1 AS level,
ARRAY[id] AS connect_path
FROM category
WHERE coalesce(parent_id, 0) = 0 UNION ALL SELECT category.id,
category.name, rec.level + 1 AS level, array_append(rec.connect_path,
category.id) AS connect_path
FROM category, rec
WHERE category.parent_id = rec.id) SELECT rec.id, rec.name, rec.level,
rec.connect_path, case connect_path @ lead(connect_path, 1) over (order
by connect_path) when true then false else true end AS is_leaf
FROM rec order by connect_path
that will give you the same information you requested plus some extra
columns with hierarchy related info.
The final piece I'm missing is how to pass a where clause: actually I
cannot make the final sql instruction to accept the parameters I'm
passing and after hours of trying it seems my sqlalchemy-fu is exhausted
and I can't fix it by myself.
To illustrate the problem, this is the select with a where clause:
select([category.c.id, category.c.name], category.c.active==True)
this is the query it generates:
with recursive rec as (SELECT dummy_hierarchy.id, 1 AS level, ARRAY[id]
AS connect_path
FROM dummy_hierarchy
WHERE dummy_hierarchy.active = %(active_1)s AND coalesce(parent_id, 0) =
0 UNION ALL SELECT dummy_hierarchy.id, rec.level + 1 AS level,
array_append(rec.connect_path, dummy_hierarchy.id) AS connect_path
FROM dummy_hierarchy, rec
WHERE dummy_hierarchy.active = %(active_1)s AND
dummy_hierarchy.parent_id = rec.id) SELECT rec.id, rec.level,
rec.connect_path, case connect_path @ lead(connect_path, 1) over (order
by connect_path) when true then false else true end AS is_leaf
FROM rec order by connect_path
and this is the error I'm getting (if I run with nosetest):
Traceback (most recent call last):
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/nose-0.11.3-py2.6.egg/nose/case.py,
line 186, in runTest
self.test(*self.arg)
File /home/mariano/Code/insite/dev/insite/insite/tests/test1_hierarchy.py,
line 261, in test8_where_clause
rs = Session.execute(qry).fetchall()
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/scoping.py,
line 129, in do
return getattr(self.registry(), name)(*args, **kwargs)
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/orm/session.py,
line 737, in execute
clause, params or {})
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py,
line 1109, in execute
return Connection.executors[c](self, object, multiparams, params)
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py,
line 1186, in _execute_clauseelement
return self.__execute_context(context)
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py,
line 1215, in __execute_context
context.parameters[0], context=context)
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/base.py,
line 1282, in _cursor_execute
self.dialect.do_execute(cursor, statement, parameters, context=context)
File
/home/mariano/Sandbox/insite/lib/python2.6/site-packages/SQLAlchemy-0.6.0-py2.6.egg/sqlalchemy/engine/default.py,
line 277, in do_execute
cursor.execute(statement, parameters)
KeyError: 'active_1'
Running from the python shell I get a totally different error but it's still
about the bind parameter (it does not fail but
it doesn't take the bind value either):
Session.execute(x.compile()).fetchall()
with recursive rec as (SELECT category.id, category.name, 1 AS level, ARRAY[id]
AS connect_path
FROM category
WHERE category.active = %(active_1)s AND coalesce(parent_id, 0) = 0 UNION ALL
SELECT category.id, category.name, rec.level + 1 AS level,
array_append(rec.connect_path, category.id) AS connect_path
FROM category, rec
WHERE category.active = %(active_1)s AND category.parent_id = rec.id) SELECT
rec.id, rec.name, rec.level, rec.connect_path, case connect_path @
lead(connect_path, 1) over (order by connect_path) when true then false else
true end AS is_leaf
FROM rec order by connect_path
{'active_1': None}
[]
The tar file in the wiki includes the whole class (+/- 200 lines of code but at
least half of
them are comments and doc strings) plus a test suite with 7 tests demonstrating
the usage.
If somebody has the time
Re: [sqlalchemy] Hierarchical queries with pgsql = 8.4.0
On May 26, 2010, at 11:47 AM, Mariano Mara wrote:
Hi everyone. I have been working in a little class that brings support
for with recursive idiom in my project. Actually the part it supports
for the moment are the necessary bits to generate hierarchical data (I
thought somebody might find it useful too so I added it as a recipe in
the wiki[1]).
The basic idea is that you submit a select expression like (see the wiki
for the whole example):
select([category.c.id, category.c.name])
and you will get a query like
with recursive rec as (SELECT category.id, category.name, 1 AS level,
ARRAY[id] AS connect_path
FROM category
WHERE coalesce(parent_id, 0) = 0 UNION ALL SELECT category.id,
category.name, rec.level + 1 AS level, array_append(rec.connect_path,
category.id) AS connect_path
FROM category, rec
WHERE category.parent_id = rec.id) SELECT rec.id, rec.name, rec.level,
rec.connect_path, case connect_path @ lead(connect_path, 1) over (order
by connect_path) when true then false else true end AS is_leaf
FROM rec order by connect_path
that will give you the same information you requested plus some extra
columns with hierarchy related info.
The final piece I'm missing is how to pass a where clause: actually I
cannot make the final sql instruction to accept the parameters I'm
passing and after hours of trying it seems my sqlalchemy-fu is exhausted
and I can't fix it by myself.
To illustrate the problem, this is the select with a where clause:
select([category.c.id, category.c.name], category.c.active==True)
I dont have time to read all your source but when you say
category.c.active==True, you get a structure like:
_BinaryExpression
Column('active'), operator.eq, _BindParamClause('active_1', value=True)
the value of that bind maybe could get lost if you aren't compiling the
statement fully with the same compiler object. for example, if you had
something like this:
@compiles(FooBar)
def compile_foo_bar(element, compiler, **kw):
return FOO BAR + str(element.value)
the str(element.value), if that is also a ClauseElement, is going to invoke a
whole new compiler with its own set of bind param values. You don't want to
do that. You want to say:
@compiles(FooBar)
def compile_foo_bar(element, compiler, **kw):
return FOO BAR + compiler.process(element.value)
thus keeping everything within the same context. this not only maintains all
the binds but also maintains the behavior of the backend being compiled against.
hope this helps...
--
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.
Re: [sqlalchemy] Hierarchical queries with pgsql = 8.4.0
Excerpts from Michael Bayer's message of Wed May 26 13:23:01 -0300 2010:
On May 26, 2010, at 11:47 AM, Mariano Mara wrote:
Hi everyone. I have been working in a little class that brings support
for with recursive idiom in my project. Actually the part it supports
for the moment are the necessary bits to generate hierarchical data (I
thought somebody might find it useful too so I added it as a recipe in
the wiki[1]).
The basic idea is that you submit a select expression like (see the wiki
for the whole example):
select([category.c.id, category.c.name])
and you will get a query like
with recursive rec as (SELECT category.id, category.name, 1 AS level,
ARRAY[id] AS connect_path
FROM category
WHERE coalesce(parent_id, 0) = 0 UNION ALL SELECT category.id,
category.name, rec.level + 1 AS level, array_append(rec.connect_path,
category.id) AS connect_path
FROM category, rec
WHERE category.parent_id = rec.id) SELECT rec.id, rec.name, rec.level,
rec.connect_path, case connect_path @ lead(connect_path, 1) over (order
by connect_path) when true then false else true end AS is_leaf
FROM rec order by connect_path
that will give you the same information you requested plus some extra
columns with hierarchy related info.
The final piece I'm missing is how to pass a where clause: actually I
cannot make the final sql instruction to accept the parameters I'm
passing and after hours of trying it seems my sqlalchemy-fu is exhausted
and I can't fix it by myself.
To illustrate the problem, this is the select with a where clause:
select([category.c.id, category.c.name], category.c.active==True)
I dont have time to read all your source but when you say
category.c.active==True, you get a structure like:
_BinaryExpression
Column('active'), operator.eq, _BindParamClause('active_1', value=True)
the value of that bind maybe could get lost if you aren't compiling the
statement fully with the same compiler object. for example, if you had
something like this:
@compiles(FooBar)
def compile_foo_bar(element, compiler, **kw):
return FOO BAR + str(element.value)
the str(element.value), if that is also a ClauseElement, is going to invoke a
whole new compiler with its own set of bind param values. You don't want to
do that. You want to say:
@compiles(FooBar)
def compile_foo_bar(element, compiler, **kw):
return FOO BAR + compiler.process(element.value)
thus keeping everything within the same context. this not only maintains
all the binds but also maintains the behavior of the backend being compiled
against.
hope this helps...
For sure it helps, thanks for the pointer. Will try to fix my problem
with it.
Mariano
--
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.
[sqlalchemy] Re: Acting on creation of model instances
On May 26, 9:43 am, Michael Bayer mike...@zzzcomputing.com wrote: You can expire the attribute manually ahead of time if you want it to reload its value (should be fine within after_commit). No, it seems that in after_commit the newly added instance is not yet in session.identity_map, so session.expire throws an exception: InvalidRequestError: Instance 'Story at 0x102e46ad0' is not persistent within this Session. At this point I'm confused. Why would it not be persistent after a commit? (If I remove the extra code, the Story is persisted just fine, so it's not like the transaction is failing without the extension.) -- 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.
[sqlalchemy] innerjoin and eagerloading
I have a users table, with a lazy=False, innerjoin=True relation to the
preferences table
(these could probably be the same table but they are separate for
historical reasons).
One of the gotchas that I am running into is when I do an outerjoin on
to the user's table,
it effectively becomes a join.
r = session.query(Report).options(eagerload(Report.Users)).get(report_name)
returns 0 rows when report_name doesn't have any subscribed users I can
and do work around this
by assing in a options(lazyload('Users.Preferences')) to the query.
The generated SQL looks like:
SELECT bunch of columns
FROM report
LEFT OUTER JOIN user_report AS user_report_1 ON report.name =
user_report_1.report
LEFT OUTER JOIN users AS users_1 ON users_1.user_id = user_report_1.user
JOIN user_prefs AS user_prefs_1 ON users_1.user_id = user_prefs_1.user
WHERE report.name = %(param_1)s
However I wonder if it is feasible for SQLAlchemy to detect this and
generate this instead:
SELECT bunch of columns
FROM report
LEFT OUTER JOIN user_report AS user_report_1 ON report.name =
user_report_1.report
LEFT OUTER JOIN (users JOIN user_prefs AS user_prefs_1 ON users.user_id
= user_prefs_1.user )
AS users_1 ON users_1.user_id = user_report_1.user
Like I mentioned, I already have a work-around for this, and I realize
this is a function of the way I
defined my mappers.
--
David Gardner
Pipeline Tools Programmer
Jim Henson Creature Shop
dgard...@creatureshop.com
--
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.
Re: [sqlalchemy] Re: Acting on creation of model instances
On May 26, 2010, at 1:33 PM, Dan Ellis wrote: On May 26, 9:43 am, Michael Bayer mike...@zzzcomputing.com wrote: You can expire the attribute manually ahead of time if you want it to reload its value (should be fine within after_commit). No, it seems that in after_commit the newly added instance is not yet in session.identity_map, so session.expire throws an exception: InvalidRequestError: Instance 'Story at 0x102e46ad0' is not persistent within this Session. At this point I'm confused. Why would it not be persistent after a commit? (If I remove the extra code, the Story is persisted just fine, so it's not like the transaction is failing without the extension.) its in the identity map after the flush succeeds, which is well before after_commit() is called. However, you can't really use the Session itself for database access inside of after_commit(). There is information on this available here: http://www.sqlalchemy.org/trac/wiki/UsageRecipes/UniqueSession . Here, your after_commit() hooks start fresh with a new session. If you want to use the state of objects from the parent session in the new session without reloading from the new transaction, use merge(..., load=False). -- 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.
Re: [sqlalchemy] innerjoin and eagerloading
On May 26, 2010, at 1:39 PM, David Gardner wrote:
I have a users table, with a lazy=False, innerjoin=True relation to the
preferences table
(these could probably be the same table but they are separate for historical
reasons).
One of the gotchas that I am running into is when I do an outerjoin on to the
user's table,
it effectively becomes a join.
r = session.query(Report).options(eagerload(Report.Users)).get(report_name)
returns 0 rows when report_name doesn't have any subscribed users I can and
do work around this
by assing in a options(lazyload('Users.Preferences')) to the query.
The generated SQL looks like:
SELECT bunch of columns
FROM report
LEFT OUTER JOIN user_report AS user_report_1 ON report.name =
user_report_1.report
LEFT OUTER JOIN users AS users_1 ON users_1.user_id = user_report_1.user
JOIN user_prefs AS user_prefs_1 ON users_1.user_id = user_prefs_1.user
WHERE report.name = %(param_1)s
However I wonder if it is feasible for SQLAlchemy to detect this and generate
this instead:
SELECT bunch of columns
FROM report
LEFT OUTER JOIN user_report AS user_report_1 ON report.name =
user_report_1.report
LEFT OUTER JOIN (users JOIN user_prefs AS user_prefs_1 ON users.user_id =
user_prefs_1.user )
AS users_1 ON users_1.user_id = user_report_1.user
Like I mentioned, I already have a work-around for this, and I realize this
is a function of the way I
defined my mappers.
unfortunately the syntax x JOIN (y JOIN z) doesn't work on a lot of backends,
so the eagerloading is designed in such a way as to string all the joins
together from left to right.
im a little curious why I haven't hit this issue myself, a more immediate fix
in SQLA would be to ignore the innerjoin flag when its already down the line of
a string of outer joins.
the x join (y join z) allowance could perhaps be worked in as an option at
some point, though this might be elaborate.
In this case, you might find you get better results overall by using
subqueryload(Report.users) instead of eagerload(). You'd get two queries
instead of one, but all the joins would be inner. in the case of no rows
you'd of course only emit one query with no joins at all.
--
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.
[sqlalchemy] Roundin' up the children!
Hi,
I've got the following tables in my app (only showing applicable
columns here) storing categories for my app:
Base
- id (int) PK
- deleted (int) - 0/1 as a value
Category
- id (int) PK/FK - refers to Base.id
- parent_id (int) FK - self-referential to Category.id
I then have a Category object, which inherits from Base. All's good.
What I'm trying to do is when I get my Category object I only get
children which aren't deleted=1. My original property in my mapper was
this:
'children': relation(Category,
primaryjoin=TABLES.CATEGORY.c.id==
TABLES.CATEGORY.c.parent_id,
backref=backref('parent',
remote_side=[TABLES.CATEGORY.c.id]
),
),
Which works fine but gets everything. So I changed it to this:
'children': relation(Category, secondary=TABLES.BASE,
primaryjoin=TABLES.CATEGORY.c.id==TABLES.CATEGORY.c.parent_id,
secondaryjoin=and_(TABLES.BASE.c.id==TABLES.CATEGORY.c.id,
TABLES.BASE.c.deleted==False),
foreign_keys=[TABLES.CATEGORY.c.id],
backref=backref('parent',
remote_side=[TABLES.CATEGORY.c.id]
),
),
and I get nothing. Not a single object. Is my issue:
1. Foreign key related?
2. Join related?
3. Developer related?
Any help here is appreciated.
Thanks!
Jon
--
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.
Re: [sqlalchemy] Roundin' up the children!
On May 26, 2010, at 4:56 PM, ObjectEvolution wrote:
Hi,
I've got the following tables in my app (only showing applicable
columns here) storing categories for my app:
Base
- id (int) PK
- deleted (int) - 0/1 as a value
Category
- id (int) PK/FK - refers to Base.id
- parent_id (int) FK - self-referential to Category.id
I then have a Category object, which inherits from Base. All's good.
What I'm trying to do is when I get my Category object I only get
children which aren't deleted=1. My original property in my mapper was
this:
'children': relation(Category,
primaryjoin=TABLES.CATEGORY.c.id==
TABLES.CATEGORY.c.parent_id,
backref=backref('parent',
remote_side=[TABLES.CATEGORY.c.id]
),
),
Which works fine but gets everything. So I changed it to this:
'children': relation(Category, secondary=TABLES.BASE,
primaryjoin=TABLES.CATEGORY.c.id==TABLES.CATEGORY.c.parent_id,
secondaryjoin=and_(TABLES.BASE.c.id==TABLES.CATEGORY.c.id,
TABLES.BASE.c.deleted==False),
foreign_keys=[TABLES.CATEGORY.c.id],
backref=backref('parent',
remote_side=[TABLES.CATEGORY.c.id]
),
),
the way secondary works is:
parent - primaryjoin- secondary - secondaryjoin - child
So primaryjoin has to be in terms of category and base, as does
secondaryjoin. Also you don't use remote_side with secondary.
foreign_keys are also usually implicit from your Table metadata and its rare
these are needed (unless an error message asks for them, which often indicates
something else is the actual issue).
what you have here really does not appear to be a many-to-many relationship,
its one-to-many/many-to-one. So if you want the relationship to add a where
criterion for the base.deleted, you likely just want to use and_() all within
the primaryjoin.and_(category.id==category.parent_id , base.deleted==False,
base.id==category.id).
and I get nothing. Not a single object. Is my issue:
1. Foreign key related?
2. Join related?
3. Developer related?
Any help here is appreciated.
Thanks!
Jon
--
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.
--
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.
[sqlalchemy] Re: Roundin' up the children!
Thanks for the input Michael. I think the polymorphism is messing
things up...just a hunch. Your suggestion didn't work but this ended
up working:
'children': relation(Category,
primaryjoin=and_(TABLES.CATEGORY.c.id==TABLES.CATEGORY.c.parent_id,
TABLES.BASE.c.deleted==False),
backref=backref('parent',
remote_side=(TABLES.CATEGORY.c.id)),
),
Does that make sense? I was excited that it worked at first but then I
wasn't quite sure how it worked. How does it recognize TABLES.BASE?
I'm thinking that it might be best to have a mapping instead of what
we have now given our polymorphism. Thoughts on that?
On May 26, 3:00 pm, Michael Bayer mike...@zzzcomputing.com wrote:
On May 26, 2010, at 4:56 PM, ObjectEvolution wrote:
Hi,
I've got the following tables in my app (only showing applicable
columns here) storing categories for my app:
Base
- id (int) PK
- deleted (int) - 0/1 as a value
Category
- id (int) PK/FK - refers to Base.id
- parent_id (int) FK - self-referential to Category.id
I then have a Category object, which inherits from Base. All's good.
What I'm trying to do is when I get my Category object I only get
children which aren't deleted=1. My original property in my mapper was
this:
'children': relation(Category,
primaryjoin=TABLES.CATEGORY.c.id==
TABLES.CATEGORY.c.parent_id,
backref=backref('parent',
remote_side=[TABLES.CATEGORY.c.id]
),
),
Which works fine but gets everything. So I changed it to this:
'children': relation(Category, secondary=TABLES.BASE,
primaryjoin=TABLES.CATEGORY.c.id==TABLES.CATEGORY.c.parent_id,
secondaryjoin=and_(TABLES.BASE.c.id==TABLES.CATEGORY.c.id,
TABLES.BASE.c.deleted==False),
foreign_keys=[TABLES.CATEGORY.c.id],
backref=backref('parent',
remote_side=[TABLES.CATEGORY.c.id]
),
),
the way secondary works is:
parent - primaryjoin- secondary - secondaryjoin - child
So primaryjoin has to be in terms of category and base, as does
secondaryjoin. Also you don't use remote_side with secondary.
foreign_keys are also usually implicit from your Table metadata and its
rare these are needed (unless an error message asks for them, which often
indicates something else is the actual issue).
what you have here really does not appear to be a many-to-many relationship,
its one-to-many/many-to-one. So if you want the relationship to add a where
criterion for the base.deleted, you likely just want to use and_() all
within the primaryjoin. and_(category.id==category.parent_id ,
base.deleted==False, base.id==category.id).
and I get nothing. Not a single object. Is my issue:
1. Foreign key related?
2. Join related?
3. Developer related?
Any help here is appreciated.
Thanks!
Jon
--
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
athttp://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.
[sqlalchemy] Use a foreign key mapping to get data from the other table using Python and SQLAlchemy.
Basically, I've got these simple classes mapped to tables, using
SQLAlchemy. I know they're missing a few items but those aren't
essential for highlighting the problem.
class Customer(object):
def __init__(self, uid, name, email):
self.uid = uid
self.name = name
self.email = email
def __repr__(self):
return str(self)
def __str__(self):
return Cust: %s, Name: %s (Email: %s) %(self.uid,
self.name, self.email)
The above is basically a simple customer with an id, name and an email
address.
class Order(object):
def __init__(self, item_id, item_name, customer):
self.item_id = item_id
self.item_name = item_name
self.customer = None
def __repr__(self):
return str(self)
def __str__(self):
return Item ID %s: %s, has been ordered by customer no.
%s %(self.item_id, self.item_name, self.customer)
This is the `Orders` class that just holds the order information: an
id, a name and a reference to a customer. It's initialised to `None`
to indicate that this item doesn't have a customer yet. The code's job
will assign the item a customer.
The following code maps these classes to respective database tables.
# SQLAlchemy database transmutation
engine = create_engine('sqlite:///:memory:', echo=False)
metadata = MetaData()
customers_table = Table('customers', metadata,
Column('uid', Integer, primary_key=True),
Column('name', String),
Column('email', String)
)
orders_table = Table('orders', metadata,
Column('item_id', Integer, primary_key=True),
Column('item_name', String),
Column('customer', Integer, ForeignKey('customers.uid'))
)
metadata.create_all(engine)
mapper(Customer, customers_table)
mapper(Orders, orders_table)
Now if I do something like:
for order in session.query(Order):
print order
I can get a list of orders in this form:
Item ID 1001: MX4000 Laser Mouse, has been ordered by customer no.
12
=
What I want to do is find out customer 12's name and email address
(which is why I used the ForeignKey into the Customer table). How
would I go about it?
=
--
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.
