Hi, I'm a recent adopter of sqlalchemy, starting fresh with all the 2.0 stuff. Thanks so much for the entire teams hard work!
I have a small issue with the merge functionality and it could honestly just be a misunderstanding of the function from my own point of view. I have a fairly complicated database structure that with 15 nested children tables, some of which have thousands of read-only records. I'd prefer to not have to add all those records to the session in order to run a merge, but when I try to do that, the subsequent flush emits sql updates that set the child foreign keys to null. If there is a way to avoid those updates for orphaned children and just ignore them if they aren't included in the incoming data by primary key, that would really help me, if not, I can look into adding them all to the incoming entity so they'll be ignored. MCVE: ``` from sqlalchemy import Column, Integer, String, ForeignKey, create_engine, select from sqlalchemy.orm import registry, declarative_base, relationship, sessionmaker from sqlalchemy.sql.sqltypes import Numeric import unittest Base = declarative_base() Mapper_Registry = registry() ### MODELS @Mapper_Registry.mapped class Item: __tablename__ = 'item' id = Column(Integer, primary_key=True) model_number = Column(String) item_prices = relationship("Item_Price", back_populates="item", lazy ="joined") @Mapper_Registry.mapped class Item_Price: __tablename__ = 'item_price' id = Column(Integer, primary_key=True) item_id = Column(Integer, ForeignKey('item.id')) price = Column(Numeric) item = relationship("Item", back_populates="item_prices", lazy="joined", viewonly=True) ### TESTS class Test_OrphanRecordFKMerge(unittest.TestCase): engine = create_engine('sqlite:///:memory:', echo=True, echo_pool='debug', future=True) Session = sessionmaker(bind=engine) session = Session() def setUp(self): Base2 = Mapper_Registry.generate_base() Base2.metadata.create_all(self.engine) # Create a base item to run tests on t_item = Item() t_item.model_number = 'TestItem' t_price1 = Item_Price() t_price1.price = 1.00 t_item.item_prices.append(t_price1) t_price2 = Item_Price() t_price2.price = 4.00 t_item.item_prices.append(t_price2) self.session.add(t_item) self.session.commit() def tearDown(self): Base.metadata.drop_all(self.engine) def test_item_update(self): self.session.expunge_all() # Incoming item data from remote api or flat file incoming_item = Item() incoming_item.model_number = 'TestItem' incoming_price1 = Item_Price() incoming_price1.price = 777.00 incoming_item.item_prices.append(incoming_price1) # Now we have an incoming item, we need to query the database for the existing item and reconcile the primary keys # so that it can be updated correctly persisted_item = self.session.execute(select(Item).where(Item.model_number == 'TestItem')).scalars().first() incoming_item.id = persisted_item.id # let us imagine that the new price should not overwrite either old price self.session.merge(incoming_item) self.session.commit() self.session.expunge_all() final_result = self.session.execute(select(Item).where(Item.model_number == 'TestItem')).scalars().first() # the following test fails as both the other price records have had their foreign keys set to null after the merge # so the len(final_result.item_prices) == 1 self.assertEqual(len(final_result.item_prices), 3) if __name__ == '__main__': unittest.main() ``` Output: ``` 2021-12-08 09:34:46,053 INFO sqlalchemy.engine.Engine UPDATE item_price SET item_id=? WHERE item_price.id = ? 2021-12-08 09:34:46,053 INFO sqlalchemy.engine.Engine [generated in 0.00056s] ((None, 1), (None, 2)) 2021-12-08 09:34:46,055 INFO sqlalchemy.engine.Engine INSERT INTO item_price (item_id, price) VALUES (?, ?) 2021-12-08 09:34:46,055 INFO sqlalchemy.engine.Engine [cached since 0.03964s ago] (1, 777.0) 2021-12-08 09:34:46,057 INFO sqlalchemy.engine.Engine COMMIT ``` The update statement above is what I am trying to avoid, but I'd still like to use the merge functionality if possible. Thanks for any guidance and for all you've put into this amazing library. -- 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 view this discussion on the web visit https://groups.google.com/d/msgid/sqlalchemy/acacfa82-fd70-4de1-9148-6746aa644ca3n%40googlegroups.com.