Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

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?

share|improve this question

2 Answers

up vote 0 down vote

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.

share|improve this answer
Thanks but I prefer to use raw SQL. Is there any way to shove ' implicit_returning=True engine kw' to flask-sqlalchemy? – greggyNapalm 5 hours ago
I'm not sure how you can get it if you use raw sql, but if you do alchemy insert(), it works ok, I've edited my answer to show this. – Pawelmhm 4 hours ago
I like raw SQL and do not whant to declare my schema second time in python code. All what I whant is get PK id after insert with db.engine.connect().execute('''raw sql insert here''' – greggyNapalm 3 hours ago
up vote 0 down vote

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.