Can anyone help with these questions? Nothing urgent, I'm just interested in understanding more about SQLAlchemy.
On 16 February 2017 at 04:07, Zsolt Ero <zsolt....@gmail.com> wrote: > I'm starting to understand it! Just a few very quick questions: > > 1. Is it a good strategy to always use onupdate='CASCADE', > ondelete='CASCADE' for all foreign keys if the db supports it (in my > case Postgres 9.5 + psycopg2)? > > 2. If I'd like to use it on an already populated db, can I "alter > table" to use these CASCADE options (for example in an alembic > migration)? > > 3. For delete, is this simply an optimisation step and the default > behaviour is also perfectly fine? > > 4. Simply adding the above parameters is not automatically changing > SQLAlchemy's delete strategy, I also need to tell it to use passive > deletes. Do I also need the cascade="all, delete-orphan" option like > in the docs? > > Zsolt > > > On 15 February 2017 at 17:25, mike bayer <mike...@zzzcomputing.com> wrote: >> >> >> On 02/15/2017 09:45 AM, Zsolt Ero wrote: >>> >>> 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. >> >> >> the ORM only deletes rows one at a time based on primary key match. So if >> you have a relationship() that is configured to cascade deletes, and you >> have not instructed the system that "ON DELETE CASCADE" will take care of >> those collections, it will need to ensure these collections are present in >> memory (e.g. the SELECT) and target each row for deletion individually. You >> see only one DELETE statement but you'll note it has multiple parameter sets >> to indicate every row being deleted. Background on how to optimize this is >> at >> http://docs.sqlalchemy.org/en/latest/orm/collections.html#using-passive-deletes >> . >> >> In this specific case there seem to be two SELECT statements but that >> appears to be because of the awkward production of a new object that has the >> same primary key as another object. In the logs you'll see an UPDATE but >> this is actually a special case "hack"; normally, we'd see a DELETE of the >> old row and an INSERT of the new one, however the unit of work does not >> support this process. There is an option to allow it to work this way in >> specific cases, although this feature is not present in SQLAlchemy at this >> time. In the absence of that feature, the behavior is that if the same >> primary key is present on one object being deleted and another one being >> added in the same flush, they are rolled into an UPDATE. Then the >> collection is being deleted and re-added again too, so this is a bit of a >> crazy example; using a straight UPDATE with correct cascade rules is >> obviously much more efficient. >> >> >> >>> >>> 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 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.
