Hello! I am new to sqlalchemy and was hoping to get some insight on the
following issue:
import sqlalchemyfrom sqlalchemy import Column, Integer, Stringfrom
sqlalchemy.ext.declarative import declarative_basefrom sqlalchemy.ext.hybrid
import hybrid_propertyfrom sqlalchemy.orm import relationship, sessionmakerfrom
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.
