Ah! Now I understand the genesis of the error and what I was doing
wrong. Many thanks for pointing that out. Yes I am indeed running
0.7.2. I'll test it on 0.7.8 and report back.
Thanks again for clearing me up on correlation.
On Jul 9, 12:17 am, Michael Bayer <mike...@zzzcomputing.com> wrote:
> yeah, actually, that's just what the behavior needs to be going forward,
> there are some improvements in 0.8 in that you'll be able to say
> correlate_except(SnortEvent), since you definitely don't want it correlated,
> so once that API is in place I'll add to the docs.
>
> The situation is that by saying lazy=False, you're asking for "SELECT
> event.*, sig_alias.*, (your subquery correlated against sig_alias) FROM event
> LEFT OUTER JOIN signature AS sig_alias...". So it is natural that because
> "event" is in the FROM clause of the enclosing query, auto-correlation will
> want to remove "event" from the subquery. So explicit definition of
> correlation is needed here, and should really be in all of these
> column_property() cases so the docs will be updated.
>
> On Jul 8, 2012, at 2:35 PM, Michael Bayer wrote:
>
>
>
>
>
>
>
> > I have it appearing in 0.7.3, which would imply you're on 0.7.2 on the
> > working system.
>
> > On Jul 8, 2012, at 2:31 PM, Michael Bayer wrote:
>
> >> its a bug. Here's a workaround, declare this outside of the Signature
> >> class:
>
> >> Signature.sig_count = column_property(
> >> select([func.count('*')]).\
> >> where(SnortEvent.signature == Signature.id).\
> >> correlate(Signature.__table__)
> >> )
>
> >> On Jul 8, 2012, at 12:32 PM, RedBaron wrote:
>
> >>> I have written some code on (I think) SqlAlchemy 0.7.5 on my office
> >>> computer and it works perfectly. Now I tried to run the same code on
> >>> my home computer and installed Sqlalchemy 0.7.8. However, the first
> >>> query statement returned an error "InvalidRequestError: Select
> >>> statement 'SELECT count(%s) AS count_1 FROM event, signature AS
> >>> signature_1 WHERE event.signature = signature_1.sig_id' returned no
> >>> FROM clauses due to auto-correlation; specify correlate(<tables>) to
> >>> control correlation manually"
>
> >>> The tables in question are
> >>> class SnortEvent(Base):
> >>> __tablename__="event"
>
> >>> sid=Column(Integer,ForeignKey("sensor.sid"),primary_key=True)
> >>> cid=Column(mysql.MSInteger(unsigned=True),primary_key=True)
> >>> signature=Column(Integer,ForeignKey("signature.sig_id"))
> >>> timestamp=Column(DateTime)
> >>> isdel=Column('is_deleted',Boolean,default=False,nullable =False)
>
> >>> processed_event=relationship("ProcessedEvent",uselist=False,backref=backref
> >>> ("event",lazy='select'),cascade="all,delete-
> >>> orphan",lazy=False)
>
> >>> iphdr=relationship("IpHdr",uselist=False,backref=backref("event",lazy=True)
> >>> ,cascade="all,delete-
> >>> orphan",lazy=False)
> >>> #id = column_property(sid,cid)
> >>> def __init__(self,sid,cid,sig,ts):
> >>> self.sid=sid
> >>> self.cid=self.cid
> >>> self.timestamp=ts
> >>> self.signature=sig
>
> >>> def __repr__(self):
> >>> return "<Alert:%d-%d>"%(self.sid,self.cid)
>
> >>> class Signature(Base):
> >>> __tablename__="signature"
>
> >>> id=Column("sig_id",Integer,primary_key=True)
> >>> name=Column("sig_name",String(255))
>
> >>> class_id=Column("sig_class_id",Integer,ForeignKey("sig_class.sig_class_id")
> >>> )
> >>> sig_priority=Column(Integer)
> >>> sig_rev=Column(Integer)
> >>> sig_sid=Column(Integer)
> >>> sig_gid=Column(Integer)
>
> >>> references=relationship("SigReference",backref="sig",cascade="all",lazy=Tru
> >>> e)
>
> >>> events=relationship("SnortEvent",backref=backref("sig",lazy=False),cascade=
> >>> "all,delete-
> >>> orphan",lazy="joined",primaryjoin="Signature.id==SnortEvent.signature")
> >>> sig_count =
> >>> column_property(select([func.count('*'),]).where(SnortEvent.signature==id))
>
> >>> def __repr__(self):
> >>> return "<Signature:%s>"%(self.name)
>
> >>> The funny thing is that the error is raised if I try to use the all()
> >>> on query but slicing returns results just fine
> >>>>>> event = session.query(SnortEvent)
> >>>>>> event = event[1:1000]
> >>> ..........A long list of events
> >>>>>> event.all()
> >>> Above ERROR
> >>>>>> event[:1100]
> >>> .......Another long list
> >>>>>> event[:]
> >>> Above ERROR
>
> >>> I frankly cannot guess the cause of the error(which was not there in
> >>> ver 0.7.5) or its absense in limited query
> >>> Any help is appreciated
>
> >>> --
> >>> You received this message because you are subscribed to the Google Groups
> >>> "sqlalchemy" group.
> >>> To post to this group, send email to sqlalchemy@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 sqlalchemy@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 sqlalchemy@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 sqlalchemy@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.
