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-1: 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,
