akumoth/peewee-issues huggingface dataset:
Issue: Support for "<@" in PostgreSQL arrays
Author: joaodlf
State: closed
Created: 2021-12-03 16:51:46

Peewee currently supports `@>` (`.contains()`) and `&&` (`contains_any()`) - [docs](https://docs.peewee-orm.com/en/latest/peewee/playhouse.html?highlight=array#ArrayField.contains)

It would be nice for Peewee to also support `<@`, as expressed in the [PostgreSQL docs](https://www.postgresql.org/docs/current/functions-array.html):

> anyarray <@ anyarray → boolean
>
> Is the first array contained by the second?
>
> ARRAY[2,2,7] <@ ARRAY[1,7,4,2,6] → t

Note that this is a completely different behavior to `@>`:

> anyarray @> anyarray → boolean
> 
> Does the first array contain the second, that is, does each element appearing in the second array equal some element of the first array? (Duplicates are not treated specially, thus ARRAY[1] and ARRAY[1,1] are each considered to contain the other.)
> 
> ARRAY[1,4,3] @> ARRAY[3,1,3] → t

I would imagine this implemented as something like `.contained_by()`?

Thanks for the suggestion - this is implemented as `contained_by`.

Thanks for the **incredible** feedback in such a short notice, do you plan on releasing a minor version with this feature any time in the near future?

Besides this one change, there aren't really any other notable changes yet to justify a new release, in my opinion -- everything else is just docs or example updates. I'll probably wait until after the holidays.

If you want to use this now, but don't want to pin to `master` branch, I'd suggest just implementing your own subclass of ArrayField that implements `contained_by()` and using that in the meantime.
Issue: pwiz uses wildcard imports of peewee
Author: tdennisliu
State: closed
Created: 2021-11-30 22:57:07

Hiya,

I've found pwiz very helpful in setting up my scripts, but since it wildcard imports `peewee` into the script. See below from the README:

```
from peewee import *
import datetime


db = SqliteDatabase('my_database.db')

class BaseModel(Model):
    class Meta:
        database = db

class User(BaseModel):
    username = CharField(unique=True)

class Tweet(BaseModel):
    user = ForeignKeyField(User, backref='tweets')
    message = TextField()
    created_date = DateTimeField(default=datetime.datetime.now)
    is_published = BooleanField(default=True)
```

I find this very difficult to troubleshoot if something goes wrong, and it takes a long time to simply find and replace the classes.

What I would like to see:

```
import peewee as pw
import datetime


db = pw.SqliteDatabase('my_database.db')

class BaseModel(pw.Model):
    class Meta:
        database = db

class User(BaseModel):
    username = pw.CharField(unique=True)

class Tweet(BaseModel):
    user = pw.ForeignKeyField(User, backref='tweets')
    message = pw.TextField()
    created_date = pw.DateTimeField(default=datetime.datetime.now)
    is_published = pw.BooleanField(default=True)
```

The [python docs](https://docs.python.org/3/tutorial/modules.html#importing-from-a-package) do state that this practise should be avoided.

As far as I can tell it's not too difficult to change this behaviour for pwiz, and I can set up a PR. 
Thoughts?

This is a convention that is used throughout peewee examples, as well as my own code. Peewee module defines `__all__`, which controls what objects are exported, and I am happy with that. In your own code you are welcome to use whatever style you like, though.
Issue: fn.Avg() gets coerced into integer
Author: gjask
State: closed
Created: 2021-11-29 10:09:09

When using `fn.Avg()` on integer field, result will also gets coerced into integer. To illustrate problem I have created this example.

```python
class TestTable(Model):
    id = AutoField()
    value = IntegerField()
```

```sql
insert into testtable (value) values (3), (4);

select * from testtable;
+----+-------+
| id | value |
+----+-------+
| 1  | 3     |
| 2  | 4     |
+----+-------+
```

```python
val = TestTable.select(
        fn.Avg(TestTable.value).alias("avg_value"),
    ).scalar()

print(val, type(val)
# 3 <class 'int'>
```
The expected result would be `3.5` or `Decimal(3.5)`

peewee = 3.14.8
database is MySQL


You want to specify `.coerce(False)`:

```python

TestTable.select(
        fn.Avg(TestTable.value).coerce(False).alias("avg_value"),
).scalar()
```

http://docs.peewee-orm.com/en/latest/peewee/api.html#Function.coerce
Issue: Bug related to create_table function in your Model
Author: kezzhang
State: closed
Created: 2021-11-26 11:22:24

It is actually about one of your examples in version 2.9.1 and before, redis_vtable, in line 208, RedisView.create_table(), this will report error. But when I change that line to something like cursor.execute("create virtual table redisview using RedisModule()"), the whole program worked. 

So I highly doubt that there is something wrong with your create_table function especially under the apsw_ext situation. Besides, when you truly want to do extensions for something, doing it halfway is worse than nothing.

I could swear I ran all the examples and they worked so possibly something has changed. 2.x is unmaintained since Feb 2018 so hard to say.
Issue: FlaskDb wrapper from extension
Author: nabinbhusal80
State: closed
Created: 2021-11-24 09:13:45

Hi I am using FlaskDb on my flask application which always opens connection to db on each request and closes connection once request ends. This is a great feature but I have some endpoints which don't require anything from db and they kind of live for long time because complex computations. 

How do I exempt db connections for specific endpoints? Or is there any other way i can use this? Your help will be appreciated.

I've implemented this, thanks for the suggestion. The endpoints can be specified using:

```python
FLASKDB_EXCLUDED_ROUTES = ('view1', 'view2'...)

# Or,
flask_db = FlaskDB(app, excluded_routes=set('view1', 'view2',...))
```

Hey @coleifer thank you so much for the solution. Somehow i missed it in docs. I was banging my head on this for a while. So amazing to see the quick response on this. 
I really appreciate it.
Issue: Remote Postgres Use Case
Author: aiqc
State: closed
Created: 2021-11-22 13:32:39

Hiya. 

It sounded like you were fishing for use cases with respect to Postgres in the docs.
> https://docs.peewee-orm.com/en/latest/peewee/playhouse.html?highlight=postgresql#PostgresqlExtDatabase 
> 
> In the future I would like to add support for more of postgresql’s features. If there is a particular feature you would like to see added, please open a Github issue.


I'm using and love PeeWee w SQLite for local development and vertical scale (single desktop/ server). It's a great way to provide a 1-line install for contributors and open source users. 

I was hoping to drop a SQLite file into AWS EFS and mount many jobs/users to it simultaneously, but concurrent writes over NFS are a no-go zone for SQLite. Right now I have to manually manage my own lock file. 

So my solution would be to serve PSQL via AWS RDS. I searched for "postgresql" in the peewee docs because I don't want to switch ORMs. However, I didn't see anything like database IP address or port - so I was wondering if this implementation would support a remote psql db?

Yes, peewee supports all the same parameters as psycopg2, so:

```python

# for example:
db = PostgresqlDatabase('db_name', host='192.168.1.30', port=5432, user='postgres', password='xyz')
```

Psycopg2 connection doc: https://www.psycopg.org/docs/module.html#psycopg2.connect
Issue: how to make order_by  smarter
Author: viponedream
State: closed
Created: 2021-11-19 10:08:36

i want to accept a parameter, 

    def get_all_order_by(self, _order_by) -> list:
        table = self.get_table()
        res = table.select().order_by(table._order_by.desc())    --> this doesnot work
        item_list = [d.to_item() for d in res]
        return item_list
Issue: `get_or_create` isn't automatically including the primary key
Author: rbracco
State: closed
Created: 2021-11-12 01:21:19

I have a field `ModelResult` with a foreignkey for an `Audio` class. I recently updated `ModelResult` to take a 2nd foreign key for a `Model` class. When I call `get_or_create` I get the error `IntegrityError: NOT NULL constraint failed: modelresult.id`. For some reason the id isn't being automatically included/incremented, but if I include it manually in the defaults (e.g. {"id": 10000000}, it works). My intention is that if a row exists with that combination of audio and model, update it, otherwise create it. Any idea where I might be going wrong? 

Thank you by the way, peewee is a pleasure to work with. 

```
audio = AudioFile.get(AudioFile.filename == fname)
tm, created = TrainModel.get_or_create(name=model_name)
defaults = {
        "prediction": pred,
        "loss": loss,
    }
mr, created = ModelResult.get_or_create(audio_id=audio, model_id=tm, defaults=defaults)
# if mr already exists, update it with new values
```

I'm getting some really weird results. I tried to hack around it by calling `ModelResult.get(audio_id=af.id, model_id=tm.id)` and manually updating if it existed, and if it didnt, catching the `DoesNotExist` and manually creating the `ModelResult` with

```
max_id = ModelResult.select(fn.MAX(ModelResult.id)).scalar() + 1
mr = ModelResult(
            id=max_id,
            prediction=pred,
            loss=loss,
            audio_id=af.id,
            model_id=tm.id,
        )
mr.save()
```
It worked for like 20 rows and then the rows stopped getting added and the ID stopped incrementing. I set a breakpoint and everything looks good in the ModelResult instance but mr.save() is returning 0. I searched and found #1198 which makes me think something is really wrong. 

**Edit:** I started using `mr.save(force_insert=True)` which worked and then checked the docs and saw that it happens when you create your own non-integer primary key which I don't think I did. My schema doesn't use `PrimaryKey` anywhere but I checked the schema in SQLite browser and I now see that the `id` field for `ModelResult` is `NUMERIC` not `INTEGER`. I'm not sure how this happened but I believe that's the issue. I'm going to explore a bit more and I'll post here and close out if I confirm. 

There's nothing wrong with peewee, surely this is something in your code or schema.

```python
class Audio(Base):
    name = TextField()

class Note(Base):
    name = TextField()

class Result(Base):
    audio = ForeignKeyField(Audio)
    note = ForeignKeyField(Note, null=True)

db.create_tables([Audio, Note, Result])

a1 = Audio.create(name='a1')
a2 = Audio.create(name='a2')
n1 = Note.create(name='n1')
n2 = Note.create(name='n2')

r1a, created = Result.get_or_create(audio=a1)
assert created
r1b, created = Result.get_or_create(audio=a1)
assert not created
assert r1a.id == r1b.id

r2a, created = Result.get_or_create(audio=a2, note=n2)
assert created
r2b, created = Result.get_or_create(audio=a2, note=n2)
assert not created
assert r2a.id == r2b.id
```
Issue: Document example usage of object_id_name
Author: pylipp
State: closed
Created: 2021-11-11 18:22:13

In combination with `column_name` defined, an AttributeError is raised otherwise.
This behavior was confusing me at first, until I found #1302. The docs (apart from the ForeignKeyField API) don't mention object_id_name

I found the text and admonition confusing, even in context. I'll pass for now but will think about whether some improvement to the docs is needed for this situation.
Issue: Insert doesn't return lastrowid for Postgresql
Author: sebo-b
State: closed
Created: 2021-11-07 20:44:01

Minimal test case:
```
test = Table('test',('id','value'),primary_key='id')
test.bind(db)

lastRowId = test.insert({test.value: 10}).execute()
```

Actual:
lastRowId is None

Expected:
lastRowId contains generated primary key


In Postgresql implementation, for receiving inserted id RETURNING clause is used. The following code works correctly:
`lastRowId = test.insert({test.value: 10}).returning(test.id).execute()`
however in the current implementation equivalent to this is used:
`lastRowId = test.insert({test.value: 10}).returning('id').execute()`

Postrgres returns something like this: `Column(name='?column?', type_code=25)`
which then is wrapped and looks like this `{'?column?': 'id'}`
then `last_insert_id` is trying to access `cursor[0][0]` which raises an error so None is returned




Postgres/psycopg2 does not provide a lastrowid db-api method -- you need to use RETURNING with postgres. This is handled automatically for you when you use the models APIs, but **not** when you are using the low-level query builder (e.g. `Table`).
Issue: ReconnectMixin with atomic cause exception
Author: mattangus
State: closed
Created: 2021-10-28 09:23:19

Using the `ReconnectMixin` and executing a query after a long wait inside an `atomic` block causes an exception.

Here is some sample code:

```python
class ReconnectMySQLDatabase(ReconnectMixin, pw.MySQLDatabase):
    """Class to make MySQL automatically reconnect after long periods without querying the database
    """
    pass
DB = ReconnectMySQLDatabase(...)

# wait a long time
tweets = Tweet.select()
with DB.atomic():
    for t in tweets:
        t.user = "me"
    Tweet.bulk_update(tweets, fields=["user"], batch_size=100)
```

This will cause an exception saying that the connection was reset inside a transaction.

Shouldn't the transaction initialization cause the reconnect.

The transaction details are wrapped by the driver, so I can't really comment on why this is the case with pymysql. I'd suggest possibly trying to ping the db before beginning transactions if its possible your connection has been idle for quite some time. You can do this quite easily.

https://pymysql.readthedocs.io/en/latest/modules/connections.html#pymysql.connections.Connection.ping

```python

db = ReconnectMySQLDatabase(...)

try:
    db.connection().ping(reconnect=False)
except:
    db.close()
    db.connect()
```

Couple things to consider, though. Why are you leaving open connections sitting idle for a long time? This may indicate a problematic design. If this is a web app, you should probably be using the PooledMySQLDatabase and opening/closing connections each request. If this is something else, however, I'd suggest either a) connect/close when you need to do work, or b) use something like the code above to ensure connection liveness.
Issue: tests.apsw_ext.TestAPSWExtension tests failing (LockedError) after 10be40b2ea3d76a9 (RETURNING support)
Author: mgorny
State: closed
Created: 2021-10-27 08:47:59

I'm getting 12 test errors with the git master (and 3.14.6) release that are regressions compared to 3.14.4. An example test error looks like the following:

```
======================================================================
ERROR: test_update_delete (tests.apsw_ext.TestAPSWExtension)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/tmp/peewee/tests/base.py", line 246, in tearDown
    self.database.drop_tables(self.requires, safe=True)
  File "/tmp/peewee/peewee.py", line 3352, in drop_tables
    model.drop_table(**kwargs)
  File "/tmp/peewee/peewee.py", line 6730, in drop_table
    cls._schema.drop_all(safe, drop_sequences, **options)
  File "/tmp/peewee/peewee.py", line 5838, in drop_all
    self.drop_table(safe, **options)
  File "/tmp/peewee/peewee.py", line 5711, in drop_table
    self.database.execute(self._drop_table(safe=safe, **options))
  File "/tmp/peewee/peewee.py", line 3173, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/tmp/peewee/playhouse/apsw_ext.py", line 122, in execute_sql
    cursor.execute(sql, params or ())
apsw.LockedError: LockedError: database table is locked
```

Full build and test log: [test.log](https://github.com/coleifer/peewee/files/7424610/test.log)

I have reproduced the problem with Python 3.8.12 and 3.10.0.

A quick bisect points to the following commit as the first bad one:

```
commit 10be40b2ea3d76a9e410e1e5d0a7b5c1c58c8467 (refs/bisect/bad)
Author: Charles Leifer <coleifer@gmail.com>
Date:   2021-10-21 15:52:55 +0200

    Add provisional support for RETURNING for sqlite_ext/mariadb.
    
    Sqlite has kind-of unusual behavior with regards to lastrowid and the
    rowid in general, so I'm retaining the original behavior for the
    standard SqliteDatabase, but using SqliteExtDatabase will now support
    RETURNING for Sqlite 3.35 or newer.
    
    Similarly, MariaDBConnectorDatabase will also support RETURNING for
    MariaDB 10.5 or newer.
```

Thanks for reporting and bisecting, I've got a fix and will push a new release later today.

Thank you, I can confirm that tests pass on master now.
Issue: Implementation of Modulo operator in documentation 
Author: gaspardbb
State: closed
Created: 2021-10-25 07:55:13

## Problem

In the [documentation](https://docs.peewee-orm.com/en/latest/peewee/query_operators.html?#adding-user-defined-operators), it is said that the modulo operator can be implemented with
```python
from peewee import *
from peewee import Expression # the building block for expressions

def mod(lhs, rhs):
    return Expression(lhs, '%', rhs)
```

This gives a `IndexError: list index out of range`, due to the `%` causing string formatting issues. 

## Fix

Replacing `%` with `%%`. 
```python
def mod(lhs, rhs):
    return Expression(lhs, '%%', rhs)
```

That's not a big issue, but when we know nothing about Python 2 string formatting and SQL, it's a bit unsettling :) 

What database driver are you using this with, out of curiosity? The example works fine with Sqlite but I'm wondering if this is a result of psycopg2 doing its mogrifying client-side.

This was with Postgre. Indeed, the issue was raised on l. 3144, `cursor.execute(sql, params or ())`.
Issue: removed a  consider-using-in pitfall case
Author: NaelsonDouglas
State: closed
Created: 2021-10-24 18:15:28

**Problem**:
The code was incurring into the consider-using-in pitfall case, as described by Pylint documentation here https://github.com/vald-phoenix/pylint-errors/blob/master/plerr/errors/refactoring/R1714.md

**Solution**:
Applied adequate refactoring. Only a single line of code was changed

I'll pass.
Issue: Just warrning when insert_many NULL value into a Not Allow Null field
Author: ThaoPN
State: closed
Created: 2021-10-22 10:29:27

I have a table like below
```
CREATE TABLE `tests` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `text` varchar(45) NOT NULL,
  `not_null_field` varchar(45) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
```

and the code I used to insert data:
```
class Test(BaseModel):
    id = AutoField()
    text = CharField(null=False)
    not_null_field = CharField(null=False)

    class Meta:
        table_name = "tests"


def add_one_null_row():
    Test.insert_many([{Test.text: None}]).execute()


def add_multiple_null_row():
    Test.insert_many(
        [
            {Test.text: "abc", Test.not_null_field: None}
        ]
    ).execute()
```

When I `insert_many` with one record like above, I got the error `peewee.IntegrityError: (1048, "Column 'not_null_field' cannot be null")`

But when I `insert_many` with more than one record, I just got a warning 
`.../python3.7/site-packages/pymysql/cursors.py:170: Warning: (1048, "Column 'not_null_field' cannot be null")`
and the data be inserted successfully with `not_null_field` is `empty`.

The query I got:
```
INSERT INTO `tests` (`text`, `not_null_field`) VALUES ('abc', NULL), ('abc', NULL)
```

I don't know why I just got a warning but an error here, somebody help to correct me.

Your first query "add_one_null_row()" is not specifying any value at all for `not_null_field`, while your second query "add_multiple_null_row()" is explicitly specifying NULL for `not_null_field`. I am guessing that older MySQL (with its many quirks) handles these situations differently, raising an IntegrityError for the first case, but possibly silently converting the second case to an empty string or something like that?

In short: I think this is a MySQL thing. Both the error *and* the warning are generated by MySQL. I suspect there is a MySQL setting you can enable which will cause the IntegrityError to be raised in both cases -- possibly you are on an old MySQL or running with relaxed settings?

In any case, I suggest consulting the MySQL docs or checking out StackOverflow.

When I run this on MariaDB 10.4, I get an IntegrityError for *both* queries -- as expected. The first query gives the integrityerror for Column "text" cannot be null, and the second query gives the integrityerror for Column "not_null_field" cannot be null.
Issue: Union queries containing order_by
Author: SilvanVerhoeven
State: closed
Created: 2021-10-16 08:47:41

I receive two queries from separate modules and want to combine the queries using `UNION` (or `UNION ALL`). The following works like a charm in MySQL:

```python
def fetch_orders_query(limit, **kwargs):
        return ModuleA.fetch_orders_query(limit // 2, **kwargs) | ModuleB.fetch_orders_query(limit // 2, **kwargs)

# Resulting SQL query
('(SELECT *fields* FROM *table* AS `t1` WHERE (*predicate1*) ORDER BY *field_1* LIMIT *x*) UNION (SELECT *fields* FROM *table* AS `t2` WHERE (*predicate2*) ORDER BY *field_2* LIMIT *y*)')
```

Yet in SQLite, it throws the error `sqlite3.OperationalError: ORDER BY clause should come after UNION not before`. I couldn't find a working solution for peewee, yet I was trying to write a query like proposed in this StackOverflow post: https://stackoverflow.com/questions/10812910/combine-two-statements-with-limits-using-union, without success.

Is there a clean way to solve this issue in peewee? Executing both queries separately and combining the results is not a preferred solution, as the database is on an external server and network operations are extremely expensive performance-wise.

There's a little helper you can use to wrap a select query:

```python
class A(Base):
    a = TextField()

class B(Base):
    b = TextField()

db.create_tables([A, B])

A.insert_many([('a%d' % i,) for i in range(10)]).execute()
B.insert_many([('b%d' % i,) for i in range(10)]).execute()

q1 = A.select(A.a).order_by(A.a).limit(5)
q2 = B.select(B.b).order_by(B.b).limit(5)

# Wrap the queries in the form SELECT * FROM (SELECT ... LIMIT 5)
wq1 = q1.select_from(SQL('*'))
wq2 = q2.select_from(SQL('*'))

# Create the union - note that it may be necessary to re-bind the
# query to the database. I've fixed this, but it is not contained in the
# current release on PyPI.
u = (wq1 | wq2).bind(db)

for row in u:
    print(row)
```

This prints:

```
{'a': 'a0'}
{'a': 'a1'}
{'a': 'a2'}
{'a': 'a3'}
{'a': 'a4'}
{'a': 'b0'}
{'a': 'b1'}
{'a': 'b2'}
{'a': 'b3'}
{'a': 'b4'}
```