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/c5e74deb-d849-4a43-a601-e129918be595%40www.fastmail.com.
