Problems with Sqlachemy. Can't understand the difference

I have the following code

def update(project_id, code, description):

    if project_id is None:
        raise exception

    with session_handler() as session:
        project = session.query(Project).filter_by(project_id=project_id).first()
        project_upd = session.query(Project).filter_by(project_id=project_id)

    if project is None:
        raise ProjectDoesntExist(f"Project {project_id} does not exist.")
    data = _build_update_data(code, description)

    if  not data:
        raise ValueError("No code or description provided")

    project_upd.update(data)

So if I replace project_upd.update(data) with project.update(data) it gives the following error

Attribute Error: Project object has no attribute update.

How I can use only one variable?

1 answer

  • answered 2018-11-08 11:26 Ilja Everilä

    Though the Project model is omitted, it is clear that it has no method update() – which is the norm. The difference is that project is bound to a Project object, and project_upd to a Query object. In other words the former represents a single row in the table mapped to an object, while the latter represents a query against that table.

    Your options are:

    1. Issue an UPDATE statement only and check if it matched any rows, raise if not.
    2. Fetch the row / object for update, check if it existed and raise if not, do the update.

    I assume that session_handler() commits, if no exception was raised. If that is not the case, add an explicit session.commit() as necessary.

    1. Single UPDATE statement

    def update(project_id, code, description):
        if project_id is None:
            raise exception
    
        data = _build_update_data(code, description)
    
        if not data:
            raise ValueError("No code or description provided")
    
        with session_handler() as session:
            row_count = session.query(Project).\
                filter_by(project_id=project_id).\
                update(data)
    
            if not row_count:
                raise ProjectDoesntExist(f"Project {project_id} does not exist.")
    

    2. Fetch and update

    def update(project_id, code, description):
        if project_id is None:
            raise exception
    
        data = _build_update_data(code, description)
    
        if not data:
            raise ValueError("No code or description provided")
    
        with session_handler() as session:
            # Fetch FOR UPDATE so that no concurrent updates may proceed in between
            # getting the `Project` instance and actually updating it.
            project = session.query(Project).\
                filter_by(project_id=project_id).\
                with_for_update().\
                first()
    
            if project is None:
                raise ProjectDoesntExist(f"Project {project_id} does not exist.")
    
            for attr, val in data.items():
                setattr(project, attr, val)
    

    Locking with FOR UPDATE might be a bit unnecessary in this case, since the new values don't seem to depend on previous state of the object. Still, it is something to keep in mind.