sqlalchemy PK from raw SQL insert

Question

I'm trying to get new entrie PK id from insert with raw SQL query with SQLalchemy like this https://gist.github.com/greggyNapalm/6045595

>>> sys.version
'2.7.3 (default, Aug  1 2012, 05:14:39) \n[GCC 4.6.3]'
>>> sqlalchemy.__version__
'0.8.1'
>>> from sqlalchemy import create_engine
>>> engine = create_engine('postgresql://usr:psswd/db', convert_unicode=True, implicit_returning=True)
>>> conn = engine.connect()
>>>
>>> rv = conn.execute('select * from engine')
>>> [el for el  in rv]
[(1, u'phantom', u'Yandex cretaed IO engine.'), (2, u'jmeter', u'apache foundation load testing tool.')]
>>>
>>> rv = conn.execute('''insert into "engine" (name, description) values ('someth-new', 'New secr')''')
>>> rv.inserted_primary_key
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/home/gkomissarov/.virtualenvs/lunaport_server/local/lib/python2.7/site-packages/sqlalchemy/util/langhelpers.py", line 614, in __get__
    obj.__dict__[self.__name__] = result = self.fget(obj)
  File "/home/gkomissarov/.virtualenvs/lunaport_server/local/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 566, in inserted_primary_key
    "Statement is not a compiled "
InvalidRequestError: Statement is not a compiled expression construct.
>>>
>>> [el for el  in rv]
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/home/gkomissarov/.virtualenvs/lunaport_server/local/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 531, in __iter__
    row = self.fetchone()
  File "/home/gkomissarov/.virtualenvs/lunaport_server/local/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 790, in fetchone
    self.cursor, self.context)
  File "/home/gkomissarov/.virtualenvs/lunaport_server/local/lib/python2.7/site-packages/sqlalchemy/engine/base.py", line 1027, in _handle_dbapi_exception
    util.reraise(*exc_info)
  File "/home/gkomissarov/.virtualenvs/lunaport_server/local/lib/python2.7/site-packages/sqlalchemy/engine/result.py", line 720, in _non_resultmpl
    "This result object does not return rows. "
ResourceClosedError: This result object does not return rows. It has been closed automatically.
>>> 
>>> [el for el  in conn.execute('select * from engine')]
[(1, u'phantom', u'Yandex cretaed IO engine.'), (2, u'jmeter', u'apache foundation load testing tool.'), (3, u'someth-new', u'New secr')]

without luck, what I'm doing wrong? How to get that?

Answer 1

rv = conn.execute('''
    insert into "engine" (name, description) 
    values ('someth-new', 'New secr')
    returning *
''')
[el for el  in rv]