Issue: peewee mysql how to execute 'insert many on duplicate key update'
Author: yangbh
State: closed
Created: 2015-11-11 09:38:47

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


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


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.


Hi there, quick question regarding this comment

https://stackoverflow.com/a/9168948/2774823

it seems sometimes ON DUPLICATE KEY UPDATE is preferable?

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:

```python
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())
```

@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.


@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

@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 :)