[FIXED] FLASK SQLAlchemy query,filter and give multiple returns

Issue

I am trying to create an API with flask-sqlalchemy that takes employee_id number and returns all info of them. Some employees are in two departments so I want it to return json of all the departments that the employee might belong. The code runs but it gives me an empty list when I enter an employee id.

@app.route('/getrate/<int:employee_id>', methods=['GET', 'POST'])
def getrate(employee_id):
    ratings=employee_ratings.filter_by(employee_id="employee_id").all()
    allRatings=[]
    for rating in ratings:
        newResponse={
            "id":rate.id,
            "employee_id":rate.employee_id,
            "name":rate.name,
            "department":str(rate.department),
            "phone":rate.phone,
            "title":str(rate.title),
            "score":rate.score,
            "rank":str(rate.rank)  
        }

        # print(type(newResponse))
        allRatings.append(newResponse)

    all = {
        "all":allRatings
    }

    print(type(all))

    return all

Solution

You give SQLAlchemy a string not the real employee_id. Therefore you have to change ratings to this:

ratings = credit_scores.query.filter_by(employee_id=employee_id).all()

The second problem I see is, that you are referencing a variable, that is not assigned, which is rate. You have to change rate to rating.

for rating in ratings:
    newResponse = {
        "id":rating.id,
        "employee_id":rating.employee_id,
        "name":rating.name,
        "department":str(rating.department),
        "phone":rating.phone,
        "title":str(rating.title),
        "score":rating.score,
        "rank":str(rating.rank)  
    }

    # print(type(newResponse))
    allRatings.append(newResponse)

Actually Im not sure, if your DBMS has stored department as a data structured list. If this is the case than change str(rating.department) to list(rating.department), which is still valid with the JSON format.

Answered By - lambdaxpy