Issue: support insert ignore ,  on duplicate key update or replace for mysql?
Author: 631068264
State: closed
Created: 2016-09-17 15:25:14

support insert ignore ,  on duplicate key update or replace for mysql. I want to use `insert_many` to  insert data into mysql. But one of column is a `UNIQUE KEY` so how to solve this.

I hope it can like this `execute(ignore=True)`


OK . I find only `ignore` in  [InsertQuery.on_conflict](http://docs.peeweeorm.com/en/latest/peewee/api.html#InsertQuery.on_conflict). But what is `INSERT OR IGNORE INTO` ?  In Mysql only `INSERT  IGNORE INTO` is right( I  didn't use PostgreSQL or SQLite ).  I think you should divide **peewee** into different versions for different DBMS and it can adapt to some features in different DBMS. 

I like **peewee** than **SQLAlchemy** because the former‘s document and api design are much more beautiful than the latter. I hope **peewee** become more perfect.


At the moment the upsert/insert or replace code is a bit fragmented and poorly designed.

Have you tried using the `upsert()` syntax, or could you share the insert statement you're executing along with the generated SQL?


I create a pull request. I hope you can have a look.@coleifer
https://github.com/coleifer/peewee/pull/1070


I would like that upsert behaviour work with all databases. If this is too complex with sql support on specific databases, maybe with a fallback to create_or_get() then save()
I'm getting db type from conf file at runtime. There is also peewee-mssql addition. For compability I'm doing create_or_get() then save(). It would be good to have upsert() work with optimized sql or fallback. Or if you have other advices?

Hi, I am having the same issue. Any plans to get the PR merged? I am not much experienced with Python, but in case some help is needed to get this done, I can give a shot.
Thanks!

> Have you tried using the `upsert()` syntax, or could you share the insert statement you're executing along with the generated SQL?

The big problem with `upsert()` in MySQL is that it generates a `REPLACE INTO` which deletes the matching rows and re-inserts them.  If there is a foreign key to that row then those keys are taken into account (either cascading the delete or blocking the delete).  However, an `INSERT INTO ... ON DUPLICATE KEY UPDATE` doesn't have this issue.

As a workaround, I did the following:

``` python
class CustomInsertQuery(InsertQuery):
    """ Custom implementation of peewee's InsertQuery that provides
    proper syntax for `IGNORE` inserts on MySQL.

    https://github.com/coleifer/peewee/issues/1067
    """

    def sql(self):
        sql, params = super(CustomInsertQuery, self).sql()
        if sql.startswith('INSERT OR'):
            sql = sql.replace('INSERT OR', 'INSERT')
        return sql, params
```

``` python
class CustomBaseModel(Model):

    @classmethod
    def insert(cls, __data=None, **insert):
        fdict = __data or {}
        fdict.update([(cls._meta.fields[f], insert[f]) for f in insert])
        return CustomInsertQuery(cls, fdict)

    @classmethod
    def insert_many(cls, rows, validate_fields=True):
        return CustomInsertQuery(cls, rows=rows, validate_fields=validate_fields)
```


Please see new discussion thread in #1307 (which consolidates discussion of upsert functionality for sqlite/mysql/postgres).