RE: [sqlalchemy] Updating records in table not working
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of jos.carpente...@yahoo.com Sent: 26 July 2011 18:27 To: sqlalchemy@googlegroups.com Subject: [sqlalchemy] Updating records in table not working I'm using Postgres as a database. I try to create new records or update existing records with data. The data is parsed from a csv file. Creating new records works fine. But when a record already exists, the update fails with: IntegrityError: (IntegrityError) duplicate key value violates unique constraint stock_item_pkey I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' I've also tried updata, but that fails too and als mentions a depreciated statement. The new data is going to a single table. The PrimaryKey is the item number (item with value itemno in snippet below). Since the item is unique, I don't let Postgres create an id. new = Item(item=itemno, ...) db.session.add(new) db.session.commit() I'm pretty new with SA and I might overlook something. How can I solve this? I *think* you should be able to use session.merge instead: http://www.sqlalchemy.org/docs/orm/session.html#merging temp = Item(item=itemno, ...) new = db.session.merge(temp) db.session.commit() (note that 'merge' returns a new object attached to the session) Hope that helps, Simon -- 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.
Re: RE: [sqlalchemy] Updating records in table not working
On Wednesday, 27 July 2011 08:23:14 UTC, Simon King wrote: I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' Only for brand new instances, not associated with a session. For *detached* instances the identity is known and the instances will be in session but not in session.new, so an UPDATE will be issued. Regards, - Gulli -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/oCVN7_jgj4cJ. 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.
RE: RE: [sqlalchemy] Updating records in table not working
-Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Gunnlaugur Briem Sent: 27 July 2011 10:36 To: sqlalchemy@googlegroups.com Subject: Re: RE: [sqlalchemy] Updating records in table not working On Wednesday, 27 July 2011 08:23:14 UTC, Simon King wrote: I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' Only for brand new instances, not associated with a session. For *detached* instances the identity is known and the instances will be in session but not in session.new, so an UPDATE will be issued. Regards, - Gulli Ah, I see. Thanks for the clarification. Cheers, Simon -- 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.
Re: [sqlalchemy] Updating records in table not working
On Jul 27, 2011, at 5:52 AM, King Simon-NFHD78 wrote: -Original Message- From: sqlalchemy@googlegroups.com [mailto:sqlalchemy@googlegroups.com] On Behalf Of Gunnlaugur Briem Sent: 27 July 2011 10:36 To: sqlalchemy@googlegroups.com Subject: Re: RE: [sqlalchemy] Updating records in table not working On Wednesday, 27 July 2011 08:23:14 UTC, Simon King wrote: I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I think this is incorrect - 'add' always corresponds to 'INSERT' Only for brand new instances, not associated with a session. For *detached* instances the identity is known and the instances will be in session but not in session.new, so an UPDATE will be issued. Regards, - Gulli Ah, I see. Thanks for the clarification. Whether the object has a key or not is what decides between it being transient-pending or detached-persistent once add()-ed back to the session: from sqlalchemy.orm import attributes attributes.instance_state(myobject).key is not None where instance_state() is going to give you the ._sa_instance_state attribute we stick on there, but we keep access through the public function. The .key is stuck on the object after it gets through a flush(), or when we construct it from an incoming row. Otherwise there is not a .key and the object is transient-pending. We originally had save() and update() because we copied Hibernate's scheme exactly, as well as save_or_update() which in Hibernate's case does a guess. In SQLAlchemy we have it much easier due to Python's open ended nature, we just check if we put a key or not.So we just made it add() to simplify. Cheers, Simon -- 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. -- 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.
[sqlalchemy] Updating records in table not working
I'm using Postgres as a database. I try to create new records or update existing records with data. The data is parsed from a csv file. Creating new records works fine. But when a record already exists, the update fails with: * * * IntegrityError: (IntegrityError) duplicate key value violates unique constraint stock_item_pkey* I've looked at the SA documentation and as far as I can see the 'add' does an insert or an update. I've also tried updata, but that fails too and als mentions a depreciated statement. The new data is going to a single table. The PrimaryKey is the item number (item with value itemno in snippet below). Since the item is unique, I don't let Postgres create an id. *new = Item(item=itemno, ...)* *db.session.add(new)* *db.session.commit()* * * *I'm pretty new with SA and I might overlook something. How can I solve this? * -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/IC00kz76FS4J. 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.