how flask-sqlalchemy turn off returning id while insert data

I’m trying to insert data to PostgreSQL 8.3.23 by Flask-SQLAlchemy==2.3.2, the errors show that INSERT statement is not supported in this version of Greenplum Database. so how can I turn off returning id in (flask-)sqlalchemy while insert.

class BU(db.Model):
    __tablename__ = 'bu'
    id = db.Column(db.Integer, primary_key=True, autoincrement=True)
    bu_code = db.Column(db.String(125), index=True, comment=u'some')
    bu = db.Column(db.String(125), nullable=False, comment=u'some')
    is_game = db.Column(db.Boolean, default=False, comment=u'some')
    status = db.Column(db.Integer, default=0, info={'table_enums': [{'value': 0, 'text': u"some"}, {'value': 1, 'text': u"some"}]})
    add_time = db.Column(db.DateTime, default=datetime.now, comment=u'some')

ipdb> res = BU(bu_code='333', bu='hello')
ipdb> db.session.add(res)
ipdb> db.session.flush()
*** NotSupportedError: (psycopg2.NotSupportedError) The RETURNING clause of the INSERT statement is not supported in this version of Greenplum Database.
 [SQL: 'INSERT INTO bu (bu_code, bu, is_game, status, add_time) VALUES (%(bu_code)s, %(bu)s, %(is_game)s, %(status)s, %(add_time)s) RETURNING bu.id'] [parameters: {'status': 0, 'bu': 'hello', 'is_game': False, 'bu_code': '333', 'add_time': datetime.datetime(2019, 3, 24, 16, 53, 49, 613000)}] (Background on t
his error at: http://sqlalche.me/e/tw8g)

Thanks in advance

Answer

You need to set the implicit_returning create_engine parameter to False.

engine = create_engine(url, implicit_returning=False)

When True, a RETURNING- compatible construct, if available, will be used to fetch newly generated primary key values when a single row INSERT statement is emitted with no existing returning() clause. This applies to those backends which support RETURNING or a compatible construct, including PostgreSQL, Firebird, Oracle, Microsoft SQL Server. Set this to False to disable the automatic usage of RETURNING.

Since version 2.4 Flask-SQLAlchemy provides a SQLALCHEMY_ENGINE_OPTIONS configuration option to pass a dictionary of keyword arguments for engine creation, so you can do

app.config['SQLALCHEMY_ENGINE_OPTIONS'] = {'implicit_returning': False}

The following section applies to Flask-SQLAlchemy < 2.4, which had not been released when this answer was originally composed

Flask-SQLAlchemy doesn’t expose engine creation directly, it might be possible to override SQLAlchemy.apply_driver_hacks to inject the extra parameter.

This method is called before engine creation and used to inject driver specific hacks into the options. The options parameter is a dictionary of keyword arguments that will then be used to call the sqlalchemy.create_engine() function.

There is an outstanding pull-request to expose a simpler way to do this.