Issue: upsert fails when applying on insert_many
Author: hitzg
State: closed
Created: 2015-02-26 10:49:57

I got confused using `insert_many` and `upsert`. But I'm not sure if this is a bug of peewee or just a limitation of sqlite. But either way the result is not what I would expect.
(also I was not sure if I should post this as an issue or on stackoverflow. I'm happy to post it there if you feel its more appropriate)

Minimal example:

``` python
import peewee as pw

db = pw.SqliteDatabase(':memory:')

# simple model with unique field
class Person(pw.Model):
    name = pw.CharField(unique=True)
    class Meta:
        database = db

# create two sets of persons with some duplicates
persons1 = [dict(name=n) for n in 'anna john carl maria'.split()]
persons2 = [dict(name=n) for n in 'anna john mark lena'.split()]

# initialize the db
db.connect()
db.create_tables([Person], True)

# insert first set of persons
with db.transaction():
    query = Person.insert_many(persons1)
    print "  query 1:", query
    query.execute()

# use upsert to savely add the second set
with db.transaction():
    query = Person.insert_many(persons2).upsert(upsert=True)
    print "  query 2:", query
    query.execute()

# output all persons
print "Persons in db:"
for p in Person.select():
    print "  ", p.name
```

Output:

```
query 1: <class '__main__.Person'> INSERT INTO "person" ("name") VALUES (?), (?), (?), (?) [u'anna', u'john', u'carl', u'maria']
  query 2: <class '__main__.Person'> INSERT OR REPLACE INTO "person" ("name") VALUES (?), (?), (?), (?) [u'anna', u'john', u'mark', u'lena']
Traceback (most recent call last):
  File "upsert_minimal.py", line 29, in <module>
    query.execute()
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2683, in execute
    last_id = InsertQuery(self.model_class, row).execute()
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2685, in execute
    return self.database.last_insert_id(self._execute(), self.model_class)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2243, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2877, in execute_sql
    self.commit()
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2732, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 2869, in execute_sql
    cursor.execute(sql, params or ())
peewee.IntegrityError: column name is not unique
```

Doing each upsert separately works though:

``` python
with db.transaction():
    for person in persons2:
        Person.insert(**person).upsert(upsert=True).execute()
```

Versions:
- Ubuntu 12.04
- peewee: 2.4.7
- sqlite3: 3.7.9
- sqlite3 (python package): 2.6.0


Nice post, I will look into this.


Interestingly, here is the output when I run your sample script (slightly cleaned up output):

``` python
  query 1: INSERT INTO "person" ("name") VALUES (?), (?), (?), (?)
  query 2: INSERT OR REPLACE INTO "person" ("name") VALUES (?), (?), (?), (?)
Persons in db:
   carl
   maria
   anna
   john
   mark
   lena
```

The list of names is correct and no `IntegrityError` is raised. I'm using SQLite 3.8.8 so perhaps that's the difference?


Yes, this seems to be related to the version of SQLite: Inserting multiple rows is supported since 3.7.11 ([source](http://www.sqlite.org/releaselog/3_7_11.html)). Before it was still feasible using a more complex syntax: ([ref](http://stackoverflow.com/a/1734067/4177384)).

So indeed, this fails on my system:

``` bash
 sqlite3 :memory: "create table person (name string);
> insert into person (name) values ('a'), ('b');"
```

with `Error: near ",": syntax error` (as expected).
However, shouldn't then also the first insert in the example above fail? (as it seems to be independent of the "or replace" clause??

But I guess we can close this, as it is a _not_ bug of peewee.


Do you mind trying out ab815a6? I think I may have found the particular bug as it relates to older SQLite versions.


Nice! I have just tested it and it fixes the issue.
Thanks a lot!