peewee mysql how to execute 'insert many on duplicate key update'

[yangbh]

Hi Charles,
how to execute sql command like

insert into table(ip,hostname,...)
values
('','',...)
('','',...)
('','',...)
...
on duplicate key update hostname=values(hostname),

upsert and on_conflict does not work

In [49]: result
Out[49]: 
[{'country': '',
  'guid': '122.225.227.21:80',
  'head': '',
  'hostname': '122.225.227.21',
  'ip': '122.225.227.21',
  'ipr': '',
  'port': 80,
  'protocol_type': 'tcp',
  'province': '',
  'server': '',
  'title': ''},
...]
In [50]: basicinfo_ip_finger.insert_many(result).on_conflict('REPLACE').execute()

ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'OR REPLACE INTO `basicinfo_ip_finger` (`ip`, `port`, `protocol_type`, `hostname`' at line 1")

please help, thanks

[yangbh]

here is my poor solution

q=basicinfo_ip_finger.insert_many(result).sql()
db.execute_sql(q[0]+' on duplicate key update hostname=values(hostname),...', q[1])

any good idea?
thanks

[coleifer]

I looked at the docs and it seems like MySQL supports a command REPLACE that does what you want. I've added support for this in 2d2df5ca3b69073f4b9efef1ed6d2ad21406298b . I think this is actually preferable to using ON DUPLICATE KEY UPDATE because REPLACE does not require a separate clause describing the values.

[Zylatis]

Hi there, quick question regarding this comment

https://stackoverflow.com/a/9168948/2774823

it seems sometimes ON DUPLICATE KEY UPDATE is preferable?

[coleifer]

Peewee does support ON DUPLICATE KEY UPDATE:

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

MySQL supports upsert via the ON DUPLICATE KEY UPDATE clause. For example:

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())

[xzycn]

@coleifer
if I have a filed with ddl like this

created_on datetime NOT NULL DEFAULT CURRENT_TIMESTAMP

if I use replace, it will result in the filed "created_on" is "updated" to current time, because the origin record is deleted and a new record created,this is not my expected.Otherwise, Insert into ..... ON DUPLICATE KEY UPDATE work well.

[coleifer]

@xzycn - you should consult the documentation for your database, my dude. It clearly says that REPLACE works by deleting and re-inserting in the event of a conflict.

https://dev.mysql.com/doc/refman/8.0/en/replace.html

[xzycn]

@coleifer
I know that, just didn't claim it.I just want to say:"it seems sometimes ON DUPLICATE KEY UPDATE is preferable", thank you for your reply :)