Mike,
Thanks for the great explanation and the quick response! I think am I
starting to get it now. I guess the same or similar idea though leads to
the following (somewhat confusing) behavior:
(I added a repr and setters for the hybrid properties)
track1 = Track(album="tpab", albumartist="K.dot", track_num=1, year=2015)
track2 = Track(album="tpab", albumartist="K.dot", track_num=1, year=2015)
track2.album = "gkmc"
session.merge(track1)
session.merge(track2)
session.commit()
print(session.query(Track).all())
Results in
[K.dot - gkmc (2015): 1]
But
track1 = Track(album="tpab", albumartist="K.dot", track_num=1, year=2015)
track2 = Track(album="tpab", albumartist="K.dot", track_num=1, year=2015)
track1.album = "gkmc"
session.merge(track1)
session.merge(track2)
session.commit()
print(session.query(Track).all())
Results in
[K.dot - gkmc (2015): 1, K.dot - tpab (2015): 1]
I feel like I'm starting to tread in non-standard waters here, and I am
beginning to question whether my use of `session.merge()` is appropriate
for my usecase. Any recommendations?
On Thursday, September 10, 2020 at 10:44:09 PM UTC-5 Mike Bayer wrote:
> When you create a Track object and don't assign the primary key attributes
> directly, the instance key of the object is all nulls:
>
> Track.__mapper__.identity_key_from_instance(track1)
> (<class '__main__.Track'>, (1, None, None, None), None)
>
> Track.__mapper__.identity_key_from_instance(track2)
> (<class '__main__.Track'>, (1, None, None, None), None)
>
> this is because while the assignment of _album_obj will set the primary
> key attributes, that doesn't happen until the flush, so right now Track has
> no primary key.
>
> merge() sees the first object, puts it in as a new object that's pending
> without a primary key.
>
> then the second merge() call occurs. a flush proceeds first, which flushes
> track1, assigning the _album_obj which sets up the primary key attributes
> appropriately on track1. track2 comes in, and the apparent primary key
> is again all nulls, so it again goes in as a pending object to be
> inserted. the commit tries to flush and it fails because the object's
> primary key once calculated by _album_obj conflicts with what's in the
> database. merge() had no idea you intended this to be the same primary key
> identity because those attributes are not set up front.
>
> When you explicitly set up the three pk attributes on Track, then the
> merge() process can see the apparent primary key you had in mind and it can
> correctly match "track2" with the "track1" that's already in the database.
>
> hope this helps!
>
>
>
>
>
>
> On Thu, Sep 10, 2020, at 9:44 PM, Jacob Pavlock wrote:
>
> Hello! I am new to sqlalchemy and was hoping to get some insight on the
> following issue:
>
> *import* sqlalchemy*from* sqlalchemy *import* Column, Integer, String*from*
> sqlalchemy.ext.declarative *import* declarative_base*from*
> sqlalchemy.ext.hybrid *import* hybrid_property*from* sqlalchemy.orm *import*
> relationship, sessionmaker*from* sqlalchemy.schema *import*
> ForeignKeyConstraint
>
> Session = sessionmaker()
> Base = declarative_base()
> *class* *Album*(Base):
>
> __tablename__ = "albums"
>
> artist = Column(String, nullable=*False*, primary_key=*True*)
> title = Column(String, nullable=*False*, primary_key=*True*)
> year = Column(Integer, nullable=*False*, primary_key=*True*)
>
> tracks = relationship(
> "Track", back_populates="_album_obj", cascade="all, delete-orphan"
> )
>
> *def* *__init__*(self, artist: str, title: str, year: int):
> self.artist = artist
> self.title = title
> self.year = year
> *class* *Track*(Base): *# noqa: WPS230*
> __tablename__ = "tracks"
>
> *# track_num + Album = unique track*
> track_num = Column(Integer, nullable=*False*, primary_key=*True*,
> autoincrement=*False*)
> _albumartist = Column(String, nullable=*False*, primary_key=*True*)
> _album = Column(String, nullable=*False*, primary_key=*True*)
> _year = Column(Integer, nullable=*False*, primary_key=*True*,
> autoincrement=*False*)
>
> artist = Column(String, nullable=*False*, default="")
> title = Column(String, nullable=*False*, default="")
>
> _album_obj = relationship("Album", back_populates="tracks")
>
> __table_args__ = (
> ForeignKeyConstraint(
> [_albumartist, _album, _year], *# type: ignore*
> [Album.artist, Album.title, Album.year],
> ),
> )
>
> @hybrid_property
> *def* *album*(self) -> *str*:
> *return* self._album_obj.title
>
> @hybrid_property
> *def* *albumartist*(self) -> *str*:
> *return* self._album_obj.artist
>
> @hybrid_property
> *def* *year*(self) -> *int*:
> *return* self._album_obj.year
>
> *def* *__init__*(self, album: str, albumartist: str, track_num: int,
> year: int):
> self._album_obj = Album(artist=albumartist, title=album, year=year)
> self.track_num = track_num
> *# self._album = album*
> *# self._albumartist = albumartist*
> *# self._year = year*
>
> engine = sqlalchemy.create_engine("sqlite:///:memory:", echo=*True*)
> Session.configure(bind=engine)
> Base.metadata.create_all(engine) *# creates tables*
> session = Session()
>
> track1 = Track(album="tpab", albumartist="K.dot", track_num=1, year=2015)
> track2 = Track(album="tpab", albumartist="K.dot", track_num=1, year=2015)
>
> session.merge(track1)
> session.merge(track2)
> session.commit()
>
>
> When executing the above code, I get
>
> Traceback (most recent call last):
> File "test_merge.py", line 81, in <module>
> session.commit()
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 1042, in commit
> self.transaction.commit()
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 504, in commit
> self._prepare_impl()
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 483, in _prepare_impl
> self.session.flush()
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 2523, in flush
> self._flush(objects)
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 2664, in _flush
> transaction.rollback(_capture_exception=True)
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/util/langhelpers.py",
> line 68, in __exit__
> compat.raise_(
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/util/compat.py",
> line 182, in raise_
> raise exception
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/orm/session.py",
> line 2624, in _flush
> flush_context.execute()
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
> line 422, in execute
> rec.execute(self)
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/orm/unitofwork.py",
> line 586, in execute
> persistence.save_obj(
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
> line 205, in save_obj
> for (
> File
> "/home/jacob/src/moe/.venv/lib/python3.8/site-packages/sqlalchemy/orm/persistence.py",
> line 405, in _organize_states_for_save
> raise orm_exc.FlushError(
> sqlalchemy.orm.exc.FlushError: New instance <Track at 0x7f72ccd17f70> with
> identity key (<class '__main__.Track'>, (1, 'K.dot', 'tpab', 2015), None)
> conflicts with persistent instance <Track at 0x7f72ccc901c0>
>
>
> But, If I uncomment
>
> *# self._album = album*
> *# self._albumartist = albumartist*
> *# self._year = year*
>
>
> The code works fine. I don't quite understand why the above code is
> needed, and happened to add it just by random testing to see if I could pin
> down the error. I think the section in the docs on merge tips
> <https://docs.sqlalchemy.org/en/13/orm/session_state_management.html#merge-tips>
>
> is the closet thing I could find, but I can't quite understand if the same
> thing there is happening here. Any insight would be greatly appreciated!
>
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to [email protected].
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/8f3ecc9d-8ab3-407d-b946-ef67aa21a3afn%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/8f3ecc9d-8ab3-407d-b946-ef67aa21a3afn%40googlegroups.com?utm_medium=email&utm_source=footer>
> .
>
>
>
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/98ab5a17-da18-4b88-9dc2-fcf650bbd5c6n%40googlegroups.com.
