Flask sqlalchemy add column during query without tuple result

Question

I am working on a flask project and I am stuck on an issue where I am trying to add a case statement to a query as a column.

I have tried lots of potential solutions and the closest I have found so far is add_columns() the problem is that this makes the query return a tuple with the object of the model I queried and result from the case statement, what I want is just the model object that has the result of the case in it.

here is some simplified code that shows my issue

from flask import Flask
from sqlalchemy.sql.expression import case
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
app.config["SQLALCHEMY_TRACK_MODIFICATIONS"] = False
app.config["SQLALCHEMY_DATABASE_URI"] = "sqlite:///database.db"
db = SQLAlchemy(app)


class Image(db.Model):
    __tablename__ = 'images'

    id = db.Column(db.Integer, primary_key=True)
    url = db.Column(db.Text)
    model_id = db.Column(db.Integer)


db.create_all()


@app.route('/')
def hello_world():
    image_ids = [1, 2, 3, 4, 5]
    image_weights = [0.1, 0.4, 0.23232, 0.21, 0.001]
    print(list(zip(image_ids, image_weights)))

    case_statement = case(
        [(uid == Image.id, weight) for uid, weight in zip(image_ids, image_weights)]
    )
    results = db.session.query(Image).add_columns(case_statement.label("weight")).filter(
        Image.id.in_(image_ids)).order_by("weight")
    print(results)
    print(results.all())
    return 'Hello World!'


if __name__ == '__main__':
    app.run(debug=True)

the output of print(results.statement.compile(compile_kwargs={"literal_binds": True})) is

SELECT images.id,
       images.url,
       images.model_id,
       CASE WHEN (images.id = 1) THEN 0.1 WHEN (images.id = 2) THEN 0.4 WHEN (images.id = 3) THEN 0.23232 WHEN (images.id = 4) THEN 0.21 WHEN (images.id = 5) THEN 0.001 END AS weight
  FROM images
 WHERE images.id IN (1, 2, 3, 4, 5) 
 ORDER BY weight;

the output of print(results.all())

[(<Image 5>, 0.001), (<Image 1>, 0.1), (<Image 4>, 0.21), (<Image 3>, 0.23232), (<Image 2>, 0.4)]

the result I am looking for is

[<Image 5>, <Image 1>, <Image 4>, <Image 3>, <Image 2>]

One easy solution to this is to just go over each item in the list of results and use setattr to set a attribute weight of the image object to the second item in the tuple but I can't do this as I want to make a function that returns a query object with the filters applied not a list of objects

add_columns will always add a new column. Check out [this SO post](https://stackoverflow.com/questions/23381644/how-to-order-data-in-sqlalchemy-by-list). Maybe it helps. Nice question +1 — above_c_level, Mar 28 '20 at 10:17
@Sikan thanks for the help. I had a look at that post and even though it did not give me an answer directly it did still help, Thanks — Hanan F, Mar 28 '20 at 22:58

Hanan F · Accepted Answer · 2020-03-28T23:04:56.533

After searching through the sqlalchemy documentation I found Query-time SQL expressions as mapped attributes

This dose exactly what I want It lets me set a mapped attribute weight that is the result of a case statement.

changes I made to my code

class Image(db.Model):
    __tablename__ = 'images'

    id = db.Column(db.Integer, primary_key=True)
    url = db.Column(db.Text)
    model_id = db.Column(db.Integer)

    weight = db.query_expression()

@app.route('/')
def hello_world():
    image_ids = [1, 2, 3, 4, 5]
    image_weights = [0.1, 0.4, 0.23232, 0.21, 0.001]

    case_statement = case([(uid == Image.id, weight) for uid, weight in zip(image_ids, image_weights)]).label("weight")
    results = db.session.query(Image).filter(Image.id.in_(image_ids)).options(
        db.with_expression(Image.weight, case_statement)).order_by(case_statement)
    print(results.statement.compile(compile_kwargs={"literal_binds": True}))
    for result in results:
        print(result, result.weight)
    return 'Hello World!'

results.all() now returns

[<Image 5>, <Image 1>, <Image 4>, <Image 3>, <Image 2>]

and each item in this list now as an attribute called weight

If there is a better way of doing this please let me know thanks

Flask sqlalchemy add column during query without tuple result

1 Answers1

changes I made to my code

Linked