How to get inserted_primary_key from db.engine.connect().execute call

Question

I'm using:

CPython 2.7.3,
Flask==0.10.1
Flask-SQLAlchemy==0.16
psycopg2==2.5.1
and
postgresql-9.2

Trying to get PK from insert call with alchemy.

Getting engine like so:

app = Flask(__name__)
app.config.from_envvar('SOME_VAR')
app.wsgi_app = ProxyFix(app.wsgi_app)  # Fix for old proxyes

db = SQLAlchemy(app)

And executing insert query in app:

    from sqlalchemy import text, exc
    def query():
        return db.engine.connect().execute(text('''
        insert into test...'''), kw)
    rv = query()

But trying access inserted_primary_key property, get:

InvalidRequestError: Statement is not an insert() expression construct.

How to enable implicit_returning in my case, reading the docs doesn't help?

Answer 1

You can use the RETURNING clause and handle this yourself:

INSERT INTO test (...) VALUES (...) RETURNING id

Then you can retrieve the id as you normally retrieve values from queries.

Note that this works on Postgres, but does not work on other db engines like MySQL or sqlite.

I don't think there is a db agnostic way to do this within SQLAlchemy without using the ORM functionality.

Answer 2

Is there any reason you do text query instead of normal sqlalchemy insert()? If you're using sqlalchemy it will probably be much easier for you to rephrase your query into:

from sqlalchemy import text, exc, insert

# in values you can put dictionary of keyvalue pairs
# key is the name of the column, value the value to insert
con = db.engine.connect()
ins = tablename.insert().values(users="frank")
res = con.execute(ins)
res.inserted_primary_key
[1] 

This way sqlalchemy will do the binding for you.