akumoth/peewee-issues huggingface dataset:
Issue: Question: why does clear_limit default to True (simply for backwards compatibility?)
Author: bryhoyt
State: closed
Created: 2014-07-23 22:59:36

I realise it may well break backwards compatibility to change anything at this point, but I'm interested in understanding the rationale, if any, for the way things are, so I can grok the API better.

As far as I see it, LIMIT and OFFSET are genuinely part of a given query -- if I want to know the total number of results, I will execute .count() on a query with no limit/offset. If I want to know how many results in a query filtered by a column, then I'll execute .count() on that query, and expect the filtered number of results. Thus, if I execute .count() on a query filtered by limit/offset, I expect the result to be the limited number.

Any reason for the default being the opposite to what I expect?

(I haven't tested this, but I believe both SQLAlchemy and Django ORM work in the way I'd expect -- not that that's necessarily a point in favour, just an interesting reference. Looks like SQLAlchemy may have struggled with this a bit, and settled on an always-does-what-you'd-expect answer using a subquery: http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#counting)


I agree and have updated wrapped_count() to have `clear_limit` default to `False`. I also added the option to pass that parameter through the call to `count()`. Finally, I made it so that calls to `count()` for queries that have a limit or offset will use wrapped_count() instead.


If for some reason I have misunderstood your concerns, please leave a comment. Based on my understanding of your comments I think I've fixed the behavior of count. Thanks for taking the time to bring these issues to my attention. I hope that you'll keep submitting quality issues like these!


Everything looks great to me -- your commit addresses my concerns perfectly.

My only question at this point would be whether anyone out there is depending on the current behaviour. But hopefully not -- and it could be something you could perhaps handle better with a comment in the release notes or docs for the next version. I've checked flask-admin's peewee module, and it has no problems in either case, because it calls .count() before the limit is applied anyway.
Issue: Should count() default to wrapped_count() when query contains a limit or offset?
Author: bryhoyt
State: closed
Created: 2014-07-23 22:43:39

As far as I can tell, count() returns meaningless results when the query contains a limit or offset (neither the total, nor the limited, number of results).

As per the docs, "count() will automatically default to wrapped_count() in the event the query is distinct or has a grouping".

I think that's nice; exactly what you want in the case of distinct or group by. Wouldn't it make sense for it to do the same if the query has a limit or offset?

If not, what's the rationale behind that?


Additionally, I think count() should have a clear_limit parameter which it passes through to wrapped_count() -- I'm mentioning this as a comment here, not a separate issue, because it would only make sense if count deferred to wrapped_count for limits and offsets.


> I think that's nice; exactly what you want in the case of distinct or group by. Wouldn't it make sense for it to do the same if the query has a limit or offset?

Yeah, now that I think about it I agree.


Thanks, looks great -- really appreciate the quick fixes. I'm loving using Peewee with Flask -- thanks for the great ORM!
Issue: wrapped_count() parameter clear_limit is undocumented
Author: bryhoyt
State: closed
Created: 2014-07-23 22:40:03

I was confused when count() was behaving unexpectedly, and discovered wrapped_count() by trawling the issues page, but it took me a bit more probing to find the clear_limit parameter to get the actual number of results in the query, rather than the total without limit/offset.

Would be helpful if clear_limit were documented here: https://peewee.readthedocs.org/en/latest/peewee/api.html#SelectQuery.wrapped_count


Thanks for pointing this out.
Issue: Added support for postgres raw encryption functions.
Author: stas
State: closed
Created: 2014-07-23 16:02:42

This introduces a new field type for `playhouse.postgres_ext` to handle encrypted columns.

This should work with AES and Blowfish algorithms. The encryption is handled by the [pgcrypto](http://www.postgresql.org/docs/9.3/static/pgcrypto.html) module.

Although this works, this is not very optimized. See #401
I opened this PR mostly because I'm interested in having encryption support in peewee.
Although this is pretty raw, I hope to get some feedback and other people review and play with this.

Thanks a lot for all the work on peewee!
Issue: Database functions fields support
Author: stas
State: closed
Created: 2014-07-23 15:50:21

The issue is related to the way field nodes are parsed.
At this moment there's no way you can dynamically embed a function in the field `SelectQuery` (although this is partially achievable through psycopg2 API for PostgreSQL, other database adapters are very unlikely to provide the same functionality).

A good example for this use-case could be the pgcrypto functions that can be used to encrypt/decrypt data at the query level. Below is an example SQL query I would like to be able to build through the `Field` API:

``` sql
INSERT INTO mytable (mycolumn) VALUES (encrypt_iv('TEST_DATA', '_key', '_iv', 'aes'));
SELECT t1."id", decrypt_iv(t1."mycolumn", '_key', '_iv', 'aes') as mycolumn FROM mytable as t1;
```


I think I'm going to pass on merging this, but thank you for your hard work!


@coleifer It would be great if you could share your opinions on this.

Btw, this issue is not directly relevant to the PR #402 you just closed, which is fine tbh.


> It would be great if you could share your opinions on this.

For sure, sorry for being vague. I think this could lead to subtle bugs, especially if you wanted to work with the raw value stored in the column (without the function). I also think that there are limited use-cases -- encryption/decryption is really the only one that comes to mind.

In my opinion, it would be better to be explicit:

``` python

SecretModel.select(
    SecretModel.id,
    fn.decrypt_iv(SecretModel.data, 'my key', 'iv', 'aes').alias('data'))

SecretModel.insert(
    data=fn.encrypt_iv('shhhh', 'my key', 'iv', 'aes')).execute()
```


Thanks, this is really helpful!
Issue: Add support for MySQL TIMESTAMP field
Author: denilsonsa
State: closed
Created: 2014-07-23 15:12:34

Peewee already has support for `datetime` field, but if I'm working with a "legacy" database, it would be desirable to also have [`timestamp` field](https://dev.mysql.com/doc/refman/5.5/en/datetime.html).


You can always implement a custom field type: http://peewee.readthedocs.org/en/latest/peewee/models.html#creating-a-custom-field

I do not have any plans at the moment to implement the timestamp field, however.


For future reference, MySQL's `TIMESTAMP` field behaves almost like `DateTimeField`, so a trivial implementation looks like this:

```
# Custom field, behaves just like DateTimeField.
class TimestampField(DateTimeField):
    db_field = 'timestamp'

MySQLDatabase.register_fields({'timestamp': 'TIMESTAMP'})
```

However, this implementation is likely incomplete. In special, a `DATETIME` field stores the date in the exact format passed to the database, while a `TIMESTAMP` field will convert to UTC while writing, and convert from UTC to local time in a SELECT result.

For a simple order_by, I can use `TIMESTAMP` and `DATETIME` the same way. For anything else, we might need extra code.
Issue: Request: joining subqueries
Author: alvra
State: closed
Created: 2014-07-21 15:36:47

I'd like to be able to construct queries like

``` sql
SELECT *
FROM t1
INNER JOIN (
    SELECT * FROM t2
) ON t2.rel_id = t2.id
```

so that's joining a subquery (`peewee.SelectQuery`) instead of a table (`peewee.Model` or `peewee.ModelAlias`). (Of course, this makes more sense if the subquery is a bit more complex, but you get the idea.)
Currently (2.2.5), when trying this, it doensn't work.

```
>>> Related.select().join(Thing, on=Thing.related==thing)  # works as expected
<class 'peewee.Related'> SELECT ... FROM "related" AS t1 INNER JOIN "thing" AS t2 ON (t2."related_id" = t1."id") []
>>> Related.select().join(Thing.select(), on=Thing.related==thing)  # fails unfortunately
Traceback (most recent call last):
  File "<console>", line 1, in <module>
  File "peewee.py", line 1677, in __repr__
    instance = collected_models[key]
  File "peewee.py", line 2059, in sql
    return query
  File "peewee.py", line 1254, in generate_select
    left_field = field
  File "peewee.py", line 1203, in calculate_alias_map
    if node._negated:
  File "peewee.py", line 1194, in <lambda>
    unknown = True
AttributeError: 'SelectQueryWrapper' object has no attribute '_meta'
```

When looking through the code it seems Peewee doesn't support this, and I couldn't find a simple way to make this work.


``` python
things = Thing.select().alias('j1')
Related.select().join(things, on=(Related.thing == things.c.id))
```


Thanks :)
Issue: Support Request: Nested dicts (1 -> n) select query
Author: alexlatchford
State: closed
Created: 2014-07-21 04:05:56

Hi Charles,

I'm looking to produce a list of dictionaries from a query so it's easy to serialise to json for API output.. The twist over the conventional usage of `dicts()` is that I'd like to perform a join and have that aggregate into a sub-list..

Puesdo-code example:

``` python
class Filter(Model):
    uuid = UUIDField(primary_key=True)
    name = CharField()

class Keyword(Model):
    name = CharField()
    filter = ForeignKeyField(Filter)

# Create and populate tables..

json = Filter.select(Filter.uuid, Filter.name, Keyword.name).join(Keyword, JOIN_LEFT_OUTER).dicts()
```

I'd like the API to output something like this..

``` json
[
    {
         "uuid": "...",
         "name": "My filter 1",
         "keywords": ["a", "b", "c"]
    },
    {
         "uuid": "...",
         "name": "My filter 2",
         "keywords": ["d", "e", "f"]
    },
    {
         "uuid": "...",
         "name": "My filter 3",
         "keywords": []
    },
    ...
]
```

Sorry for the puesdo-code, is there a helper that could perform something like this or should I just roll my own? It'll be a reasonably common operation for this API unfortunately thus I'm asking to see if I've missed anything in the manual :)

Thanks,
Alex


Unfortunately this is not currently supported using `.dicts()`. Searching 1 -> N can be accomplished using the special `prefetch` API. Behind-the-scenes, `prefetch` will construct the appropriate subqueries to ensure you don't have O(n) queries when fetching "keywords" in your example.

Your best bet is to write something like the following:

``` python
filters = Filter.select().order_by(Filter.name.desc())
keywords = Keyword.select().order_by(Keyword.keyword.desc())
filter_keyword = prefetch(filters, keywords)

def filter_to_dict(f):
    data = f._data.copy()
    data['keywords'] = [k.keyword for k in f.keywords_prefetch]
    return data

print map(filter_to_dict, filter_keyword)
```

Would print:

```
[{'keywords': [u'k3-3', u'k3-2', u'k3-1'], 'id': 3, 'name': u'filter3'},
 {'keywords': [u'k2-3', u'k2-2', u'k2-1'], 'id': 2, 'name': u'filter2'},
 {'keywords': [u'k1-3', u'k1-2', u'k1-1'], 'id': 1, 'name': u'filter1'}]
```


Awesome, cheers :)
Issue: Add support for conditional saving model.
Author: yadutaf
State: closed
Created: 2014-07-19 14:34:37

Concurrent nodes of an application attempting to update the same record might result in data corruption/loss. To help with this scenario, this PR adds an optional `conditions` parameter on `Model.Save`. When the conditions are not met, updated record count will be `0` letting the application know the failure.


Thank you but I will pass on this, since it can be accomplished with an `UpdateQuery`.
Issue: SQLite doesn't like double parens
Author: jhorman
State: closed
Created: 2014-07-18 08:14:40

This sample program shows the problem

``` python
from peewee import Model
from peewee import fn
from peewee import SqliteDatabase

db = SqliteDatabase(':memory:')

class User(Model):
    class Meta:
        db = db

User.create_table()

list(User.select().where(fn.Exists(User.select(User.id))))
```

Func always wraps the nodes in parens: https://github.com/coleifer/peewee/blob/2.2.5/peewee.py#L1141

And nested selects are always in parens: https://github.com/coleifer/peewee/blob/2.2.5/peewee.py#L1174

One approach might just be in the `isinstance(node, Func)` detect if the sql to wrap already `startswith('(')` and `endswith(')')`, but maybe there is something more clever.


``` sql
SELECT t1."id"
FROM "users" AS t1 
WHERE Exists((SELECT t2."id" FROM "users" AS t2))
```


Nice, thanks!
Issue: problem with Model.select(SQL('*'))...
Author: goir
State: closed
Created: 2014-07-18 07:34:05

Hi,

I have a very complex Query and want to use SQL('*') to select all fields to avoid specifying all fields and making the query even longer.
Very basic example:

```
from peewee import SqliteDatabase, PrimaryKeyField, CharField, Model, SQL, fn

db = SqliteDatabase(':memory:')

class Model1(Model):
    id = PrimaryKeyField()
    column = CharField()

    class Meta(object):
    database = db

Model1.create_table()
Model1.create(id=1, column='bar')
Model1.create(id=2, column='foo')
Model1.create(id=3, column='baz')

res = Model1.select(SQL("*"))
print res
for a in res:
    print a
```

which results in the following error:

```
<class '__main__.Model1'> SELECT * FROM "model1" AS t1 []
  File "<path>/tests/bla.py", line 30, in <module>
    for a in res:
  File "<path>/env_local/local/lib/python2.7/site-packages/peewee.py", line 1615, in next
    obj = self.iterate()
  File "<path>/env_local/local/lib/python2.7/site-packages/peewee.py", line 1601, in iterate
    self.initialize(self.cursor.description)
  File "<path>/env_local/local/lib/python2.7/site-packages/peewee.py", line 1643, in initialize
    select_column = self.column_meta[i]
IndexError: list index out of range
```

i'm using the current master.

Thanks
Goir


Peewee does not currently support this, unfortunately. You can always pass in the model class to `.select()` and it will select the appropriate columns.
Issue: UUID fixes
Author: alexlatchford
State: closed
Created: 2014-07-16 00:43:42

Hi Charles,

Found another couple of bugs in the UUID implementation.
1. Finish off the UUIDField ForeignKeyField fixes, forgot to do the python_value implementation.. `UUID(None)` fails hard :(
2. Using a model as a query param fails for UUID primary key models with the following error:

```
[16/Jul/2014:10:05:35] HTTP Traceback (most recent call last):
  File "/Library/Python/2.7/site-packages/cherrypy/_cprequest.py", line 656, in respond
    response.body = self.handler()
  File "/Library/Python/2.7/site-packages/cherrypy/lib/encoding.py", line 188, in __call__
    self.body = self.oldhandler(*args, **kwargs)
  File "/Library/Python/2.7/site-packages/cherrypy/_cpdispatch.py", line 34, in __call__
    return self.callable(*self.args, **self.kwargs)
  File "/Users/alexlatchford/Code/abbreviate/a8-product/a8_product/../a8_product/routes/external_api/v1/summaries.py", line 190, in POST
    db_image = SummaryImage.get(SummaryImage.summary == db_summary, SummaryImage.src == image['src'])
  File "/Library/Python/2.7/site-packages/peewee.py", line 3049, in get
    return sq.get()
  File "/Library/Python/2.7/site-packages/peewee.py", line 2093, in get
    return clone.execute().next()
  File "/Library/Python/2.7/site-packages/peewee.py", line 2132, in execute
    self._qr = ResultWrapper(model_class, self._execute(), query_meta)
  File "/Library/Python/2.7/site-packages/peewee.py", line 1838, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/Library/Python/2.7/site-packages/playhouse/postgres_ext.py", line 255, in execute_sql
    self.commit()
  File "/Library/Python/2.7/site-packages/peewee.py", line 2283, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "/Library/Python/2.7/site-packages/playhouse/postgres_ext.py", line 248, in execute_sql
    res = cursor.execute(sql, params or ())
ProgrammingError: can't adapt type 'UUID'
```

I think this example should demonstrate the problem!

``` python
from peewee import *
from playhouse.postgres_ext import *
from uuid import UUID

class SomeModelA(Model):
    uuid = UUIDField(primary_key=True)

class SomeModelB(Model):
    name = CharField()
    some_model_a = ForeignKeyField(SomeModelA)

# Create and populate the tables etc..

some_model_a = SomeModelA.get(SomeModelA.uuid == UUID('<uuid>'))
some_model_b = SomeModelB.get(SomeModelB.some_model_a == some_model_a)
# throws ProgrammingError
```

Thanks,
Alex


Awesome cheers Charles. 

This is amazing, I've already taken your code and am currently building and about to push to live :)
Issue: create_table() got an unexpected keyword argument 'options'
Author: danieldiekmeier
State: closed
Created: 2014-07-15 11:39:33

Okay, so I'm trying to create a FTSModel according to the instructions here: http://peewee.readthedocs.org/en/latest/peewee/playhouse.html#FTSModel

I have a model `Book` and a model

```
class FTSBook(FTSModel):
    content = TextField()
```

that should be created and filled with

```
FTSBook.create_table(content=Book)
FTSBook.rebuild()
FTSBook.optimize()
```

but on the `create_table`-statement, I get the following error:

```
Traceback (most recent call last):
  File "upgrade.py", line 218, in <module>
    FTSBook.create_table(content=Book)
  File "/Users/Daniel/dev/python/lesetagebuch/venv/lib/python3.4/site-packages/playhouse/sqlite_ext.py", line 102, in create_table
    cls._meta.database.create_table(cls, options=options)
TypeError: create_table() got an unexpected keyword argument 'options'
```

So it seems to be an error inside the sqlite_ext-Module. Why is the options-keyword unexpected? I tried to just to `FTSBook.create_table()` (without any options), but I get the same error.


Your models need to be using the `playhouse.sqlite_ext.SqliteExtDatabase` instead of the normal `peewee.SqliteDatabase`.


So:

``` python
class FTSBook(FTSModel):
    content = TextField()
    class Meta:
        database = <some SqliteExtDatabase() instance>
```
Issue: meta_options are overwritten by clauses in child-classes
Author: alexlatchford
State: closed
Created: 2014-07-15 08:13:13

Hi Charles,

I think this example should be valid but is having difficulties:

``` python
from peewee import *

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

class BaseModel(Model):
    created = DateTimeField()

    class Meta:
        database = db
        order_by = ('-created',)

class Extension(BaseModel):
    some_field = CharField()
```

Gives the following error:

```
alexs-air:Code alexlatchford$ python test.py 
Traceback (most recent call last):
  File "test.py", line 13, in <module>
    class Extension(BaseModel):
  File "/Library/Python/2.7/site-packages/peewee.py", line 2990, in __new__
    cls._meta.prepared()
  File "/Library/Python/2.7/site-packages/peewee.py", line 2861, in prepared
    field = self.fields[clause.lstrip('-')]
AttributeError: 'DateTimeField' object has no attribute 'lstrip'
```

Sorry I haven't had time to investigate it and find a patch, I'll see what I can do if this really annoys me. I'm just putting the order_by declarations on the sub-classes instead of the parent :)

Thanks,
Alex


Thanks for reporting this -- yes, peewee replaces the `order_by` string with a clause, but this is acting up in the chlid-classes. I will get a fix out soon.
Issue: Dirty field not set in RelationDescriptor __set__ override
Author: jhorman
State: closed
Created: 2014-07-09 05:50:32

Setting foreign key values doesn't mark those fields dirty.

https://github.com/coleifer/peewee/blob/2.2.5/peewee.py#L885

I am also wondering if Model.prepared should clear dirty fields. As is, just fetching objects from the DB ends up marking all the fields dirty.

https://github.com/coleifer/peewee/blob/2.2.5/peewee.py#L3140

Both of these issues make it hard to call save with `only=self.dirty_fields`


Another related question is just, why not default to always only saving dirty fields. Just wondering if I am missing something where that would be unsafe? That does seem to be the behavior when you have a CompositeKey? https://github.com/coleifer/peewee/blob/2.2.5/peewee.py#L3159


> Another related question is just, why not default to always only saving dirty fields.

It's easier to understand.


I can just actually lead to some unclear behavior. What happened to me is that I have an incrementing field on an object, num_members. I never modify this in code, only in a trigger, but save was overwriting num_members with an older value. The advantage of only saving modified fields is that you can be sure there are no unintended overwrites like this.
Issue: Duplicate results when iterating a select query and creating unrelated models
Author: moloney
State: closed
Created: 2014-07-09 03:46:51

I know modification of an iterable while iterating over it can cause problems, but it seems surprising that I can't iterate a select query while creating completely unrelated model instances.

``` python
import peewee as pw

class DatabaseModel(pw.Model):
    class Meta:
        database = pw.SqliteDatabase(':memory:')

class A(DatabaseModel):
    name = pw.CharField()

class B(DatabaseModel):
    name = pw.CharField()

A.create_table()
B.create_table()

for name in ('test1', 'test2'):
    A.create(name=name)

for a in A.select():
    print a.name
    B.create(name='blah')
```

Produces:

```
test1
test2
test1
test2
```

Not sure if this is expected, but I couldn't find anything in the documentation.


I found that wrapping the for loop in a transaction solves the problem, so I guess this is expected behavior.  It does still seem worth it to make this more clear in the documentation.


Check out:
- https://github.com/coleifer/peewee/issues/12#issuecomment-5614404
- https://github.com/coleifer/peewee/issues/81