Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Flask SQLAlchemy query, specify column names

People also ask

How do I get column names in SQLAlchemy?

To access the column names we can use the method keys() on the result. It returns a list of column names. Since, we queried only three columns, we can view the same columns on the output as well.

How do I select in SQLAlchemy?

The select() method of table object enables us to construct SELECT expression. The resultant variable is an equivalent of cursor in DBAPI. We can now fetch records using fetchone() method. Here, we have to note that select object can also be obtained by select() function in sqlalchemy.

How do I get column names in Python using SQL?

Approach: Connect to a database using the connect() method. Create a cursor object and use that cursor object created to execute queries in order to create a table and insert values into it. Use the description keyword of the cursor object to get the column names.

What does First () do in SQLAlchemy?

Return the first result of this Query or None if the result doesn't contain any row. first() applies a limit of one within the generated SQL, so that only one primary entity row is generated on the server side (note this may consist of multiple result rows if join-loaded collections are present).


You can use the with_entities() method to restrict which columns you'd like to return in the result. (documentation)

result = SomeModel.query.with_entities(SomeModel.col1, SomeModel.col2)

Depending on your requirements, you may also find deferreds useful. They allow you to return the full object but restrict the columns that come over the wire.


session.query().with_entities(SomeModel.col1)

is the same as

session.query(SomeModel.col1)

for alias, we can use .label()

session.query(SomeModel.col1.label('some alias name'))

You can use load_only function:

from sqlalchemy.orm import load_only

fields = ['name', 'addr', 'phone', 'url']
companies = session.query(SomeModel).options(load_only(*fields)).all()

You can use Model.query, because the Model (or usually its base class, especially in cases where declarative extension is used) is assigned Sesssion.query_property. In this case the Model.query is equivalent to Session.query(Model).

I am not aware of the way to modify the columns returned by the query (except by adding more using add_columns()).
So your best shot is to use the Session.query(Model.col1, Model.col2, ...) (as already shown by Salil).


You can use Query.values, Query.values

session.query(SomeModel).values('id', 'user')