Thanks a lot! I would be still interested in your answer for 3. and 4. Especially, what is the difference between update and delete from's behaviour here? Why does SQLAlchemy know how to "cascade" a delete just on the client side, while for update it needs server side CASCADE support?
On 15 February 2017 at 16:51, mike bayer <[email protected]> wrote: > onupdate=CASCADE is an option of ForeignKey, not Column: > Table( > 'collections_images', Base.metadata, > Column('collection_id', > ForeignKey('collections.id', onupdate='CASCADE'), > primary_key=True, ), > Column('image_id', ForeignKey('images.id'), primary_key=True)) > > > > > On 02/15/2017 09:45 AM, Zsolt Ero wrote: >> >> Thanks Mike for looking into this. >> >> I've created a minimal program which reproduces my error. As a >> context, this is a init script for a Pyramid app, but is run from >> command line, not in a request loop. The tables are dropped and >> recreated at start. Inklesspen helped me figure out the transaction >> manager over IRC and I've simplified it into one single block which is >> both simpler and more reliable. >> >> So about SQLAlchemy's behaviour: >> >> 1. I do not see anything related to CASCADE in echo when I use >> onupdate='CASCADE'. >> 2. Update does not work in my case, logs attached. >> 3. A manual hack of creating a new collection and deleting the old one >> does work. It means that delete does not need CASCADE, but SQLAlchemy >> can calculate the order of calls, if I understand right? >> 4. An interesting thing is that SQLAlchemy does 3 select calls in the >> delete case, even if 1 would be enough. Can be seen in the logs. >> >> Zsolt >> >> >> >> >> >> On 15 February 2017 at 04:17, mike bayer <[email protected]> wrote: >>> >>> >>> >>> On 02/14/2017 08:15 PM, Zsolt Ero wrote: >>>> >>>> >>>> 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. >>> >>> >>> >>> the examples seem to move "transaction.manager" around, which we assume >>> is >>> the Zope transaction manager and that by using the context manager the >>> Session.commit() method is ultimately called, which raises this error. >>> One >>> guess is that in the second two examples, the Session is not actually >>> getting committed, because no invocation of "dbsession" is present within >>> the "with transaction.manager" block and I have a vague recollection that >>> zope.transaction might work this way. Another guess is that in the >>> second >>> two examples, maybe you already changed the data in the DB and the >>> operation >>> you're doing has no net change to the rows. >>> >>> In any case, all three examples you should echo the SQL emitted so you >>> can >>> see what it's doing. Setting up the onupdate="CASCADE" should fix this >>> problem. As to why that didn't work from you, keep in mind that is a >>> CREATE >>> TABLE directive so if you just changed it in your model and didn't >>> recreate >>> the tables, or at least recreate the foreign key constraints using ALTER >>> to >>> drop and create them again with the CASCADE rule set up; this is a server >>> side rule. >>> >>> Here's the MCVE to demonstrate: >>> >>> from sqlalchemy import * >>> from sqlalchemy.orm import * >>> from sqlalchemy.ext.declarative import declarative_base >>> import md5 as _md5 >>> import random >>> import string >>> >>> >>> def md5(text): >>> return str(_md5.md5(text)) >>> >>> >>> def random_string(num): >>> return ''.join(random.choice( >>> string.ascii_uppercase + string.digits) for _ in range(num)) >>> >>> Base = declarative_base() >>> >>> >>> class Image(Base): >>> __tablename__ = 'images' >>> id = Column(String, primary_key=True, default=lambda: >>> random_string(16)) >>> collections = relationship( >>> 'Collection', secondary='collections_images', >>> back_populates='images') >>> date_created = Column(DateTime, default=func.now()) >>> >>> >>> class Collection(Base): >>> __tablename__ = 'collections' >>> id = Column(String, primary_key=True, default=lambda: >>> random_string(16)) >>> name = Column(String) >>> images = relationship( >>> 'Image', secondary='collections_images', >>> back_populates='collections', >>> order_by='desc(Image.date_created)', >>> lazy='dynamic') >>> >>> >>> collections_images = Table( >>> 'collections_images', Base.metadata, >>> Column('collection_id', >>> ForeignKey('collections.id', onupdate="CASCADE"), >>> primary_key=True), >>> Column('image_id', ForeignKey('images.id'), primary_key=True) >>> ) >>> >>> e = create_engine("postgresql://scott:tiger@localhost/test", echo=True) >>> >>> Base.metadata.drop_all(e) >>> Base.metadata.create_all(e) >>> >>> s = Session(e) >>> >>> with s.transaction: >>> s.add(Collection(name='c1', images=[Image(), Image(), Image()])) >>> >>> with s.transaction: >>> collections = s.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] >>> >>> >>> >>> >>>> >>>> I have the following models: >>>> >>>> |classImage(Base):id >>>> >>>> >>>> =Column(String,primary_key=True,default=lambda:random_string(16))collections >>>> >>>> >>>> =relationship('Collection',secondary='collections_images',back_populates='images')classCollection(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: >>>> >>>> |withtransaction.manager:collections >>>> =dbsession.query(Collection).all()forcollection incollections:image_ids >>>> =[i.id fori incollection.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 withtransaction.manager:forcollection >>>> incollections:image_ids =[i.id fori >>>> incollection.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()forcollection >>>> incollections:image_ids =[i.id fori >>>> incollection.images.all()]image_ids_string >>>> =','.join(sorted(image_ids))+collection.name >>>> withtransaction.manager:collection.id =md5(image_ids_string)[:16]| >>>> >>>> >>>> The exception for the first one is: >>>> >>>> | >>>> sqlalchemy.exc.IntegrityError:(psycopg2.IntegrityError)update ordeleteon >>>> table "collections"violates foreign key constraint >>>> "fk_collections_images_collection_id_collections"on table >>>> "collections_images"DETAIL:Key(id)=(jC3sN8952urTGrqz)isstill referenced >>>> fromtable "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 [email protected] >>>> <mailto:[email protected]>. >>>> To post to this group, send email to [email protected] >>>> <mailto:[email protected]>. >>>> Visit this group at https://groups.google.com/group/sqlalchemy. >>>> For more options, visit https://groups.google.com/d/optout. >>> >>> >>> >>> -- >>> 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 a topic in >>> the >>> Google Groups "sqlalchemy" group. >>> To unsubscribe from this topic, visit >>> https://groups.google.com/d/topic/sqlalchemy/breBc7iStF0/unsubscribe. >>> To unsubscribe from this group and all its topics, send an email to >>> [email protected]. >>> To post to this group, send email to [email protected]. >>> Visit this group at https://groups.google.com/group/sqlalchemy. >>> For more options, visit https://groups.google.com/d/optout. >> >> > > -- > 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 a topic in the > Google Groups "sqlalchemy" group. > To unsubscribe from this topic, visit > https://groups.google.com/d/topic/sqlalchemy/breBc7iStF0/unsubscribe. > To unsubscribe from this group and all its topics, send an email to > [email protected]. > To post to this group, send email to [email protected]. > Visit this group at https://groups.google.com/group/sqlalchemy. > For more options, visit https://groups.google.com/d/optout. -- 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 [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
