In SqlAlchemy 0.4 when I tried to save (using method "save") to
database object which interferes row which already exists in database
(for example I have table with primary key `id`, row with id = 3 in
this table, and I am trying to save object with the same id = 3) the
operation raises error that object is already persistent.
In Sqlalchemy 0.5.7 there is no "save" method, but there is "add"
method. But when I'm trying to save (using "add" method) object which
interferes row which is already in database - operation is successful.
But I need to have some error raised in that situation, or maybe other
solution that informs me that I'm not actually inserting object but
updating.
What method can I use? I read documentation and I didn't found
solution.
> In SqlAlchemy 0.4 when I tried to save (using method "save") to > database object which interferes row which already exists in database > (for example I have table with primary key `id`, row with id = 3 in > this table, and I am trying to save object with the same id = 3) the > operation raises error that object is already persistent.
> In Sqlalchemy 0.5.7 there is no "save" method, but there is "add" > method. But when I'm trying to save (using "add" method) object which > interferes row which is already in database - operation is successful. > But I need to have some error raised in that situation, or maybe other > solution that informs me that I'm not actually inserting object but > updating.
> What method can I use? I read documentation and I didn't found > solution.
> Am I missing something?
you should be getting an IntegrityError raised if you add() a new object to the session that has a primary key which is the same as one already in the database. add() is the same as the former save_or_update() method, which would call straight down to save() if the object were pending.
When you refer to needing to know if you're inserting or updating, im not clear on what the issue is - if you have an object that is "detached", meaning it has a row in the DB, you add() it to a session, the session already has an object with that identity, you get an error. So I'm not really sure what the specific problem is here.
> > In SqlAlchemy 0.4 when I tried to save (using method "save") to
> > database object which interferes row which already exists in database
> > (for example I have table with primary key `id`, row with id = 3 in
> > this table, and I am trying to save object with the same id = 3) the
> > operation raises error that object is already persistent.
> > In Sqlalchemy 0.5.7 there is no "save" method, but there is "add"
> > method. But when I'm trying to save (using "add" method) object which
> > interferes row which is already in database - operation is successful.
> > But I need to have some error raised in that situation, or maybe other
> > solution that informs me that I'm not actually inserting object but
> > updating.
> > What method can I use? I read documentation and I didn't found
> > solution.
> > Am I missing something?
> you should be getting an IntegrityError raised if you add() a new object to the session that has a primary key which is the same as one already in the database. add() is the same as the former save_or_update() method, which would call straight down to save() if the object were pending.
> When you refer to needing to know if you're inserting or updating, im not clear on what the issue is - if you have an object that is "detached", meaning it has a row in the DB, you add() it to a session, the session already has an object with that identity, you get an error. So I'm not really sure what the specific problem is here.
1. I'm not getting IntergrityError. You said that add is the same as
save_or_update() method, but I'm interested in method which is the
same as save() method - this method raises IntegrityError when I am
trying to add new object to the the session that has a primary key
which is the same as one already in the database.
2. My issue is that I'm creating REST API on my database. When
somebody is trying to create object with existing id (there is an
object in db with this id) I don't want to allow him to update this
object - I want to reject his request.
> On Apr 8, 6:34 pm, Michael Bayer <mike...@zzzcomputing.com> wrote:
> > On Apr 8, 2011, at 10:54 AM, gwozdziu wrote:
> > > Hi!
> > > In SqlAlchemy 0.4 when I tried to save (using method "save") to
> > > database object which interferes row which already exists in database
> > > (for example I have table with primary key `id`, row with id = 3 in
> > > this table, and I am trying to save object with the same id = 3) the
> > > operation raises error that object is already persistent.
> > > In Sqlalchemy 0.5.7 there is no "save" method, but there is "add"
> > > method. But when I'm trying to save (using "add" method) object which
> > > interferes row which is already in database - operation is successful.
> > > But I need to have some error raised in that situation, or maybe other
> > > solution that informs me that I'm not actually inserting object but
> > > updating.
> > > What method can I use? I read documentation and I didn't found
> > > solution.
> > > Am I missing something?
> > you should be getting an IntegrityError raised if you add() a new object to the session that has a primary key which is the same as one already in the database. add() is the same as the former save_or_update() method, which would call straight down to save() if the object were pending.
> > When you refer to needing to know if you're inserting or updating, im not clear on what the issue is - if you have an object that is "detached", meaning it has a row in the DB, you add() it to a session, the session already has an object with that identity, you get an error. So I'm not really sure what the specific problem is here.
> 1. I'm not getting IntergrityError. You said that add is the same as
> save_or_update() method, but I'm interested in method which is the
> same as save() method - this method raises IntegrityError when I am
> trying to add new object to the the session that has a primary key
> which is the same as one already in the database.
> 2. My issue is that I'm creating REST API on my database. When
> somebody is trying to create object with existing id (there is an
> object in db with this id) I don't want to allow him to update this
> object - I want to reject his request.
here is the piece of code:
#before executing this code we have object with id = 5 in our database
obj = self.mapper.get_from_dto(dto) # creating the object from values
provided by user , this object has id = 5
self.mapper.add(obj) # we are putting new object (with id = 5). It
conflicts with already existing object
self.mapper.commit() # commit. New object overrides old one
This is not what I'm expecting. I don't want to overriding (updating)
old object (row).
> #before executing this code we have object with id = 5 in our database
> obj = self.mapper.get_from_dto(dto) # creating the object from values > provided by user , this object has id = 5 > self.mapper.add(obj) # we are putting new object (with id = 5). > It > conflicts with already existing object > self.mapper.commit() # commit. New object overrides old one
this all depends on what get_from_dto() does. If it says:
obj = MyObject(<values from the user>)
now "obj" is known as "transient". When you add() it to the session, it becomes "pending". When flush() proceeds, you will get an Integrity Error if ID # 5 already exists. If you want to merge the values from MyObject onto a possibly already existing object in the DB, you use merge: obj = session.merge(obj).
On the other hand, if get_from_dto() says:
obj = session.query(MyObject).get(5)
then "obj" is "persistent", and is already in the session. Calling session.add(obj) has no net change. There's no error but also nothing has actually happened.
then "obj" is detached. When you call session.add(obj), "obj" becomes the object that represents row #5 in the session. This might be what you're looking for, but this is an unusual case. There is of course no error because you are handing the Session an object that is detached, and becomes persistent again - it represents row #5 from the database already, so its the correct row, though it may be out of date. If you've done something to it that you don't want to persist, that's more on your end, but it's a little strange your application isn't able to be aware of that much sooner.
Anyway, you can test for "identity" using:
from sqlalchemy.orm.util import has_identity if not has_identity(obj): session.add(obj)
if the case is as simple as, the row is out of date and you want to guard against that, you can use a version_id_col for that use case - it will raise if the version you give to the session is older than what's currently in the database.
> > #before executing this code we have object with id = 5 in our database
> > obj = self.mapper.get_from_dto(dto) # creating the object from values
> > provided by user , this object has id = 5
> > self.mapper.add(obj) # we are putting new object (with id = 5).
> > It
> > conflicts with already existing object
> > self.mapper.commit() # commit. New object overrides old one
> this all depends on what get_from_dto() does. If it says:
> obj = MyObject(<values from the user>)
> now "obj" is known as "transient". When you add() it to the session, it becomes "pending". When flush() proceeds, you will get an Integrity Error if ID # 5 already exists. If you want to merge the values from MyObject onto a possibly already existing object in the DB, you use merge: obj = session.merge(obj).
> On the other hand, if get_from_dto() says:
> obj = session.query(MyObject).get(5)
> then "obj" is "persistent", and is already in the session. Calling session.add(obj) has no net change. There's no error but also nothing has actually happened.
> then "obj" is detached. When you call session.add(obj), "obj" becomes the object that represents row #5 in the session. This might be what you're looking for, but this is an unusual case. There is of course no error because you are handing the Session an object that is detached, and becomes persistent again - it represents row #5 from the database already, so its the correct row, though it may be out of date. If you've done something to it that you don't want to persist, that's more on your end, but it's a little strange your application isn't able to be aware of that much sooner.
> Anyway, you can test for "identity" using:
> from sqlalchemy.orm.util import has_identity
> if not has_identity(obj):
> session.add(obj)
> if the case is as simple as, the row is out of date and you want to guard against that, you can use a version_id_col for that use case - it will raise if the version you give to the session is older than what's currently in the database.
