Selecting columns from multiple table join in SQLAlchemy Core

[apoorv.x.k...@barclays.com]

Hi All,

 

I am joining 3 tables in SQLAlchemy Core and selecting all columns as follows:

 

    rows = self.db.execute(self.execs.join(

                             self.orders.join(self.instruments)

                         ).select(whereClause)).reduce_columns())

 

It works well but if I want to select a subset of columns:

 

    reqdCols = [order.c.id, exec.c.last_modified, instruments.type]

    rows = self.db.execute(self.execs.join(

                             self.orders.join(self.instruments)

                          ).select(reqdCols, whereClause)).reduce_columns())

It doesn't works and gives following error:

 

    Traceback (most recent call last):

      File "<stdin>", line 1, in <module>

      File "/apps/qtdist/pkgs/pub/cpython/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 807, in select

        return Select(collist, whereclause, from_obj=[self], **kwargs)

      File "/apps/qtdist/pkgs/pub/cpython/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/selectable.py", line 2219, in __init__

        whereclause).self_group(against=operators._asbool)

      File "/apps/qtdist/pkgs/pub/cpython/python-2.7.6/lib/python2.7/site-packages/sqlalchemy/sql/elements.py", line 3438, in _literal_as_text

        "SQL expression object or string expected."

    sqlalchemy.exc.ArgumentError: SQL expression object or string expected.

 

Alternative would be to use select instead of Join.select and make it implicit join with where clauses:

 

    joinConditions = (orders.c.colx == execs.colx) & (execs.c.coly == instruments.c.coly)

    select(reqdCols).where(and_(whereClause, joinConditions)

 

But I would prefer explicit join over implicit for performance reasons. Is there any way to select subset of columns using explicit joins?

 

Regards,

Apoorv

 

PS: I have post the same in Stack Overflow Question: http://stackoverflow.com/questions/25656935/selecting-columns-from-mulitple-table-join-in-sqlalchemy-core#

_______________________________________________

This message is for information purposes only, it is not a recommendation, advice, offer or solicitation to buy or sell a product or service nor an official confirmation of any transaction. It is directed at persons who are professionals and is not intended for retail customer use. Intended for recipient only. This message is subject to the terms at: www.barclays.com/emaildisclaimer.

For important disclosures, please see: www.barclays.com/salesandtradingdisclaimer regarding market commentary from Barclays Sales and/or Trading, who are active market participants; and in respect of Barclays Research, including disclosures relating to specific issuers, please see http://publicresearch.barclays.com.

_______________________________________________

[Jonathan Vanasco]

the first thing I noticed, is that you're referencing the "idea" of the tables in two separate ways : `self.execs` and `execs`

are they the same python object?

[Michael Bayer]

I think the join.select(columns, whereclasue) part here is wrong, the select() method unfortunately does not have that calling signature (I wish it did, but there’s a lot of legacy there).

You can get the columns you want more explicitly, select([c1, c2, c3, ..]).select_from(my_join).

The exception raised here is probably sqlalchemy being surprised by the list of columns being sent (the error message would do better to report on what it sees).

-- 
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 tosqlalchemy+...@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/d/optout.