On Fri, Sep 11, 2020, at 2:26 AM, Jacob Pavlock wrote:
>
> 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?
merge() is just a little weird in this regard, it's not my first go-to method
for sure. it's based on the same idea in Hibernate. I would not have ever
thought of such a method on my own.
The specific issue in your example above is that you are setting "track1.album
= "gkmc"", but you have this attribute set to be populated from that of your
Album() object which I would assume is overwriting it during the flush.
that is, you have an Album object on your Track, it doesn't actually make sense
to change track1.album = "foo" without changing that whole object.
I guess what most people would do here would be to not use natural primary keys
(e.g. use a surrogate primary key, like an integer). your table rows would
use less data and the model would be simpler to manipulate.
> 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
>
> <https://groups.google.com/d/msgid/sqlalchemy/98ab5a17-da18-4b88-9dc2-fcf650bbd5c6n%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/e7220db3-7aa1-4d3b-a579-aac73d935abc%40www.fastmail.com.
