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.wilkin...@gmail.com> 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+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.
--
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.
