How to dynamically select columns to query in SQLAlchemy?

Question

I want to select specific values from a table, by looking at the code from SQLAlchemy session.py

def query(self, *entities, **kwargs):
    """Return a new ``Query`` object corresponding to this ``Session``."""

    return self._query_cls(entities, self, **kwargs)

it does seem that as a function parameter, it accepts a tuple. So what I did was:

query = ('order', 'location')
columnsStr = 'order, location'
table = 'locations'
sql = "SELECT {0} FROM {1}".format(columnsStr, table)
data = session.query(query).from_statement(sql).all()

And it produces this error - InvalidRequestError: SQL expression, column, or mapped entity expected - got '('order', 'location')'

Why doesn't this work with a tuple?

P.S.

If I change the values of these:

query = 'location'
columnsStr = 'location'

I do get a result, but only for that single column.

John · Accepted Answer · 2013-03-22 17:49:03Z

3

Try

data = session.query('order', 'location').from_statement(sql).all()

alternately if you want to keep your tuple structure you can do something like this

data = session.query(*query).from_statement(sql).all()

the reason is when you pass in a tuple python then puts that tuple in a tuple

>>> x = (1,2,3)
>>> def f(*x):
    print x
>>> f(x)
((1, 2, 3),)
>>> f("location", "order")
('location', 'order')
>>>

answered Mar 22, 2013 at 17:49

John

13.8k7 gold badges53 silver badges104 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Morgan Wilde Over a year ago

That is absolutely right, thanks! I did finally catch that you do need to unpack your arguments tuple using *argumentsInTuple syntax from docs.python.org/2/tutorial/… but thanks a bunch for the answer!

Collectives™ on Stack Overflow

How to dynamically select columns to query in SQLAlchemy?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related