Temporarily disable increment in SQLAlchemy
<p>I am running a Flask application with SQLAlchemy (1.1.0b3) and Postgres.</p>

<p>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.</p>

<p>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?</p>

<pre><code>@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
</code></pre>

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

<pre><code>duplicate key value violates unique constraint "object_pkey"
DETAIL:  Key (id)=(2) already exists.
</code></pre>

<p>Usually, the id is generated <a href="http://docs.sqlalchemy.org/en/rel_0_9/core/metadata.html#sqlalchemy.schema.Column.params.autoincrement" rel="nofollow">incrementally</a> but when that id is already used, there is no check for that. How can I get between the auto-increment and the INSERT?</p>

<p>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.</p>

<p>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:</p>

<pre><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
</code></pre>

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

python rest flask sqlalchemy auto-increment