akumoth/peewee-issues huggingface dataset:
Issue: [Question] How to select row with most recent date per user?
Author: loynoir
State: closed
Created: 2022-01-28 03:51:37

### Question
Hi, sir.

What's the peewee way, to select row with most recent date per user?

### Input
id | user | val
-- | -- | --
1 | jane | 11
2 | john | 21
3 | bob | 32
4 | bob | 31
5 | john | 22


### Output

id | user | val
-- | -- |  --
1 | jane | 11
4 | bob | 31
5 | john | 22


<details>
  <summary>Prepare Code</summary>
  <p>
 

```python
from peewee import SqliteDatabase, Model, TextField, IntegerField
from playhouse.sqlite_ext import AutoIncrementField

db = SqliteDatabase(
    "./foobar.db",
    pragmas={
        "journal_mode": "wal",
        "cache_size": -1 * 64000,
        "foreign_keys": 1,
        "ignore_check_constraints": 0,
        "synchronous": 0,
    },
)


class Score(Model):
    id = AutoIncrementField()
    user = TextField()
    val = IntegerField()

    class Meta:
        database = db


db.connect()
db.create_tables([Score])
for [user, val] in [
    ["jane", 11],
    ["john", 21],
    ["bob", 32],
    ["bob", 31],
    ["john", 22],
]:
    Score.create(user=user, val=val)

```
  </p>
</details>




<details>
  <summary></summary>
  <p>
 

Maybe keep same order as user field?

id | user | val
-- | -- |  --
1 | jane | 11
5 | john | 31
4 | bob | 22
  </p>
</details>


Thanks.

Post your questions on stackoverflow or irc. This is the issue tracker.
Issue: drop_tables does not delete "schema" tables in MySQL
Author: ascaron37
State: closed
Created: 2022-01-25 09:48:21

I have a problem testing my application with MySQL. I have two separate databases on one MySQL server, say DB1 and DB2. I create a DB Object with `db1 = MySQLDatabase('DB1', ...)`. All my models from DB2 have a schema attribute in the inner Meta class:
```
class DBModel(Model):
    class Meta:
        database = db1

class Model2(DBModel):
    id = AutoField()

    class Meta:
        schema = 'DB2'
```
In the application this works well, but for testing purposes I want to create and drop all tables. All models from the first DB get created and deleted properly. My problem now is that creating the tables in the second DB works as expected, but drop_tables does not drop the tables from the second DB.
```
    db1.create_tables((Model2,))  # creates the table in the second DB
    db1.drop_tables((Model2,))  # does not drop tables from second DB
```

Is my usage of the schema attribute a lucky incident from the standpoint that it works in my application? Can I force drop_tables to delete also the tables from the other schemas?

So the reason the drop doesn't work is because Peewee, when "safe=True" (the default), attempts to check if the table exists in the current db. To get this working, you can just specify `safe=False`:

```python
# This will skip the check that the table exists, and then issue the
# appropriate drop table.
db1.drop_tables([Model2], safe=False)
```

Perfect, thank you!
Issue: PooledMySQLDatabase MaxConnectionsExceeded
Author: Ahern542968
State: closed
Created: 2022-01-22 02:08:23

I use PooledMySQLDatabase  for  a pool , when I with atomic for create , but  case MaxConnectionsExceeded
```
def logic_create_event_item(operate_dict):
    user_id = operate_dict["user_id"]
    content_type = "event"
    event_id = operate_dict["event_id"]
    operate_type = "create"
    user_operate_log = UserOperateLog.get_or_none(
        UserOperateLog.user_id == user_id, UserOperateLog.content_type == content_type,
        UserOperateLog.event_id == event_id, UserOperateLog.operate_type == operate_type)
    if user_operate_log:
        return
    with settings.DB.atomic() as txn:
        try:
            user_operate_log = UserOperateLog()
            user_operate_log.user_id = user_id
            user_operate_log.content_type = content_type
            user_operate_log.content_id = event_id
            user_operate_log.operate_type = operate_type
            user_operate_log.content = "创建埋点"
            user_operate_log.save(force_insert=True)
        except Exception as e:
            txn.rollback()
            raise e
```
![image](https://user-images.githubusercontent.com/32890883/150620761-475a7d32-ecc7-4fdd-8843-a3f1718617f7.png)






http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#pool

You are not managing your connections.

Call connect at the beginning of a request/thread, and close() at the end. This ensures that your conns are returned to the pool.
Issue: Select with complex where statement don't transform correctly
Author: spacialhufman
State: closed
Created: 2022-01-21 17:36:05

I was building a complex query in my app which is describe below:

`query = (self.repository.getSelect((
            fieldId,
            fieldReceivedAt,
            fieldMac,
            fieldCode,
            fieldDomain,
            fieldStatus
        ))
            .where(
                fieldId.between(initialId, endId + 10000) &
                SystemEventModel.Message.contains('done query') &
                self.__getCodeFuncField().in_([t.code for t in tickets]) &
                SystemEventModel.FromHost.in_([t.mac for t in tickets])
            )
            .order_by(fieldCode))`

The sintax seems correct by when I run the application, the ORM makes my query look like below:

`SELECT t1.ID AS id, t1.ReceivedAt AS received_at, t1.FromHost AS mac, TRIM(SUBSTR(SUBSTR(t1.Message, INSTR(t1.Message, '#'), 11), 1, INSTR(SUBSTR(t1.Message, INSTR(t1.Message, '#'), 11), ' '))) AS code, TRIM(IF((INSTR(t1.Message, 'dns name exists') > 0), 'dns name exists, but no appropriate record', SUBSTR(TRIM(SUBSTR(SUBSTR(t1.Message, INSTR(t1.Message, '#'), 100), INSTR(SUBSTR(t1.Message, INSTR(t1.Message, '#'), 100), ' '), 100)), 1, INSTR(TRIM(SUBSTR(SUBSTR(t1.Message, INSTR(t1.Message, '#'), 100), INSTR(SUBSTR(t1.Message, INSTR(t1.Message, '#'), 100), ' '), 100)), ' ')))) AS domain, TRIM(IF((INSTR(t1.Message, 'dns name exists') > 0), 1, IF((INSTR(t1.Message, 'dns does not exist') > 0), 0, 2))) AS status FROM SystemEvents AS t1 WHERE ((((id BETWEEN 23706618 AND 23726554) AND (t1.Message LIKE '%done query%')) AND (TRIM(SUBSTR(SUBSTR(t1.Message, INSTR(t1.Message, '#'), 11), 1, INSTR(SUBSTR(t1.Message, INSTR(t1.Message, '#'), 11), ' '))) IN ('#118729', '#118730', '#12485651', '#12485652', '#12485653', '#129837753', '#129837754', '#1301639'))) AND (0 = 1)) ORDER BY code`

Why the last where was transformed into `(0 = 1)` if it is a **where in** statement?

Any help would be appreciated.

Thanks

Peewee will generate that `0 = 1` when you write an expression that evalutes to `WHERE x IN ()` (which is always false):

```python
.where(Model.field.in_([]))
```

So in your example, at least one of your lists is empty, or alternatively your `tickets` iterator is a generator or something and returns a list, but when iterated the 2nd time it is consumed and returns an empty list.
Issue: generate_models from DatabaseProxy ValueError
Author: LLjiahai
State: closed
Created: 2022-01-19 02:42:34

db = DatabaseProxy()
db.initialize(RetryMySQLDatabase.get_db_instance(**kw_config))
models = generate_models(db)

ValueError: Introspection not supported for <peewee.DatabaseProxy object at 0x000002859D87FE40>

In this case, just instantiate the introspector directly - or pass it the underlying db instance:

```python
# Option 1:
introspector = Introspector(MySQLMetadata(database))
introspector.generate_models()

# Option 2:
models = generate_models(db.obj)
```

I gave this another look and made a better fix. Going forward introspection will work with a proxy just fine.
Issue: How to get all data from my Model?
Author: peepo5
State: closed
Created: 2022-01-18 12:50:29

My class looks like this:

```py
class WaitPayment(BaseModel):
    txn_id = TextField(unique=True, primary_key=True)
    discord_id = TextField()
    ms_expires = DecimalField()
```

and I want to recieve all of its records as a dict. However, when I use `WaitPayment.select()` or `WaitPayment.select().dicts()`, it returns:

```sql
SELECT "t1"."txn_id", "t1"."discord_id", "t1"."ms_expires" FROM "waitpayment" AS "t1"
```
in the terminal, which is not what I want. I do not want the sql query, I want the result. Any help? Thanks.

You need to read the documentation.

Take 10 or 15 minutes and go through the quick start.

http://docs.peewee-orm.com/en/latest/peewee/quickstart.html#retrieving-data

So I have to iterate over the select to get any results? Your answer to my question did not help.

http://docs.peewee-orm.com/en/latest/peewee/querying.html#selecting-a-single-record

http://docs.peewee-orm.com/en/latest/peewee/querying.html#selecting-multiple-records

To answer my own question, yes you must iterate. @coleifer are there any ways of not iterating to save time? like recieving an array of classes instead of just something to iterate upon. Thanks. And please give some explanation rather than just sending a link, thank you.

https://docs.python.org/3/library/functions.html#func-list
Issue: Field argument 'help_text' seem have not realized in MySQL...
Author: xfl12345
State: closed
Created: 2022-01-17 04:03:44

I think the 'help_text' parameter may become the 'comment' value in MySQL.
It did not work as i expect.
Neigther of 'verbose_name' parameter.
I would appreciate if the little feature has been realized.
Thanks a lot.
(BTW: peewee is so much easier to use than SQLAlchemy!!!Can't help coding with peewee.)

The `help_text` and `verbose_name` are used for integrations with flask-peewee (or other user-facing applications) and do not have any impact on the schema.
Issue: potential raw_unicode_escape error when debugging customized BINARY field 
Author: est
State: closed
Created: 2022-01-10 05:49:22

Hi,

I created a BINARY field following the doc

https://docs.peewee-orm.com/en/latest/peewee/models.html#creating-a-custom-field

When debugging the SQL, peewee will try `utf8` or `raw_unicode_escape` the `bytes()` parameter, at one time my bytes happens to be `b"\x0ee\xd4\xee\xff\xc0\x02r\xc2\\U0'\x00"`, which will error in [_query_val_transform](https://github.com/coleifer/peewee/blob/master/peewee.py#L683)

```
UnicodeDecodeError
'rawunicodeescape' codec can't decode bytes in position 9-11: truncated \UXXXXXXXX escape
```

I think it's because we assume the in SQL parameter, `bytes()` are displayable strings but unfortunately it will fail when there's an illegal "\U" literal inside the string.

Did you not read the [code comment](https://github.com/coleifer/peewee/blob/master/peewee.py#L658-L661)? This is not part of the public API.

> Did you not read the code comment? This is not part of the public API.

Hi coleifer, I am using `logger.debug("sql is %s", query)` with one of the parameter as a customized mysql BINARY field. I think it fits the code comment of  "This function is intended for debugging or logging purposes ONLY".

Unless there's another way to debug the SQL I am unaware of?


The correct way to do this is:

```python

query = MyModel.select().where(MyModel.foo == 'bar')
sql, params = query.sql()
```

You can inspect the sql string, which contains proper placeholders for the parameters, and the parameters themselves.
Issue: table_exists(MyModel) should raise error
Author: jonashaag
State: closed
Created: 2022-01-05 09:45:29

Misuse of `table_exists` API always returns `False`:

```py
db.table_exists(MyModel)
```

IMO this should either raise an error, or be identical to

```py
db.table_exists(MyModel.table_name)
```

Thanks, this method now accepts a model class.
Issue: Add ModelSelect to __all__ for linting
Author: 1kastner
State: closed
Created: 2022-01-02 19:35:36

First of all thank you very much for creating this project and sharing it under the MIT license!

Recently I have started to rely more on type-hinting in my projects to have better tool support and in some cases making my intentions more obvious for the readers of the code. While doing that, I got the warning that ModelSelect is not part of the official interface as it is not listed in `__all__`. However, I would like to pass a query which is not yet executed to another method or function and only there execute it. Thus my request to add `ModelSelect` to `__all__` and treat it as an official part of the API.

Of course there could be design choices or other ideas that contradict this. In that case I would love to learn more about the future intentions of how this project will continue.

I'm going to pass - I don't really see a need to export this.
Issue: how to search by pair of columns
Author: sherrrrr
State: closed
Created: 2021-12-31 09:08:14

How can I write something like this using peewee in mysql?
```
SELECT whatever 
FROM t
WHERE (col1, col2)
       IN ((val1a, val2a), (val1b, val2b), ...) ;
```

Here is an example (tested with mariadb 10.6):

```python
class Reg(Model):
    key = CharField()
    value = TextField()

Reg.insert_many([('k%02d' % i, 'v%02d' % i) for i in range(100)]).execute()
cols = Tuple(Reg.key, Reg.value)
values = [('k02', 'v02'), ('k04', 'v04'), ('k06', 'v06')]
query = (Reg.select()
         .where(cols.in_(values))
         .order_by(Reg.key))
for reg in query:
    print(reg.key, reg.value)

# Prints:
# k02 v02
# k04 v04
# k06 v06
```
Issue: Does not accept pandas dataframe int/float columns with NA
Author: ashishkumarazuga
State: closed
Created: 2021-12-29 06:42:55

The pandas dataframe containing columns which are int64 or float64 in nature, containing NA or NaN are not accepted by peewee. And throws a `DataError('integer out of range\n')`, during an insert.


This error is being thrown by your database, via your database driver, and is not raised by Peewee.
Issue: distutils has been deprecated in Python 3.10
Author: tirkarthi
State: closed
Created: 2021-12-29 04:56:21

https://www.python.org/dev/peps/pep-0632/#migration-advice

https://github.com/coleifer/peewee/blob/5a55933ed28b261fbe100568125fffe0ba854b99/setup.py#L5-L8

Steve Dower @zooba, a Microsoft employee, is railroading through this idiotic pep. From what I can see there are quite a few people who disagree with his decision:

https://discuss.python.org/t/pep-632-deprecate-distutils-module/5134/29

He comes off as a smug, smarmy nitwit who cannot appreciate that real world users will be affected by this decision. Once again, Microsoft employees shitting up the party? Or just another petty tyrant who found a source of narcissistic supply in open source? You decide.

As much as I sympathize with the reaction, the code should be updated in the future. It seems that the pep isn't going away.

Fallback to the vendored distutils inside setuptools for now: 7ab5e401d85c1eec7aafbbe2fdf3c0344f82422b

Anyone who stumbles upon this and can find examples of prior art for projects that build C extensions and are going through a similar migration, I'd appreciate links. I glanced around at a few projects I'm familiar with, and all of them are still on `distutils`.
Issue: formatargspec was removed in Python 3.11
Author: tirkarthi
State: closed
Created: 2021-12-18 14:43:19

https://github.com/coleifer/peewee/blob/5a55933ed28b261fbe100568125fffe0ba854b99/tests/libs/mock.py#L190

https://docs.python.org/3.11/whatsnew/3.11.html#removed

> the formatargspec function, deprecated since Python 3.5; use the inspect.signature() function and Signature object directly.

That doesn't matter. The stdlib-provided mock is used for any python 3 or newer, and that old version is vendored for Python 2.x users.

Got it, sorry for the noise.
Issue: AttributeError: 'Expression' object has no attribute 'execute'
Author: thisnugroho
State: closed
Created: 2021-12-11 02:11:25

It's appear after upgrading from version 3.14.4 to peewee latest version 3.14.8,
i decide to upgrade because i want to using `Model.get_or_none()` function, it's not available in previous version.
and the error appear i'm trying to running this:
```python
SomeModel().select().get_or_none(SomeModel.field== value)
```
my configuration looklike this:
```python
db = MySQLDatabase(
    env("DB_DATABASE"),
    user=env("DB_USERNAME"),
    password=env("DB_PASSWORD"),
    host=env("DB_HOST"),
    port=int(env("DB_PORT")),
)
```

is there something that i need to adjust ? or i'm just missing something.

screenshoot:
![image](https://user-images.githubusercontent.com/49790011/145660391-f7bf7cf7-b00a-4073-998d-9122faabe720.png)


details
OS:Ubuntu 20.04
MYSQL Version = 8.0.27

The `get_or_none()`, when used on a select query, does not accept any parameters -- though the error message is unhelpful here.

In your code you are:

1. `SomeModel.select()` which creates a `ModelSelect` query
2. Call the `get_or_none()` method on the `ModelSelect` query

I think what you intended to do is this:

```python

SomeModel.select().where(SomeModel.field == value).get_or_none()
```

Alternatively, you can use the *shortcut* method on model, which *does* accept an expression:

```python

SomeModel.get_or_none(SomeModel.field == value)
```

* http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.get_or_none
Issue: Allow to use peewee.SQL from `from_`
Author: d0d0
State: closed
Created: 2021-12-10 09:58:34

**Problem:**
Tried to apply custom SQL to `from_` function from this example https://github.com/coleifer/peewee/issues/1289#issuecomment-307128658 However in my query, I use some joins.

Here is example
```python
q = User.select(User, Tweet).from_(SQL(f'`users` as `t1` USE INDEX(`user_id`) ')).join(Tweet)
for x in q:
   print(x)
```

But without fix, exception is thrown
```
Traceback (most recent call last):
  File "JetBrains\PyCharm2021.2\scratches\scratch_71.py", line 27, in <module>
    for file in q:
  File "venv\lib\site-packages\peewee.py", line 4408, in next
    self.cursor_wrapper.iterate()
  File "venv\lib\site-packages\peewee.py", line 4325, in iterate
    self.initialize()  # Lazy initialization.
  File "venv\lib\site-packages\peewee.py", line 7587, in initialize
    if curr not in self.joins:
TypeError: unhashable type: 'SQL'
```

**Solution**
When initializing ModelCursorWrapper skip SQL instances

I don't really like this solution. The join will not correctly set-up the model graph, so the best solution in my opinion is to write:

```python
q = User.select(User, Tweet).from_(SQL(f'`users` as `t1` USE INDEX(`user_id`) ')).join(Tweet)
for x in q.objects():
   # assume username is on User table, and content is on Tweet table.
   print(x.username, x.content)
```
Issue: Query "where not in empty list" does not work as expected
Author: skontar
State: closed
Created: 2021-12-08 08:43:36

I was using equivalent of `.where(~Person.name << some_list)` and it behaves unexpectedly when the list is empty. See below example.

```python
import peewee
from peewee import *

db = SqliteDatabase(':memory:')


class Person(Model):
    name = CharField()

    class Meta:
        database = db


db.create_tables([Person])

Person(name="Alice").save()
Person(name="Bob").save()
Person(name="Cliff").save()

q = Person.select().where(Person.name << ['Bob'])
print(q)
# SELECT "t1"."id", "t1"."name" FROM "person" AS "t1" WHERE ("t1"."name" IN ('Bob'))
print(list(q.tuples()))
# [(2, 'Bob')]

q = Person.select().where(~Person.name << ['Bob'])
print(q)
# SELECT "t1"."id", "t1"."name" FROM "person" AS "t1" WHERE (NOT "t1"."name" IN ('Bob'))
print(list(q.tuples()))
# [(1, 'Alice'), (3, 'Cliff')]

q = Person.select().where(Person.name << [])
print(q)
# SELECT "t1"."id", "t1"."name" FROM "person" AS "t1" WHERE (0 = 1)
print(list(q.tuples()))
# []

q = Person.select().where(~Person.name << [])
print(q)
# SELECT "t1"."id", "t1"."name" FROM "person" AS "t1" WHERE (0 = 1)
# Expected: SELECT "t1"."id", "t1"."name" FROM "person" AS "t1" WHERE (1 = 1) ??? Maybe?
print(list(q.tuples()))
# []
# Expected: [(1, 'Alice'), (2, 'Bob'), (3, 'Cliff')]


print(peewee.__version__)
# 3.14.8

print(peewee.sqlite3.sqlite_version)
# 3.34.1
```

The problem is python operator precedence. The following work as expected:

```python

Person.select().where(Person.name.not_in([]))

Person.select().where(~(Person.name << []))

Person.select().where(~(Person.name.in_([])))
```

Thanks for reply. I see now, I got confused by SQL operator precedence. 
Would it not be better to translate to `WHERE (NOT "t1"."name" IN ())` for empty list, similarly as it does for non-empty list?

I understand the reasoning and the problem, but I think this can potentially mask the issue until the list becomes empty one day and return unexpected results.
Issue: Peewee 2 does not work with python 3.10
Author: ribx
State: closed
Created: 2021-12-04 11:06:22

The problem is, that the exports of the `collections` modules were moved to `collections.abc`:

```
docker run --rm -ti python:3.10 sh -c 'pip install cython &>/dev/null && pip install peewee==2.10.2'
....
Collecting peewee==2.10.2
  Downloading peewee-2.10.2.tar.gz (516 kB)
     |████████████████████████████████| 516 kB 3.7 MB/s 
    ERROR: Command errored out with exit status 1:
     command: /usr/local/bin/python -c 'import io, os, sys, setuptools, tokenize; sys.argv[0] = '"'"'/tmp/pip-install-i073ept4/peewee_0592019e5eb643a48b6ef02e66fcd5d8/setup.py'"'"'; __file__='"'"'/tmp/pip-install-i073ept4/peewee_0592019e5eb643a48b6ef02e66fcd5d8/setup.py'"'"';f = getattr(tokenize, '"'"'open'"'"', open)(__file__) if os.path.exists(__file__) else io.StringIO('"'"'from setuptools import setup; setup()'"'"');code = f.read().replace('"'"'\r\n'"'"', '"'"'\n'"'"');f.close();exec(compile(code, __file__, '"'"'exec'"'"'))' egg_info --egg-base /tmp/pip-pip-egg-info-efaxgva5
         cwd: /tmp/pip-install-i073ept4/peewee_0592019e5eb643a48b6ef02e66fcd5d8/
    Complete output (7 lines):
    Traceback (most recent call last):
      File "<string>", line 1, in <module>
      File "/tmp/pip-install-i073ept4/peewee_0592019e5eb643a48b6ef02e66fcd5d8/setup.py", line 64, in <module>
        version=__import__('peewee').__version__,
      File "/tmp/pip-install-i073ept4/peewee_0592019e5eb643a48b6ef02e66fcd5d8/peewee.py", line 124, in <module>
        from collections import Callable
    ImportError: cannot import name 'Callable' from 'collections' (/usr/local/lib/python3.10/collections/__init__.py)
    ----------------------------------------
```


```
$ docker run --rm -ti python:3.9
>>> from collections import Callable
<stdin>:1: DeprecationWarning: Using or importing the ABCs from 'collections' instead of from 'collections.abc' is deprecated since Python 3.3, and in 3.10 it will stop working
```

The fix should be quite simple, by just replacing `collections` with `collections.abc`.

I am stuck to peewee 2 because I am using this extension:
https://github.com/cour4g3/peewee-mssql

I can also create a PR.

That version is not receiving patches, unfortunately. Your best bet is to fork it and patch it. You won't lose sync with upstream if you stay on 2.x.