[sqlalchemy] Re: How are Oracle IDs generated?

[Michael Bayer]

On Feb 14, 2008, at 6:01 PM, Waldemar Osuch wrote:




 On Feb 14, 2:06 pm, Richard Jones [EMAIL PROTECTED] wrote:
 On Feb 14, 5:13 pm, Waldemar Osuch [EMAIL PROTECTED] wrote:

 On Feb 13, 8:03 pm, Richard Jones [EMAIL PROTECTED] wrote:

 Thanks, I should have been clearer: how is ID generation done when
 there is no sequence assigned to the column?


 Then my guess is that the generation does not happen and you have
 to provide the value of the ID yourself.
 One could have an insert trigger doing the work but then SA
 would not know about the new ID and you would have to flush, clear
 and requery before being able to add related record.

 Any particular reason you do not want to specify the Sequence
 in the definition?

 Anyway enough with the guesses.
 Time for an expert to step in :-)

if you wanted to use a trigger or something, the issue with Oracle  
specifically is that cx_oracle provides no way of immediately  
retreiving a newly generated primary key default. So we use sequences  
so that the sequence can be pre-executed before each INSERT and we  
then have the new ID.  If you had some other function which generates  
an ID that can also be specified using a ColumnDefault object;  but  
the limitation is that it needs to be something which can run  
standalone (i.e. not solely a trigger on the table).

Also with Oracle we don't any implicit id generation scheme; you  
have to give it something explicitly, either a sequence,  
ColumnDefault, or always provide primary key identifiers explicitly in  
the INSERT parameters.

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

[sqlalchemy] Re: How are Oracle IDs generated?

[Waldemar Osuch]

On Feb 13, 8:03 pm, Richard Jones [EMAIL PROTECTED] wrote:
 I've tried poking through the documentation and source to determine
 this, but it's still unclear to me how SQLAlchemy generates IDs for
 new rows in Oracle.

 There's support for sequences in the oracle backend, but there don't
 appear to be sequences created for my tables.


It is a two step process.
 - define the sequences in Oracle
 - indicate to SA that you want to use it for a given table

For example:
from sqlalchemy import *
meta = MetaData()

roles = Table('box_role',
meta,
Column('id', Integer, Sequence('seq_box_role_id'),
primary_key=True),
Column('name', String(50), nullable=False),
)

According to:
http://www.sqlalchemy.org/docs/04/metadata.html#metadata_defaults_sequences
the first step may not be required but if your DBA has some naming
conventions
he wants you to follow making them by hand may be a good idea.

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