I would like to change a primary key's value, to be deterministic, based on
a multi-to-multi relation. Thus I'm populating the tables with a temporary
ids (just random strings), then calculating the right, unique id, and
changing it afterwards.
I have the following models:
class Image(Base):
id = Column(String, primary_key=True, default=lambda: random_string(16))
collections = relationship('Collection', secondary='collections_images',
back_populates='images')
class Collection(Base):
id = Column(String, primary_key=True, default=lambda: random_string(16))
images = relationship('Image', secondary='collections_images',
back_populates='collections', order_by='desc(Image.date_created)',
lazy='dynamic')
Table('collections_images', Base.metadata,
Column('collection_id', ForeignKey('collections.id'), primary_key=True),
Column('image_id', ForeignKey('images.id'), primary_key=True))
My problem is the following:
1.
Out of the 3 examples below, only one triggers an integrity exception,
the other two does not.
Why?
In all three I'm trying to write to this primary key, which is
referenced, thus should produce an exception. Yet, in 2. and 3. it seem
nothing is happening when collection.id = is set. When I debug via SQL
queries it shows absolutely nothing called for the collection.id = line.
2.
How can I solve this problem? I mean how can I change a primary key's
value which is also used in a multi-to-multi relation?
The DB is PostgreSQL 9.5 with psycopg2.
The examples are:
A. triggers exception:
with transaction.manager:
collections = dbsession.query(Collection).all()
for collection in collections:
image_ids = [i.id for i in collection.images.all()]
image_ids_string = ','.join(sorted(image_ids)) + collection.name
collection.id = md5(image_ids_string)[:16]
B. does not trigger exception
collections = dbsession.query(Collection).all()# ^ and v only these two lines
are swapped with transaction.manager:
for collection in collections:
image_ids = [i.id for i in collection.images.all()]
image_ids_string = ','.join(sorted(image_ids)) + collection.name
collection.id = md5(image_ids_string)[:16]
C. also does not trigger exception
collections = dbsession.query(Collection).all()
for collection in collections:
image_ids = [i.id for i in collection.images.all()]
image_ids_string = ','.join(sorted(image_ids)) + collection.name
with transaction.manager:
collection.id = md5(image_ids_string)[:16]
The exception for the first one is:
sqlalchemy.exc.IntegrityError: (psycopg2.IntegrityError) update or delete
on table "collections" violates foreign key constraint
"fk_collections_images_collection_id_collections" on table
"collections_images" DETAIL: Key (id)=(jC3sN8952urTGrqz) is still
referenced from table "collections_images".
I've also tried onupdate='CASCADE' for both columns in collections_images
but didn't change anything.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
