Re: [sqlalchemy] turning only loaded columns into a dict
On Wednesday, May 2, 2018 at 5:53:56 PM UTC-4, Mike Bayer wrote: > > if you only care about things that are loaded, like before, look in > inspect(instance).dict , that's what's loaded > Thanks. I'll migrate my proof-of-concept to use `inspect`. -- 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] turning only loaded columns into a dict
if you only care about things that are loaded, like before, look in
inspect(instance).dict , that's what's loaded
On Wed, May 2, 2018 at 4:24 PM, Jonathan Vanasco wrote:
> I have a mixin that helps convert object to JSON using a `columns_as_dict`
> method.
>
> it looks like this:
>
> from sqlalchemy.orm import class_mapper as sa_class_mapper
>
> class Mixin(object):
> def columns_as_dict(self):
> _cls = self.__class__
> return dict((col.name, getattr(self, col.name)) for col in
> sa_class_mapper(_cls).mapped_table.c)
>
>
> I pinpointed a performance issue where the db was getting hit when
> `load_only` was used on the objects.
>
> The simplest fix I could think of, is fetching column values from the
> object's dict instead of via getattr . Is there a more appropriate way?
>
> from sqlalchemy.orm import class_mapper as sa_class_mapper
>
> class Mixin(object):
> def columns_as_dict(self):
> _cls = self.__class__
> return {col.name: self.__dict__[col.name]
> for col in sa_class_mapper(_cls).mapped_table.c
> if col.name in self.__dict__
> }
>
>
> --
> 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.
Re: [sqlalchemy] Is it possible to call a stored procedure that takes a table-valued parameter with SQLAlchemy?
On Wed, May 2, 2018 at 4:12 PM, Cameron Hassler
wrote:
> I'm trying to execute a stored procedure on MSSQL Server that takes a
> single, table-valued parameter. The parameter itself is of a custom type
> "StringTable" defined like so:
>
> CREATE TYPE [dbo].[StringTable] AS TABLE([strValue] [nvarchar](max) NULL)
>
> Is it possible to execute this procedure using SQLAlchemy? I've spent hours
> with the documentation, StackOverflow and Google, and haven't come up with
> anything. FYI, this database is in control of another department in my
> company, and I can't modify anything there.
did you try:
with engine.connect() as conn:
conn.execute("stored_procedure(parameter)")
?
how do you invoke this procedure anywhere else? any examples?
>
> --
> 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.
Re: [sqlalchemy] Is FLOAT(53) the best way to guarantee a double-precision generic column type
FLOAT types will have floating point inaccuracy. I have no idea if 53 is actually supported, usually floating point accuracy starts to hit various limits at something like 10 digits. Usually the NUMERIC type is the best bet since it uses Python Decimal objects. You should work up some tests that try to round-trip 53 points of precision between all those backends. On Wed, May 2, 2018 at 2:02 PM, Van Klaveren, Brian N. wrote: > I'm trying to work on a universal table generator based on in-memory table > objects. The code is based on the work from the pandas to_sql. > > I'll be targeting Oracle, Postgres, MySQL, and SQLite for sure. > > It seems like making sure to use Float(53) is the best way to guarantee that > a column will be generated with a double-precision Floating point in all of > these database backends without introspecting the engine at runtime. > > Pandas does that here: > https://github.com/pandas-dev/pandas/blob/master/pandas/io/sql.py#L879 > > Is this generally true? > > Also, should I use the generic Float type, or the SQL FLOAT type? It doesn't > seem like there's a huge functional difference. > > Brian > > -- > 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.
Re: [sqlalchemy] How to override append and remove methods used in orm.relationships
On Wed, May 2, 2018 at 12:22 PM, Diego Quintana wrote:
> Hello, thanks again for your help. I'm not sure I understand what you said
> totally, and I believe this is the most simple MCVE I can provide.
>
> My local tests use postgresql, but I'm setting an in-memory sqlite3 engine
> here. I'm not fond of the differences between two backends, but the tests
> run without problems.
So this is great, and shows the problem. but what you are trying to
do here is deeply complicated.I was going to just type out
everything I did to figure this out but this was way too long a
process.
at the core is that when you remove a child from the parent in the
_remove_pets event, you want to prevent the _remove_children() event
from actually happening, I think.
If I remove a pet from a parent, then we remove the child from the
parent, and *only* that pet. we dont remove other pets that might be
associated with that child.
if I remove a child from the parent, then we remove *all* pets
associated with the child from that parent.
This seems like it's a contradiction. I have parent p1, not referring
to child c1, but it refers to pet p1 which *does* refer to child c1,
and that is valid. There's basically two flavors of "remove child
from parent", is that right?
I tried to work on an implementation here which would also have to be
extremely clever but I realized I don't actually understand what this
is supposed to do. if "remove child from parent" has two different
flavors then there needs to be all kinds of trickery to protect the
events from each other.
> import sqlalchemy as sa
> from sqlalchemy.ext.declarative import declarative_base
>
> Base = declarative_base()
>
> # many to many relationship between parents and children
> parents_children_relationship = sa.Table('parents_children_relationship',
> Base.metadata,
> sa.Column('parent_id', sa.Integer, sa.ForeignKey('parents.id')),
> sa.Column('child_id', sa.Integer, sa.ForeignKey('children.id')),
> sa.UniqueConstraint('parent_id', 'child_id'))
>
> # many to many relationship between User and Pet
> parents_pets_relationship = sa.Table('parents_pets_relationship',
> Base.metadata,
> sa.Column('parent_id', sa.Integer, sa.ForeignKey('parents.id')),
> sa.Column('pet_id', sa.Integer, sa.ForeignKey('pets.id')),
> sa.UniqueConstraint('parent_id', 'pet_id'))
>
> class Parent(Base):
> __tablename__ = 'parents'
>
> id = sa.Column(sa.Integer, primary_key=True)
> name = sa.Column(sa.String(64))
>
> # many to many relationship between parent and children
> # my case allows for a children to have many parents. Don't ask.
> children = sa.orm.relationship('Child',
> secondary=parents_children_relationship,
> backref=sa.orm.backref('parents',
> lazy='dynamic'),
> lazy='dynamic')
>
> # many to many relationship between parents and pets
> pets = sa.orm.relationship('Pet',
> secondary=parents_pets_relationship,
> backref=sa.orm.backref('parents',
> lazy='dynamic'), #
> lazy='dynamic')
>
>
> def __repr__(self):
> return '' % (self.name)
>
> class Child(Base):
> __tablename__ = 'children'
> id = sa.Column(sa.Integer, primary_key=True)
> name = sa.Column(sa.String(64))
> # parents =
>
> # one to many relationship with pets
> pets = sa.orm.relationship('Pet', backref='child', lazy='dynamic')
>
> def __repr__(self):
> return '' % (self.name)
>
> class Pet(Base):
> __tablename__ = 'pets'
> id = sa.Column(sa.Integer, primary_key=True)
> name = sa.Column(sa.String(64))
> # child = backref relationship with cities
> child_id = sa.Column(sa.Integer, sa.ForeignKey('children.id'),
> nullable=True)
> # parents =
>
> def __repr__(self):
> return '' % (self.name)
>
>
>
> @sa.event.listens_for(Parent.children, 'append')
> def _append_children(parent, child, initiator):
> """
> If a new child is appended to the parent, this listener
> will also add the pets bound to the child being bound to the parent.
> """
> # appends also the pets bound to the child that the
> # parent is being appended to
> parent.pets.extend(child.pets.all())
>
> @sa.event.listens_for(Parent.children, 'remove')
> def _remove_children(parent, child, initiator, *args, **kwargs):
> """
> If a child is removed from the parent, this listener
> will also remove only remove_single_pet -->
> """
>
> remove_single_pet = kwargs.get('remove_single_pet', None)
>
> if remove_single_pet is not None:
> parent.pets.remove(remove_single_pet)
> else: # removes every pet
> for pet in child.pets:
> parent.pets.remove(pet)
>
> @sa.event.listens_for(Parent.pets, 'remove')
> def _remove_pets(parent, pet, initiator, *args, **kwargs):
> """
>
[sqlalchemy] turning only loaded columns into a dict
I have a mixin that helps convert object to JSON using a `columns_as_dict`
method.
it looks like this:
from sqlalchemy.orm import class_mapper as sa_class_mapper
class Mixin(object):
def columns_as_dict(self):
_cls = self.__class__
return dict((col.name, getattr(self, col.name)) for col in
sa_class_mapper(_cls).mapped_table.c)
I pinpointed a performance issue where the db was getting hit when
`load_only` was used on the objects.
The simplest fix I could think of, is fetching column values from the
object's dict instead of via getattr . Is there a more appropriate way?
from sqlalchemy.orm import class_mapper as sa_class_mapper
class Mixin(object):
def columns_as_dict(self):
_cls = self.__class__
return {col.name: self.__dict__[col.name]
for col in sa_class_mapper(_cls).mapped_table.c
if col.name in self.__dict__
}
--
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] Is it possible to call a stored procedure that takes a table-valued parameter with SQLAlchemy?
I'm trying to execute a stored procedure on MSSQL Server that takes a single, table-valued parameter. The parameter itself is of a custom type "StringTable" defined like so: CREATE TYPE [dbo].[StringTable] AS TABLE([strValue] [nvarchar](max) NULL) Is it possible to execute this procedure using SQLAlchemy? I've spent hours with the documentation, StackOverflow and Google, and haven't come up with anything. FYI, this database is in control of another department in my company, and I can't modify anything there. -- 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] Is FLOAT(53) the best way to guarantee a double-precision generic column type
I'm trying to work on a universal table generator based on in-memory table objects. The code is based on the work from the pandas to_sql. I'll be targeting Oracle, Postgres, MySQL, and SQLite for sure. It seems like making sure to use Float(53) is the best way to guarantee that a column will be generated with a double-precision Floating point in all of these database backends without introspecting the engine at runtime. Pandas does that here: https://github.com/pandas-dev/pandas/blob/master/pandas/io/sql.py#L879 Is this generally true? Also, should I use the generic Float type, or the SQL FLOAT type? It doesn't seem like there's a huge functional difference. Brian -- 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] How to override append and remove methods used in orm.relationships
Hello, thanks again for your help. I'm not sure I understand what you said
totally, and I believe this is the most simple MCVE I can provide.
My local tests use postgresql, but I'm setting an in-memory sqlite3 engine
here. I'm not fond of the differences between two backends, but the tests
run without problems.
import sqlalchemy as sa
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
# many to many relationship between parents and children
parents_children_relationship = sa.Table('parents_children_relationship',
Base.metadata,
sa.Column('parent_id', sa.Integer, sa.ForeignKey('parents.id')),
sa.Column('child_id', sa.Integer, sa.ForeignKey('children.id')),
sa.UniqueConstraint('parent_id', 'child_id'))
# many to many relationship between User and Pet
parents_pets_relationship = sa.Table('parents_pets_relationship',
Base.metadata,
sa.Column('parent_id', sa.Integer, sa.ForeignKey('parents.id')),
sa.Column('pet_id', sa.Integer, sa.ForeignKey('pets.id')),
sa.UniqueConstraint('parent_id', 'pet_id'))
class Parent(Base):
__tablename__ = 'parents'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(64))
# many to many relationship between parent and children
# my case allows for a children to have many parents. Don't ask.
children = sa.orm.relationship('Child',
secondary=parents_children_relationship,
backref=sa.orm.backref('parents', lazy='dynamic'
),
lazy='dynamic')
# many to many relationship between parents and pets
pets = sa.orm.relationship('Pet',
secondary=parents_pets_relationship,
backref=sa.orm.backref('parents', lazy='dynamic'
), #
lazy='dynamic')
def __repr__(self):
return '' % (self.name)
class Child(Base):
__tablename__ = 'children'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(64))
# parents =
# one to many relationship with pets
pets = sa.orm.relationship('Pet', backref='child', lazy='dynamic')
def __repr__(self):
return '' % (self.name)
class Pet(Base):
__tablename__ = 'pets'
id = sa.Column(sa.Integer, primary_key=True)
name = sa.Column(sa.String(64))
# child = backref relationship with cities
child_id = sa.Column(sa.Integer, sa.ForeignKey('children.id'), nullable=
True)
# parents =
def __repr__(self):
return '' % (self.name)
@sa.event.listens_for(Parent.children, 'append')
def _append_children(parent, child, initiator):
"""
If a new child is appended to the parent, this listener
will also add the pets bound to the child being bound to the parent.
"""
# appends also the pets bound to the child that the
# parent is being appended to
parent.pets.extend(child.pets.all())
@sa.event.listens_for(Parent.children, 'remove')
def _remove_children(parent, child, initiator, *args, **kwargs):
"""
If a child is removed from the parent, this listener
will also remove only remove_single_pet -->
"""
remove_single_pet = kwargs.get('remove_single_pet', None)
if remove_single_pet is not None:
parent.pets.remove(remove_single_pet)
else: # removes every pet
for pet in child.pets:
parent.pets.remove(pet)
@sa.event.listens_for(Parent.pets, 'remove')
def _remove_pets(parent, pet, initiator, *args, **kwargs):
"""
If a pet is removed from the parent, and the parent also is related
to the child that has access to that pet, then
* removes relationship with the child, and
* keeps relationship with the remaining pets, except the one that was
removed
"""
if pet.child in parent.children.all():
remove_single_pet = pet
_remove_children(parent, pet.child, initiator, remove_single_pet)
test ###
import unittest
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
class BasicTestModelCase(unittest.TestCase):
def setUp(self):
e = create_engine("sqlite://", echo=True)
Base.metadata.create_all(e)
Session = sessionmaker(bind=e)
self.session = Session()
def tearDown(self):
# Base.metadata.drop_all()
pass
def test_child_pet_relationship_on_parents_combined(self):
"""
Test that a parent can be hold children and pets that don't
belong necessary to the child, given the behaviour tested in the
previous test.
"""
# create new parent
test_parent = Parent(name='test_parent')
child1 = Child(id=1,
name='FakeChild1')
child2 = Child(id=2,
name='FakeChild2')
pet1 = Pet(id=1,
name='FakePet1',
child_id=1)
Re: [sqlalchemy] How to override append and remove methods used in orm.relationships
On Wed, May 2, 2018 at 10:14 AM, Diego Quintana wrote:
> This worked.
>
> I'm trying to achieve some rather tricky behaviour, where
>
> Adding a children to some parent will also add the child's pets to the
> parent
> Removing a children from some parent will also remove every current
> relationship that the Parent has with such pet
> If upon removal of a pet from a Parent, there is a Pet.child that is also in
> Parent.children,
>
> remove that Child from Parent, but keep existing relationships in
> Parent.pets except the pet that is being removed
> else only remove the pet from the parent
>
>
> Some code I'm using for this is
>
> @db.event.listens_for(Parent.children, 'append')
> def _append_children(parent, child, initiator):
> """
> If a new child is appended to the parent, this listener
> will also add the pets bound to the child being bound to the parent.
> """
> # appends also the pets bound to the child that the
> # parent is being appended to
> parent.pets.extend(child.pets.all())
>
> @db.event.listens_for(Parent.children, 'remove')
> def _remove_children(parent, child, initiator, *args, **kwargs):
> """
> If a child is removed from the parent, this listener
> will also remove only remove_single_pet -->
> """
>
> remove_single_pet = kwargs.get('remove_single_pet', None)
>
> if remove_single_pet is not None:
> parent.pets.remove(remove_single_pet)
> else: # removes every pet
> for pet in child.pets:
> parent.pets.remove(pet)
>
>
> @db.event.listens_for(Parent.pets, 'remove')
> def _remove_pets(parent, pet, initiator, *args, **kwargs):
> """
> If a pet is removed from the parent, and the parent also is related
> to the child that has access to that pet, then
>
> * removes relationship with the child, and
> * keeps relationship with the remaining pets, except the one that was
> removed
> """
>
> if pet.child in parent.children.all():
> remove_single_pet = pet
> _remove_children(parent, pet.child, initiator, remove_single_pet)
>
>
> test.py
>
> def test_child_pet_relationship_on_parents(self):
>
>
> # create new parent
> test_parent = Parent(name='test_parent')
>
> # commit parent to the database
> db.session.add(test_parent)
> db.session.commit()
>
> child1 = Child(id=1,
> name='FakeChild1')
>
> child2 = Child(id=2,
> name='FakeChild2')
>
> pet1 = Pet(id=1,
>name='FakePet1',
>child_id=1)
>
> pet2 = Pet(id=2,
>name='FakePet2',
>child_id=2)
>
> pet3 = Pet(id=3,
>name='FakePet3',
>child_id=1)
>
> db.session.add(child1)
> db.session.add(child2)
> db.session.add(pet1)
> db.session.add(pet2)
> db.session.add(pet3)
>
> db.session.commit()
>
> # add parent to the child
> child1.parents.append(test_parent)
> # add parent to the child
> pet2.parents.append(test_parent)
>
> # persist changes in the db
> db.session.add(child1)
> db.session.add(pet2)
> db.session.commit()
>
> # check that previous relationships are intact
> self.assertTrue(child1.pets.all() == [pet1, pet3])
> self.assertTrue(child2.pets.all() == [pet2])
>
> # resultant elements should be only child1, its pets and the single
> Pet
> self.assertTrue(test_parent.children.all() == [child1])
> self.assertTrue(test_parent.pets.all() == [pet1, pet2, pet3])
>
> # remove child from parent
> pet3.parents.remove(test_parent)
>
> # resultant elements should be remaining pets, and no child
> self.assertTrue(test_parent.children.all() == [])
> self.assertTrue(test_parent.pets.all() == [pet1, pet2]) # pet2 was
> not touched,
> # but pet1
> should remain since only
> # pet3 was
> removed
> # child1
> should be also removed since
> #
> relationship is unbalanced, i.e.
> # user can't
> have access to a child if it
> # does not
> have access to all of the child's pets
>
>
>
> I'm having errors
>
> sqlalchemy.orm.exc.StaleDataError: DELETE statement on table
> 'parent_pets_relationship' expected to delete 1 row(s); Only 0 were matched.
it looks like you have an explicit association mapping on the
secondary table as described in the green box in the section
https://docs.sqlalchemy.org/en/latest/orm/basic_relationships.html#
Re: [sqlalchemy] How to override append and remove methods used in orm.relationships
This worked.
I'm trying to achieve some rather tricky behaviour, where
1. Adding a children to some parent will also add the child's pets to
the parent
2. Removing a children from some parent will also remove every current
relationship that the Parent has with such pet
3. If upon removal of a pet from a Parent, there is a Pet.child that is
also in Parent.children,
- remove that Child from Parent, but keep existing relationships in
Parent.pets *except the pet that is being removed*
- else only remove the pet from the parent
Some code I'm using for this is
@db.event.listens_for(Parent.children, 'append')
def _append_children(parent, child, initiator):
"""
If a new child is appended to the parent, this listener
will also add the pets bound to the child being bound to the parent.
"""
# appends also the pets bound to the child that the
# parent is being appended to
parent.pets.extend(child.pets.all())
@db.event.listens_for(Parent.children, 'remove')
def _remove_children(parent, child, initiator, *args, **kwargs):
"""
If a child is removed from the parent, this listener
will also remove only remove_single_pet -->
"""
remove_single_pet = kwargs.get('remove_single_pet', None)
if remove_single_pet is not None:
parent.pets.remove(remove_single_pet)
else: # removes every pet
for pet in child.pets:
parent.pets.remove(pet)
@db.event.listens_for(Parent.pets, 'remove')
def _remove_pets(parent, pet, initiator, *args, **kwargs):
"""
If a pet is removed from the parent, and the parent also is related
to the child that has access to that pet, then
* removes relationship with the child, and
* keeps relationship with the remaining pets, except the one that was
removed
"""
if pet.child in parent.children.all():
remove_single_pet = pet
_remove_children(parent, pet.child, initiator, remove_single_pet)
test.py
def test_child_pet_relationship_on_parents(self):
# create new parent
test_parent = Parent(name='test_parent')
# commit parent to the database
db.session.add(test_parent)
db.session.commit()
child1 = Child(id=1,
name='FakeChild1')
child2 = Child(id=2,
name='FakeChild2')
pet1 = Pet(id=1,
name='FakePet1',
child_id=1)
pet2 = Pet(id=2,
name='FakePet2',
child_id=2)
pet3 = Pet(id=3,
name='FakePet3',
child_id=1)
db.session.add(child1)
db.session.add(child2)
db.session.add(pet1)
db.session.add(pet2)
db.session.add(pet3)
db.session.commit()
# add parent to the child
child1.parents.append(test_parent)
# add parent to the child
pet2.parents.append(test_parent)
# persist changes in the db
db.session.add(child1)
db.session.add(pet2)
db.session.commit()
# check that previous relationships are intact
self.assertTrue(child1.pets.all() == [pet1, pet3])
self.assertTrue(child2.pets.all() == [pet2])
# resultant elements should be only child1, its pets and the single
Pet
self.assertTrue(test_parent.children.all() == [child1])
self.assertTrue(test_parent.pets.all() == [pet1, pet2, pet3])
# remove child from parent
pet3.parents.remove(test_parent)
# resultant elements should be remaining pets, and no child
self.assertTrue(test_parent.children.all() == [])
self.assertTrue(test_parent.pets.all() == [pet1, pet2]) # pet2 was
not touched,
# but pet1
should remain since only
# pet3 was
removed
# child1
should be also removed since
#
relationship is unbalanced, i.e.
# user
can't have access to a child if it
# does not
have access to all of the child's pets
I'm having errors
sqlalchemy.orm.exc.StaleDataError: DELETE statement on table
'parent_pets_relationship' expected to delete 1 row(s); Only 0 were matched.
and the logger says nothing much. I suspect I'm falling into some weird
recursion, calling the listener from another listener, where the second
call can't find something that was already deleted.
This might be a long shot, but I'm hoping this pattern might be solved
already.
Am Donnerstag, 26. April 2018 13:00:45 UTC-3 schrieb Mike Bayer:
>
> On Thu, Apr 26, 2018 at 11:04 AM, Diego Quintana
Re: [sqlalchemy] best ways to preserve and analyze detached/unbound items
yes, persistent_to_detached: http://docs.sqlalchemy.org/en/latest/orm/session_events.html#persistent-to-detached On Tue, May 1, 2018 at 11:18 PM, Jonathan Vanasco wrote: > Thanks for all this help, Mike! > > On Tuesday, May 1, 2018 at 8:56:35 PM UTC-4, Mike Bayer wrote: >> >> at what "moment in time"?I thought you might mean when they are >> expired from the Session, easy enough use the expire event >> >> http://docs.sqlalchemy.org/en/latest/orm/events.html?highlight=expire%20event#sqlalchemy.orm.events.InstanceEvents.expire >> but then you are saying "detached by their nature", as though they are >> created that way. > > > This event looks like what I need. I'll add it into my debugger's code > tomorrow! one of the `persistent_to_` events might be better... more below. > >> >> inspect(instance).expired_attributes > > > The inspector would have been a better idea. I got lazy and was just going > through the object in pdb! > >> >> but when you say "dead weakref" I think we are again getting at this >> "moment in time" you refer towards >> ... >> preserve it fromwhen ? when is it there? why did it go away ? > > > The debugging tool essentially decorates a web request with a > middleware-like context-wrapper like flow, then allows it to be inspected in > another browser window. The particular functionality I'm working with > stashes some ORM objects onto the request record, and a custom debugging > panel is used to audit/inspect the objects that were stashed on the request > (the last 20 requests are stored in-memory and available to the debugging > tool). 'By their inherent nature' meant everything in the debugger tool > occurred in a session that had previously ended with an explicit `close()`. > > The flow looks like this: > > * request made > * debugger wrapper starts > ** sqlalchemy session starts > *** activity > ** sqlalchemy session close() > * debugger wrapper cleanup > * debugger wrapper ends > > So I'm basically trying to access the object's state in `*** activity` prior > to `** close` > > The events look right. If I can't hack something with them, I'll just stash > a dict of the object if there is a flag for the dev environment present. > > -- > 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.
