Select specific columns from table in SQLAlchemy

Question

I'm trying to select specific columns from a table like this:

users = Table('users', metadata, autoload=True)
s = users.select([users.c.email])
results = s.execute()
print results

and I'm getting this error:

> Traceback (most recent call last):   File "my_mailer.py", line 35, in
> <module>
>     s = users.select([users.c.email])   File "/task/__pips__/sqlalchemy/sql/selectable.py", line 175, in select
>     return Select([self], whereclause, **params)   File "/task/__pips__/sqlalchemy/sql/selectable.py", line 2082, in __init__
>     self._whereclause = _literal_as_text(whereclause)   File "/task/__pips__/sqlalchemy/sql/elements.py", line 2745, in
> _literal_as_text
>     "SQL expression object or string expected." sqlalchemy.exc.ArgumentError: SQL expression object or string
> expected.

So I tried this:

users = Table('users', metadata, autoload=True)
s = users.select('email')
results = s.execute()
print results

And got this in response:

> Traceback (most recent call last):   File "my_mailer.py", line 36, in
> <module>
>     results = s.execute()   File "/task/__pips__/sqlalchemy/sql/base.py", line 124, in execute
>     return e._execute_clauseelement(self, multiparams, params)   File "/task/__pips__/sqlalchemy/engine/base.py", line 1605, in
> _execute_clauseelement
>     return connection._execute_clauseelement(elem, multiparams, params)   File "/task/__pips__/sqlalchemy/engine/base.py", line 761,
> in _execute_clauseelement
>     compiled_sql, distilled_params   File "/task/__pips__/sqlalchemy/engine/base.py", line 874, in
> _execute_context
>     context)   File "/task/__pips__/sqlalchemy/engine/base.py", line 1023, in _handle_dbapi_exception
>     exc_info   File "/task/__pips__/sqlalchemy/util/compat.py", line 185, in raise_from_cause
>     reraise(type(exception), exception, tb=exc_tb)   File "/task/__pips__/sqlalchemy/engine/base.py", line 867, in
> _execute_context
>     context)   File "/task/__pips__/sqlalchemy/engine/default.py", line 388, in do_execute
>     cursor.execute(statement, parameters) sqlalchemy.exc.ProgrammingError: (ProgrammingError) argument of WHERE
> must be type boolean, not type character varying LINE 3: WHERE email

Sure enough, the first argument here is the 'whereclause', not 'columns' like everywhere else, this is reflected in the documentation:

This argument is not present on the form of select() available on Table.

Question: how can I select only specific columns using select on the table? And generally, why on earth the columns argument is not available on select on the table? I can't understand why somebody made the decision to make this different than the standard select.

van · Accepted Answer · 2014-01-30 15:11:45Z

21

Use general purpose select instead of Table.select:

stmt = select([users.c.email])
result = conn.execute(stmt)

answered Jan 30, 2014 at 15:11

van

77.6k13 gold badges174 silver badges179 bronze badges

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

2 Comments

lawicko Over a year ago

I know that I can do this, but do you know if there is any way of doing the same with the table select flavour? Not being able to do the same with the table select renders it defective in my opinion.

van Over a year ago

Read the second link of the documentation: it is explicit about it: no, you cannot do it. In a way table.select is just a syntactic sugar.

Dario Roman Garcia Gonzalez · Accepted Answer · 2017-08-04 18:44:53Z

16

With metadata you can do this:

metadata = MetaData(bind=engine)
tblusers = metadata.tables['users']
tblproducts = metadata.tables['products']
# example 1: only columns you want.
data_users = tblusers.select().with_only_columns([tblusers.c.id, tblusers.c.name]).execute()
# example 2: w/ where & order_by
data_products = tblproducts.select().with_only_columns([tblproducts.c.id, tblproducts.c.price, tblproductos.c.description]).where(tblproducts.c.stock > 0).order_by(tblproducts.c.brand).execute()

Be well...

SqlAlchemy Docs

edited Aug 4, 2017 at 18:44

answered Aug 4, 2017 at 17:28

Dario Roman Garcia Gonzalez

60814 silver badges17 bronze badges

Collectives™ on Stack Overflow

Select specific columns from table in SQLAlchemy

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related