Updating records in table not working

[jos.car...@yahoo.com]

 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?

[King Simon-NFHD78]

> -----Original Message-----
> From: sqlal...@googlegroups.com [mailto:sqlal...@googlegroups.com]
> On Behalf Of jos.car...@yahoo.com
> Sent: 26 July 2011 18:27
> To: sqlal...@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

[Gunnlaugur Briem]

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

[King Simon-NFHD78]

> -----Original Message-----
> From: sqlal...@googlegroups.com [mailto:sqlal...@googlegroups.com]

Ah, I see. Thanks for the clarification.

Cheers,

Simon

[Michael Bayer]

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 sqlal...@googlegroups.com.
> To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
> For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.
>