Temporarily disable increment in SQLAlchemy

Question

I am running a Flask application with SQLAlchemy (1.1.0b3) and Postgres.

With Flask I provide an API over which the client is able to GET all instances of a type database and POST them again on a clean version of the Flask application, as a way of local backup. When the client posts them again, they should again have the same ID as they had when he downloaded them.

I don't want to disable the "increment" option for primary keys for normal operation but if the client provides an ID with a POST and wishes to give a new resource said ID I would like to set it accordingly without breaking the SQLAlchemy. How can I access/reset the current maximum value of ids?

@app.route('/objects', methods = ['POST'])
def post_object():

    if 'id' in request.json and MyObject.query.get(request.json['id']) is None: #1
        object = MyObject()
        object.id = request.json['id']
    else: #2
        object = MyObject()

    object.fillFromJson(request.json)
    db.session.add(object)
    db.session.commit()
    return jsonify(object.toDict()),201

When adding a bunch of object WITH an id #1 and then trying to add on WITHOUT an id or with a used id #2, I get.

duplicate key value violates unique constraint "object_pkey"
DETAIL:  Key (id)=(2) already exists.

Usually, the id is generated incrementally but when that id is already used, there is no check for that. How can I get between the auto-increment and the INSERT?

Answer 1

After adding an object with a fixed ID, you have to make sure the normal incremental behavior doesn't cause any collisions with future insertions.

A possible solution I can think of is to set the next insertion ID to the maximum ID (+1) found in the table. You can do that with the following additions to your code:

@app.route('/objects', methods = ['POST'])
def post_object():
    fixed_id = False
    if 'id' in request.json and MyObject.query.get(request.json['id']) is None: #1
        object = MyObject()
        object.id = request.json['id']
        fixed_id = True
    else: #2
        object = MyObject()

    object.fillFromJson(request.json)
    db.session.add(object)
    db.session.commit()

    if fixed_id:
        table_name = MyObject.__table__.name
        db.engine.execute("SELECT pg_catalog.setval(pg_get_serial_sequence('%s', 'id'), MAX(id)) FROM %s;" % (table_name, table_name))

    return jsonify(object.toDict()),201

The next object (without a fixed id) inserted into the table will continue the id increment from the biggest id found in the table.