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. Maybe it helps. Nice question +1 — above_c_level
– above_c_level, Commented Mar 28, 2020 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
– Hanan F, Commented Mar 28, 2020 at 22:58

Hanan F · Accepted Answer · 2020-03-28 23:04:56Z

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

Collectives™ on Stack Overflow

Flask sqlalchemy add column during query without tuple result

1 Answer 1

changes I made to my code

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

changes I made to my code

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related