0

I have a flask API that consumes SQL queries using SQL Alchemy

Sometimes I have objects in my array that contains null values, I would like to remove them but havent been succesful in my attempts..

class SliceData(Resource):
  def get(self, axis_choice, legend_choice, year_choice):
    connection = db_connect.connect()
    query = select([
                    getattr(workers.c, axis_choice),
                    getattr(workers.c, legend_choice),
                    func.count(workers.c.id).label('count')
                    ])
    query = query.where(workers.c.year == year_choice)
    query = query.group_by( getattr(workers.c, legend_choice), getattr(workers.c, axis_choice))
    result = connection.execute(query)
    return jsonify({'data': [dict(row) for row in result]})

The code above outputs JSON data that looks like this

{
    "data": [
        {
            "age": 46,
            "count": 33,
            "years_training": 2
        },
        {
            "age": 32,
            "count": 67,
            "years_training": 0
        },
        {
            "age": 51,
            "count": 1262,
            "years_training": null
        },
    ]
}

I have tried getting rid of null values using SQLAlchemy with query = query.where(workers.c.years_training !=None)

but that ends up changing the query

So I would like to remove the objects that have null values in my return jsonify so that my output looks like this

{
        "data": [
    {
        "age": 46,
        "count": 33,
        "years_training": 2
    },
    {
        "age": 32,
        "count": 67,
        "years_training": 0
    },
        ]
    }
invrt
  • 689
  • 6
  • 24
  • Does this answer your question? [Select NULL Values in SQLAlchemy](https://stackoverflow.com/questions/5602918/select-null-values-in-sqlalchemy) – Grismar Mar 27 '20 at 00:14
  • You can do `x = { "data": [d for d in x["data"] if d["years_training"] is not None]}` but the question I linked as a duplicate is a better approach. – Grismar Mar 27 '20 at 00:16
  • 1
    @Grismar `return jsonify({'data': [dict(row) for row in result if all(v is not None for v in row.values()]})` is a bit more generic – Jon Clements Mar 27 '20 at 00:20
  • 3
    Actually, what you needed is the link I posted - but hey, whatever code works is the code you get to maintain :) – Grismar Mar 27 '20 at 01:08
  • @Grismar Lol, that sounded a bit condescending – invrt Mar 27 '20 at 03:09
  • 1
    Was hoping the smiley would take the edge off it - fair comment though, but it's something I see a bit too often in everyday work: people going with the solution that works and is easy to implement, but comes back to haunt them in their future. Figured a bit of a nudge was in order - nothing personal. – Grismar Mar 28 '20 at 01:53
  • No worries my man, and I definitely used the easier the solution. Thanks for looking out though – invrt Mar 29 '20 at 19:25

0 Answers0