auto increment oracle sequence in sqlalchemy insert

Question

I have a table with the following column:

id = sa.Column(sa.Integer, sa.Sequence('id_seq'), primary_key=True, nullable=False)

Usually, I have no problems inserting data, but every time I insert rows with specific id, it causes problems with Oracle since the sequence is not modified accordingly.

For example, assuming that the table is new and the sequence starts at 1, if I insert a row specifying id=2, the sequence doesn't change which will cause the next insert to fail.

I do understand that Oracle does not support auto increment but what is the proper way of handling this under sqlalchemy? Do I need to manually change the sequence after such insert statement? can I bind to an event or use any other magic to make it work like other dialects? (choose max(id)+1)

Oracle doesn't have "autoincrement" like MySQL, but it has sequence. — jcho360

turboc turboc · Accepted Answer · 2014-07-19T03:32:45

I do not know sqlalchemy, but I would create an oracle sequence and a "before insert trigger" that sets the id to the sequence.nextval

My syntax is a little rusty but basically

create sequence myautonum start with 1 nomaxvalue nocycle;

create or replace trigger mybeforetrigger
before insert on <tablename>
for each row
begin
if (new.id is NULL) then
  new.id=myautonum.nextval;
endif
end;

like I said my syntax may be off a little, but I think you get the idea.

auto increment oracle sequence in sqlalchemy insert

1 Answers