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 <mike...@zzzcomputing.com> 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 sqlalchemy+unsubscr...@googlegroups.com
>> <mailto:sqlalchemy+unsubscr...@googlegroups.com>.
>> To post to this group, send email to sqlalchemy@googlegroups.com
>> <mailto:sqlalchemy@googlegroups.com>.
>> 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
> 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.
--
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.
delete.log
Description: Binary data
#!/usr/bin/env python
# pip install transaction zope.sqlalchemy psycopg2
import hashlib
import transaction
import zope.sqlalchemy
from random import SystemRandom
from sqlalchemy import Column, Unicode, ForeignKey, String, Table
from sqlalchemy.engine import create_engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import configure_mappers, sessionmaker, relationship
from sqlalchemy.schema import MetaData
rng = SystemRandom()
metadata = MetaData()
Base = declarative_base(metadata=metadata)
def get_session_factory(engine):
factory = sessionmaker()
factory.configure(bind=engine)
return factory
def get_tm_session(session_factory, transaction_manager):
dbsession = session_factory()
zope.sqlalchemy.register(dbsession, transaction_manager=transaction_manager)
return dbsession
def md5(str):
m = hashlib.md5()
m.update(str)
return m.hexdigest()
def random_string(length, allowed_chars='abcdefghijklmnopqrstuvwxyz'
'ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789'):
return ''.join(rng.choice(allowed_chars) for _ in range(length))
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')
class Collection(Base):
__tablename__ = 'collections'
id = Column(String, primary_key=True, default=lambda: random_string(16))
name = Column(Unicode(400), nullable=False)
images = relationship('Image', secondary='collections_images', back_populates='collections', lazy='dynamic')
Table('collections_images', Base.metadata,
Column('collection_id', ForeignKey('collections.id'), primary_key=True, onupdate='CASCADE'),
Column('image_id', ForeignKey('images.id'), primary_key=True))
if __name__ == '__main__':
configure_mappers()
# db
engine = create_engine('postgresql+psycopg2://test@/test', echo=True)
old_metadata = MetaData(engine, True)
old_metadata.drop_all(engine)
Base.metadata.create_all(engine)
session_factory = get_session_factory(engine)
dbsession = get_tm_session(session_factory, transaction.manager)
with transaction.manager:
c1 = Collection(name=u'c1', images=[Image(id='1a'), Image(id='1b'), Image(id='1c')])
c2 = Collection(name=u'c2', images=[Image(id='2a'), Image(id='2b'), Image(id='2c')])
c3 = Collection(name=u'c3', images=[Image(id='3a'), Image(id='3b'), Image(id='3c')])
dbsession.add(c1)
dbsession.add(c2)
dbsession.add(c3)
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
new_id = md5(image_ids_string)[:16]
# this one gives IntegrityError with or without CASCADE
collection.id = new_id
# this one works even without CASCADE, but is a manual hack
# new_collection = Collection(id=new_id, name=collection.name,
# images=collection.images)
# dbsession.add(new_collection)
# dbsession.delete(collection)
update.log
Description: Binary data
