SQLAlchemy update if unique key exists
Solution 1
You can try this
def get_or_increase_tag(tag_name):
tag = session.query(Tag).filter_by(tag=tag_name).first()
if not tag:
tag = Tag(tag_name)
else:
tag.cnt += 1
return tag
You can check the link https://stackoverflow.com/search?q=Insert+on+duplicate+update+sqlalchemy
Solution 2
From version 1.2 SQLAlchemy will support on_duplicate_key_update
for MySQL
There is also examples of how to use it:
from sqlalchemy.dialects.mysql import insert insert_stmt = insert(my_table).values( id='some_existing_id', data='inserted value') on_duplicate_key_stmt = insert_stmt.on_duplicate_key_update( data=insert_stmt.values.data, status='U' ) conn.execute(on_duplicate_key_stmt)
From version 1.1 SQLAlchemy support on_conflict_do_update
for PostgreSQL
from sqlalchemy.dialects.postgresql import insert insert_stmt = insert(my_table).values( id='some_existing_id', data='inserted value') do_update_stmt = insert_stmt.on_conflict_do_update( constraint='pk_my_table', set_=dict(data='updated value') ) conn.execute(do_update_stmt)
Related videos on Youtube
Nemoden
Interested in: Photography, but I have no camera. Investing, but I have no money. Inventing, but I have no thoughts. Cooking, but I have no ingredients. Singing, but I have no voice. Making music, but I have no notes. Flying, but I have no wings.@Nemoden
Updated on July 12, 2020Comments
-
Nemoden almost 4 years
I've got a class:
class Tag(Base, TimestampMixin): """Tags""" __tablename__ = 'tags' __table_args__ = {'mysql_engine' : 'InnoDB', 'mysql_charset' : 'utf8' } id = Column(Integer(11), autoincrement = True, primary_key = True) tag = Column(String(32), nullable = False, unique = True) cnt = Column(Integer(11), index = True, nullable = False, default = 1) def __init__(self, tag): t = session.query(Tag).filter_by(tag=tag).first() if t: self.cnt = t.cnt+1 self.tag = t.tag else: self.tag = tag def __repr__(self): return "<Tag('%s')>" % (self.tag, ) def __unicode__(self): return "%s" % (self.tag, )
When adding tag:
tag = Tag('tag') session.add(tag) session.commit()
I want it to update existing
tag
.Of course, I could've done this:
tag = session.query(Tag).filter_by(tag='tag').first() if tag: tag.cnt++ else: tag = Tag('tag') session.add(tag) session.commit()
but, keeping such logic in
Tag
class seems to be more clear - possibly keeps me off of the shotgun surgery.How do I get there? I'm pretty new to
Python
andSQLAlchemy
, so any additional thoughts on my code will be appreciated.Thank you.
P.S.
SQLAlchemy
is SO GIGANTIC and they don't provide a handy way to doINSERT ... ON DUPLICATE KEY UPDATE
, huh? WOW!-
Emil M about 12 yearstry session.merge(tag) instead of session.add(tag)
-
Hennadii Madan over 5 years+100 on the P.S.
-
-
willyhakim over 6 yearsThere should be a create_or_update function django orm style
-
bl79 about 4 yearstypo:
data=insert_stmt.values.data
insteaddata=insert_stmt.inserted.data
-
Ken about 3 yearsHow can you do this with session?