CODEWITHSUNDEEP
pythonflasksqlalchemy
Ben Shekhtman
Ben Shekhtman

Reputation: 615

Checking if values in row in table already exists before adding in Flask, SQLAlchemy

I have a UserService join table which looks as so:

[
    {
        "id": 1,
        "service_id": 1,
        "user_id": 1
    },
    {
        "id": 2,
        "service_id": 2,
        "user_id": 1
    },
    {
        "id": 3,
        "service_id": 2,
        "user_id": 2
    },
    {
        "id": 4,
        "service_id": 3,
        "user_id": 1
    },
    {
        "id": 5,
        "service_id": 3,
        "user_id": 2
    }
]

When creating a new UserService I don't want the user to be able to add an entry if the values already exist. My current if statement is currently returning the else statement. What am i doing wrong here?

@bp.route('/user/<userid>/add-service/<serviceid>', methods=['POST'])
def add_service_to_user(userid, serviceid):

    user = db_session.query(User).filter(User.id == userid).first()
    service = db_session.query(Service).filter(Service.id == serviceid).first()

    all_user_services = db_session.query(UserService).all()

    for service in all_user_services:
        if serviceid == service.service_id and userid == service.user_id:
            return jsonify({'Message': f'User with id: {userid} already has Service with id: {serviceid}'})
    else:
        return 'hello'

Upvotes: 0

Views: 996

Answers (1)

Ibraheem Alyan
Ibraheem Alyan

Reputation: 522

you can do that on the database side, simply use the and operator in the .filter()

.query(Service).filter(Service.id == serviceid && Service.user_id == userid).first()

also you may want to define types for the serviceid and userid parameters since they are integers. the code may look like

@bp.route('/user/<int:userid>/add-service/<int:serviceid>', methods=['POST'])
def add_service_to_user(userid, serviceid):

    service = db_session.query(Service).filter(Service.id == serviceid && Service.user_id == userid).first()

    if service is not None:
        return jsonify({'Message': f'User with id: {userid} already has Service with id: {serviceid}'})
    else:
        return 'hello'

Upvotes: 1

Related Questions