Issue: Peewee and custom primary keys
Author: SegFaultAX
State: closed
Created: 2016-05-11 00:22:07

Hello peewee team! I'm trying to understand some odd behavior when using custom primary keys with peewee. Kindly consider the following snippet:

``` python
# dependencies: peewee==2.8.1
import peewee

db = peewee.SqliteDatabase(":memory:")

class BaseModel(peewee.Model):
    class Meta:
        database = db

class ExampleOne(BaseModel):
    field1 = peewee.TextField()
    field2 = peewee.TextField()

class ExampleTwo(BaseModel):
    field1 = peewee.TextField(primary_key=True)
    field2 = peewee.TextField()

class ExampleThree(BaseModel):
    field1 = peewee.TextField()
    field2 = peewee.TextField()
    field3 = peewee.TextField()
    class Meta:
        primary_key = peewee.CompositeKey("field1", "field2")

def main():
    db.create_tables([ExampleOne, ExampleTwo, ExampleThree])

    ex1 = ExampleOne()
    ex1.field1 = "Field 1 value"
    ex1.field2 = "Field 2 value"
    print ex1.save()

    ex2 = ExampleTwo()
    ex2.field1 = "Field 1 value"
    ex2.field2 = "Field 2 value"
    print ex2.save()

    ex3 = ExampleThree()
    ex3.field1 = "Field 1 value"
    ex3.field2 = "Field 2 value"
    ex3.field3 = "Why is this required?"
    print ex3.save()

if __name__ == "__main__":
    main()
```

If you run this script, the output will be

```
1
0
0
```

My _expectation_ was 1's across the board (indicating 3 rows inserted into 3 separate tables). But that's not what happened...

Why do `ExampleTwo` and `ExampleThree` above fail to persist to the database? Specifically, I don't understand why specifying `primary_key=True` on `field1` of `ExampleTwo` means that it cannot save a new record (especially in a database we know a priori has no data in it and therefore no conflict). Of course setting `force_insert=True` works as expected, but why is that **necessary** in this context? Furthermore, composite primary keys [seem to be natively supported](http://docs.peewee-orm.com/en/latest/peewee/models.html#composite-primary-keys), but for whatever reason `ExampleThree` still fails to persist the new record (I'm guessing for the same reason `ExampleTwo` fails).

Finally (and I think unrelated to my actual question), you'll notice that I added a third field to `ExampleThree`. If I omit that field, I get the following error:

```
Traceback (most recent call last):
  File "wat.py", line 44, in <module>
    main()
  File "wat.py", line 41, in main
    print ex3.save()
  File "/Users/mkbernard/.pyenv/versions/vmware-scripts/lib/python2.7/site-packages/peewee.py", line 4742, in save
    rows = self.update(**field_dict).where(self._pk_expr()).execute()
  File "/Users/mkbernard/.pyenv/versions/vmware-scripts/lib/python2.7/site-packages/peewee.py", line 3117, in execute
    return self.database.rows_affected(self._execute())
  File "/Users/mkbernard/.pyenv/versions/vmware-scripts/lib/python2.7/site-packages/peewee.py", line 2656, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/Users/mkbernard/.pyenv/versions/vmware-scripts/lib/python2.7/site-packages/peewee.py", line 3492, in execute_sql
    self.commit()
  File "/Users/mkbernard/.pyenv/versions/vmware-scripts/lib/python2.7/site-packages/peewee.py", line 3316, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/Users/mkbernard/.pyenv/versions/vmware-scripts/lib/python2.7/site-packages/peewee.py", line 3485, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: near "WHERE": syntax error
```

I haven't dug in to figure out why that last bit happened, but it's shocking to me that a table with only primary key fields blows up peewee. Regardless, my bigger issue is understanding the failing examples above. Any insight would be greatly appreciated!


> Of course setting force_insert=True works as expected, but why is that necessary in this context?

It's [explained in the documentation](http://docs.peewee-orm.com/en/latest/peewee/models.html#id3).

Perhaps showing the SQL will help:

``` python
import logging
logger=logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())

>>> e2 = ExampleTwo(field1='f1', field2='f2')
>>> e2.save()
('UPDATE "exampletwo" SET "field2" = ? WHERE ("exampletwo"."field1" = ?)', [u'f2', u'f1'])
0

>>> e2.save(force_insert=True)
('INSERT INTO "exampletwo" ("field1", "field2") VALUES (?, ?)', [u'f1', u'f2'])
1

>>> ex3 = ExampleThree(field1='f1', field2='f2')
>>> ex3.save()
('UPDATE "examplethree" SET  WHERE (("examplethree"."field1" = ?) AND ("examplethree"."field2" = ?))', [u'f1', u'f2'])
OPERATIONAL ERROR BECAUSE NO VALUES BEING SET"

>>> ex3.save(force_insert=True)
('INSERT INTO "examplethree" ("field1", "field2") VALUES (?, ?)', [u'f1', u'f2'])
INTEGRITY ERROR BECAUSE YOU DECLARED FIELD3 AS NOT NULL (pass null=True for nullable)

>>> ex3,field3 = 'not null'
>>> ex3.save(force_insert=True)
('INSERT INTO "examplethree" ("field1", "field2", "field3") VALUES (?, ?, ?)', [u'f1', u'f2', u'not null'])
1
```

What did you expect? How is peewee supposed to know, when dealing with a non-auto-incremented primary key, whether the user means to update an existing record in the db or they are creating one? To perform runtime checks would be incredibly inefficient.

Peewee provides a shortcut, `ExampleTwo.create(field1='foo', field2='bar')` that will use `force_insert` behind the scenes.

Hopefully this clarifies the issue for you.


@coleifer First of all, I did completely miss that paragraph after glancing over that section more than once. Whoops. :) Thanks for the clarification! My expectation was simply that it would always try an insert on a record it knew was not constructed as the result of a query to the database, rather than automatically varying the operation based on the presence (or non-presence) of the primary key. (I'm fine with a constraint violation error in that case, because if I meant `get_or_create`/`upsert` I would have used that explicitly). But otherwise this logic makes sense to me. Thanks again!


Glad to help. Peewee is active record as opposed to a more stateful orm. As such there is no in memory representation of an object from a query vs an object constructed in code. This simplifies the orm implementation as well as the intuition behind its operation but it may not be obvious immediately.


I removed my prior comment because it was redundant with the documentation and I need to slow down and read! Thanks again for all your time. :D