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.
