Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

[Zsolt Ero]

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  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  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())
>>>
>>>
>>> 

Re: [sqlalchemy] questions regarding entity default load strategies

[Gerald Thibault]

> here are also "wildcard" keys but 
I don't think those cover exactly the use case you're trying to do, 
which is basically "Order.items everywhere in the query"

If I uncomment the two commented out lines in my test, I can do 
session.query(User) and it is able to properly handle the join regardless 
of where it occurs. Is there not a way to make the Load() system behave the 
same way?

user = relationship(User, backref=backref('orders', lazy='subquery'))

...

order = relationship(Order, backref=backref('items', lazy='joined'))

will work. Both are operating on a specific relationship, but when I try

query = Query(User).options(
Load(User).subqueryload('orders'),
Load(Order).joinedload('items'),
  )


It is unable to determine that Load(User).subqueryload('orders') is 
referring to User.orders? Am I just misunderstanding the meaning of the arg 
being passed to Load()?

Does the arg refer to which base entity is being queried, rather than the 
current 'node' in the chain?

If I use Query(User), will it only load strategies that are created using 
Load(User)?

And when a subquery is issued, does it still retain the same base entity? I 
thought maybe a subquery for orders would have 'Order' as the base, and 
trigger the load strategies which were set with Load(Order), but they 
aren't touch at all.

Thanks for the quick reply, you're totally awesome (as always).

On Wednesday, February 15, 2017 at 2:34:47 PM UTC-8, Mike Bayer wrote:
>
>
>
> On 02/15/2017 04:39 PM, Gerald Thibault wrote: 
> > I have 3 classes, like so: 
> > 
> > | 
> > class User(Base): 
> >   __tablename__ = 'users' 
> >   id = Column(Integer, primary_key=True) 
> > 
> > class Order(Base): 
> >   __tablename__ = 'orders' 
> >   id = Column(Integer, primary_key=True) 
> >   user_id = Column(Integer, ForeignKey(User.id)) 
> >   user = relationship(User, backref=backref('orders')) 
> >   #user = relationship(User, backref=backref('orders', lazy='subquery')) 
> > 
> > class Item(Base): 
> >   __tablename__ = 'items' 
> >   id = Column(Integer, primary_key=True) 
> >   order_id = Column(Integer, ForeignKey(Order.id)) 
> >   order = relationship(Order, backref=backref('items')) 
> >   #order = relationship(Order, backref=backref('items', lazy='joined')) 
> > | 
> > 
> > The commented out variations of the relationships are the working ones, 
> > which allow me to do 
> > 
> > | 
> > results = session.query(User).all() 
> > | 
> > 
> > and have it grab the users, then the join between the orders and items 
> > in a second subquery. 
> > 
> > I have been trying to reproduce this behavior using the per-entity 
> > default loading strategies described 
> > at 
> http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#per-entity-default-loading-strategies
>  
> > but have been unable to get the same behavior. 
> > 
> > This works: 
> > | 
> > session.query(User).options( 
> >   Load(User).subqueryload('orders').joinedload('items') 
> > ) 
> > | 
> > 
> > But I am trying to build something programmatically, so I'm hoping to 
> > avoid the chaining. What I want to work, but does not, is this: 
> > 
> > | 
> > session.query(User).options( 
> >   Load(User).subqueryload('orders'), 
> >   Load(Order).joinedload('items'), 
>
> Well the "chaining" is needed to the degree that it matches the "paths" 
> being loaded.  So here's some other ways that would work: 
>
> query(User).options( 
>  Load(User).subqueryload('orders'), 
>  Load(User).defaultload('orders').joinedload('items') 
> ) 
>
>
> query(User).options( 
>  subqueryload("orders"), 
>  joinedload("orders.items") 
> ) 
>
>
> But you can see, there's no way to refer to Order without qualifying 
> that this is coming from the User.orders relationship.  Because your 
> query could be referring to Order in any number of ways simultaneously, 
> the paths have to match up, the paths here being: 
>
> User 
> User/orders 
> User/orders/items 
>
>
> > 
> > Is it possible to use the Load(...) system to replicate the behavior of 
> > the lazy attribute provided to a relationship, as in, when the query is 
> > constructed, it behaves _exactly_ as if the value provided to Load(...) 
> > was actually set as the 'lazy' keyword of the attribute? 
>
> the loader options that you send to options() are a mirror of the 
> arguments you send to the "lazy" keyword on relationship, but the 
> options need to know what relationship() they're referring towards, so 
> that's why the path thing is there.  There are also "wildcard" keys but 
> I don't think those cover exactly the use case you're trying to do, 
> which is basically "Order.items everywhere in the query"; I can see how 
> that would be possible but I don't believe there's a direct route to 
> that without inspecting the mappings. 
>
> As far as the "paths", there are ways to progammatically figure them 
> out. If you had a User class and said, "give me all the relationships 
> that refer to Order", this can be done using the 
> 

Re: [sqlalchemy] questions regarding entity default load strategies

[mike bayer]

On 02/15/2017 04:39 PM, Gerald Thibault wrote:

I have 3 classes, like so:

|
class User(Base):
  __tablename__ = 'users'
  id = Column(Integer, primary_key=True)

class Order(Base):
  __tablename__ = 'orders'
  id = Column(Integer, primary_key=True)
  user_id = Column(Integer, ForeignKey(User.id))
  user = relationship(User, backref=backref('orders'))
  #user = relationship(User, backref=backref('orders', lazy='subquery'))

class Item(Base):
  __tablename__ = 'items'
  id = Column(Integer, primary_key=True)
  order_id = Column(Integer, ForeignKey(Order.id))
  order = relationship(Order, backref=backref('items'))
  #order = relationship(Order, backref=backref('items', lazy='joined'))
|

The commented out variations of the relationships are the working ones,
which allow me to do

|
results = session.query(User).all()
|

and have it grab the users, then the join between the orders and items
in a second subquery.

I have been trying to reproduce this behavior using the per-entity
default loading strategies described
at 
http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#per-entity-default-loading-strategies
but have been unable to get the same behavior.

This works:
|
session.query(User).options(
  Load(User).subqueryload('orders').joinedload('items')
)
|

But I am trying to build something programmatically, so I'm hoping to
avoid the chaining. What I want to work, but does not, is this:

|
session.query(User).options(
  Load(User).subqueryload('orders'),
  Load(Order).joinedload('items'),


Well the "chaining" is needed to the degree that it matches the "paths" 
being loaded.  So here's some other ways that would work:


query(User).options(
Load(User).subqueryload('orders'),
Load(User).defaultload('orders').joinedload('items')
)


query(User).options(
subqueryload("orders"),
joinedload("orders.items")
)


But you can see, there's no way to refer to Order without qualifying 
that this is coming from the User.orders relationship.  Because your 
query could be referring to Order in any number of ways simultaneously, 
the paths have to match up, the paths here being:


User
User/orders
User/orders/items




Is it possible to use the Load(...) system to replicate the behavior of
the lazy attribute provided to a relationship, as in, when the query is
constructed, it behaves _exactly_ as if the value provided to Load(...)
was actually set as the 'lazy' keyword of the attribute?


the loader options that you send to options() are a mirror of the 
arguments you send to the "lazy" keyword on relationship, but the 
options need to know what relationship() they're referring towards, so 
that's why the path thing is there.  There are also "wildcard" keys but 
I don't think those cover exactly the use case you're trying to do, 
which is basically "Order.items everywhere in the query"; I can see how 
that would be possible but I don't believe there's a direct route to 
that without inspecting the mappings.


As far as the "paths", there are ways to progammatically figure them 
out. If you had a User class and said, "give me all the relationships 
that refer to Order", this can be done using the 
inspect(User).relationships collection.The information is there 
you'd just need to traverse it.


A little tricky so here's a POC:

from sqlalchemy import *
from sqlalchemy.orm import *
from sqlalchemy.ext.declarative import declarative_base

Base = declarative_base()


class User(Base):
__tablename__ = 'users'
id = Column(Integer, primary_key=True)


class Order(Base):
__tablename__ = 'orders'
id = Column(Integer, primary_key=True)
user_id = Column(Integer, ForeignKey(User.id))
user = relationship(User, backref=backref('orders'))


class Item(Base):
__tablename__ = 'items'
id = Column(Integer, primary_key=True)
order_id = Column(Integer, ForeignKey(Order.id))
order = relationship(Order, backref=backref('items'))


def find_all_order(query):
options = []
seen = set()

def _add_order(ent, path=()):
ent = inspect(ent)
if ent is inspect(Order):
print("Appending joinedload(%s.items" % (".".join(path), ))
options.append(
joinedload(
"%s.items" % (".".join(path), )
)
)

for rel in ent.relationships:
if rel in seen:
continue
seen.add(rel)
_add_order(rel.mapper, path + (rel.key, ))

for desc in query.column_descriptions:
_add_order(desc['entity'])

return query.options(*options)

e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)

s = Session(e)

s.add_all([
User(orders=[
Order(items=[Item(), Item()]),
Order(items=[Item()])
]),
User(orders=[Order(items=[Item()])])
])
s.commit()

q = s.query(User).options(subqueryload(User.orders))
q = find_all_order(q)

for user in q:
print user
for order in user.orders:

[sqlalchemy] Re: questions regarding entity default load strategies

[Gerald Thibault]

Here is a minimal script which shows what I'm trying to do and where things 
are going wrong.

-- 
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.
from sqlalchemy import *
from sqlalchemy import event
from sqlalchemy.engine import Engine
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import (backref, relationship, joinedload, subqueryload,
sessionmaker)
from sqlalchemy.orm.query import Query
from sqlalchemy.orm.strategy_options import Load


engine = create_engine('sqlite:///test.db')
Base = declarative_base(bind=engine)
Session = sessionmaker(bind=engine)

class User(Base):
  __tablename__ = 'users'
  id = Column(Integer, primary_key=True)

class Order(Base):
  __tablename__ = 'orders'
  id = Column(Integer, primary_key=True)
  user_id = Column(Integer, ForeignKey(User.id))
  user = relationship(User, backref=backref('orders'))
  #user = relationship(User, backref=backref('orders', lazy='subquery'))

class Item(Base):
  __tablename__ = 'items'
  id = Column(Integer, primary_key=True)
  order_id = Column(Integer, ForeignKey(Order.id))
  order = relationship(Order, backref=backref('items'))
  #order = relationship(Order, backref=backref('items', lazy='joined'))

class QueryCounter(object):
  __slots__ = ['count']

  def __enter__(self):
event.listen(Engine, 'before_cursor_execute', self.callback)
self.count = 0
return self

  def __exit__(self, *exc):
event.remove(Engine, 'before_cursor_execute', self.callback)
return False

  def callback(self, *args, **kwargs):
self.count += 1
counter = QueryCounter()

def populate():
  session = Session()
  user = session.query(User).first()
  if not user:
user = User()
order = Order(user=user)
item = Item(order=order)
session.add(item)
session.commit()
  session.close()

def run_test(name, query):
  print 'Running test %r' % name
  session = Session()
  with counter:
user = query.with_session(session).first()
for order in user.orders:
  for item in order.items:
pass
  try:
assert counter.count == 2
print '  test passed'
  except:
print '  test failed (expected 2 queries, %d were issued)' % counter.count
  session.close()

if __name__ == '__main__':
  Base.metadata.drop_all()
  Base.metadata.create_all()
  populate()

  #engine.echo = True

  # bound chained strats work
  query = Query(User).options(
Load(User)
  .subqueryload('orders')
  .joinedload('items')
  )
  run_test('bound/chained', query)

  # unbound chained strats also work
  query = Query(User).options(
subqueryload('orders').joinedload('items')
  )
  run_test('unbound/chained', query)

  # attempting to set an entity default does not work
  query = Query(User).options(
Load(User).subqueryload('orders'),
Load(Order).joinedload('items'),
  )
  run_test('entity-default 1', query)

  # this also doesn't work
  query = (Query(User)
.options(Load(User).subqueryload('orders'))
.options(Load(Order).joinedload('items'))
  )
  run_test('entity-default 2', query)

[sqlalchemy] questions regarding entity default load strategies

[Gerald Thibault]

I have 3 classes, like so:

class User(Base):
  __tablename__ = 'users'
  id = Column(Integer, primary_key=True)

class Order(Base):
  __tablename__ = 'orders'
  id = Column(Integer, primary_key=True)
  user_id = Column(Integer, ForeignKey(User.id))
  user = relationship(User, backref=backref('orders'))
  #user = relationship(User, backref=backref('orders', lazy='subquery'))

class Item(Base):
  __tablename__ = 'items'
  id = Column(Integer, primary_key=True)
  order_id = Column(Integer, ForeignKey(Order.id))
  order = relationship(Order, backref=backref('items'))
  #order = relationship(Order, backref=backref('items', lazy='joined'))

The commented out variations of the relationships are the working ones, 
which allow me to do

results = session.query(User).all()

and have it grab the users, then the join between the orders and items in a 
second subquery.

I have been trying to reproduce this behavior using the per-entity default 
loading strategies described 
at 
http://docs.sqlalchemy.org/en/latest/orm/loading_relationships.html#per-entity-default-loading-strategies
 
but have been unable to get the same behavior.

This works:
session.query(User).options(
  Load(User).subqueryload('orders').joinedload('items')
)

But I am trying to build something programmatically, so I'm hoping to avoid 
the chaining. What I want to work, but does not, is this:

session.query(User).options(
  Load(User).subqueryload('orders'),
  Load(Order).joinedload('items'),
)

I'm trying to have each class able to return its own load strategy options, 
which i could then feed into options.

Is it possible to use the Load(...) system to replicate the behavior of the 
lazy attribute provided to a relationship, as in, when the query is 
constructed, it behaves _exactly_ as if the value provided to Load(...) was 
actually set as the 'lazy' keyword of the attribute? I poked around in the 
source a bit, and with my failed attempt, JoinedLoader.__init__ is never 
even called. The subquery is issued, but is not joined against anything. 
I'm not sure how to make this work.

-- 
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.

Re: [sqlalchemy] Using `INSERT...ON CONFLICT` with ORM

[Mike Bayer]

Well first off the ORM on the persistence side only identifies objects by
primary key.   Is "bar" the primary key here ?  That would be one
requirement.

The semantics of INSERT on conflict most closely match those of
Session.merge().   If you're dealing with primary key, merge will do this
operation right now but it uses separate SELECT and INSERT/UPDATE.  an ORM
integration of merge() and INSERT on conflict would need some very
motivated contributors to come on board and help implement and test.  It
can be done as a third party extension to start with.

On Feb 15, 2017 2:13 PM, "Calvin Young"  wrote:

> I use the SQLAlchemy ORM in my application, and I know I can use something
> the following to perform an `INSERT...ON CONFLICT` statement:
>
> from sqlalchemy.dialects.postgresql import insert
>
>
>
> class Foo(Base):
>   ...
>   bar = Column(Integer)
>
>
> foo = Foo(bar=1)
>
>
> insert_stmt = insert(Foo).values(bar=foo.bar)
> do_update_stmt = insert_stmt.on_conflict_do_update(
> set_=dict(
> bar=insert_stmt.excluded.bar,
> )
> )
>
> session.execute(do_update_stmt)
>
> Is there a better solution that doesn't require dropping into the
> Expression Language? It'd be great if we had a solution that automatically
> detected the fields that need to be inserted / update, and that
> automatically refreshed the `foo` instance after the committing to the db.
>
> --
> 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.
>

-- 
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.

[sqlalchemy] Using `INSERT...ON CONFLICT` with ORM

[Calvin Young]

I use the SQLAlchemy ORM in my application, and I know I can use something 
the following to perform an `INSERT...ON CONFLICT` statement:

from sqlalchemy.dialects.postgresql import insert



class Foo(Base):
  ...
  bar = Column(Integer)


foo = Foo(bar=1)


insert_stmt = insert(Foo).values(bar=foo.bar)
do_update_stmt = insert_stmt.on_conflict_do_update(
set_=dict(
bar=insert_stmt.excluded.bar,
)
)

session.execute(do_update_stmt)

Is there a better solution that doesn't require dropping into the 
Expression Language? It'd be great if we had a solution that automatically 
detected the fields that need to be inserted / update, and that 
automatically refreshed the `foo` instance after the committing to the db.

-- 
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.

Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

[mike bayer]

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  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 = 

Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

[Zsolt Ero]

Thank you for the explanation! It is indeed deeper than I first
thought, but I understand it now.

Finally, consider question 4. (multiple select on delete) to be a bug
report, even if harmless.



On 15 February 2017 at 17:07, mike bayer  wrote:
> a "delete" cascade can be done on the client side because it involves a
> simple ordering of steps:
>
> 1. DELETE the rows that depend on the target row
>
> 2. DELETE the target row.
>
>
> an "update" cascade, OTOH, can't work this way.  Because the dependent row
> remains existing and needs to have a primary key value at all times, and
> that primary key needs to correspond to a row in the target table. The only
> way to do this client side (without disabling or dropping the constraints
> themselves) would be:
>
> 1. INSERT a new row into the target table with the new primary key value
>
> 2. UPDATE the rows that depend on the target row
>
> 3. DELETE the old row from the target table
>
> For a simple UPDATE of the target table, this is not feasible; INSERT/DELETE
> is a very different operation than an UPDATE at many levels; data wise,
> isolation/concurrency-wise, etc.  Only "ON UPDATE CASCADE" configured on the
> server level can accommodate the flow as an UPDATE on the target table,
> where Postgresql internally handles the cascading of the primary key change
> to all dependent tables without violating referential integrity.
>
>
>
>
> On 02/15/2017 11:02 AM, Zsolt Ero wrote:
>>
>> 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  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 
 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 

Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

[mike bayer]

a "delete" cascade can be done on the client side because it involves a 
simple ordering of steps:


1. DELETE the rows that depend on the target row

2. DELETE the target row.


an "update" cascade, OTOH, can't work this way.  Because the dependent 
row remains existing and needs to have a primary key value at all times, 
and that primary key needs to correspond to a row in the target table. 
The only way to do this client side (without disabling or dropping the 
constraints themselves) would be:


1. INSERT a new row into the target table with the new primary key value

2. UPDATE the rows that depend on the target row

3. DELETE the old row from the target table

For a simple UPDATE of the target table, this is not feasible; 
INSERT/DELETE is a very different operation than an UPDATE at many 
levels; data wise, isolation/concurrency-wise, etc.  Only "ON UPDATE 
CASCADE" configured on the server level can accommodate the flow as an 
UPDATE on the target table, where Postgresql internally handles the 
cascading of the primary key change to all dependent tables without 
violating referential integrity.




On 02/15/2017 11:02 AM, Zsolt Ero wrote:

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  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  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'

Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

[Zsolt Ero]

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  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  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:
>>> 

Re: [sqlalchemy] Unable to use the 'mock' strategy: AttributeError: 'MockConnection' object has no attribute 'run_callable'

[Manuel]

mike bayer  writes:
> On 02/14/2017 11:02 AM, Manuel wrote:
>> Thanks, I'm still in the early stages of this project and any comments
>> are highly appreciated.  What I'm trying to actually accomplish is to
>> build some complex queries to be executed against an Odoo [1] DB.  Odoo
>> has it's own ORM, but it lacks some features I like the most about
>> SQLAlchemy:
>>
>> - A clear API to define custom mappings.
>>
>> - A clear low-level API to create SQL-like sentences (even coupled to
>>   PostgreSQL) that would be a highly appreciated.
>>
>>   See [2] for a hard to maintain and test method.
>>
>> I'd keep Odoo's models for the description of the DB layer.  But I would
>> like more flexibility to represent the Python-side of some models.
>>
>> Using the 'mock' strategy I thought I could run the SQL myself like and
>> funnel the SQL execution back to Odoo's cursors.  Something like::
>
>
> if Odoo gives you a "cursor", that implies you'd produce a dialect for Odoo.
> Dialects can be produced for anything, while a pep249 DBAPI is the easiest, it
> is possible to create limited dialects against anything else.
>
> The most exotic example is my proof of concept dialect against Pandas
> dataframes: https://bitbucket.org/zzzeek/calchipan/ .  It doesn't use SQL at
> all, the SQL compiler produces objects that work on Pandas objects.
>
>
>>
>>   def execute(self, sql, *params, **other):
>>  # self.obj.cr is wrapper around pyscopg2's cursor
>>  self.obj.cr.execute(sql, params)  # How to merge params and other?
>>  return do_something_with(self.obj.cr.fetchall())
>>
>> If the 'executor' returns a ResultProxy-like, the 'mock' strategy would
>> work?  If it should work, then the problem would be to create a
>> ResultProxy compliant object that bridges Odoo's world to SA's.
>
> "mock" is really a very quick one-off that isn't going to do much outside of
> grabbing simple DDL.If you're looking to create full front-to-back
> SQLAlchemy round trips over Odoo, your best bet is the dialect, buliding on
> top of a pep-249-style DBAPI implementation against whatever Odoo provides.
>

I've cloned calchipan, and also looking at the implementation of the
standard dialects.  Let's see if something comes up.

Thanks and best regards,
Manuel.

-- 
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.

Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

[mike bayer]

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  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

Re: [sqlalchemy] Changing primary key's value in a multi-to-multi relation

[Zsolt Ero]

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  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
>>
>>