sqlalchemy always runs all the INSERT statements before the DELETEs.
The reasons for this have to do with the dependency on UPDATE
statements, i.e. that inserts must occur before dependent updates and
deletes must occur after dependent updates, and the unit of work
currently doesn't have the capability to break out UPDATE statements
into those for which only inserts depend on and those for which only
deletes depend on. You may also observe that Hibernate has this
same limitation.
So, if you have a row to insert that requires a previous entry with a
matching value on a unique constraint to be deleted, do the delete,
issue a flush(), then do the insert.
On Feb 19, 2009, at 8:22 AM, oberger wrote:
>
> Hi all,
>
> I'm using Elixir & SQLAlchemy to run a routing Application.
> The App will store people their trips and their corresponding
> trip_stops.
>
> I ran into a strange problem with the ordering_list.
> I included the source of the Person table to be complete.
>
> If I run this code:
>
> oli = model.Person(login='oberger', email = 'my_mal',
> password='secrt', last_name='Berger', first_name = 'Oliver')
> short_trip = model.Trip(description='short trip')
> oli.trips.append(short_trip)
>
> ts = model.Trip_Stop(adress='Town1')
> ts2 = model.Trip_Stop(adress='Town2')
> ts3 = model.Trip_Stop(adress='Town3')
> ts4 = model.Trip_Stop(adress='Town4')
>
> short_trip.trip_stops.append(ts)
> short_trip.trip_stops.append(ts2)
> short_trip.trip_stops.append(ts3)
> short_trip.trip_stops.insert(2, ts4)
> model.Session.commit()
>
> del short_trip.trip_stops[1]
> model.Session.commit()
>
> I'll get this error from the oracle database:
> "
> IntegrityError: (IntegrityError) ORA-00001: Unique Constraint
> (TRAV_OWNER.SYS_C007296) verletzt
> 'UPDATE trip_stop SET waypoint_nr=:waypoint_nr WHERE
> trip_stop.trip_sid = :trip_stop_trip_sid AND
> = :trip_stop_waypoint_nr' {'trip_stop_waypoint_nr': 2, 'waypoint_nr':
> 1, 'trip_stop_trip_sid': 1}
> "
>
> I am an oracle guy and know this database very well.
> It seems that SQLAlchemy is updating the rows in table trip_stop in
> the wrong order, so that a
> UNIQUE KEY violation occours. If the updates are sent in the correct
> order, no Error wil occour.
> I think this is a bug.
>
> Here is my model:
> If anyone can help me, Thanks a lot
>
> Oliver
>
>
>
>
> class Person(Entity):
> sid = Field(Integer, Sequence('PERSON_SEQ'),
> primary_key=True)
> email = Field(String(60), required=True)
> login = Field(String(20), required=True)
> password = Field(String(100) )
> last_name = Field(String(50))
> first_name = Field(String(40))
> gender = Field(Boolean)
> is_smoker = Field(Boolean)
> zip_code = Field(String(20))
> city = Field(String(80))
> last_login = Field(DateTime, PassiveDefault(text
> ('sysdate')), required=True)
> date_updated = Field(DateTime, PassiveDefault(text
> ("sysdate")), required=True)
> date_created = Field(DateTime, PassiveDefault(text
> ("sysdate")), required=True)
> locked_flag = Field(Boolean, PassiveDefault('0'),
> required=True)
> verified = Field(Boolean, PassiveDefault('0'),
> required=True)
> trips = OneToMany('Trip')
>
> def __repr__(self):
> return '<%r %r, email: %r, last_name: %s, first_name: %s>' \
> % (self.__class__.__name__.capitalize(), self.login,
> self.email, self.last_name, self.first_name)
>
> @events.before_insert
> @events.before_update
> def encrypt_password(self):
> if self.password:
> self.password = encrypt_value(self.password)
> self.password_check = self.password
>
>
>
> from sqlalchemy.ext.orderinglist import ordering_list
> class Trip(Entity):
> sid = Field(Integer, Sequence('TRIP_SEQ'),
> primary_key=True)
> person = ManyToOne('Person',colname =
> 'person_sid', ondelete='cascade' , required=True)
> description = Field(String(200), required=True)
> seat_count = Field(Integer, PassiveDefault('1'),
> required=True)
> smoking_allowed = Field(Boolean, PassiveDefault('0'),
> required=True)
> passenger_gender = Field(String(1), PassiveDefault('A'),
> required=True)
> expired = Field(Boolean, PassiveDefault('0'),
> required=True)
> date_updated = Field(DateTime, PassiveDefault(text
> ("SYSDATE")), required=True)
> date_created = Field(DateTime, PassiveDefault(text
> ("sysdate")), required=True)
> type = Field(String(1), PassiveDefault('S'),
> required=True)
> valid_from = Field(DateTime)
> trip_stops = OneToMany('Trip_Stop',
> collection_class=ordering_list('waypoint_nr'),
> order_by='waypoint_nr',
> cascade="all, delete, delete-orphan")
>
> def __repr__(self):
> return '<%r %r, desc: %r>' \
> % (self.__class__.__name__.capitalize(), self.sid,
> self.description)
>
>
> from sqlalchemy.ext.orderinglist import ordering_list
>
>
> class Trip_Stop(Entity):
> trip = ManyToOne('Trip',colname = 'trip_sid',
> ondelete='cascade', primary_key=True)
> waypoint_nr = Field(Integer, primary_key=True)
> adress = Field(String(200), required=True)
> geo_x = Field(Float, default=0, required=True)
> geo_y = Field(Float, default=0, required=True)
> duration_in_minutes = Field(Integer, default=7,
> required=True)
> price_in_euro = Field(Numeric(precision=22, scale=2,
> asdecimal=True), PassiveDefault(text("0")), required=True)
>
> def __repr__(self):
> return '<%r %r, desc: %r>' \
> % (self.__class__.__name__.capitalize(),
> self.waypoint_nr, self.adress)
>
>
> >
--~--~---------~--~----~------------~-------~--~----~
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
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
