[MonetDB-users] python cursor missing lastrowid attribute?

newer
[MonetDB-users] Why does MonetDB...

older
[MonetDB-users] Another query that...

m h

26 Nov 2007 26 Nov '07

10:09 p.m.

In MonetSQLdb BaseCursor.insert_id, the docstring says to use the lastrowid attribute. When I try to access that lastrowid on the cursor instance I get an AttributeError. Should I file a bug for this? matt

Show replies by date

Niels Nes

26 Nov 26 Nov

10:15 p.m.

On Mon, Nov 26, 2007 at 03:09:38PM -0700, m h wrote:

...

In MonetSQLdb BaseCursor.insert_id, the docstring says to use the lastrowid attribute.

When I try to access that lastrowid on the cursor instance I get an AttributeError.

Should I file a bug for this?

I think we should remove this remark. The server only sends rows-affected info on insert (and other update statements), ie its not easy to implement this feature (and this is afcourse mostly guessing what the python BaseCursor.insert_id/lastrowid is supposed to be do?). Niels (you known we are looking for experts how are willing to fix our api's) As db architecture guys we would like other to pickup the apis and improve them ;-)

...

matt

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

-- Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

m h

10:27 p.m.

On Nov 26, 2007 3:15 PM, Niels Nes wrote:

...

On Mon, Nov 26, 2007 at 03:09:38PM -0700, m h wrote:

...
In MonetSQLdb BaseCursor.insert_id, the docstring says to use the lastrowid attribute.

When I try to access that lastrowid on the cursor instance I get an AttributeError.

Should I file a bug for this?

I think we should remove this remark. The server only sends rows-affected info on insert (and other update statements), ie its not easy to implement this feature (and this is afcourse mostly guessing what the python BaseCursor.insert_id/lastrowid is supposed to be do?).

Hmmm, most all the other python db cursors implement this. Here is the pep-249 info: This read-only attribute provides the rowid of the last modified row (most databases return a rowid only when a single INSERT operation is performed). If the operation does not set a rowid or if the database does not support rowids, this attribute should be set to None. The semantics of .lastrowid are undefined in case the last executed statement modified more than one row, e.g. when using INSERT with .executemany(). Warning Message: "DB-API extension cursor.lastrowid used" I realize this is "optional" but it seems like along the way support for this was either available or going to be available.

...

Niels

(you known we are looking for experts how are willing to fix our api's) As db architecture guys we would like other to pickup the apis and improve them ;-)

...

I don't consider myself to be a db expert ;( But implementing the sqlalchemy backend is a good testcase for monetdb. (Plus it might attract people who are experts along the way). Many of the people in the python community had never heard about Monetdb before I blogged about it a few days back....

Niels Nes

10:38 p.m.

On Mon, Nov 26, 2007 at 03:27:39PM -0700, m h wrote:

...

On Nov 26, 2007 3:15 PM, Niels Nes wrote:

...
On Mon, Nov 26, 2007 at 03:09:38PM -0700, m h wrote:

...
In MonetSQLdb BaseCursor.insert_id, the docstring says to use the lastrowid attribute.

When I try to access that lastrowid on the cursor instance I get an AttributeError.

Should I file a bug for this?

I think we should remove this remark. The server only sends rows-affected info on insert (and other update statements), ie its not easy to implement this feature (and this is afcourse mostly guessing what the python BaseCursor.insert_id/lastrowid is supposed to be do?).

Hmmm, most all the other python db cursors implement this. Here is Maybe I should have a look at those other cursors. the pep-249 info:

This read-only attribute provides the rowid of the last modified row (most databases return a rowid only when a single INSERT operation is performed). If the operation does not set a rowid or if the database does not support rowids, this attribute should be set to None. I guess then we should set it to None. Problem is that we would like to controll the rowid, ie for example when we insert the rowid may not be fixed (could change).

The semantics of .lastrowid are undefined in case the last executed statement modified more than one row, e.g. when using INSERT with .executemany().

Warning Message: "DB-API extension cursor.lastrowid used"

I realize this is "optional" but it seems like along the way support for this was either available or going to be available. Currently its not and we went for a different solution for sequence numbers.

...
Niels

(you known we are looking for experts how are willing to fix our api's) As db architecture guys we would like other to pickup the apis and improve them ;-)

...
I don't consider myself to be a db expert ;(

But implementing the sqlalchemy backend is a good testcase for monetdb. (Plus it might attract people who are experts along the way). Many of the people in the python community had never heard about Monetdb before I blogged about it a few days back.... Great that they learned MonetDB, maybe the 'cwi' also rings a bell with them ? If not they may known that guido (mister python) once worked at the cwi.

Niels

...

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Martin Kersten

10:55 p.m.

Niels Nes wrote:

...

Great that they learned MonetDB, maybe the 'cwi' also rings a bell with them ? If not they may known that guido (mister python) once worked at the cwi.

and invented Python while working at CWI, before leaving to the US. Actually, in the same department as where MonetDB evolved. And another early Python expert is still around ;-) Martin

m h

11:57 p.m.

On Nov 26, 2007 3:38 PM, Niels Nes wrote:

...

On Mon, Nov 26, 2007 at 03:27:39PM -0700, m h wrote:

...
On Nov 26, 2007 3:15 PM, Niels Nes wrote:

...
On Mon, Nov 26, 2007 at 03:09:38PM -0700, m h wrote:

...
In MonetSQLdb BaseCursor.insert_id, the docstring says to use the lastrowid attribute.

When I try to access that lastrowid on the cursor instance I get an AttributeError.

Should I file a bug for this?

I think we should remove this remark. The server only sends rows-affected info on insert (and other update statements), ie its not easy to implement this feature (and this is afcourse mostly guessing what the python BaseCursor.insert_id/lastrowid is supposed to be do?).

Hmmm, most all the other python db cursors implement this. Here is Maybe I should have a look at those other cursors.

If the ids are changing often, it might be difficult....

...

...
the pep-249 info:

This read-only attribute provides the rowid of the last modified row (most databases return a rowid only when a single INSERT operation is performed). If the operation does not set a rowid or if the database does not support rowids, this attribute should be set to None. I guess then we should set it to None. Problem is that we would like to controll the rowid, ie for example when we insert the rowid may not be fixed (could change).

Are these changed often?

...

...
The semantics of .lastrowid are undefined in case the last executed statement modified more than one row, e.g. when using INSERT with .executemany().

Warning Message: "DB-API extension cursor.lastrowid used"

I realize this is "optional" but it seems like along the way support for this was either available or going to be available.

Currently its not and we went for a different solution for sequence numbers.

Him, if there is a way to get these sequence numbers that might help too. Can it be done via SQL?

...

...
...
Niels

(you known we are looking for experts how are willing to fix our api's) As db architecture guys we would like other to pickup the apis and improve them ;-)

...
I don't consider myself to be a db expert ;(

But implementing the sqlalchemy backend is a good testcase for monetdb. (Plus it might attract people who are experts along the way). Many of the people in the python community had never heard about Monetdb before I blogged about it a few days back....

Great that they learned MonetDB, maybe the 'cwi' also rings a bell with them ? If not they may known that guido (mister python) once worked at the cwi.

Seems like good things come out of cwi.... ;) The SQLAlchemy requirements for lastrowid are thus: inserting a row usually needs to return the newly generated primary key the ORM needs this but its also a pretty standard user requirement otherwise you dont have a generic way of fetching the row which you just created DBAPIs are all entirely inconsistent on how this is done which is usually because the databsase client libs themselves are inconsistent oracle, PG, firebird all rely upon an explicit sequence being pre-executed whereas sqlite and mysql have an autoincrementing behavior where you can get a single-valued PK via cursor.lastrowid

Niels Nes

27 Nov 27 Nov

12:03 a.m.

On Mon, Nov 26, 2007 at 04:57:38PM -0700, m h wrote:

...

On Nov 26, 2007 3:38 PM, Niels Nes wrote:

...
On Mon, Nov 26, 2007 at 03:27:39PM -0700, m h wrote:

...
On Nov 26, 2007 3:15 PM, Niels Nes wrote:

...
On Mon, Nov 26, 2007 at 03:09:38PM -0700, m h wrote:

...
In MonetSQLdb BaseCursor.insert_id, the docstring says to use the lastrowid attribute.

When I try to access that lastrowid on the cursor instance I get an AttributeError.

Should I file a bug for this?

I think we should remove this remark. The server only sends rows-affected info on insert (and other update statements), ie its not easy to implement this feature (and this is afcourse mostly guessing what the python BaseCursor.insert_id/lastrowid is supposed to be do?).

Hmmm, most all the other python db cursors implement this. Here is Maybe I should have a look at those other cursors.

If the ids are changing often, it might be difficult....

...
...
the pep-249 info:

This read-only attribute provides the rowid of the last modified row (most databases return a rowid only when a single INSERT operation is performed). If the operation does not set a rowid or if the database does not support rowids, this attribute should be set to None. I guess then we should set it to None. Problem is that we would like to controll the rowid, ie for example when we insert the rowid may not be fixed (could change).

Are these changed often? yes on concurrent updates.

...
...
The semantics of .lastrowid are undefined in case the last executed statement modified more than one row, e.g. when using INSERT with .executemany().

Warning Message: "DB-API extension cursor.lastrowid used"

I realize this is "optional" but it seems like along the way support for this was either available or going to be available.

Currently its not and we went for a different solution for sequence numbers.

Him, if there is a way to get these sequence numbers that might help too. Can it be done via SQL? Yes.

create sequence seq as integer; select next value for seq; Niels

...

...
...
...
Niels

(you known we are looking for experts how are willing to fix our api's) As db architecture guys we would like other to pickup the apis and improve them ;-)

...
I don't consider myself to be a db expert ;(

But implementing the sqlalchemy backend is a good testcase for monetdb. (Plus it might attract people who are experts along the way). Many of the people in the python community had never heard about Monetdb before I blogged about it a few days back....

Great that they learned MonetDB, maybe the 'cwi' also rings a bell with them ? If not they may known that guido (mister python) once worked at the cwi.

Seems like good things come out of cwi.... ;)

The SQLAlchemy requirements for lastrowid are thus:

inserting a row usually needs to return the newly generated primary key the ORM needs this but its also a pretty standard user requirement otherwise you dont have a generic way of fetching the row which you just created DBAPIs are all entirely inconsistent on how this is done which is usually because the databsase client libs themselves are inconsistent oracle, PG, firebird all rely upon an explicit sequence being pre-executed whereas sqlite and mysql have an autoincrementing behavior where you can get a single-valued PK via cursor.lastrowid

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

m h

12:30 a.m.

On Nov 26, 2007 5:03 PM, Niels Nes wrote:

...

On Mon, Nov 26, 2007 at 04:57:38PM -0700, m h wrote:

...
On Nov 26, 2007 3:38 PM, Niels Nes wrote:

...
On Mon, Nov 26, 2007 at 03:27:39PM -0700, m h wrote:

...
On Nov 26, 2007 3:15 PM, Niels Nes wrote:

...
On Mon, Nov 26, 2007 at 03:09:38PM -0700, m h wrote:

...
In MonetSQLdb BaseCursor.insert_id, the docstring says to use the lastrowid attribute.

When I try to access that lastrowid on the cursor instance I get an AttributeError.

Should I file a bug for this?

I think we should remove this remark. The server only sends rows-affected info on insert (and other update statements), ie its not easy to implement this feature (and this is afcourse mostly guessing what the python BaseCursor.insert_id/lastrowid is supposed to be do?).

Hmmm, most all the other python db cursors implement this. Here is Maybe I should have a look at those other cursors.

If the ids are changing often, it might be difficult....

...
...
the pep-249 info:

This read-only attribute provides the rowid of the last modified row (most databases return a rowid only when a single INSERT operation is performed). If the operation does not set a rowid or if the database does not support rowids, this attribute should be set to None. I guess then we should set it to None. Problem is that we would like to controll the rowid, ie for example when we insert the rowid may not be fixed (could change).

Are these changed often? yes on concurrent updates.

...
...
The semantics of .lastrowid are undefined in case the last executed statement modified more than one row, e.g. when using INSERT with .executemany().

Warning Message: "DB-API extension cursor.lastrowid used"

I realize this is "optional" but it seems like along the way support for this was either available or going to be available.

Currently its not and we went for a different solution for sequence numbers.

Him, if there is a way to get these sequence numbers that might help too. Can it be done via SQL? Yes.

create sequence seq as integer; select next value for seq;

Ok, (sorry if I'm being slow). So if a table has an autoincremented column, is that using sequences? Can I pull the next value out using SQL?

Niels Nes

7:01 a.m.

On Mon, Nov 26, 2007 at 05:30:49PM -0700, m h wrote:

...

On Nov 26, 2007 5:03 PM, Niels Nes wrote:

...
On Mon, Nov 26, 2007 at 04:57:38PM -0700, m h wrote:

...
On Nov 26, 2007 3:38 PM, Niels Nes wrote:

...
On Mon, Nov 26, 2007 at 03:27:39PM -0700, m h wrote:

...
On Nov 26, 2007 3:15 PM, Niels Nes wrote:

...
On Mon, Nov 26, 2007 at 03:09:38PM -0700, m h wrote: > In MonetSQLdb BaseCursor.insert_id, the docstring says to use the > lastrowid attribute. > > When I try to access that lastrowid on the cursor instance I get an > AttributeError. > > Should I file a bug for this?

I think we should remove this remark. The server only sends rows-affected info on insert (and other update statements), ie its not easy to implement this feature (and this is afcourse mostly guessing what the python BaseCursor.insert_id/lastrowid is supposed to be do?).

Hmmm, most all the other python db cursors implement this. Here is Maybe I should have a look at those other cursors.

If the ids are changing often, it might be difficult....

...
...
the pep-249 info:

This read-only attribute provides the rowid of the last modified row (most databases return a rowid only when a single INSERT operation is performed). If the operation does not set a rowid or if the database does not support rowids, this attribute should be set to None. I guess then we should set it to None. Problem is that we would like to controll the rowid, ie for example when we insert the rowid may not be fixed (could change).

Are these changed often? yes on concurrent updates.

...
...
The semantics of .lastrowid are undefined in case the last executed statement modified more than one row, e.g. when using INSERT with .executemany().

Warning Message: "DB-API extension cursor.lastrowid used"

I realize this is "optional" but it seems like along the way support for this was either available or going to be available.

Currently its not and we went for a different solution for sequence numbers.

Him, if there is a way to get these sequence numbers that might help too. Can it be done via SQL? Yes.

create sequence seq as integer; select next value for seq;

Ok, (sorry if I'm being slow). So if a table has an autoincremented column, is that using sequences? Can I pull the next value out using SQL? auto_increment uses (anonymous) sequence numbers and its default value is set to 'next value for';

Niels

...

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

m h

6:35 p.m.

...

...
...
create sequence seq as integer; select next value for seq;

Ok, (sorry if I'm being slow). So if a table has an autoincremented column, is that using sequences? Can I pull the next value out using SQL? auto_increment uses (anonymous) sequence numbers and its default value is set to 'next value for';

Great. Is there a way to ask sequences for the previous value? Or is it only next?

Niels Nes

6:53 p.m.

On Tue, Nov 27, 2007 at 11:35:35AM -0700, m h wrote:

...

...
...
...
create sequence seq as integer; select next value for seq;

Ok, (sorry if I'm being slow). So if a table has an autoincremented column, is that using sequences? Can I pull the next value out using SQL? auto_increment uses (anonymous) sequence numbers and its default value is set to 'next value for';

Great. Is there a way to ask sequences for the previous value? Or is it only next? only next.

Niels

...

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

m h

6:59 p.m.

On Nov 27, 2007 11:53 AM, Niels Nes wrote:

...

On Tue, Nov 27, 2007 at 11:35:35AM -0700, m h wrote:

...
...
...
...
create sequence seq as integer; select next value for seq;

Ok, (sorry if I'm being slow). So if a table has an autoincremented column, is that using sequences? Can I pull the next value out using SQL? auto_increment uses (anonymous) sequence numbers and its default value is set to 'next value for';

Great. Is there a way to ask sequences for the previous value? Or is it only next? only next.

So if I use SERIAL or AUTOINCREMENT columns, what is the way to get the most recently inserted ID? (Am assumming it's not possible?)

Niels Nes

7:13 p.m.

On Tue, Nov 27, 2007 at 11:59:20AM -0700, m h wrote:

...

On Nov 27, 2007 11:53 AM, Niels Nes wrote:

...
On Tue, Nov 27, 2007 at 11:35:35AM -0700, m h wrote:

...
...
...
...
create sequence seq as integer; select next value for seq;

Ok, (sorry if I'm being slow). So if a table has an autoincremented column, is that using sequences? Can I pull the next value out using SQL? auto_increment uses (anonymous) sequence numbers and its default value is set to 'next value for';

Great. Is there a way to ask sequences for the previous value? Or is it only next? only next.

So if I use SERIAL or AUTOINCREMENT columns, what is the way to get the most recently inserted ID? (Am assumming it's not possible?) not possible or revert the process x = select next value for seq; (x in python i mean). insert into .. etc

Niels

...

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Martin Kersten

7:16 p.m.

...

...
...
Great. Is there a way to ask sequences for the previous value? Or is it only next?

only next.

So if I use SERIAL or AUTOINCREMENT columns, what is the way to get the most recently inserted ID? (Am assumming it's not possible?)

how about a max() over the column?

Niels Nes

7:18 p.m.

On Tue, Nov 27, 2007 at 08:16:30PM +0100, Martin Kersten wrote:

...

...
...
...
Great. Is there a way to ask sequences for the previous value? Or is it only next?

only next.

So if I use SERIAL or AUTOINCREMENT columns, what is the way to get the most recently inserted ID? (Am assumming it's not possible?)

how about a max() over the column?

how about concurrent inserts ;-( Niels

...

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Martin Kersten

7:24 p.m.

Niels Nes wrote:

...

On Tue, Nov 27, 2007 at 08:16:30PM +0100, Martin Kersten wrote:

...
...
...
...
Great. Is there a way to ask sequences for the previous value? Or is it only next?

only next.

So if I use SERIAL or AUTOINCREMENT columns, what is the way to get the most recently inserted ID? (Am assumming it's not possible?)

how about a max() over the column?

how about concurrent inserts ;-(

begin doit commit

...

Niels

...
------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Fabian Groffen

7:41 p.m.

On 27-11-2007 20:24:02 +0100, Martin Kersten wrote:

...

...
...
...
So if I use SERIAL or AUTOINCREMENT columns, what is the way to get the most recently inserted ID? (Am assumming it's not possible?)

how about a max() over the column?

how about concurrent inserts ;-(

begin doit commit

Shame on you, Martin. Indeed, retrieve the next value from the sequence, and use it in your program to do the insert.

m h

8:13 p.m.

On Nov 27, 2007 12:18 PM, Niels Nes wrote:

...

On Tue, Nov 27, 2007 at 08:16:30PM +0100, Martin Kersten wrote:

...
...
...
...
Great. Is there a way to ask sequences for the previous value? Or is it only next?

only next.

So if I use SERIAL or AUTOINCREMENT columns, what is the way to get the most recently inserted ID? (Am assumming it's not possible?)

how about a max() over the column?

how about concurrent inserts ;-(

Thanks much for the responses. Sorry to beat a dead horse.... (Am currently in the process of simulating AUTOINC and SERIAL with SEQUENCES as suggested, since the ORM part of SQLAlchemy relies heavily on knowing the id). So let me rephrase my question to try to eliminate the concurrency issue. Is is possible to get the ID of the item I recently inserted (ignoring that others might have inserted them). Perhaps the concurrency can't be eliminated here, since the cursor doesn't know the id, it's all server side. Hope I'm not being too annoying, I'm just trying to understand how things work.

Fabian Groffen

8:27 p.m.

On 27-11-2007 13:13:13 -0700, m h wrote:

...

Thanks much for the responses.

Sorry to beat a dead horse.... (Am currently in the process of simulating AUTOINC and SERIAL with SEQUENCES as suggested, since the ORM part of SQLAlchemy relies heavily on knowing the id).

So let me rephrase my question to try to eliminate the concurrency issue. Is is possible to get the ID of the item I recently inserted (ignoring that others might have inserted them). Perhaps the concurrency can't be eliminated here, since the cursor doesn't know the id, it's all server side.

Hope I'm not being too annoying, I'm just trying to understand how things work.

As far as I am aware, the problem should be solved in the same was as it is done for PostgreSQL, as it uses the same (SQL standard) strategy. Last time I checked, they didn't have a method either to retrieve the last id value. I don't know if we have currval() implemented, but I guess we could, and it might solve your problem at this level. http://www.postgresql.org/docs/7.4/interactive/functions-sequence.html

Niels Nes

9:46 p.m.

On Tue, Nov 27, 2007 at 09:27:20PM +0100, Fabian Groffen wrote:

...

On 27-11-2007 13:13:13 -0700, m h wrote:

...
Thanks much for the responses.

Sorry to beat a dead horse.... (Am currently in the process of simulating AUTOINC and SERIAL with SEQUENCES as suggested, since the ORM part of SQLAlchemy relies heavily on knowing the id).

So let me rephrase my question to try to eliminate the concurrency issue. Is is possible to get the ID of the item I recently inserted (ignoring that others might have inserted them). Perhaps the concurrency can't be eliminated here, since the cursor doesn't know the id, it's all server side.

Hope I'm not being too annoying, I'm just trying to understand how things work.

As far as I am aware, the problem should be solved in the same was as it is done for PostgreSQL, as it uses the same (SQL standard) strategy. Last time I checked, they didn't have a method either to retrieve the last id value.

I don't know if we have currval() implemented, but I guess we could, and it might solve your problem at this level.

http://www.postgresql.org/docs/7.4/interactive/functions-sequence.html

Probably he want (needs) PQoidValue Returns the OID of the inserted row, if the SQL command was an INSERT that inserted exactly one row into a table that has OIDs. Otherwise, returns InvalidOid. Oid PQoidValue(const PGresult *res); I guess this could be a feature request ;-) Niels

...

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Niels Nes

9:53 p.m.

On Tue, Nov 27, 2007 at 10:46:06PM +0100, Niels Nes wrote:

...

On Tue, Nov 27, 2007 at 09:27:20PM +0100, Fabian Groffen wrote:

...
On 27-11-2007 13:13:13 -0700, m h wrote:

...
Thanks much for the responses.

Sorry to beat a dead horse.... (Am currently in the process of simulating AUTOINC and SERIAL with SEQUENCES as suggested, since the ORM part of SQLAlchemy relies heavily on knowing the id).

So let me rephrase my question to try to eliminate the concurrency issue. Is is possible to get the ID of the item I recently inserted (ignoring that others might have inserted them). Perhaps the concurrency can't be eliminated here, since the cursor doesn't know the id, it's all server side.

Hope I'm not being too annoying, I'm just trying to understand how things work.

As far as I am aware, the problem should be solved in the same was as it is done for PostgreSQL, as it uses the same (SQL standard) strategy. Last time I checked, they didn't have a method either to retrieve the last id value.

I don't know if we have currval() implemented, but I guess we could, and it might solve your problem at this level.

http://www.postgresql.org/docs/7.4/interactive/functions-sequence.html

Probably he want (needs)

PQoidValue

Returns the OID of the inserted row, if the SQL command was an INSERT that inserted exactly one row into a table that has OIDs. Otherwise, returns InvalidOid.

Oid PQoidValue(const PGresult *res);

I guess this could be a feature request ;-)

or the mysql Method insert_id int insert_id() Description Returns the id of the last INSERT query into a table with an AUTO INCREMENT field. Niels

...

...
------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

--

Niels Nes, Centre for Mathematics and Computer Science (CWI) Kruislaan 413, 1098 SJ Amsterdam, The Netherlands room C0.02, phone ++31 20 592-4098, fax ++31 20 592-4312 url: http://www.cwi.nl/~niels e-mail: Niels.Nes@cwi.nl

------------------------------------------------------------------------- This SF.net email is sponsored by: Microsoft Defy all challenges. Microsoft(R) Visual Studio 2005. http://clk.atdmt.com/MRT/go/vse0120000070mrt/direct/01/ _______________________________________________ MonetDB-users mailing list MonetDB-users@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/monetdb-users

Stefan Manegold

8:29 p.m.

On Tue, Nov 27, 2007 at 01:13:13PM -0700, m h wrote:

...

On Nov 27, 2007 12:18 PM, Niels Nes wrote:

...
On Tue, Nov 27, 2007 at 08:16:30PM +0100, Martin Kersten wrote:

...
...
...
...
Great. Is there a way to ask sequences for the previous value? Or is it only next?

only next.

So if I use SERIAL or AUTOINCREMENT columns, what is the way to get the most recently inserted ID? (Am assumming it's not possible?)

how about a max() over the column?

how about concurrent inserts ;-(

Thanks much for the responses.

Sorry to beat a dead horse.... (Am currently in the process of simulating AUTOINC and SERIAL with SEQUENCES as suggested, since the ORM part of SQLAlchemy relies heavily on knowing the id).

So let me rephrase my question to try to eliminate the concurrency issue. Is is possible to get the ID of the item I recently inserted (ignoring that others might have inserted them). Perhaps the concurrency can't be eliminated here, since the cursor doesn't know the id, it's all server side.

well, maybe not quite want you want/expect, but how about: if the item (tuple) has a key other than the autoinc/serial/... ID, just lookup the item by this key (tha you know since you inserted it) and project on the ID. This is not "elegant" at all, there are now performance "guarantees, but it's purely non-procedural very basic SQL... Stefan

...

Hope I'm not being too annoying, I'm just trying to understand how things work.

6241

Age (days ago)

6242

Last active (days ago)

List overview

Download

21 comments

5 participants

participants (5)

Fabian Groffen
m h
Martin Kersten
Niels Nes
Stefan Manegold

[MonetDB-users] python cursor missing lastrowid attribute?

m h

m h

m h

m h

m h

m h

m h

tags

participants (5)