akumoth/peewee-issues huggingface dataset:
Issue: The client was disconnected by the server because of inactivity.
Author: GreatBahram
State: closed
Created: 2021-05-26 08:28:09

Hi there,
I got this exception when I have a long connection that exceeds both `wait_timeout` and `interactive_timeout` timeouts:
```python
  File "/usr/lib/python3/dist-packages/peewee.py", line 5513, in get
    return sq.get()
  File "/usr/lib/python3/dist-packages/peewee.py", line 5911, in get
    return clone.execute(database)[0]
  File "/usr/lib/python3/dist-packages/peewee.py", line 1587, in inner
    return method(self, database, *args, **kwargs)
  File "/usr/lib/python3/dist-packages/peewee.py", line 1658, in execute
    return self._execute(database)
  File "/usr/lib/python3/dist-packages/peewee.py", line 1809, in _execute
    cursor = database.execute(self)
  File "/usr/lib/python3/dist-packages/peewee.py", line 2666, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/usr/lib/python3/dist-packages/peewee.py", line 2660, in execute_sql
    self.commit()
  File "/usr/lib/python3/dist-packages/peewee.py", line 2451, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/usr/lib/python3/dist-packages/peewee.py", line 178, in reraise
    raise value.with_traceback(tb)
  File "/usr/lib/python3/dist-packages/peewee.py", line 2653, in execute_sql
    cursor.execute(sql, params or ())
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 209, in execute
    res = self._query(query)
  File "/usr/lib/python3/dist-packages/MySQLdb/cursors.py", line 315, in _query
    db.query(q)
  File "/usr/lib/python3/dist-packages/MySQLdb/connections.py", line 226, in query
    _mysql.connection.query(self, query)
peewee.OperationalError: (4031, 'The client was disconnected by the server because of inactivity. See wait_timeout and interactive_timeout for configuring this behavior.')
```
I spotted the [shortcuts](https://github.com/coleifer/peewee/blob/master/playhouse/shortcuts.py) module inside the peewee code handles these errors: 2006,2013, 2014. I think this can be added to the `ReconnectMixin` class as well.

Do you want me to add this into the `ReconnectMixin` class?

Thanks
Issue: Inner Select as Operation
Author: josiahlaivins
State: closed
Created: 2021-05-24 19:39:57

I am trying to setup a Azure SQL Db connector for peewee. Currently Model's use a local sqlite db for local storage/caching. I would like to connect to Azure SQL DB. I have found that this is not instantly straight forward:

When executing a `db.create_tables([table for table in classes if table not in db.get_tables()])` we get a create table failure:
```bash
{peewee.py:3132} DEBUG - ('CREATE TABLE IF NOT EXISTS "some_table" \
("some_table_id" INTEGER NOT NULL PRIMARY KEY,  "some_column" TEXT)', [])
....
[42000] [Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Incorrect syntax near \
the keyword 'IF'. (156) (SQLExecDirectW)"
```
This is because for whatever reason, ASDB has a different way to checking if a table exists:
```python
# peeewee                                                          ASDB
"IF NOT EXISTS": "IF NOT EXISTS(select * from sysobjects where name='sometable' and xtype='U')
```

My solution is to try operations:
```python
class AzureSQLDatabase(Database):
    operations={
        "IF NOT EXISTS": "IF NOT EXISTS(select * from sysobjects where name='sometable' and xtype='U')"
    }
```
However it doesn't seem like that operation is being applied to a model's create_table execution. There is also the issue that
I need to know the model's name in the operation. 
`SomeTable._meta.database=asdb`
```python
ctx = SomeTable._schema._create_context()
ctx.literal('IF NOT EXISTS')
```

Is there an easy solution in peewee to solve this? I think the quick and dirty way is to overwrite `execute_sql` to do the replacement, however I assumed that I can use `operations` to solve this. Is there somewhere to look at operations that contain queries?

I am also have a hard time finding where `operations` gets used? Like where is the line that does the sql replacement.

Not really, Peewee does not make an effort to support microsoft tsql.

@coleifer Thanks for the quick response! I understand that peewee doesnt support tsql. However I am making my own database object to support it. I thought I could just do a similar thing to the SQliteDatabase code. I appreciate any opinions from you on this. I could boil my problem down to the question whether peewee supports this form of operations:
```python
operations={
        "IF NOT EXISTS": "IF NOT EXISTS(select * from sysobjects where name='sometable')"
    }
```
Where there is a select statement with params inside. Unless this is what you mean by `not make an effort to support microsoft tsql.` in that tsql requires the ability to replace simple operations with subqueries?

If your response is that the operations field doesnt support something as complex as a subquery, then that helps a lot, I would likely need to do some deeper customization then. Thanks!

You will want to subclass the `SchemaManager` and override the `_create_table()` method, then instruct your base models to specify your custom SchemaManager implementation. The SchemaManager is where the logic lives for constructing the various DML queries.
Issue: Value not getting stored?
Author: Donkere-vader
State: closed
Created: 2021-05-21 05:23:12

I'm not really sure if this is a bug or not. But it is very unexpected behavior.

So in my code I have two classes ``List`` and ``ListItem``.

```py
class Item(Model):
    # snip
    def set_value(self, value):
        pass  # snip

class ListItem(Item):
    pass  # snip

class List(Model):
    # snip

    def append(self, value):
        new_item = ListItem(parent=self)
        new_item.set_value(value)
        new_item.save()
        print("Value from object:", new_item.value)
        print("Value from queried object: ", ListItem.select().where(ListItem.id == new_item.id).get().value)
```

the ``ListItem.set_value(value)`` function does basically set the field ``value`` to the str of the value and the field ``value_type`` to what type it is. To later convert it back to a python object of the correct type.

if for example the parameter ``value`` of the function ``append`` would be ``2``. I would expect the following output:

```bash
Value from object: 2
Value from queried object:  2
```

However, as you might have guessed, that's not the output. The real output is:

```bash
Value from object: 2
Value from queried object:  <class 'peewee.CharField'>
```

Can this be a bug? Or am I doing something wrong?

Any help would be appreciated :)

Full code is up on [pastebin](https://pastebin.com/fcSydNai) the ``append`` function is on ln. 127

You're doing some very strange stuff with so many operator overloads and things, so it's hard to tell what is going on. Specifically, overriding getitem / setitem on model classes should probably be avoided.

I'd suggest simplifying your code as much as possible to track this down, as I'm quite sure it's not a bug in Peewee but rather in your implementation.

Oh it was a very very stupid problem.

The code for my ``ListItem`` class looked like this:

```py
class ListItem(Item):
    parent = ForeignKeyField(List, backref="items")
    value = CharField
```
Notice the last line, I set value to the class CharField instead of an instance of CharField. I forgot the braces 🤦🏻 .
Thanks for your response anyways!  

Line should've been:
```py
    value = CharField()
```
Issue: Is JSONPath for postgresql support planned?
Author: DimaKalbfleysh
State: closed
Created: 2021-05-20 08:10:31

Colleagues, good afternoon, JSONB has become an integral part of many projects. And I believe that many would like to see the implementation of this functionality (JSONPath) in peewee!

There is some simple jsonpath support for the `__getitem__()`-type operations on jsonb fields. Because jsonpath is basically an entire language of its own, I've chosen not to implement some kind of python layer for now.

You can call the postgres jsonpath functions quite easily from peewee via `fn.jsonpath_XXX()` and pass in the field instance and the desired path as a string.

```python
fn.jsonb_path_exists(JsonModel.data, '$.owner_user_id ? (@ == 4843)')
```
Issue: [question] Use query alias for group_by/order_by
Author: atanasoff-yordan
State: closed
Created: 2021-05-19 11:47:06

As the documentation says [here](https://docs.peewee-orm.com/en/latest/peewee/querying.html#sorting-records) we should put the column on the order_by/group_by, for example, here the `.order_by(fn.COUNT(Tweet.id).desc())`
My question: since we defined an alias for the count, can we get a reference to that alias to use it in the  group_by/order_by? instead of writing again the whole thing?
thx

```python
query = (User
         .select(User.username, fn.COUNT(Tweet.id).alias('num_tweets'))
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User.username)
         .order_by(fn.COUNT(Tweet.id).desc()))
```

Peewee will do the right thing and use the alias if you write it this way:

```python
ct = fn.COUNT(Tweet.id).alias('ct')
query = (User
         .select(User.username, ct)
         .join(Tweet, JOIN.LEFT_OUTER)
         .group_by(User.username)
         .order_by(ct))
```

Produces:

```sql
SELECT "t1"."username", COUNT("t2"."id") AS "ct" 
FROM "user" AS "t1" 
LEFT OUTER JOIN "tweet" AS "t2" ON ("t2"."user_id" = "t1"."id") 
GROUP BY "t1"."username" 
ORDER BY "ct"
```
Issue: Conditional query of multiple in
Author: xiaoqge
State: closed
Created: 2021-05-19 06:11:12

Does peewee support multiple in query conditions? Can batch query if not supported?
```
pre_out = Txouts.select(Txouts.tx_id,Txouts.value,Txouts.scriptpubkey)\
                         .where(Txouts.tx_id==ins["prevout_tx_id"],Txouts.n==ins["prevout_n"]).dicts()
```

such as
```
SQL：
SELECT * from M 
where
(R, D) in (("200909","1"),("511904","1"),("200908","4"));
```

Peewee supports multiple IN conditions:

```python

# postgres:
in_list = ValuesList([(1, 'u1'), (2, 'u2'), (7, 'u7'), (8, 'u8')])
query = User.select().where(Tuple(User.id, User.username).in_(in_list))


# note sqlite requires additional parentheses:
from peewee import EnclosedNodeList
in_list = EnclosedNodeList([in_list])
query = User.select().where(Tuple(User.id, User.username).in_(in_list))
```

thank you
Issue: Unable to specify custom column name
Author: alexin
State: closed
Created: 2021-05-13 12:30:41

Hello,

I am unable to set a custom column for a primary key.

For example, the following model

```python
class Product(peewee.Model):
    reference = peewee.IntegerField(column_name='reference', primary_key=True),
```

results in the following SQL query

```sql
CREATE TABLE IF NOT EXISTS "product" ("id" INTEGER NOT NULL PRIMARY KEY)
```

Note that I request the name 'reference', but instead I get 'id'.

**Python**: 3.9.4
**Peewee**: 3.14.4

Am I missing something?

Thank you,
Alexandre Vieira

You have a trailing comma after your peewee.IntegerField() which is probably the issue.

This code works fine:

```python
class Product(Model):
    reference = IntegerField(column_name='reference', primary_key=True)
    class Meta:
        database = db
```

Produces:

```sql
CREATE TABLE IF NOT EXISTS "product" ("reference" INTEGER NOT NULL PRIMARY KEY)
```

Please debug your code a bit more before opening issues.

Thank you for pointing that out. I am so used to Java-like languages that I easily missed that comma.
I understand it is dumb mistake and I could have avoided it. In my defense, I am dumbfounded I got no error nor warning, that could have saved me hours of figuring what was happening.
Issue: peewee.OperationalError: near "RETURNING": syntax error
Author: bravegag
State: closed
Created: 2021-05-12 22:05:20

I'm on peewee 3.14.4 and sqlite, and I'm getting the above error `peewee.OperationalError: near "RETURNING": syntax error` for a simple insert_many query adapted to my context from exactly the same as stated in the docs i.e.

```
query = (User
         .insert(email='foo@bar.com', created=fn.now())
         .returning(User))
```

I have also tried returning User.id or even leaving returning arguments empty but I get the same error.

The generated sql is something like:

```
INSERT INTO table (a, b, c, ...) VALUES (...), (...), (...) RETURNING table.id
```

I found [this discussion](https://sqlite.org/forum/info/a4dde39b614ec0b2) and the issue is my version of sqlite3 3.29.0 which is lower than 3.35.x from where the RETURNING clause is supported :(.

Is there another way to accomplish the same use-case without RETURNING?

>Is there another way to accomplish the same use-case without RETURNING?

If you leave off the `returning()` (which is unsupported in sqlite < 3.35) then Peewee will already return you the last insert rowid, which it seems like is all you want anyways. For multi-row inserts there is no equivalent, however.
Issue: WHERE-IN query missing VALUES keyword leading to sqlite error
Author: bravegag
State: closed
Created: 2021-05-12 20:12:59

I have the following use case in peewee version 3.14.4:

```
query = A.select().where(Tuple(A.a1, A.a2).in_([('x1', 'y1'), ('x2', 'y2')]))
print(query)
```

This will generate for sqlite a query similar to:
```
SELECT ... 
FROM a 
WHERE (a1, a2) IN (('x1', 'y1'), ('x2', 'y2'))
```
which leads to the sqlite error: `Error while executing SQL query on database X: row value misused`

peewee SQL generator is missing the VALUES keyword like the correct sql code should be:
```
SELECT ... 
FROM a 
WHERE (a1, a2) IN (VALUES('x1', 'y1'), ('x2', 'y2'))
```


Sqlite is finnicky about parentheses.

This should work:

```python

vl = ValuesList([('x1', 'y1'), ('x2', 'y2')])
# wrap in extra parentheses for sqlite
vl_p = EnclosedNodeList([vl])

q = A.select().where(Tuple(A.a1, A.a2).in_(vl_p))
```
Issue: [Playhouse.migrator] Implement add_foreign_key_constraint or document solution for sqlite
Author: thigg
State: closed
Created: 2021-05-11 20:36:15

`add_foreign_key_constraint` for the sqlite migrator throws a NotImplemented error.

A possible solution would be to rename the table, recreate the table, copy the data, drop the copy. (as described in https://www.techonthenet.com/sqlite/foreign_keys/foreign_delete.php)

This could either be implemented or at least a comment could be added which points to a possible solution.

For reference: 

I solved it with this function:

```python
def recreate_table(table: Type[Model], tablename: str):
    logger.info("copying table %s", tablename)
    table_copy_name = tablename + "_x"
    col_names = ",".join(col.name for col in db.get_columns(tablename))
    db.execute_sql("ALTER TABLE %s RENAME TO %s;" % (tablename, table_copy_name))
    db.create_tables([table])
    sql = "INSERT INTO %s (%s) SELECT %s FROM %s;" % (tablename, col_names, col_names, table_copy_name)
    #todo: check for returned sql errors
    db.execute_sql(sql)
    logger.info("deleting copy of table %s", tablename)
    db.execute_sql("DROP TABLE %s_x;" % tablename)
```
Note that you need the column names because the order might change when recreating the table. (E.g. if you replace a deferred constraint with a direct one)

(Just has the problem, that the alter table also renames already existing constraints. Thus make sure, that you do not run this on a table, that is already referenced by a constraint, because otherwise the constraint is changed to `<table>_x`)

I've decided to pass on implementing this for Sqlite, but it is possible, as you suggest.

Maybe this could be mentioned in the documentation then?
Issue: Insert operations of models with non-PK foreign keys fail when passing a target object instance
Author: smuething
State: closed
Created: 2021-05-11 15:27:09

I have a model `A` with a `ForeignKeyField` column that uses the `field` parameter to set a non-pk reference column in the target model:

```python
class Target(Model):
    key = FixedCharField(8,unique=True)

class A(Model):
    target = ForeignKeyField(Target,field="key",lazy_load=False)
```

When trying to create instances of `A`, I can do so with `create()`, but not with the `ModelInsert`-based methods:

```python
t = Target.create(key="X")

A.create(target=t) # works

A.insert(target="X").execute() # works
A.insert(target=t).execute()   # fails
```

In the last case, the generated SQL uses the primary key of `t` instead of `t.key` because the code path in `ModelInsert` does not apply the `field` parameter of the `ForeignKeyField` to the passed-in model instance. The attached patch fixes the problem for me.
Issue: [Feature] Add support for the precision parameters to the FLOAT field
Author: enpaul
State: closed
Created: 2021-05-11 01:20:42

Hello, thank you so much for creating and maintaining this library! Peewee has been my go-to for several years now, both at work and on personal projects and it's honestly been a joy to use.

Recently I encountered a situation where I needed to specify the [precision parameters](https://dev.mysql.com/doc/refman/8.0/en/floating-point-types.html) for the MySQL `FLOAT` field, and after a search through this repo and the docs I ended up opening a [question on Stackoverflow](https://stackoverflow.com/questions/67475853/specify-float-column-precision-in-peewee-with-mariadb-mysql) where I got a very helpful answer that this patch is based on.

---

This patch adds two new init parameters to the `FloatField` class:
* `max_digits`, which corresponds to the `M` precision parameter on the MySQL `FLOAT` class and defines the number of digits that can be returned accurately from the column
* `decimal_places`, which corresponds to the `D` precision parameter on the MySQL `FLOAT` class and defines the number of digits after the decimal point which can be returned accurately from the column

Currently each of these new parameters has a default of `None` and are excluded from the return value of `get_modifiers()` so that the current behavior of the field isn't changed. However, if the defaults were changed to `max_digits=6` and `decimal_places=0` then the current behavior would be changed but the behavior of the field would be consistent across database backends.

Ehh, I'm going to pass on this, as I think it could be misleading. This is a mysql-only thing, as far as I know, and the data is still stored as a floating-point (e.g. approximate) value anyways. If you want to specify a precision then you would really want to use the DecimalField (numeric type).

I'd suggest adding your own subclass of FloatField to your libraries if you want this functionality.

That makes sense, thanks for taking a look at it! I had thought that this was a mysql+postgres feature, but it looks that you're right and it's only mysql. Thanks again for the great library!
Issue: LSMTable.get_by_id raises KeyError which breaks __contains__ functionality on ModelBase
Author: vdksoda
State: closed
Created: 2021-05-08 05:55:21

I have a scenario where I need to test for the presence of a key in a `LSMTable`. I noticed that `ModelBase` does support a `__contains__` method. So decided to do something like:
```
app_db = PooledSqliteExtDatabase(
        database=sqlite_db_filename,
        max_connections=500,
        pragmas=[
            ("journal_mode", "wal"),
            ("cache_size", -1024 * 64),
            ("foreign_keys", 1),
            ("ignore_check_constraints", 0),
            ("synchronous", 0),
            ("page_size", 1024 * 64),
        ],
)
app_db.load_extension('./lsm')

key = str(uuid.uuid4())
with app_db as db, db.bind_ctx([KV]):  # where KV is an LSM Table
    is_in_db = key in KV

``` 
This part `key in KV` raises a `KeyError`. Per your design, would you expect users to handle this error or would you prefer to fix in the peewee library?

The potential fixes in peewee could be:

1. Unify `LSMTable` and `ModelBase` by raising `DoesNotExist` in the `get_by_id` method and recast to `KeyError` only in `__getitem__`. The `__getitem__` change could be implemented at `ModelBase` given the interface mirrored is dictionary like; or
2. Overwrite `__contains__` in LSMTables to reflect that `KeyError` is thrown by `LSMTable.get_by_id`.

This is what I could come up with in the short time I reviewed the codebase. Would be great to hear your thoughts.

PS: Thank you for your blogs; I have learnt a lot from them especially as they cover working with novel datastores. 

You bring up a good point about having this be consistent with other models. I've removed the code which was raising the `KeyError` and updated the docs as well. It should behave the same as regular models now.
Issue: PostgreSQL Autoreconnect / migrations
Author: konradkur
State: closed
Created: 2021-05-07 11:35:40

Hello guys,

i try find solutions for postgresql autoreconnect, any ideas?

next question, the best migration system for peewee it?

thanks,
Konrad

Automatic reconnect can be done with the help of the `playhouse.shortcuts.ReconnectMixin`, but you will definitely need to modify the `reconnect_errors` attribute so that it corresponds to Postgres-specific error conditions. The reconnect mixin should not be used with Postgres unless you REALLY know what you are doing. See the source code: https://github.com/coleifer/peewee/blob/master/playhouse/shortcuts.py

Peewee provides a helper for writing migrations: http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#schema-migrations

I can't vouch for any of the "automatic" migration tools.
Issue: The problem of storing timestamps
Author: xiaoqge
State: closed
Created: 2021-05-07 07:37:21

Hello, can't the DateTimeField field store a timestamp without a time zone? Thank you very much for your help every time

```
from peewee import *

db = PostgresqlDatabase('peewee_test',user='postgres',host="10.10.17.2",password='111')
#db2 = PostgresqlDatabase('peewee_test',user='postgres',host="10.10.17.2",password='111')  # 2nd connection

class Currnumber(Model):
    tx_at = DateTimeField()
    class Meta:
        database = db

db.create_tables([Currnumber])
Currnumber.create(tx_at="1620372845")
```

peewee.DataError: date/time field value out of range: "1620372845"
LINE 1: INSERT INTO "currnumber" ("tx_at") VALUES ('1620372845') RET...
                                                   ^
HINT:  Perhaps you need a different "datestyle" setting.

I know the following way：
**So you should:

Use DateTimeField if you want to work with datetime in python and store the data using the datetime data-type in your sql db.
Use TimestampField if you want to work with datetime in python and store the data using an integer in your sql db.
Use IntegerField if you want to work with timestamps as integers in python and store the data as an integer in your db.**

However, timescaledb of postgresql creates supertables to store time series data at intervals of time fields, which makes peewee unable to store supertable data of timescaledb，Below is their documentation, so what can I do to solve？Thanks again

https://docs.timescale.com/timescaledb/latest/how-to-guides/hypertables/create/#create-a-hypertable