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+...@googlegroups.com
> <mailto:
sqlalchemy+...@googlegroups.com>.
> To post to this group, send email to
sqlal...@googlegroups.com
> <mailto:
sqlal...@googlegroups.com>.
> Visit this group at
https://groups.google.com/group/sqlalchemy.
> For more options, visit
https://groups.google.com/d/optout.