Thank you Mike for this detailled response ! The UniqueObject recipe is very interesting, but not very suitable to my case because my webservice don't use the same session on each Post insertion.
Maybe a session.merge_all() method can help to improve performance in such cases by grouping the underlying SQL queries (only one SELECT and one INSERT) for all instances. Le mardi 23 mai 2017 19:48:11 UTC+3, Mike Bayer a écrit : > > we don't have ON DUPLICATE KEY UPDATE for MySQL as of yet however there > is a PR that I will attend to at some point for possible 1.2 inclusion. > > However, note you can just as well just emit the SQL string for ON > DUPLCIATE KEY UPDATE if you're only targeting MySQL. Then just create > the object you need that corresponds to this row, then use > make_transient_to_detached() -> session.add() to make it happen (see > example below). > > Theoretically, the functionality of session.merge() could provide an > alternate form that makes use of ON DUPLICATE KEY UPDATE automatically > however for the foreseeable future, you'd need to roll this yourself > once you make use of the SQL statement. > > The traditional way to handle this is largely like what you have except > that client-side caching is used to make it more efficient. One > example is > https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/UniqueObject, > which caches per single object. Other ways include just selecting a > whole batch of objects based on keys you know you will be working with > up front. > > > from sqlalchemy import * > from sqlalchemy.orm import * > from sqlalchemy.ext.declarative import declarative_base > > Base = declarative_base() > > post_tags = Table( > "post_tags", Base.metadata, > Column("postid", ForeignKey('post.id'), primary_key=True), > Column("tagid", ForeignKey('tag.key'), primary_key=True) > ) > > > 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) > > e = create_engine("mysql://scott:tiger@localhost/test", echo=True) > Base.metadata.drop_all(e) > Base.metadata.create_all(e) > s = Session(e) > > > def make_tag(name): > s.execute( > "INSERT INTO tag (`key`, `count`) VALUES (:name, 1) " > "ON DUPLICATE KEY UPDATE count = count + 1", > {"name": name} > ) > tag_obj = Tag(key=name) > make_transient_to_detached(tag_obj) > return s.merge(tag_obj, load=False) > > > s.add(Post(_tags=[make_tag("tag_a")])) > s.commit() > > s.add(Post(_tags=[make_tag("tag_a")])) > s.commit() > > assert s.query(Tag).count() == 1 > > > > On 05/22/2017 09:59 AM, yoch....@gmail.com <javascript:> wrote: > > Hi, > > > > I'm trying to create a tag system with a many-to-many relationship > > approach, and I have problems with the updating phase. > > > > | > > classPost(Base): > > __tablename__ ='post' > > > > id =Column(Integer,primary_key=True) > > _tags =relationship('Tag',secondary='post_tags') > > > > @property > > deftags(self): > > return';'.join(tag.key fortag inself._tags) > > > > @tags.setter > > deftags(self,s): > > lst =[Tag(key=tag)fortag ins.split(';')] > > self._tags =lst > > > > classTag(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 > > deftags(self,s): > > sess =object_session(self) > > lst =[sess.merge(Tag(key=tag))fortag ins.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+...@googlegroups.com <javascript:> > > <mailto:sqlalchemy+unsubscr...@googlegroups.com <javascript:>>. > > To post to this group, send email to sqlal...@googlegroups.com > <javascript:> > > <mailto:sqlal...@googlegroups.com <javascript:>>. > > Visit this group at https://groups.google.com/group/sqlalchemy. > > For more options, visit https://groups.google.com/d/optout. > -- 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.
