akumoth/peewee-issues huggingface dataset:
Issue: Incorrect data saving
Author: sorokousov
State: closed
Created: 2021-02-25 15:44:14

I apologize in advance for my English.

 I will describe the problem in a few steps:

1. Creating a model for storing a deck of cards:
```
db = pw.MySQLDatabase(database=db, user=user, password=password, host=host, port=port)`

class PTH(pw.Model):`
    deck_original = pw.TextField(null=True)
    deck_current = pw.TextField(null=True)
    cards_left = pw.TextField(null=True)

    class Meta:
        database = db
```

2. Create object:
```
new_deck: list = generate_deck()
pth = PTH.create(deck_original=str(new_deck),
                            deck_current=str(new_deck),
                            cards_left=str([]))
pth.save()
print(type(new_deck), new_deck)
# <class 'str'> [[13, 'K', 'd', 'Kd', 'K♦'], [3, '3', 'd', '3d', '3♦'], [14, 'A', ...
```

3. And then strange things happen:
```
pth = PTH.get_or_none(PTH.id == pth_id)

new_deck: list = generate_deck()
print('where does the error occur?-1', type(new_deck), new_deck)
# where does the error occur?-1 <class 'list'> [[2, '2', 's', '2s', '2♠'], [8, '8', 's', '8s', '8♠'], [6, '6', ...

print('where does the error occur?-2', type(pth.deck_original), pth.deck_original)
# where does the error occur?-2 <class 'str'> [[13, 'K', 'd', 'Kd', 'K♦'], [3, '3', 'd', '3d', '3♦'], [14, 'A', ...

bliat = str(new_deck)
print('where does the error occur?-3', type(bliat), bliat)
# where does the error occur?-3 <class 'str'> [[2, '2', 's', '2s', '2♠'], [8, '8', ...

pth.deck_original = bliat,
pth.deck_current = bliat,
pth.cards_left = str([])
pth.save()

print('where does the error occur?-4', type(pth.deck_original), pth.deck_original)
# where does the error occur?-4 <class 'tuple'> ("[[2, '2', 's', '2s', '2♠'], [8, '8', 's', '8s', '8♠'], [6, '6', ...
```

Where does the tuple come from?
If I set the bliat variable to  'abcd' (str), the result will be the same.
```
# where does the error occur?-4 <class 'tuple'> ('abcd',)
```

Update:
 If you apply this approach:
```
pth.__data__['deck_original'] = new_deck
# where does the error occur?-4 <class 'tuple'> ([[12, 'Q', 'd', 'Qd', 'Q♦'], [11, 'J', 'c', 'Jc', 'J♣'], [2, '2', ...
```
A list is inserted into the tuple.

You have a trailing comma:

```python

pth.deck_original = bliat,
pth.deck_current = bliat,
```

Oh, my God, this is my mistake)))
Thanks you!!!

somehow i had the exact same mistake. Thank god this issue exist :)
Issue: Missing datatype numrange for postgresql
Author: DanielAndreasen
State: closed
Created: 2021-02-25 09:56:03

I have a case, where I need the datatype [`numrange`](https://www.postgresql.org/docs/9.2/rangetypes.html), however I cannot find it in `playhouse.postgres_ext`.
Is it possible to implement this? I know it already exists in `psycopg2._range.NumericRange`.

Just for clarification, I have tried making a table like this:

```python
from psycopg2._range import NumericRange

db = PostgresqlDatabase(...)

class Cutoff(BaseModel):
    parameter = TextField()
    interval = NumericRange()

db.connect()
db.create_tables([Cutoff])
```

but the `interval` does not appear in the generated table, and no errors are thrown either.

I have played around a bit with the source code to get this to work, and found a simple solution that seems to work. I add the following `class` to `playhouse.postgres_ext`

```python
from psycopg2._range import NumericRange

...

class NumericRange(Field, NumericRange):
    field_type = 'NUMRANGE'
```

The column is now created with all the nice functionality from the `Range` class, which [`NumericRange`](https://github.com/psycopg/psycopg2/blob/master/lib/_range.py#L457-L463) inherits from. Would you like me to open a PR with this simple solution?

I think this is incorrectly implemented. I would think you would rather do something like this:

```python

class NumericRangeField(Field):
    field_type = 'NUMRANGE'

    def adapt(self, value):
        if value is not None: return NumericRange(value)
```

The idea is that the field retains all the regular field methods/apis while exposing a value to the user which represents the range and can be converted to the appropriate sql by the psycopg2 driver.

At any rate, I do not intend to be adding support at the present time.

Alright, thanks for the answer. I guess I can just implement it directly into my models.
Issue: Enable handling of DB lock collisions
Author: lselker13
State: closed
Created: 2021-02-24 01:38:03

I'm using Peewee with SQLite DBs. Is there a way to either specify behavior in cases of lock collisions, or to programmatically detect whether a lock collision has occurred, as opposed to some other error?

A lock collision seem to result in an `OperationalError`. This is a relatively broad Peewee error (e.g. attempting to use a DB to which a process hasn't connected will cause the same error). As far as I can tell, this means that it's not possible to explicitly handle lock collisions in client code (to, e.g., wait and retry rather than crashing the process). First of all, am I missing some existing approach?

A relatively simple solution would be to create a subclass of `OperationalError` (say, a `LockCollisionError`) to be thrown in the case of a lock collision. This would be backwards-compatible with existing error handling and allow users to choose how to handle lock collisions.

I'm using Python's asyncio library. So even more useful would be a coroutine allowing client code to wait on the availability of a lock. I realize that this is a more significant project, though.

Thanks in advance!
-Leo Selker

The error is raised by the sqlite driver you are using -- I'd suggest checking the error message contained in the exception if you want to do special handling of this exception. For general suggestions on how to handle sqlite's write-locking, these posts may help:

* https://charlesleifer.com/blog/going-fast-with-sqlite-and-python/
* https://charlesleifer.com/blog/multi-threaded-sqlite-without-the-operationalerrors/

You can set a timeout on the database instance which controls the busy-timeout sqlite uses for retrying to acquire the lock:

* http://docs.peewee-orm.com/en/latest/peewee/api.html#SqliteDatabase

I'd also suggest you familiarize yourself with how sqlite locking works on the sqlite docs website.

Also, in case it wasn't clear, sqlite + peewee already specifies a default 5 second busy timeout (if lock is busy, keep attempting to acquire it) - this is a relative eternity in sqlite terms.
Issue: [Feature request] Define foreign key constraint name
Author: ms-lilibeth
State: closed
Created: 2021-02-20 16:52:57

I am working with SQLite, and I am really disappointed with the fact that peewee does not assign any name for a foreign key constraint when it builds an SQL query (`peewee.py`: `ForeignKeyField.foreign_key_constraint()`)

I have three foreign keys in my model, and the full error message from SQLite is just `FOREIGN KEY constraint failed`. Therefore, I have to guess, which one of these keys is actually wrong.

The workaround for same issue with CHECK constraints is to write the full CHECK expression by hand in a field definition:
```
camera_type = TextField(
	null=True,
	constraints=[SQL('CONSTRAINT photo_valid_camera_type '
                 'CHECK(camera_type IN ("smartphone", "webcam", "terminal", "dome"))')]
 )
```
Then the constraint name is included into error message:
`peewee.IntegrityError: CHECK constraint failed: photo_valid_camera_type`
That is not pretty, but it works.

I would like to see the same kind of message for the foreign keys too.

P.S. What about adding "name" argument for the function `peewee.Check(constraint)`?

I've gone ahead and added support for this as the changes required were not extensive, although this is honestly the first time this has come up in 10+ years so I don't know it will be of much general benefit. One gotcha: MySQL doesn't seem to support inlining named check constraints into the field definition, but I'll leave this up to the user to sort out for now (again -- seems unlikely that many people will use it and I've put a note in the docs).
Issue: Attempt new connections if the server is not ready
Author: pierky
State: closed
Created: 2021-02-19 20:48:58

When using `ReconnectMixin`, the execution of a SQL statement is attempted again if it fails because of "operational issue" (for example when the connection to the DB server is lost). During the reconnection phase, the DB server might not be ready to handle new connections, thus the call to `self.connect()` could also fail.

When using `ReconnectMixin`, the execution of a SQL statement is attempted again if it fails because of "operational issue" (for example when the connection to the DB server is lost). During the reconnection phase, the DB server might not be ready to handle new connections, thus the call to `self.connect()` could also fail.

With this PR I'm proposing an infinite loop to attempt new connections indefinitely until one is succeeded. Not the perfect solution, I know, I'd just like to see if something along this line would be accepted (I know about some *important* opinions about the disconnection handling). If so, probably some mechanism can be added to the `ReconnectMixin` class to let the developer to specify a limit of attempts or to somehow configure the behaviour.

>Not the perfect solution, I know, I'd just like to see if something along this line would be accepted

I'd suggest putting this into your own subclass of `MySQLDatabase` (or `ReconnectMixin`, whichever you use) as I don't intend to maintain this code or advocate this approach.
Issue: add_column only applies default value if column does not allow null
Author: feixie
State: closed
Created: 2021-02-19 18:58:24

I would like to add columns that allow nulls AND have a default value. While I can define columns with default values and null=True when I create the table, I can't do it via add_column because this line: https://github.com/coleifer/peewee/blob/master/playhouse/migrate.py#L329 only applies the default value when the field is not nullable. 

The `apply_default()` method is exposed as an operation, just like `add_column()`. Since "defaults" specified this way are implemented in Python rather than as a sql default constraint, I've avoided automatically applying them when the field is left nullable (as this implies an UPDATE of every row of the table).

So you would:

```python

migrate(
    migrator.add_column('my_tbl', 'my_col', field_obj),
    migrator.apply_default('my_tbl', 'my_col', field_obj),
)
```
Issue: Migrator applies the same value from callable default
Author: zgoda
State: closed
Created: 2021-02-18 20:39:55

With value generator like

```python
def gen_nanoid():
    return nanoid.generate(size=12)
```

and migration definition:

```python
def add_permalink():
    field = CharField(max_length=20, default=gen_nanoid)
    migrate(
        migrator.add_column('quip', 'permalink', field)
    )
```

All rows in the table being migrated get the same value:

```console
$ sqlite3 db.sqlite 
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> select permalink from quip;
Ufwe7VZMiloN
Ufwe7VZMiloN
Ufwe7VZMiloN
Ufwe7VZMiloN
sqlite> 
```

This makes adding a column with unique constraint not possible without some hacking (add normal non-unique column with static default, update with unique values using raw SQL while iterating over rows, add unique index on new column).

Peewee 3.14.1 with SQLite (built-in module, Python 3.8.5).

If it's a python callable what's peewee supposed to do... iterate every single row and update it? That would not work and would cause performance problems for users because it is unexpected.

If you want, you can use a sql function - sqlite lets you add user-defined functions quite easily - and even use them for column defaults. Alternatively, you can set it to some placeholder and then iterate every row if you're so inclined.
Issue: Extension support check in setup.py needs path to sqlite3.h and sqlite3.lib files on Windows.
Author: cdcadman
State: closed
Created: 2021-02-18 12:09:52

Initially, when I ran `python setup.py build_ext -i`, I got the following message.

<pre>test_pw_sqlite3.c(1): fatal error C1083: Cannot open include file: 'sqlite3.h': No such file or directory
unable to compile sqlite3 C extensions - missing headers?
setup.py:100: UserWarning: Could not find libsqlite3, SQLite extensions will not be built.
  warnings.warn('Could not find libsqlite3, SQLite extensions will not '
running build_ext</pre>

After I modified setup.py so that it could find sqlite3.h and sqlite3.lib in the extension support check, the setup worked fine.

In '_have_sqlite_extension_support()', I inserted two lines after line 71 (`compiler = new_compiler()`):
`    compiler.add_include_dir(r'C:\Program Files (x86)\Python38-32\include')` (location of my sqlite3.h file)
`    compiler.add_library_dir(r'C:\Program Files (x86)\Python38-32\libs')` (location of my sqlite3.lib file)

Glad you got it working.
Issue: DESC not working in jsonb type
Author: daeeros
State: closed
Created: 2021-02-14 13:18:54

.desc() not working in jsob table,

```
class Player(BaseModel):
   user_id = IntegerField()
   ivent_stats = BinaryJSONField(default={"safe": 0,
                                          "bomb": 0,
                                          "robbery": 0})

players = await db.execute(Player.select(Player.user_id, Player.ivent_stats).order_by((Player.ivent_stats['robbery']).desc()))
```

I trying to get robbery value from more to less, but it olvays returning less to more, what i do wrong?(

It works fine for me:

```python

class T(Model):
    data = BinaryJSONField()
    class Meta:
        database = db

db.drop_tables([T])
db.create_tables([T])
T.create(data={'k1': 'v1', 'k2': 'v2'})
T.create(data={'k1': 'v3', 'k2': 'v4'})
T.create(data={'k1': 'v5', 'k2': 'v6'})

query = T.select().order_by(T.data['k1'].desc())
for row in query:
    print(row.data['k1'])
# prints:
# v5
# v3
# v1
```
Issue: Undocumented conflict with field name "_path"?
Author: alvenhar
State: closed
Created: 2021-02-11 08:09:33

Hi, I recently encountered the problem that peewee's create_tables() call hung indefinitely and started eating up massive RAM for a minuscule database setup. I finally figured out that the "offensive" change was that I named a CharField on one of my models "_path". As soon as I changed that to "_path_str", everything was back to normal, so I guess "_path" conflicts with the underlying peewee implementation.
That's fine, it was just hard to find as the docs didn't mention any reserved words/identifiers and this one is not of the usual python "hidden magic". Might look into that?

Thanks for the report. Not going to take any action on this at the moment, but feel free to open a patch to add a section to the docs.
Issue: Support for a new db type
Author: pfw
State: closed
Created: 2021-02-11 01:31:15

I'm interested in peewee since it is lightweight and looks approachable while still supporting what we require. We have some data in FirebirdSQL and I was going to have a look at getting peewee going against it as an experiment and learning experience. 

To get it up and running would it 'just' require implementing a subclass of Database with all of the methods raising NotImplementedError implemented or are there other places that need attention? Obviously to start with this is just an experiment and to get something correct would require tests.

Pretty much. You'll want to use a db-api 2.0 compatible driver, e.g. one of:

https://www.firebirdsql.org/file/documentation/drivers_documentation/python/3.3.0/Python-DB-API-2.0.html

You would subclass `Database` and start by implementing the `_connect()` method and go from there. I'm not familiar with Firebird so I am not sure what subtleties you may encounter along the way. The sqlalchemy codebase might provide some helpful hints, as well:

https://github.com/sqlalchemy/sqlalchemy/tree/master/lib/sqlalchemy/dialects/firebird

Since this is not a bugfix I will close, but feel free to raise any questions you encounter along the way and I'll try to help.

Good one, thank for the pointers. At the moment it's definitely an experiment but I'll update if we get anywhere with it.
Issue: Syntax error near "VALUES" when using ValuesList and Tuple
Author: cvanea
State: closed
Created: 2021-02-08 13:19:14

Hi, 

I'm trying to select rows from a table where the given row values are in a list of tuples. As I understand it, SQLite can only support this kind of IN operation if the right-hand side is a subquery expression. Therefore, in SQLite the following works:

```sql
CREATE TABLE T1(x,y,z);
INSERT INTO T1(x,y,z) VALUES(1,2,3),(2,3,4),(1,NULL,5);
SELECT * FROM T1 WHERE ((x,y) in (VALUES (1,2), (2,3)));
```

I've tried to implement this using peewee as follows:

```python
coords = [(1,2), (2,3)]
vl = ValuesList(coords, columns=('x', 'y'))
selection = T1.select().where(Tuple(T1.x, T1.y) << vl)
```

However, this leads to the error `{OperationalError}near "VALUES": syntax error`

Inspecting the query in the debugger it seems that peewee is trying to do something like:

```sql
SELECT * FROM T1 WHERE ((x,y) in VALUES (1,2), (2,3));
```

I believe the error comes from the lack of brackets around the VALUES clause as running the same in SQLite leads to the same error.

Apologies if there's a better way to form this kind of query or I've missed something, I'm fairly new to SQL and peewee.

Thanks!

As a workaround you can add parentheses by:

```python

from peewee import EnclosedNodeList

vl = ValuesList(coords, columns=('x', 'y'))
rhs = EnclosedNodeList([vl])
...where(Tuple(T.x, T.y).in_(rhs))
```

If you're using Sqlite, it is very picky about what it accepts. The following approaches all work with Postgres, but not Sqlite for some reason:

```python

vl = ValuesList(((1, 2), (2, 3)), columns=('x', 'y'))
q = T.select().from_(T, vl).where(
    (T.x == vl.c.x) &
    (T.y == vl.c.y))

q = T.select().from_(T).join(vl, on=(
    (T.x == vl.c.x) &
    (T.y == vl.c.y)))
```

The EnclosedNodeList suggestion worked perfectly and sped up a big update query I had quite drastically.

Thanks for looking into this and for the great library!
Issue: Can not add a not null column to a table with CHECK constraint
Author: feixie
State: closed
Created: 2021-02-06 20:17:40

Most of the tables in my sqlite db have CHECK constraints. The schema of one such table looks like this:

`CREATE TABLE "study" ("id" INTEGER NOT NULL PRIMARY KEY, "created_at" DATETIME NOT NULL, "uid" TEXT NOT NULL, "patient_id" VARCHAR(255) NOT NULL, "provider_id" VARCHAR(255) NOT NULL, "first_name" VARCHAR(255) NOT NULL, "last_name" VARCHAR(255) NOT NULL, "birthdate" DATE, "gender" VARCHAR(255), CHECK (gender IN ('female','male')), CHECK (patient_id != ''))`

When I try to do a:

`migrate.migrate(
    migrator.add_column(
        'study', 'visit_id',
        peewee.CharField(default='', null=True))
    )
)`

I get this error:
`sqlite3.OperationalError: table study__tmp__ has no column named CHECK`

I believe this is because CHECK constraints aren't being removed here: https://github.com/coleifer/peewee/blob/master/playhouse/migrate.py#L720 and they are being treated as columns to update. Tested that adding 'check ' to constraint_terms fixes the issue. Can someone with permission submit a PR? Does anyone have thoughts on a workaround in the mean time?

Thanks for the report. This is fixed in master.
Issue: Feature request: A Database for testing that just navigates through Model attributes
Author: dmyersturnbull
State: closed
Created: 2021-02-06 20:03:56

I've had a great experience with Peewee so far. Thanks for your work maintaining it!

I apologize if this is better suited for StackOverflow -- it seems like more of a feature request, but perhaps it's out of scope or there's an extant solution.

My main issue with using Peewee is that it's difficult to abstract away the database for testing.
I've tried a few solutions:
1. Have test fixtures instantiate transactions and roll back per test.
2. Have an abstract `QuerySet` containing the needed `SELECT` queries per test, and use dependency injection to swap the database-backed implementations with mocked ones.
3. Write an abstraction of peewee's `Expression`, `ColumnBase`, `Query`, etc., and use dependency injection to swap the databased-backed implementations with ones that navigate through `Model` subclasses by accessing attributes.

- (1.) is not a principled solution because it requires a database dependency, and the performance is poor.
- (2.) is effective, but requires knowing which queries are needed per test. This exposes part of the implementation (which specific queries are needed), and requires injecting a new query whenever a new query is needed.
- (3.) Is what I'm wondering about. See the example below.

```python
class Product(peewee.Model):
    seller = peewee.ForeignKeyField(column_name="seller", field="id", model=Sellers)
    class Meta: ...

class Seller(peewee.Model):
    name = peewee.CharField()
    class Meta: ...
```

In the above, a mocked database could just traverse directly a `Product` to the `Seller` via an attribute `seller` (or `_seller`).
Then using the new subclass of `Database`, this would immediately work:

```python
product = Product(seller=Seller(name="hi"))
product.seller.name = "test"
```

I recognize that this is a heavy feature request.
I began to write some code, but I'm still not familiar enough with peewee's inner-workings.
Unfortunately, I've so far found it quite difficult to write tests (in a moderately large codebase).


A "fake"/in-memory system is not going to happen. One would need to implement relational algebra and all the intricacies of a sql storage engine.

There are suggestions in the docs for testing: http://docs.peewee-orm.com/en/latest/peewee/database.html#testing-peewee-applications

Those are the recommended ways. Using a test sqlite database with fixture data being probably one of the more common.
Issue: [Question] Update if existing value is zero
Author: karambaq
State: closed
Created: 2021-02-05 16:15:26

Hey there!
I need to update the existing record field if the value of the existing field is equal to zero, how can I do it?

I've tried:

```
query = Model.update(field_name=new_value).where(Model.id=id and Model.field_name == 0)
query.execute()
```

But it seems like it doesn't work, am I doing something wrong?

First: post questions to stack overflow.

Second - this is covered in all the very basic docs. Please consult them:

```python
where((Model.id=id) & (Model.field_name == 0))
```

http://docs.peewee-orm.com/en/latest/peewee/contributing.html#questions