Fetching last insert id from MySQL.

[phasma]

I've got database with auto increment column called `id` and INSERT
query, whom I need to execute without model declaration in project.
meta.Session.execute() returns ResultProxy, but last_inserted_ids()
doesn't work with execute() and "SELECT LAST_INSERT_ID()" statement
sometimes return 0. Is there any other way to fetch last inserted id ?

[Michael Bayer]

"SELECT LAST_INSERT_ID()" is ultimately where the value comes from - the raw .lastrowid is present on the ResultProxy for those DBAPIs which support it, so try using that. Perhaps you're getting "0" because the transaction is going away, in which case .lastrowid should solve that issue.

> --
> You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
> To post to this group, send email to sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>

[phasma]

Lastrowid return: "Could not locate column in row for column
'lastrowid'". I try to use transaction:

trans = meta.Session.begin()
try:
meta.Session.execute("INSERT statement")
result = meta.Session.execute("SELECT LAST_INSERT_ID()")
trans.commit()
except:
trans.rollback()
raise

Now, I'm testing this, think it helps to stop loosing session between
INSERT and SELECT.

[Michael Bayer]

no its not a column on a row, its on the ResultProxy:

result = session.execute('...')

id = result.lastrowid

http://www.sqlalchemy.org/docs/core/connections.html?highlight=resultproxy#sqlalchemy.engine.base.ResultProxy.lastrowid

[phasma]

session imported from Meta ? If use Meta.Session.execute it's returns
RowProxy, which has no lastrowid parameter.

On 16 сен, 02:59, Michael Bayer <mike...@zzzcomputing.com> wrote:
> no its not a column on a row, its on the ResultProxy:
>
> result = session.execute('...')
>
> id = result.lastrowid
>

> http://www.sqlalchemy.org/docs/core/connections.html?highlight=result...

>
> On Sep 15, 2010, at 5:51 PM, phasma wrote:
>
>
>
> > Lastrowid return: "Could not locate column in row for column
> > 'lastrowid'". I try to use transaction:
>
> > trans = meta.Session.begin()
> > try:
> >    meta.Session.execute("INSERT statement")
> >    result = meta.Session.execute("SELECT LAST_INSERT_ID()")
> >    trans.commit()
> > except:
> >    trans.rollback()
> >    raise
>
> > Now, I'm testing this, think it helps to stop loosing session between
> > INSERT and SELECT.
>

[Chris Withers]

On 16/09/2010 11:49, phasma wrote:
> session imported from Meta ? If use Meta.Session.execute it's returns
> RowProxy, which has no lastrowid parameter.

Try this:

with meta.Session:
result = meta.Session.execute("INSERT statement")
print result.lastrowid

cheers,

Chris

--
Simplistix - Content Management, Batch Processing & Python Consulting
- http://www.simplistix.co.uk

[Michael Bayer]

On Sep 16, 2010, at 6:49 AM, phasma wrote:

> session imported from Meta ? If use Meta.Session.execute it's returns
> RowProxy, which has no lastrowid parameter.

execute() does not return a RowProxy. All execute() methods return a ResultProxy which consists of metadata about a result as well as an interator interface that produces RowProxy instances.

[phasma]

I've found a solution. meta.Session.execute returns RowProxy instead
of ResultProxy.

Example:

query = meta.engine.text("INSERT [...] VALUES(:text, :text1)")
result = query.execute(text="123", text1="123")

print result.lastrowid

[Chris Withers]

On 16/09/2010 14:32, Michael Bayer wrote:
>
>> session imported from Meta ? If use Meta.Session.execute it's returns
>> RowProxy, which has no lastrowid parameter.
>
> execute() does not return a RowProxy. All execute() methods return a ResultProxy which consists of metadata about a result as well as an interator interface that produces RowProxy instances.

Right, but I wonder if this mirrors what the OP reports:

>>> from sqlalchemy import create_engine,MetaData,Table,Column,Integer
>>> from sqlalchemy.orm import sessionmaker
>>> Session = sessionmaker(bind=create_engine('mysql://...',echo=True))
>>> session = Session()
>>> meta = MetaData(session.bind,reflect=True)
<snip>
>>> table = Table('testx',meta,
... Column('id',Integer(),primary_key=True),
... Column('data',Integer()))
>>> table.create()
2010-09-16 15:42:21,408 INFO sqlalchemy.engine.base.Engine.0x...e610
CREATE TABLE testx (
id INTEGER NOT NULL AUTO_INCREMENT,
data INTEGER,
PRIMARY KEY (id)
)
>>> result = session.execute('INSERT testx set data=13')
2010-09-16 15:42:39,857 INFO sqlalchemy.engine.base.Engine.0x...e610 BEGIN
2010-09-16 15:42:39,858 INFO sqlalchemy.engine.base.Engine.0x...e610
INSERT testx set data=13
2010-09-16 15:42:39,859 INFO sqlalchemy.engine.base.Engine.0x...e610 ()
>>> result.lastrowid
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "/SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/engine/base.py", line
2310, in lastrowid
return self.cursor.lastrowid
AttributeError: 'NoneType' object has no attribute 'lastrowid'

>>> type(result)
<class 'sqlalchemy.engine.base.ResultProxy'>

>>> session.commit()
2010-09-16 15:43:06,034 INFO sqlalchemy.engine.base.Engine.0x...e610 COMMIT
>>> result.lastrowid
Traceback (most recent call last):
File "<console>", line 1, in <module>
File "SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/engine/base.py", line
2310, in lastrowid
return self.cursor.lastrowid
AttributeError: 'NoneType' object has no attribute 'lastrowid'

I would have thought MySQL would support lastrowid?

Chris

[Michael Bayer]

On Sep 16, 2010, at 10:47 AM, Chris Withers wrote:

> On 16/09/2010 14:32, Michael Bayer wrote:
>>
>>> session imported from Meta ? If use Meta.Session.execute it's returns
>>> RowProxy, which has no lastrowid parameter.
>>
>> execute() does not return a RowProxy. All execute() methods return a ResultProxy which consists of metadata about a result as well as an interator interface that produces RowProxy instances.
>
> Right, but I wonder if this mirrors what the OP reports:
>

> return self.cursor.lastrowid
> AttributeError: 'NoneType' object has no attribute 'lastrowid'

thats a bug, a regression introduced in 0.6.4. will fix.

execute() still doenst return a RowProxy tho

>
> >>> type(result)
> <class 'sqlalchemy.engine.base.ResultProxy'>
>
> >>> session.commit()
> 2010-09-16 15:43:06,034 INFO sqlalchemy.engine.base.Engine.0x...e610 COMMIT
> >>> result.lastrowid
> Traceback (most recent call last):
> File "<console>", line 1, in <module>
> File "SQLAlchemy-0.6.4-py2.6.egg/sqlalchemy/engine/base.py", line 2310, in lastrowid
> return self.cursor.lastrowid
> AttributeError: 'NoneType' object has no attribute 'lastrowid'
>
> I would have thought MySQL would support lastrowid?
>
> Chris
>

[Michael Bayer]

On Sep 16, 2010, at 11:15 AM, Michael Bayer wrote:

>
> On Sep 16, 2010, at 10:47 AM, Chris Withers wrote:
>
>> On 16/09/2010 14:32, Michael Bayer wrote:
>>>
>>>> session imported from Meta ? If use Meta.Session.execute it's returns
>>>> RowProxy, which has no lastrowid parameter.
>>>
>>> execute() does not return a RowProxy. All execute() methods return a ResultProxy which consists of metadata about a result as well as an interator interface that produces RowProxy instances.
>>
>> Right, but I wonder if this mirrors what the OP reports:
>>
>> return self.cursor.lastrowid
>> AttributeError: 'NoneType' object has no attribute 'lastrowid'
>
> thats a bug, a regression introduced in 0.6.4. will fix.

this bug is fixed in r1668e771ad16 . r.lastrowid should work with 0.6.3 and previous, as does "SELECT LAST_INSERT_ID()" if you are correctly calling it on the same transaction.
Here is an example:

from sqlalchemy import *
from sqlalchemy.orm import *

m = MetaData()
t = Table("t", m,
Column("x", Integer, primary_key=True),
Column('data', String(50))
)

e = create_engine('mysql://scott:tiger@localhost/test')

t.drop(e)
t.create(e)

s = Session(e)

for i in xrange(10):
r = s.execute("insert into t (data) values ('foo')")
print s.scalar("SELECT LAST_INSERT_ID()")

output:

1
2
3
4
5
6
7
8
9
10