Hi,
I'm trying to create a tag system with a many-to-many relationship
approach, and I have problems with the updating phase.
class Post(Base):
__tablename__ = 'post'
id = Column(Integer, primary_key=True)
_tags = relationship('Tag', secondary='post_tags')
@property
def tags(self):
return ';'.join(tag.key for tag in self._tags)
@tags.setter
def tags(self, s):
lst = [Tag(key=tag) for tag in s.split(';')]
self._tags = lst
class Tag(Base):
__tablename__ = 'tag'
key = Column(String(40), primary_key=True)
count = Column(Integer)
And the `post_tags` is defined as :
CREATE TABLE `post_tags` (
`idpost` INT(10) UNSIGNED NOT NULL,
`tag` VARCHAR(40) NOT NULL,
PRIMARY KEY (`idpost`, `tag`),
INDEX `FK_post_tags` (`tag`),
FOREIGN KEY (`tag`) REFERENCES `tag` (`key`),
FOREIGN KEY (`idpost`) REFERENCES `post` (`id`) ON DELETE CASCADE
);
The problem is that when adding tags that already exists in the `tag` table
post.tags = 'a' # tag 'a' already created
it produces this exception : sqlalchemy.orm.exc.FlushError: New instance
<Tag at 0x7f7b56a3a940> with identity key (<class 'test_mm.Tag'>, ('a',))
conflicts with persistent instance <Tag at 0x7f7b567af7f0>
Ideally, I'd like to be able to produce a query like (MySQL) :
INSERT INTO tag (`key`, `count`) VALUES (%s, 1) ON DUPLICATE KEY UPDATE
count = count + 1
Instead that, the way I found to do seems much less efficient :
@tags.setter
def tags(self, s):
sess = object_session(self)
lst = [sess.merge(Tag(key=tag)) for tag in s.split(';')]
self._tags = lst
(and the counter is managed by a trigger on `post_tags` INSERT)
Is there another way to do that properly and efficiently ?
--
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 post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
