Struggling with a join

[Glenn Wilkinson]

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

[Simon King]

On Fri, Nov 29, 2013 at 1:55 PM, Glenn Wilkinson
<glenn.w...@gmail.com> 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)) )
>

Aside: those calls to MetaData() look like a mistake to me - you would
normally create a single MetaData instance and pass it to each of the
tables.

> .......
>
> 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)
>

You might want to work your way through the tutorial at
http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html. There are
actually various possible answers to your question, but the simplest
is probably:

conn = db.connect()
result = conn.execute(s)
for row in result:
print row

Applying filter conditions to a Select object is described at
http://docs.sqlalchemy.org/en/rel_0_9/core/tutorial.html#selecting.

Hope that helps,

Simon

[Glenn Wilkinson]

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

[Simon King]

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

> --
> 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.
> To post to this group, send email to sqlal...@googlegroups.com.
> Visit this group at http://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/groups/opt_out.

[Glenn Wilkinson]

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