sqlobject-discuss

[SQLObject] Unique ID Number question

[Scott C. <sco...@mi...>]

In SQLObject.html, it shows the following:

p = Person.new(firstName="John", lastName="Doe")
#>> QueryIns:
#   INSERT INTO person (last_name, middle_initial, first_name)
#   VALUES ('Doe', NULL, 'John')
#
#-- Not quite optimized, we don't remember the values we used to
#-- create the object, so they get re-fetched from the database:
#>> QueryOne:
#   SELECT last_name, middle_initial, first_name
#   FROM person
#   WHERE id = 1

Where does it learn that the new Person has an ID of 1?

I tried the same commands manually and the insert doesn't return the ID.  How 
does SQLObject know what the ID is?

Confsued,
Scott

Re: [SQLObject] Unique ID Number question

[Luke O. <lu...@me...>]

>
> Where does it learn that the new Person has an ID of 1?
>
> I tried the same commands manually and the insert doesn't return the ID.
> How does SQLObject know what the ID is?

Depends on the database/dbdriver. You'll find the implementation in
SQlObject/DBConnection.py, _queryInsertID() for each DBConnection-class. Some
Python db interfaces provide a non-standard option for getting this from the
cursor (MySQL, SQLite), some require a second query of the database (Postgres
using OIDs, for instance).

- Luke

Re: [SQLObject] Unique ID Number question

[Scott C. <sco...@mi...>]

On Thursday 02 October 2003 12:27, Luke Opperman wrote:
> > Where does it learn that the new Person has an ID of 1?
> >
> > I tried the same commands manually and the insert doesn't return the ID.
> > How does SQLObject know what the ID is?
>
> Depends on the database/dbdriver. You'll find the implementation in
> SQlObject/DBConnection.py, _queryInsertID() for each DBConnection-class.
> Some Python db interfaces provide a non-standard option for getting this
> from the cursor (MySQL, SQLite), some require a second query of the
> database (Postgres using OIDs, for instance).

I'm using Postgres. It seems like OID's would be a good replacement for ID in 
this case (save some processing).  However, I think they can roll over and 
that might cause a serious problem.

Thanks for the answer.

Cordially,
Scott

Re: [SQLObject] Unique ID Number question

[Ian B. <ia...@co...>]

On Thursday, October 2, 2003, at 12:43 PM, Scott Chapman wrote:

> In SQLObject.html, it shows the following:
>
> p = Person.new(firstName="John", lastName="Doe")
> #>> QueryIns:
> #   INSERT INTO person (last_name, middle_initial, first_name)
> #   VALUES ('Doe', NULL, 'John')
> #
> #-- Not quite optimized, we don't remember the values we used to
> #-- create the object, so they get re-fetched from the database:
> #>> QueryOne:
> #   SELECT last_name, middle_initial, first_name
> #   FROM person
> #   WHERE id = 1
>
> Where does it learn that the new Person has an ID of 1?
>
> I tried the same commands manually and the insert doesn't return the 
> ID.  How
> does SQLObject know what the ID is?

It's database-specific, and usually uses some DB-API extension.  On 
MySQL there's a lastrowid method (or something like that) that you can 
get the ID from after an insert, and Postgres has a lastoid attribute 
(or something like that).  SQLite too.  Firebird uses 
generators/sequences, and it gets the ID and then uses it in the 
insert.  Oracle will do the same.  So SQLObject is papering over this 
diversity.

   Ian