akumoth/peewee-issues huggingface dataset:
Issue: [HELP] how to union_all subquerys
Author: megachweng
State: closed
Created: 2018-08-15 13:30:13

```Python
class Wtlog(BaseModel):
    api = CharField(column_name='api_id', constraints=[SQL("DEFAULT ''")])
    ctime = DateTimeField(constraints=[SQL("DEFAULT current_timestamp()")], null=True)
    new_name = CharField(null=True)
    old_name = CharField(null=True)
```
The raw SQL is 

```MySQL
SELECT api_id,id,new_name AS name
FROM ( SELECT * FROM wtlog WHERE checked = 0 ORDER BY id DESC ) x GROUP BY api_id 
	
UNION ALL

SELECT api_id,id,new_name AS name
FROM ( SELECT * FROM wtlog WHERE checked = 0 ORDER BY id ) x GROUP BY api_id 

ORDER BY api_id, id
```

really have no idea how to solve this


I'm not clear why you are writing your queries like that...but here's how you might do it with Peewee:

```python
LA1 = Log.alias()
subq1 = LA1.select(LA1).where(LA1.checked == 0).order_by(LA1.id.desc())

lhs = (Log
       .select(subq1.c.api_id, subq1.c.id, subq1.c.new_name.alias('name'))
       .from_(subq1)
       .group_by(subq1.c.api_id))

LA2 = Log.alias()
subq2 = LA2.select(LA2).where(LA2.checked == 0).order_by(LA2.id)

rhs = (Log
       .select(subq2.c.api_id, subq2.c.id, subq2.c.new_name.alias('name'))
       .from_(subq2)
       .group_by(subq2.c.api_id))

query = lhs.union_all(rhs).order_by(SQL('1, 2'))
```

The SQL that's generated is:

```sql
SELECT "t1"."api_id", "t1"."id", "t1"."new_name" AS "name" 
FROM (
    SELECT "t2"."id", "t2"."api_id", "t2"."timestamp", "t2"."new_name", "t2"."old_name", "t2"."checked" 
    FROM "log" AS "t2" 
    WHERE ("t2"."checked" = ?) 
    ORDER BY "t2"."id" DESC) AS "t1" 
GROUP BY "t1"."api_id" 
UNION ALL 
SELECT "t3"."api_id", "t3"."id", "t3"."new_name" AS "name"
FROM (
    SELECT "t4"."id", "t4"."api_id", "t4"."timestamp", "t4"."new_name", "t4"."old_name", "t4"."checked" 
    FROM "log" AS "t4" 
    WHERE ("t4"."checked" = ?) 
    ORDER BY "t4"."id"
) AS "t3" 
GROUP BY "t3"."api_id" 
ORDER BY 1, 2
```

@coleifer I just want to select latest and soonest row of each group which is grouped by api_id，and are there effective way to implement it with peewee，appreciate your help

Check out this post, which talks about various ways to approach the "top item per group" query: http://charlesleifer.com/blog/techniques-for-querying-lists-of-objects-and-determining-the-top-related-item/

It's a common thing and there are several ways to do it.

You might find it easier to do something like:

```python
LA = Log.alias()
subq = (LA.select(
    LA.api,
    fn.MIN(LA.id).alias('min_id'), 
    fn.MAX(LA.id).alias('max_id'))
.group_by(LA.api)
.where(LA.checked == 0))

query = Log.select().join(subq, on=(
    (Log.api == subq.c.api_id) &
    ((Log.id == subq.c.min_id) | (Log.id == subq.c.max_id)))
```
Issue: `User.select().dicts()` not return dict-list
Author: bingtel
State: closed
Created: 2018-08-14 08:36:18

When I want to get a list of dict by `q = User.select().dicts()` , q is not a list of dict;
I have to `[e for e in q]` to get a dict-list.

Peewee lazily evaluates queries -- they are not evaluated until iterated (or you explicitly call .execute(), and in that case you get a special/smart iterator).

To get a list of dicts, either:

```python
q = User.select().dicts()
list_of_dicts1 = q[:]
list_of_dicts2 = list(q)
list_of_dicts3 = [d for d in q]
```
Issue: Using fn.ST_AsText does not work in a select query
Author: bmamouri
State: closed
Created: 2018-08-10 07:43:36

I am trying to select a column of type `GEOMETRY` in WKT format using the ST_AsText function in PostgreSQL. Interestingly, peewee is generating the correct SQL but the query result is not fetched using the ST_AsText function. 

```
from peewee import *

database = PostgresqlDatabase('test', user='postgres', password='', host='127.0.0.1')

class GeometryField(Field):
    """
    Custom field that makes writing
    """
    field_type = 'geometry'

class Homes(Model):
    name = CharField()
    latlng = GeometryField(null=True)

    class Meta:
        database = database


if __name__ == '__main__':
    database.drop_tables([Homes])
    database.create_tables([Homes])

    # Create
    Homes.create(name='Test', latlng=fn.ST_MakePoint(1, 10))

    print(Homes.select(Homes, fn.ST_AsText(Homes.latlng)).sql())

    # Select
    homes = Homes.select(Homes, fn.ST_AsText(Homes.latlng)).execute()
    print([model_to_dict(home) for home in homes])

    # Update
    home = Homes.get_by_id(1)
    home.name = 'Something new'
    home.save()
```

This will result in the following:
```
('SELECT "t1"."id", "t1"."name", "t1"."latlng", ST_AsText("t1"."latlng") FROM "homes" AS "t1"', [])
[{'id': 1, 'name': 'Test', 'latlng': '0101000000000000000000F03F0000000000002440'}]
```

First off, try modifying your query so that you're not selecting "latlng" twice:

```python
query = Homes.select(Homes.name, fn.ST_AsText(Homes.latlng).alias('latlng'))
```

You may not need the ".alias()" so try with/without.

@coleifer, thank you for the reply. Unfortunately, that does not work either. I still get the `latlng` column in binary representation:

```
('SELECT "t1"."id", "t1"."name", "t1"."latlng", ST_AsText("t1"."latlng") AS "latlng" FROM "homes" AS "t1"', [])
[{'id': 1, 'name': 'Test', 'latlng': '0101000000000000000000F03F0000000000002440'}]
```

@coleifer, I managed to make it work. As long as the `latlng` column selected first, it works:

```
    homes = Homes.select(fn.ST_AsText(Homes.latlng).alias('latlng'), Homes).execute()
    print([model_to_dict(home) for home in homes])
```

Result:
```
('SELECT "t1"."id", "t1"."name", "t1"."latlng", ST_AsText("t1"."latlng") AS "latlng" FROM "homes" AS "t1"', [])
[{'id': 1, 'name': 'Test', 'latlng': 'POINT(1 10)'}]
```

Yes -- that was why I suggested that you explicitly declare which columns you are selecting, rather than selecting everything (which happens when you do `.select(Homes)`) and then also selecting `latlng`. Unlike SQL, which returns your rows as a table, peewee is restricted to using Python named attributes so whichever value is selected first - for a given attribute - will be used and any other values with the same attribute ("latlng" in this case) will be ignored.
Issue: How to end a simulated user.
Author: qiaohao9
State: closed
Created: 2018-08-07 05:50:04

I now have a question like how to end a simulated user.

Scenes:

The simulated user needs to log in before the simulation request can be made to the following `@task`, but if the login fails, the user will not proceed with the subsequent tasks, and will directly exit, and also need to finish the work.

Excuse me, does peewee provide such an interface? Because I didn't see a function about ending the mock user in the documentation.
Issue: update a model field mysql can't auto update the `ON UPDATE` field?
Author: axiaoxin
State: closed
Created: 2018-08-07 04:15:13

I created a table use `mysql < test.sql`, test.sql content is:

```sql
CREATE TABLE IF NOT EXISTS `caller` (
     `id` INT NOT NULL AUTO_INCREMENT COMMENT 'caller id',
     `key` VARCHAR(36) NOT NULL DEFAULT '' COMMENT 'caller key',
     `admin` VARCHAR(255) NOT NULL DEFAULT '' COMMENT 'admin list',
     `is_deleted` TINYINT NOT NULL DEFAULT 0 COMMENT 'is deleted',
     `created_at` DATETIME NOT NULL DEFAULT NOW() COMMENT 'created time',
     `updated_at` DATETIME NOT NULL DEFAULT NOW() ON UPDATE NOW() COMMENT 'updated time',
     PRIMARY KEY (`id`),
     UNIQUE KEY `uk_key` (`key`)
)
ENGINE=InnoDB DEFAULT CHARSET=utf8
COMMENT = 'caller table';
```

then I defined a model

```python
class Caller(MySQLBaseModel):
    id = peewee.IntegerField(primary_key=True, sequence=True)
    key = peewee.CharField(default='')
    admin = peewee.CharField(default='')
    is_deleted = peewee.BooleanField(default=False)
    created_at = peewee.DateTimeField(default=datetime.datetime.now)
    updated_at = peewee.DateTimeField(default=datetime.datetime.now)

      class Meta:
         db_table = 'caller'
```

when I  update the model, the updated_at field's `ON UPDATE` dose not work? the time is still when I created it.

```python
def update_record(id, admin=None, key=None, is_deleted=None):
    data = Caller.select().where(Caller.id == id).first()
    if not data:
        return
    if admin is not None:
        data.admin = admin
    if key is not None:
        data.key = key
     if is_deleted is not None:
        data.is_deleted = is_deleted
     # data.updated_at = datetime.datetime.now()
     data.save()

      return data
```

Peewee is an active-record ORM...so when you call ".save()" it will, by default, explicitly set the values for all columns using whatever values are stored in the model instance at that time.

Additionally, the `default=...` values only apply when the model does not already have a value for the given fields. So "datetime.datetime.now()" will be called the first time the model is created, but subsequently (assuming the field values are not None) the initial values will not be overwritten.

You're probably best doing something like this:

```python
class Caller(Model):
    def save(self, *args, **kwargs):
        self.updated_at = datetime.datetime.now()
        return super(Caller, self).save(*args, **kwargs)
```

That's the more commonly-used pattern with active-record ORMs like Peewee.

Alternatively, explicitly issuing an `UPDATE` query (by calling `Caller.update()`) would probably have the effect you're looking for.

```python
obj = Caller.select()...first()
update = {}
if admin is not None:
    update[Caller.admin] = admin
if key is not None:
    update[Caller.key] = key
# etc.
if update:
    Caller.update(update).where(Caller.id == id).execute()
```

Thank you very much!
Issue: sqlite conflict_update version check ignores apsw version
Author: brandond
State: closed
Created: 2018-08-02 20:33:03

I'm using `APSWDatabase` with APSW 3.24.0, but calling `on_conflict` still raises an exception claiming 'ValueError: SQLite does not support specifying which values to preserve or update.' It appears that `conflict_update` is checking the Python sqlite3 module version, instead of the apsw sqlite version, when deciding whether or not the feature is supported.

Thanks for the quick fix! I had literally just hacked around my local install with the same monkeypatch:

```
import peewee
peewee.__sqlite_version__ = tuple(int(x) for x in apsw.sqlitelibversion().split('.'))
```
Issue: Proxy() Has No "__exit__" Attribute
Author: madelyneriksen
State: closed
Created: 2018-08-01 21:01:16

### Version, System, Python Info

* Peewee v. 3.6.4
* Python v. 3.5.2
* OS: Ubuntu 16.04 LTS

### Description of Issue

The docs mentions that we can use databases as a context manager for connections. I have also lately taken to using Proxy() to test an application's behavior on multiple databases. I ran into the following quirk:

```
import peewee as pw

database_proxy = pw.Proxy()
database = pw.SqliteDatabase("foo.db")  # So far so good

database_proxy.initialize(database)  # Initialization is working great

with database_proxy:  # At this point in the code, an AttributeError is thrown over __exit__
    do_things()
```

It seems that unlike a regular database object, a Proxy made database after initialization cannot be used as a context manager.

### Work Around

To get around this, I wrote a small context manager that manages the connection to the Proxy database. Then I put this context manager in a decorator I use to manage table creation, connection, and other boilerplate code for a function.

I am not sure if this is in line with the best practices, and if it is not I'd love to see the proper way of handling such a use case.

What is the recommended course of action here?

Well, `Proxy` works by overriding `__getattr__()` and `__setattr__()`...I think the crux of the issue is that these overrides do not cover special methods such as `__enter__` and `__exit__`. This is described in the [Python documentation](https://docs.python.org/3/reference/datamodel.html#special-method-lookup).

The fix in this case is to add specific overrides for `__enter__` and `__exit__` on the `Proxy` class itself. An alternative, which should work, is to use `with database_proxy.connection_context():` (which has slightly different semantics, in that it does not also automatically wrap with a transaction).

Thank you for responding so quickly!

I think that `with database_proxy.connection_context():` might work for my use case, and seems like a good solution. If not, using a custom context manager isn't bad either. Thanks!

Well, either way it's fixed in `master` and the fix will be included in the next release.
Issue: Subquery expressions not parenthesized as function arguments
Author: zmwangx
State: closed
Created: 2018-08-01 14:54:17

peewee version: 3.6.4

It seems that peewee does not parenthesize subquery expressions when used in a function, resulting in syntax errors (at least with SQLite).

Consider this minimal example, where we run the same query with a subquery expression twice, except in the second case we try to coalesce the subquery expression (a sum that could be null) with 0:

```py
#!/usr/bin/env python3

import logging
import sys

import peewee

logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)

db = peewee.SqliteDatabase('data.db')

class Record(peewee.Model):
    amount = peewee.IntegerField()

    class Meta:
        database = db

db.drop_tables([Record])
db.create_tables([Record], safe=True)
for amount in range(1, 6):
    Record.create(amount=amount)

RecordAlias = Record.alias()
q = (Record
     .select(RecordAlias
             .select(peewee.fn.SUM(RecordAlias.amount))
             .where(RecordAlias.amount < Record.amount).alias('sum'))
     .order_by(peewee.SQL('sum')))
for r in q:
    print(r.sum)

# Collasce a scalar subquery
q = (Record
     .select(peewee.fn.COALESCE(
         RecordAlias
         .select(peewee.fn.SUM(RecordAlias.amount))
         .where(RecordAlias.amount < Record.amount),
         0,
     ).alias('sum'))
     .order_by(peewee.SQL('sum')))
try:
    for r in q:
        print(r.sum)
except peewee.PeeweeException as e:
    sys.exit('Error: %s' % e)
```

When executed, here's the output:

```sql
('DROP TABLE IF EXISTS "record"', [])
('CREATE TABLE IF NOT EXISTS "record" ("id" INTEGER NOT NULL PRIMARY KEY, "amount" INTEGER NOT NULL)', [])
('INSERT INTO "record" ("amount") VALUES (?)', [1])
('INSERT INTO "record" ("amount") VALUES (?)', [2])
('INSERT INTO "record" ("amount") VALUES (?)', [3])
('INSERT INTO "record" ("amount") VALUES (?)', [4])
('INSERT INTO "record" ("amount") VALUES (?)', [5])
('SELECT (SELECT SUM("t1"."amount") FROM "record" AS "t1" WHERE ("t1"."amount" < "t2"."amount")) AS "sum" FROM "record" AS "t2" ORDER BY sum', [])
None
1
3
6
10
('SELECT COALESCE(SELECT SUM("t1"."amount") FROM "record" AS "t1" WHERE ("t1"."amount" < "t2"."amount"), ?) AS "sum" FROM "record" AS "t2" ORDER BY sum', [0])
Error: near "SELECT": syntax error
```

Here the correct query would be

```sql
SELECT COALESCE((SELECT SUM("t1"."amount") FROM "record" AS "t1" WHERE ("t1"."amount" < "t2"."amount")), 0) AS "sum" FROM "record" AS "t2" ORDER BY sum
```

The result of which would be

```
0
1
3
6
10
```

Fixed, thanks for reporting.
Issue: How to sort by geo distance?
Author: BadReese
State: closed
Created: 2018-07-31 07:33:48


provide a geo point, and sorted by distance

I'm assuming you're using something like PostGIS for Postgresql, or Spatialite for SQLite.

You just need to find the appropriate distance function and call it, e.g. [ST_distance](https://postgis.net/docs/ST_Distance.html):

```python
fn.ST_Distance(SomeModel.point_1, SomeModel.point_2)
```
Issue: basic database sharding
Author: iurly
State: closed
Created: 2018-07-29 22:06:27

Hi,
I'm building some data logging application for which I'd like to use SQLite.
Data comes from a limited number of data sources, which could be considered as separate subdomains, so it would make sense to have a single database for each data source (each with the same schema).
Given the nature of the data, it would also make sense to have e.g. one database per day, so that log rotation could be easily performed by just deleting the oldest file.
On the other hand, I would like to be able to access all the data from all these databases with a single query for instance. 
Would there be a way of doing this with peewee? It doesn't really have to be super-optimized.
So for instance, a select() could simply loop over all databases return the union of all results.
An insert() would find the corresponding database or create one if it doesn't exist.
I don't have the faintest idea of how query objects work under the hood, so I don't know if there's a way of extending some class to override this kind of behavior.
Any suggestion?
Thank you!

> I don't have the faintest idea of how query objects work under the hood, so I don't know if there's a way of extending some class to override this kind of behavior.

I'd suggest you take a deep dive into the code if you're considering implementing this kind of logic with peewee.

SQLite has a neat feature called "ATTACH" where you can attach different database files and access them, much like you might use separate schemas in Postgresql. Peewee supports this, though you'll need to do some work to reuse the same models across multiple schemas.

Query objects are just thin wrappers over cursor objects.
Issue: Updated postgres_ext's HStoreFields are not "dirty"
Author: droserasprout
State: closed
Created: 2018-07-23 14:30:05

Reproduced with peewee-3.6.4:
```
from datetime import datetime
import sys, os
import db_models as beo

from playhouse.shortcuts import model_to_dict, update_model_from_dict
from playhouse.postgres_ext import *

import config
from datetime import datetime
from playhouse.postgres_ext import *
import psycopg2
from playhouse.shortcuts import model_to_dict, update_model_from_dict

database = PostgresqlExtDatabase(database=config.POSTGRES_DATABASE,
                                 user=config.POSTGRES_USER,
                                 password=config.POSTGRES_PASSWORD,
                                 host=config.POSTGRES_HOST,
                                 port=config.POSTGRES_PORT,
                                 cursor_factory=psycopg2.extras.DictCursor, register_hstore=True)


class ModelBase(Model):
    class Meta:
        database = database
        only_save_dirty = True

class Waybill(ModelBase):
    id = IntegerField(primary_key=True)
    waybill_number = CharField(null=True)
    hwb_extras = HStoreField(null=True)

a = beo.Waybill.select().where(beo.Waybill.id == 1).first()
a.hwb_extras
{'Test': 'Dirty!'}

a.hwb_extras['Updated'] = 'I\'m so dirty!'
a.hwb_extras
{'Test': 'Dirty!', 'Updated': "I'm so dirty!"}

a.save()
False

a.waybill_number = '12345678'
a.save()
1
```

Yea, the way peewee detects "dirty" fields is based on observing `__set__` on a descriptor, so it's not perfect in the sense that it actually keeps track of initial state and does a comparison at `save()`-time. You are free to subclass and extend the `Model` behavior to track state more thoroughly, but at present I consider this to be a known limitation.
Issue: Update to Maintain Compatibility
Author: jseagrave21
State: closed
Created: 2018-07-22 17:50:24

This change allows compatibility between projects formerly using peewee2.6.2.

When trying to use the latest version of peewee we get the following errors relating to `_StringField`: "Could not get key 'utf-8' codec can't decode byte 0xb4 in position 3" or similar.

Making this change allows easy compatibility.

See issue #262 in neo-python project here: https://github.com/CityOfZion/neo-python/issues/262

If you're storing a byte array -- i.e., *not* UTF8-encoded text -- you should be using a `BlobField`.

@coleifer Thank you for your help! I've almost got it working with 'Blobfield'.
Issue: One sequence for all primary keys in all tables of database
Author: ak4nv
State: closed
Created: 2018-07-22 09:53:38

Hi!
This is very specific requirement: create a one sequence for all primary keys in all tables of database with start from 10000. It's simple for cli but is it possible to do in schema creation process?

Thanks!

Your best bet is to simply add the following before you create the tables:

```python

def setup_schema():
    with db:
        db.execute_sql('create sequence if not exists "sequence_name" start with 10000')
        db.create_tables([Foo, Bar, Baz, etc])
```

You can always do something like this, too:

```python
class MyAutoField(AutoField):
    def __init__(self, *a, **k):
        super(MyAutoField, self).__init__(sequence='the_sequence', *a, **k)
```

And use `id = MyAutoField()` in your models.

As I understand this code raises an exception for postgresql if you create a table. Too many default values for field or something like this. Should be `IntegerField` instead of `AutoField` and add `primary_key=True` for arguments.
If you suggest another way would be great!

Ah, thanks for the clarification. Yes, using `IntegerField(sequence='...', primary_key=True)` should do the trick.
Issue: Create index with operator classes
Author: ak4nv
State: closed
Created: 2018-07-22 09:17:33

Hi there!
Is it possible to create an index like this:
```sql
create index on taxonomies_names (lower(name) varchar_pattern_ops, name_class)
where name_class = 'scientific name';
```

Thanks!

For now the easiest way may be:

```python
class Taxonomy(Model):
    name = CharField()
    # ...

    class Meta:
        indexes = [
            SQL('create index on taxonomies_names (lower(name) varchar_pattern_ops...'),
        ]
```

Alternatively, you can:

```python

from peewee import NodeList

name = NodeList((fn.LOWER(Taxonomy.name), SQL('varchar_pattern_ops')))
index = (Taxonomy
         .index(name, Taxonomy.name_class)
         .where(Taxonomy.name_class == 'scientific name'))
Taxonomy.add_index(index)
```

The above will result in:

```python
CREATE INDEX "taxonomy_name_class" ON "taxonomy" (
    LOWER("name") varchar_pattern_ops, "name_class"
) WHERE ("name_class" = 'scientific name');
```

Docs:

* http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.index
* http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.add_index
* http://docs.peewee-orm.com/en/latest/peewee/api.html#ModelIndex
Issue: Query do not support "%s as something"
Author: m358807551
State: closed
Created: 2018-07-20 06:42:36

worked:
`PersonModel.raw("select name from person where id=%s", (1,))`
not worked:
`PersonModel.raw("select name, %s as field1 from person where id=%s", ("aaa",1))`

Is there a reason you are using ".raw()"? You could very easily write your second example as:

```python
Person.select(Person.name, Value('aaa').alias('field1')).where(Person.id == 1)
```

I'm not sure why `%s as field1` wouldn't work. What error are you receiving? What database are you using?

Aha, I see the problem. The documentation also reveals the issue: http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.raw

`Model.raw()` accepts the query parameters as `*args`, not as a tuple of arguments. So you can fix the problem:

```python
PersonModel.raw("select name, %s as field1 from person where id=%s", "aaa", 1)
```

Thank you!!
I lose my mind!!〣( ºΔº )〣