Conditional duplicate key updates with MySQL using Peewee

Question

I have a case where I need to use conditional updates/inserts using peewee. The query looks similar to what is shown here, conditional-duplicate-key-updates-with-mysql

As of now, what I'm doing is, do a get_or_create and then if it is not a create, check the condition in code and call and insert with on_conflict_replace. But this is prone to race conditions, since the condition check happens back in web server, not in db server.

Is there a way to do the same with insert in peewee?

Using: AWS Aurora-MySQL-5.7

Answer 1

Yes, Peewee supports the ON DUPLICATE KEY UPDATE syntax. Here's an example from the docs:

class User(Model):
    username = TextField(unique=True)
    last_login = DateTimeField(null=True)
    login_count = IntegerField()

# Insert a new user.
User.create(username='huey', login_count=0)

# Simulate the user logging in. The login count and timestamp will be
# either created or updated correctly.
now = datetime.now()
rowid = (User
         .insert(username='huey', last_login=now, login_count=1)
         .on_conflict(
             preserve=[User.last_login],  # Use the value we would have inserted.
             update={User.login_count: User.login_count + 1})
         .execute())

Doc link: http://docs.peewee-orm.com/en/latest/peewee/querying.html#upsert