How to get insert_primary_key from db.engine.connect (). Make a call

Question

I use:

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 call insert with alchemy.

What does the engine look like:

 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 the insert request in the application:

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

But after trying access to the inserted_primary_key property, we get:

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

How to enable implicit_returning in my case, reading documents does not help?

Answer 1

You can use the RETURNING clause and handle this yourself:

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

Then you can get the identifier, as usual, extract the values ​​from the queries.

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

I do not think that in SQLAlchemy there is an alternative way to db without using the ORM functionality.

Answer 2

Is there a reason why you are executing a text query instead of the usual sqlalchemy insert ()? If you use sqlalchemy, it will be much easier for you to rephrase your query:

 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.

Answer 3

You can use lastrowid

 rv = query() rv.lastrowid