Hi Simon, That's spot on - thanks so much. The links you gave are much better than the other tutorials I was reading, which were confusing me.
Thanks again, Glenn On Friday, 29 November 2013 15:30:55 UTC, Simon King wrote: > > Ah, OK, I see what you mean. The way that you are producing the JOIN, > although it works, is probably not exactly what you wanted. With this: > > s=select([proxs.c.mac]).outerjoin(vends, proxs.c.mac == vends.c.mac) > > ...you are first creating a query that selects from the proxs table, > then treating that as a subquery and joining it against the vends > table. Instead, you probably want to join the 2 tables directly, then > select from that join: > > j = proxs.outerjoin(vends, proxs.c.mac == vends.c.mac) > s = select([proxs.c.mac]).select_from(j).where(...) > conn.execute(s).fetchall() > > See the examples at > http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#using-joins > > Hope that helps, > > Simon > > On Fri, Nov 29, 2013 at 2:46 PM, Glenn Wilkinson > <glenn.w...@gmail.com <javascript:>> wrote: > > Hi Simon, > > > > Thanks for the reply. > > > > I've been using sqlalchemy fine with other queries not using a join, > e.g: > > > > [...] > > s = select([proxs.c.mac,vends.c.vendor, vends.c.vendorLong], > and_(*filters)) > > r = db.execute(s) > > results = r.fetchall() > > [...] > > > > The problem seems to be when using the join. Trying your solution I get > the > > same error I've been having (which I realise I forgot to link to in my > first > > email) > > > > [...] > >>>> s.execute() #Same result if trying your suggestion of > >>>> db.connect().execute(s) > > ..... #Full output here: http://bpaste.net/show/LYOxb9hmVOWxy71yZcQb/ > > ..... > > sqlalchemy.exc.StatementError: Not an executable clause (original cause: > > ArgumentError: Not an executable clause) '(SELECT proxs.mac AS mac > \nFROM > > proxs) LEFT OUTER JOIN vends ON proxs.mac = vends.mac' [] > > > > [...] > > > > As mentioned if I print out the query it looks fine: > > > > [...] > >>>> print type(s) > > <class 'sqlalchemy.sql.expression.Join'> > >>>> print s > > (SELECT proximity_sessions.mac AS mac > > FROM proximity_sessions) LEFT OUTER JOIN vendors ON > proximity_sessions.mac = > > vendors.mac > > [...] > > > > But the problem is I don't see how to execute it. I'm struggling to find > > reference to "Not an executable clause" as per the error. > > > > Best, > > Glenn > > > > > > On Friday, 29 November 2013 13:55:13 UTC, Glenn Wilkinson wrote: > >> > >> Hi all, > >> > >> I'm struggling to get a join to work, as below: > >> > >> > >> [...] > >> #Table definitions > >> table = Table('vends', MetaData(), > >> Column('mac', String(64), primary_key=True), > >> Column('vendor', String(20) ), > >> Column('vendorLong', String(30) ) > >> > >> table = Table('proxs', MetaData(), > >> Column('mac', String(64), primary_key=True), > >> Column('location', String(length=60)) ) > >> > >> ....... > >> > >> dbms="sqlite:///db.sql" > >> db = create_engine(dbms) > >> metadata = MetaData(db) > >> metadata.reflect() > >> > >> proxs = metadata.tables['proxs'] > >> vends = metadata.tables['vends'] > >> > >> s=select([proxs.c.mac]).outerjoin(vends, proxs.c.mac == vends.c.mac) > >> > >> [...] > >> > >> At this point the 's' is of type "<class > >> 'sqlalchemy.sql.expression.Join'>". The query looks correct if I print > it > >> out (and in fact executes if I paste it into my DB): > >> > >> >>> print s > >> (SELECT proxs.mac AS mac > >> FROM proxs) LEFT OUTER JOIN vends ON proxs.mac = vends.mac > >> > >> But I'm unsure of how to execute the query. I see documentation > mentioning > >> applying the filter() or execute() function to the join object, but it > has > >> no such functions. My questions are then: > >> > >> 1. How can I execute this query? > >> 2. How can I apply a filter to it? e.g. filter(proxs.c.mac == > vends.c.mac) > >> > >> Best wishes, > >> Glenn > >> > >> > >> > >> > > -- > > You received this message because you are subscribed to the Google > Groups > > "sqlalchemy" group. > > To unsubscribe from this group and stop receiving emails from it, send > an > > email to sqlalchemy+...@googlegroups.com <javascript:>. > > To post to this group, send email to > > sqlal...@googlegroups.com<javascript:>. > > > Visit this group at http://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/groups/opt_out. > -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
