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

Question

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'

If you need the pair { service_id user_id } to be unique, then enforce just that using a unique constraint and handle the error from trying to add a second entry with the same values. Query + check will always suffer of TOCTOU and you can end up with multiple entries. — Ilja Everilä
– Ilja Everilä, Commented Dec 23, 2021 at 9:33

Ibraheem Alyan · Accepted Answer · 2021-12-23 06:22:16Z

1

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'

answered Dec 23, 2021 at 6:22

Ibraheem Alyan

5221 gold badge5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related