akumoth/peewee-issues huggingface dataset:
Issue: Tuples in SQL
Author: maaaks
State: closed
Created: 2016-10-21 08:23:06

In a query, I need to compare tuples of values (I'm not sure how are they correctly called in SQL, maybe «records»?..), like this:

``` sql
where (a.field1, a.field2) < (b.field1, b.field2)
```

Currently I achieved this by calling an empty-named function through `fn` which works but seems to be a hack or maybe even a bug, depending on how you look at it:

``` python
getattr(fn, "")(a.field1, a.field2) < getattr(fn, "")(b.field1, b.field2)
```

Is there a better way to do this thing in Peewee? I am sure that it can be written manually using different `Node`s but shouldn't there be something for this out of the box?


Traditionally, peewee hasn't supported this explicitly. Though with SQLite's new row-values stuff getting released I think it should, so I'll look at a patch.


I haven't tried yet, but I just suddenly thought that I maybe could get what I want using `CommaClause`, couldn't I?


Yes that should certainly work.


In fact most likely that's how I'll end up implementing this: interpret tuples as CommaClause.


Actually, and of course I should have realized this, the Python interpreter won't let us override the comparators for tuples, so writing:

```
(User.id, User.username) == (1, 'user-1')
```

Will be evaluated by the Python interpreter (as `True`) and at no point are we aware that the comparison is operating on a 2-tuple.

So for now, if you wish to use tuples in Peewee, you should use the new `Tuple` class (available if you `from peewee import *`). The `Tuple` class is simply an alias for `EnclosedClause`, as you had suggested.

Here is how you would express the above:

```
Tuple(User.id, User.username) == (1, 'user-1')
```


Hi @coleifer , I was looking for a way to express [SQLite Row Values](https://www.sqlite.org/rowvalue.html#search_against_multi_column_keys) and only found the solution above by chance.
I believe this should be documented, and I'm willing to write a few words about it.
Where do you think it would be more appropriate?
Perhaps in the [Expressions section of the Query Operators chapter](http://docs.peewee-orm.com/en/latest/peewee/query_operators.html?highlight=where#expressions)?

Added some docs.

http://docs.peewee-orm.com/en/latest/peewee/query_operators.html#row-values
Issue: Select from query
Author: ak4nv
State: closed
Created: 2016-10-19 06:17:11

Hi!
Cannot understand how to select from query?

``` sql
select ms, count(ms) from
    (select unnest(string_to_array(medserv,' ')) as ms
        from hist h
        where h.state = 1
    ) as a
group by ms
order by ms
```


Used `from_` keyword.

``` python
    subq = (HistRec
            .select(fn.unnest(fn.string_to_array(HistRec.medserv, ' ')).alias('ms'))
            .where(HistRec.state == 1)
            .alias('subq'))

    qs = (HistRec
            .select(subq.c.ms, fn.count(subq.c.ms))
            .from_(subq)
            .group_by(subq.c.ms)
            .order_by(subq.c.ms))
```

@coleifer thanks for perfect ORM!


Glad you got it sorted out!
Issue: How to hook long query for debug information
Author: THaGKI9
State: closed
Created: 2016-10-18 10:35:37

I found that the peewee framework doesn't provide any way to intercept any long query and it's necessary to monitor such situation for further performance improvement.

Is there any efficient way?


This would need to be implemented in the application. You can use `SIGALARM` to do this:

``` python
def long_query(signal, frame):
    # handle long running query here
    pass

signal.signal(signal.SIGALRM, long_query)
signal.alarm(number_of_seconds_to_timeout)
try:
    execute_some_slow_query()
finally:
    signal.alarm(0)  # Clear alarm if we succeed before timing out.
```

You can, if using SQLite, look into the progress handler: https://docs.python.org/3/library/sqlite3.html#sqlite3.Connection.set_progress_handler
Issue: get of create_or_get does not account for unique indexes
Author: seaneagan
State: closed
Created: 2016-10-17 20:02:41

It accounts for fields that are `unique=True` [here](https://github.com/coleifer/peewee/blob/8dbf04073a0b02490cc8d86ae701523cc7481a4a/peewee.py#L4871), but not (possibly composite) unique indexes set on the meta model.


Correct, I can see about improving the code to include these types of constraints.


To be honest, there is no piece of code I regret more than get_or_create. It's so easy to write your own version that's specific to your model. But a general purpose one is complicated. Ugh.


I think I'm actually going to remove get_or_create because of all the headaches. So, closing this as won't fix.
Issue: trouble with ".select()" using peewee database
Author: leojacoby
State: closed
Created: 2016-10-16 05:57:19

I am making a peewee database. In my python code I try to retrieve rows from the model that may be empty:

`player_in_db = Player.select().where(Player.name == player.name_display_first_last)`

`Player` is the name of the model

`name` is a field instance in `Player` defined...

```
class Player(Model):
      name = CharField()
```

`player.name_display_first_last` is a string

I get an error that says `peewee.OperationalError: no such column: t1.name`

I've been trying to solve this problem for the bulk of today, but to no avail.  Any help would be much appreciated.  Let me know if you need any more information to help me.  Thanks.


It looks like you're missing assigning your database to your model:

``` python
db = SqliteDatabase('my_app.db')

class Player(Model):
    name = CharField()

    class Meta:
        database = db

db.create_tables([Player], True)
```
Issue: Playhouse extension for asyncpg
Author: kolanos
State: closed
Created: 2016-10-15 22:09:19

Apologies if this has been asked already, but I would very much be interested in a playhouse extension for asyncpg:
https://github.com/MagicStack/asyncpg

Is this in the plans? Or better yet, if a PR was submitted to add asyncpg support would it be considered?


The built-in async stuff all requires Python 3, and since Peewee supports both 2 and 3, any async will need to be implemented outside Peewee itself. I'd consider merging a `playhouse` extension, but as long as I'm still using Python 2 and `gevent`, I'm going to have trouble maintaining any 3.x-specific async code by myself.

There are some 3rd party libraries that implement async for Peewee, however:
- [peewee-async](https://github.com/05bit/peewee-async)
- [muffin-peewee](https://github.com/klen/muffin-peewee), integration with the async web framework [muffin](https://github.com/klen/muffin)

Hope that helps.


Oh, I should add that psycopg2 does support async, so you can do something like this:

``` python
import psycopg2
from psycopg2 import extensions

from gevent.socket import wait_read, wait_write


def monkey_patch():
    extensions.set_wait_callback(gevent_wait_callback)


def gevent_wait_callback(conn, timeout=None):
    while True:
        state = conn.poll()
        if state == extensions.POLL_OK:
            break
        elif state == extensions.POLL_READ:
            wait_read(conn.fileno(), timeout=timeout)
        elif state == extensions.POLL_WRITE:
            wait_write(conn.fileno(), timeout=timeout)
        else:
            raise psycopg2.OperationalError(
"Bad result from poll: %r" % state)
```


@coleifer Ok, will make it an external extension for now. I did this with [peewee-momoko](https://github.com/txtadvice/peewee-momoko), but think it can be much improved with asyncpg and async /await.
Issue: Question: how to pass a database in tests?
Author: aellwein
State: closed
Created: 2016-10-15 00:57:59

The documentation mentions about best practice of passing the database from the global scope:

```
sqlite_db = SqliteDatabase('my_app.db')

class BaseModel(Model):
    """A base model that will use our Sqlite database."""
    class Meta:
        database = sqlite_db
```

I use exact this pattern in my own "persistence.py" module and i need to use not 'my_app.db' for the integration tests but another (temporary) one, so how i am supposed to pass database instance in an integration test?   


You may find the following helpful: http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#test-utils

Basically, at run-time you can swap out the database by setting `Model1._meta.database = test_db` and doing the same for `Model2`, etc. That's what the above helper takes care of (as well as schema creation).
Issue: `model_to_dict` changes null foreign key fields to empty dicts
Author: seaneagan
State: closed
Created: 2016-10-13 15:03:28

[This line](https://github.com/coleifer/peewee/blob/dc0ac68f3a596e27e117698393b4ab64d2f92617/playhouse/shortcuts.py#L125) causes a null foreign key field to be encoded as an empty dict, instead of the null that it is.

When I try to decode the returned dict back into a model via `dict_to_model`, it chokes on the empty dict [here](https://github.com/coleifer/peewee/blob/bf9591c2d9a11579b7472220f0427e1547b2f539/peewee.py#L1311), since it finds a dict for the related object, but the dict is empty and thus missing a primary key.  If it found a null instead, it would correctly decode the foreign key field to a null value.  I tested that theory by using a function which walks the dict returned from `model_to_dict` changing all the empty dicts back to nulls:

``` python
def nullify_empty_dicts(y):
    def is_empty_dict(x): return type(x) is dict and not x
    def handle(x):
        if type(x) is dict:
            for index in x:
                handle_index(x, index)
        elif type(x) is list:
            for index in range(len(x)):
                handle_index(x, index)
    def handle_index(x, index):
        v = x[index]
        if is_empty_dict(v):
            x[index] = None
        else:
            handle(v)
    if is_empty_dict(y):
        return None
    else:
        handle(y)
        return y
```

When I pass the result of this to `dict_to_model` it works as expected.

Thanks for taking a look!
Issue: Easier way to define index type for Postgres custom fields
Author: yohanboniface
State: closed
Created: 2016-10-11 08:04:19

Thanks :)


Not sure what the purpose is for this change, can you explain?


> Not sure what the purpose is for this change, can you explain?

Allow to deal with `index_type` without the need of inheriting from `IndexedFieldMixin`.
The simpler, the better :)


I appreciate the simplicity of this... let me think about it. Thanks!
Issue: Unable to delete sqlite file after connection was closed
Author: theotherp
State: closed
Created: 2016-10-07 07:23:13

I'm working on a feature that allows restoring data from a backup ZIP. For this I want to replace the existing database file with one from the ZIP. But even after stopping and closing the SqliteQueueDatabase the file is still used by my process. I've tried to write a simple script to reproduce this issue but of course there it works fine. Do you have any ideas what could be the reason that the file is still locked and how to solve that? Thanks.


Looking at the code for the SQLiteQueueDatabase, I think I need to add some logic in there to close the connection. I'll get a patch up in a minute.


Note: I can delete the file on linux but not in windows.


I added the fix for that here: 72c5ea98209241f0fb3e2c1a330644735f803370

Basically, each worker thread needed to be responsible for closing it's own connection, which wasn't happening. This should be fixed now.


Thank you!


Although this was a somewhat obscure problem in my case, if I can help only one developer not spend hours on trying to debug this issue... For future reference:

I'm developing a flask app for which I have an automatic reloader enabled which restarts the web app every time a python file is changed (this is not enabled in production, of course). This forked the process (so that it can close the old one and restart it on change) which created a handle on the database file and locked it. That's why it could not be deleted. I started flask with the reloader disabled and voilà, the file could be deleted after closing the database. It worked on my linux test VM because there I have production code running where the reloader is disabled, but I was misled into thinking it could be an OS issue.

I'll need to check how many of my database issues are caused by this because, as you quoted correctly, sqlite connections shouldn't be taken over into forks but I never thought of this reloader and of course I did all my tests with it enabled...

![panda](https://media.giphy.com/media/14aUO0Mf7dWDXW/giphy.gif)
Issue: SqliteQueueDatabase.is_stopped() returns false even after successful stop
Author: theotherp
State: closed
Created: 2016-10-07 07:18:17

I guess because `self._is_stopped` is never set to true in `stop()`?

To reproduce:

```
from playhouse.sqliteq import SqliteQueueDatabase

db = SqliteQueueDatabase("reproduce.db")

was_started = db.start()
print("Database was started: %s" % was_started)
was_stopped = db.stop()
print("Database was stopped: %s" % was_stopped)
print("Database is stopped: %s" % db.is_stopped())
```
Issue: Deferring initialization doesn't work with SqliteQueueDatabase
Author: theotherp
State: closed
Created: 2016-10-05 18:24:17

To reproduce:

```
import os
from peewee import CharField, Model
from playhouse.sqliteq import SqliteQueueDatabase


class TestModel(Model):
    test = CharField(null=True)

db = SqliteQueueDatabase(None)

db.init("reproduce.db")
TestModel.create_table()
print("peewee.db was created: %s" % os.path.exists("peewee.db"))
print("reproduce.db was created: %s" % os.path.exists("reproduce.db"))
```


You need to call `db.start()` before any queries are going to run.


Doesn't change the outcome.


Oh, your model isn't referencing your database. Duh.


``` python
class TestModel(Model):
    test = CharField(null=True)

    class Meta:
        database = db
```


Oops, that what happens when you try to simplify your existing code to reproduce something. Sorry about that.
Issue: SqliteQueueDatabase.execute_sql() doesn't work
Author: stenci
State: closed
Created: 2016-10-04 22:27:53

I noticed that this works:

``` python
import peewee
db = peewee.SqliteDatabase('test.db')
print('journal mode = ', next(db.execute_sql('pragma journal_mode'))[0])
```

but this doesn't (with or without the `db.start()`):

``` python
from playhouse.sqliteq import SqliteQueueDatabase
db = SqliteQueueDatabase('test.db')
# db.start()
print('journal mode = ', next(db.execute_sql('pragma journal_mode'))[0])
```

I don't know if it is supposed to work, but I'm reporting it because I couldn't find any documentation about this limitation and I assume it is supposed to work.


Odd...that should work. The call to start() is needed (or initialize with autostart=True) because that sets up the worker threads which actually execute the queries. Also, the SqliteQueueDatabase sets the journal mode to wal automatically, but the query should work regardless. Are you using gevent? And when you say it doesn't work does it block forever without returning, time out, or return nothing? I'm afk otherwise I'd just check.


It returns an `AsyncCursor`, but the `next()` of it returns `TypeError: 'NoneType' object is not subscriptable`.

I don't use `gevent`. The 3 lines of code on my example are all it takes to reproduce in a clean new environment with peewee.

I don't need it to work for me, I just reported because I noticed and it smelled funny.


Aha, I see the issue. The call to `next()` is not forcing the cursor to wait for the worker thread to send back the results and fire off the event. Basically, it's using an API loophole.

Try this:

``` python
db.start()  # You always need either this, or autostart=True.
print('journal mode = ', next(iter(db.execute_sql('pragma journal_mode')))[0])  # Note "iter"
```

For performance reasons I would like to avoid checking the `Event` state every iteration (every call to next).


I thought I had a solid understanding of python's iterator protocol, but the `next` builtin function kind of threw me off. I guess that it does not call it's argument's `__iter__()` method and directly calls `__next__()` instead. Not sure how to proceed at this point, as calling wait() every time a row is retrieved is going to make this slow as hell for large result sets.
Issue: date is None with MySQL
Author: albertz
State: closed
Created: 2016-10-04 15:21:10

Via `pwiz` on my MySQL database, I get:

```
class BaseModel(Model):
    class Meta:
        database = database

class Pub(BaseModel):
    ...
    author = TextField(null=True)
    ...
    publish_date = DateField(null=True)
    ...
```

Then, when iterating `entry in Pub.select()`, `entry.publish_date` is always `None`, although all entries in the database have the date set (or at least part of it, such as year, i.e. an entry like `2016-00-00`).

Why is that? Maybe `formats` is wrong? How to fix this?

I guess that `peewee` will use `pymsql` under the hood. So I tried to do this simple thing:

```
import pymysql
conn = pymysql.connect(...)
cur = conn.cursor()
cur.execute("SELECT publish_date FROM pub")
```

And then iterating `row in cur`, I will only get `row == (None,)`.

So, not sure if this is a bug in peewee or in pymysql.

When casting the value beforehand to `char`, it works, i.e. I get the value as a string:

```
cur.execute("SELECT CAST(publish_date AS char) FROM pub")
```

So, should peewee automatically do this? How can I do that myself now as a work around?

(Also asked on StackOverflow [here](http://stackoverflow.com/questions/39854677/peewee-datefield-property-is-none-with-mysql-database).)


Peewee is using `pymysql` under-the-hood (or `MySQLdb`, whichever you've got installed), so when you are querying the value using `pymysql` directly and not receiving anything that tells me that the issue is not with Peewee but somewhere else. Perhaps with `pymysql` though I'm not sure.


I reported it to pymysql. I guess you are right, it should be fixed in pymysql, but it might make sense to also provide a work-around in peewee. Or at least, for now when this is not fixed, how would an easy work-around look like to use this in peewee? Basically doing `SELECT CAST(publish_date AS char) FROM pub`.


The problem is that the `datetime` object does not allow something like `(2016, 0, 0)`. That's why pymysql currently failed to convert it and return it. It is possible to disable this conversation in pymysql, but then, I think peewee has the same problem. Currently, peewee will just pass through the string and not convert it properly. As far as I understand, something like `entry.publish_date.year` should normally work, but it does not for my case because `entry.publish_date` is a string. Do you consider that as a bug in peewee?


Not at all a bug in peewee.
Issue: SQL views
Author: gjask
State: closed
Created: 2016-10-04 11:29:19

I think it would be useful to be able create sql views using peewee api, that would be created with other tables from model classes. As things are now it seems I am forced to create views manually using sql and then create related peewee model.


I find views to be in a bit of a gray area...kind of like triggers. My personal feeling is that there's enough variety in the types of SQL I'd need to support that until now peewee has avoided representing views or providing helpers to create/drop them. I think that for the time being I will continue to leave this unimplemented to focus on implementing and improving other features that may be more useful generally.
Issue: SQL() replacement char conflicting with Postgresql JSON operator
Author: alexlatchford
State: closed
Created: 2016-10-03 15:04:39

I've been trying to query for the existence of a string in a `JSONField` which contains a list of strings. I've resorted to trying to use raw `SQL()` because from what I can glean from reading the code / [this blog post](http://charlesleifer.com/blog/postgresql-hstore-json-data-type-and-arrays-with-peewee-orm/) this sort of operator isn't baked in just yet.

Problem is that the operator I need from postgres is `?` :/ https://www.postgresql.org/docs/current/static/functions-json.html#FUNCTIONS-JSONB-OP-TABLE

Obviously getting a conflict with the standard operator vs the param replacement token. My only suggestion might be to add in the ability to escape it using `\` potentially, that'd maintain backwards compatibility and be a nice solution.

Many thanks,
Alex


Peewee's JSON fields contain methods that wrap the Postgres operators:

http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#BinaryJSONField


Have you tried this?

```
MyModel.jsonb_field.contains('the key')
```


I'd suggest that you could also use reduce to generate a list of clauses OR--ed or AND-ed together:

```
import operator
reduce(operator.and_, [MyModel.jsonb_field.contains(key) for key in keys])
```


Also:

http://docs.peewee-orm.com/en/latest/peewee/querying.html#adding-user-defined-operators


Awesome I haven't had a chance to go back through this code again but I think one of these will do the trick. Will reopen if I have any issues, thanks for your help!
Issue: AttributeError: 'DatabaseError' object has no attribute 'message'
Author: thacoon
State: closed
Created: 2016-09-29 11:22:50

Hi,

I tried this [gist](https://gist.github.com/thedod/11048875) that is referenced to in the  [peewee/playhouse/sqlcipher_ext.py](https://github.com/coleifer/peewee/blob/master/playhouse/sqlcipher_ext.py) file, line 51. When running this I get an `AttributeError: 'DatabaseError' object has no attribute 'message'` error message.  Using `.message` is also advcied in the [docs](http://docs.peewee-orm.com/en/latest/peewee/playhouse.html?highlight=sqlcipher#sqlcipher-ext).

Full traceback:

```
$ python test.py
Passphrase: 
Traceback (most recent call last):
  File "/home/arch/.environments/passwordmanager_dev/lib/python3.5/site-packages/peewee.py", line 3676, in execute_sql
    cursor.execute(sql, params or ())
pysqlcipher3.dbapi2.DatabaseError: file is encrypted or is not a database

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "test.py", line 54, in <module>
    db.get_tables()
  File "/home/arch/.environments/passwordmanager_dev/lib/python3.5/site-packages/peewee.py", line 3893, in get_tables
    'type = ? ORDER BY name;', ('table',))
  File "/home/arch/.environments/passwordmanager_dev/lib/python3.5/site-packages/peewee.py", line 3683, in execute_sql
    self.commit()
  File "/home/arch/.environments/passwordmanager_dev/lib/python3.5/site-packages/peewee.py", line 3507, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/home/arch/.environments/passwordmanager_dev/lib/python3.5/site-packages/peewee.py", line 134, in reraise
    raise value.with_traceback(tb)
  File "/home/arch/.environments/passwordmanager_dev/lib/python3.5/site-packages/peewee.py", line 3676, in execute_sql
    cursor.execute(sql, params or ())
peewee.DatabaseError: file is encrypted or is not a database

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "test.py", line 58, in <module>
    if e.message != 'file is encrypted or is not a database':
AttributeError: 'DatabaseError' object has no attribute 'message'
```

I am currently using _peewee==2.8.3_ and _pysqlcipher3==1.0.2_.


This works in Python 2.x, Python 3.x I guess doesn't work the same way.