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
    },
        ]
    }
7
  • Does this answer your question? Select NULL Values in SQLAlchemy Commented Mar 27, 2020 at 0: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. Commented Mar 27, 2020 at 0: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 Commented Mar 27, 2020 at 0:20
  • 3
    Actually, what you needed is the link I posted - but hey, whatever code works is the code you get to maintain :) Commented Mar 27, 2020 at 1:08
  • 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. Commented Mar 28, 2020 at 1:53

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.