On Fri, Nov 29, 2013 at 1:55 PM, Glenn Wilkinson <[email protected]> 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 -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/groups/opt_out.
