Issue: Use database column defaults
Author: obensonne
State: closed
Created: 2012-10-03 21:01:48

The [peewee documentation says](https://peewee.readthedocs.org/en/latest/peewee/fields.html?highlight=default#a-note-on-validation):

_Both default and choices could be implemented at the database level as DEFAULT and CHECK CONSTRAINT respectively, but any application change would require a schema change. Because of this, default is implemented purely in python and choices are not validated but exist for metadata purposes only._

I understand this is useful when peewee is used as the main place to define a DB schema. However, in some cases it perfectly makes sense to define certain logic (like default values) on the DB level. Especially when a database is used by multiple applications, not only by an application which uses peewee for DB interaction. In such a case the schema definition usually is done outside of peewee. Here is a conflict with the DRY principle: default values have to be specified when a schema is defined _and_ in peewee models.

For instance, when I create and save a model with peewee, I would expect it to construct insert statements which only use columns explicitly specified in `Model.create(...)` or which have explicit default values in the model definition (e.g. `foo = IntegerField(default=1)`). All other, not specified attributes (i.e. columns) simply should not be used in insert statements, which practically lets the DB check for integrity errors and/or use default values defined by the schema.

Am I right that peewee does not support such partial insert statements? If yes, what about adding such a functionality?

Regards,
Oben 


I am aware of the DRY issues, which is why I doc'd it and mentioned my reasons for defining defaults in python.

As to your question, the InsertQuery behaves correctly but `create` is apparently adding everyting regardless of whether you specify it or not.  I will take a look into making create behave more like insert, as I think you're right about letting the db handle it where possible.

``` python

In [1]: from peewee import *

In [2]: class Example(Model):
   ...:     f1 = IntegerField(default=1)
   ...:     f2 = CharField(default='hello')
   ...:     f3 = CharField()
   ...:     f4 = IntegerField()
   ...:     

In [3]: Example.insert().sql()
Out[3]: ('INSERT INTO "example" ("f1","f2") VALUES (?,?)', [1, u'hello'])

In [4]: Example.insert(f3='bar').sql()
Out[4]: 
('INSERT INTO "example" ("f1","f2","f3") VALUES (?,?,?)',
 [1, u'hello', u'bar'])
```


Thanks for the quick response.

Indeed the query returned by `insert()` is what I'd expect. Would be great if `create()` has a similar behavior.

Do you plan to work on this on your own or do you prefer a patch/pull request.


Unfortunately this issue is not resolved yet.

While `Model.create()` now works as exepected, `Model.save()` still creates an update query with all defined fields. Here it would be useful if only fields actually set are be included in the query. I can think of 2 solutions:
1. Either use a special `unset` value (next to `None`) to distinguish not specified and `NULL` values, or
2. Implement a `Model.__setattr__()` method which keeps track of set fields. Then, only these set fields plus the ones passed to the model constructor plus fields with defined defaults are used for the corresponding update query.

Of course this is only needed for model instances created by `Model(**query)`, not for instances retrieved from database queries.

I guess this is a bit more tricky. I know I could use `Model.update(...).execute()`, but setting fields with attribute-style access is much more convinient.


This is all working in branch unstable/2.0 for what its worth.

Fixing in current will require using sentinel values most likely.  If you want to patch it I can merge something, but since its working in the latest branch I'll probably pass for now on writing it myself.


Great to hear that this already works in 2.0. I did not test it yet but  I guess the unstable branch is just fine for me (instead of patchng 1.0). How _experimental_ is the unstable branch?


Weeelllll, pretty.  The API is not too different, but i haven't yet upgraded flask-peewee or wtf-peewee to work with it.  Soon i'm going to merge it into master, just need to get everything documented.