akumoth/peewee-issues huggingface dataset:
Issue: Operator does not exist: json = json?
Author: andycasey
State: closed
Created: 2022-08-26 02:59:56

👋🏻  thanks for peewee, it's awesome.

I'm having troubles with `playhouse.postgres_ext.JSONField`. The minimum reproducible code is:
```python
from playhouse.postgres_ext import *
database = PostgresqlExtDatabase(...)

class DataProduct(Model):
    class Meta:
        database = database
    
    id = AutoField()
    
    release = TextField()
    filetype = TextField()
    kwargs = JSONField()

DataProduct.create_table()

dp, created = DataProduct.get_or_create(
    release="new",
    filetype="some",
    kwargs={"version": 3.14}
)
```

This raises the following exception:
```
.../peewee.py in execute_sql(self, sql, params, commit)
   3142             cursor = self.cursor(commit)
   3143             try:
-> 3144                 cursor.execute(sql, params or ())
   3145             except Exception:
   3146                 if self.autorollback and not self.in_transaction():

ProgrammingError: operator does not exist: json = json
LINE 1: ...ND ("t1"."filetype" = 'some')) AND ("t1"."kwargs" = CAST('{"...
                                                             ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
```

I have the following relevant versions:
- `peewee`: 3.14.4
- `psycopg2`: 2.9.1
- PostgreSQL: 12.2

What am I doing wrong here?

Edit: the equivalent code works fine when I am using `playhouse.sqlite_ext.SqliteExtDatabase`.



Sorry, I just found #2504. Closing.
Issue: Consider exposing the underlying query in `prefetch()`
Author: ilyakamens
State: closed
Created: 2022-08-23 02:45:53

Mr. Leifer,

Thanks for creating and maintaining such a great library. We've used it at Propel since August 18, 2015 starting with version 2.6.2.

I'd like to use `prefetch()` to avoid N+1 queries, but am having trouble doing so because `prefetch()` returns a list instead of a query. We use peewee queries (e.g., `User.select().where(...)`) as part of a generalized data migration system. Here's what some of the code in that system looks like:

```python
if checkpoint:
    checkpoint_id = self.checkpoint_to_id(checkpoint)
    query = query.where(query.model.id > checkpoint_id)

rows = list(query.order_by(query.model.id).limit(batch_size))
modified = pool.imap(lambda row: self._migrate_row(row, dry_run), rows)
```

As you can see, a list of peewee models is difficult to integrate into this API. Would you consider some kind of non-breaking API change to enable access to the underlying `prefetch()` query? Something like `prefetch_query()`, or perhaps a `kwarg` like `query=True` (or whatever you're amenable to)? I'd also be happy to submit a PR.

Thanks again.

Prefetch does not expose a query because the results are not composed of a single query, but rather `k` queries for however many tables are being prefetched. Dependent rows are iterated and an in-memory map is built for efficiently assigning them to their parent model instance/row. They're not able to be composed like your usual peewee queries.

Also note, I'd advise profiling to make sure `prefetch()` is actually faster in your case. It will likely come down to the ratio of the sizes of your outermost -> innermost loops whether it even provides a speedup or not.

What's the value of `pq.query` before it's wrapped in a list [here](https://github.com/coleifer/peewee/blob/b36aea92d5c111278a9d0649afab89f9f1ffe0ac/peewee.py#L7934)?

These lines are more relevant https://github.com/coleifer/peewee/blob/b36aea92d5c111278a9d0649afab89f9f1ffe0ac/peewee.py#L7927-L7932 but that is the outermost query, which has already been consumed and all the cached row instances in its CursorWrapper have their related child-instances populated, and so-on down the chain.
Issue: Declarative table partitioning (Postgres)
Author: foobarto
State: closed
Created: 2022-08-22 09:35:41

I'm trying to figure out how to create a table with [declarative partitioning in Postgres](https://www.postgresql.org/docs/current/ddl-partitioning.html) so the syntax looks like this:

```sql
CREATE TABLE measurement (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
) PARTITION BY RANGE (logdate);
```

And also individual partitions, like so:

```sql
CREATE TABLE measurement_y2006m02 PARTITION OF measurement
    FOR VALUES FROM ('2006-02-01') TO ('2006-03-01');

CREATE TABLE measurement_y2006m03 PARTITION OF measurement
    FOR VALUES FROM ('2006-03-01') TO ('2006-04-01');
```

I guess either could be done with raw SQL but I'm wondering if at very least there is any way to use the `Meta` on the model to append the `PARTITION BY RANGE` when using `db.create_tables()`.

Here's an example. You can use `Meta.table_settings`:

https://github.com/coleifer/peewee/blob/master/tests/schema.py#L433-L448

```python
    def test_table_settings(self):
        class KVSettings(TestModel):
            key = TextField(primary_key=True)
            value = TextField()
            timestamp = TimestampField()
            class Meta:
                database = self.database
                table_settings = ('PARTITION BY RANGE (timestamp)',
                                  'WITHOUT ROWID')
        self.assertCreateTable(KVSettings, [
            ('CREATE TABLE "kv_settings" ('
             '"key" TEXT NOT NULL PRIMARY KEY, '
             '"value" TEXT NOT NULL, '
             '"timestamp" INTEGER NOT NULL) '
             'PARTITION BY RANGE (timestamp) '
             'WITHOUT ROWID')])
```

Doc of model metadata options (scroll to table): http://docs.peewee-orm.com/en/latest/peewee/models.html#model-options-and-table-metadata
Issue: iterating or extending `Function` produces an infinite loop
Author: joshkaplan
State: closed
Created: 2022-08-18 19:12:16

### Problem
Both of these examples produce an infinite loop:

```python3
import peewee

pwf = peewee.fn.count()

# example 1:
for i in pwf:
    print(f'hello {str(i)}')

# example 2:
x = []
x.extend(pwf)
```

The offending code is `ColumnBase.__getitem__`: https://github.com/coleifer/peewee/blob/master/peewee.py#L1236-L1242

I'm not sure if this only applies to `Function`, or if it affects all non-slice `ColumnBase` classes.

For context, we did run into this in a real-world use-case.

### Expected Behavior
I'm not 100% sure, but at the very least this probably should `raise` rather than infinitely loop, which is worse because it is silent and effectively a memory leak.

### Proposed Solutions
_(Disclaimer: I'm not deeply familiar with `peewee`'s internals, so this is more directional than a fully thought-out solution.)_

From the [Python docs](https://docs.python.org/3/reference/datamodel.html#object.__getitem__):
> Note [for](https://docs.python.org/3/reference/compound_stmts.html#for) loops expect that an [IndexError](https://docs.python.org/3/library/exceptions.html#IndexError) will be raised for illegal indexes to allow proper detection of the end of the sequence.

So if raising in all non-slice cases doesn't make sense, perhaps just raising an `IndexError` at the appropriate time (e.g. after 1 loop)?

We don't want to raise an IndexError since it is not inherently an iterable type, so I'm going with NotImplementedError for now.

3.15.2 released now, which contains this fix.
Issue: allow non-atomic get_or_create?
Author: mayli
State: closed
Created: 2022-08-17 01:14:30

The current [get_or_create](https://github.com/coleifer/peewee/blob/eeff376be5af089e7d3849284a1c1d71b5df215f/peewee.py#L6626-L6627) requires the db supports `atomic()`, which is not necessary in most cases.
Is it possible add a parameter to allow non-atomic `get_or_create`?

You are welcome to write your own implementation and place it on a `Base` model class you use.
Issue: Added special expressions to boolean field
Author: alteralt
State: closed
Created: 2022-08-07 13:52:35

Added an expression so that in the request instead of:
```python
Model.select().where(Model.boolean_field == True)
````
To write:
```python
Model.select().where(Model.boolean_field.is_true())
````

The same expression already exists:
https://github.com/alteralt/peewee/blob/b8c629bc100c4886c5b87b6eb62cce66c84a0d9c/peewee.py#L1197

Going to pass for now, thanks.
Issue: How to create enum in peewee ?
Author: raoehtesham79
State: closed
Created: 2022-07-30 06:11:42

I am trying to create enum data type in peewee model but there is not such thing in peewee . Kindly, please help me to sort out this. Here is an example , what I want:
class travel_mode(enum.Enum):
    Air = 1
    Sea = 2
    NA = 3
but how to use it in peewee model

#630 

https://gist.github.com/b1naryth1ef/607e92dc8c1748a06b5d

Thanks for your response . I have seen this method . This is also not working.
Issue: How to access _database from query?
Author: mkmoisen
State: closed
Created: 2022-07-26 15:52:00

I have a library function that needs to check the information about the `db` before taking action on a query:

```
def execute_update(query):
    if _is_returning_supported(query._database):
        return _execute_returning_dml(query)

    return _execute_nonreturning_update(query)
```

So far the only way I know how to do it is by requiring the `db` to be passed as a parameter to my function, or by accessing the private `query._database`.

Is there another way? If not, can we add a public member?

Presumably you would know something about the database that's executing the queries, right? Or is this library restricted to operating on queries in absence of any other context? The `_database` attr is fine to use, I don't anticipate it changing any time soon.

@coleifer 

I'm writing a library that is to be used by other applications. The library doesn't know what database the user will use.

I need different behavior for postgres vs mysql. This function will return the updated result set if it is in postgres, or it will query the updated table and return the result if it is mysql.

I can use the `_database` attribute, but I suppose I would rather not rely on a private attribute if possible.

A Database Proxy cannot be used for this case, could it?

Check out this example from the `playhouse` on how I've differentiated functionality by db: https://github.com/coleifer/peewee/blob/master/playhouse/kv.py#L16-L56

I would recommend using `_database` and then looking up the corresponding property, e.g. `if query._database.returning_clause:`. Common properties are listed in the `class Database(...):` in peewee.py.
Issue: Composite Foreign Key
Author: mkmoisen
State: closed
Created: 2022-07-22 06:15:12

The docs [state](https://www.bookstack.cn/read/peewee-orm-3.13.1-en/spilt.5.c7972b36e7689f24.md):

>  Peewee does not support the concept of a “composite foreign-key”

I assume the reason is because it makes the ORM joins complicated. 

However, I simply need to create a composite foreign-key in the database in order to maintain referential integrity for a particular use case. I will never need to join on these two columns through the ORM.

How can I instruct peewee to create a composite foreign-key at the database level, while defining my Model?

You can add a constraint to the table in the following manner:

```python
class Post(Model):
    author = TextField()
    title = TextField()
    class Meta:
        database = db
        primary_key = CompositeKey('author', 'title')

class Comment(Model):
    post_author = TextField()
    post_title = TextField()
    comment = TextField()
    class Meta:
        constraints = [
            SQL('foreign key (post_author, post_title) references post(author, title)'),
        ]
        database = db
        depends_on = [Post]  # Since no explicit FK field, this ensures tables created in proper order.

db.create_tables([Post, Comment])

p1 = Post.create(author='a1', title='t1')
p2 = Post.create(author='a2', title='t2')
Comment.create(post_author=p1.author, post_title=p1.title, comment='p1-c')
Comment.create(post_author=p2.author, post_title=p2.title, comment='p2-c')
with db.atomic():
    try:
        Comment.create(post_author='a1', post_title='tx', comment='p1-x')
    except IntegrityError:
        print('received expected integrity error.')
with db.atomic():
    try:
        Comment.create(post_author='ax', post_title='t1', comment='p1-x')
    except IntegrityError:
        print('received expected integrity error.')

db.drop_tables([Post, Comment])
```

See the table of supported Model metadata options:

http://docs.peewee-orm.com/en/latest/peewee/models.html#model-options-and-table-metadata
Issue: how use MATCH AGAINST 
Author: danil-cpu
State: closed
Created: 2022-07-18 16:25:20

```MySQL
SELECT id, title, description,
MATCH (title, description) AGAINST ('word' WITH QUERY EXPANSION) AS score
FROM `script`
ORDER BY score DESC```

how use query in MySQL peewee?

You can use the helper in `playhouse.mysql_ext`:

```python
from playhouse.mysql_ext import Match

match = Match((Script.title, Script.description), 'some word', 'with query expansion')
Script.select(Script, match.alias('score'))
```

Alternatively, if you want to just build the expression manually, you can write a helper function to make this less tedious:

```python
from peewee import *
from peewee import NodeList  # Not exported by default

class Note(Model):
    title = TextField()
    body = TextField()

def match(word):
    match = fn.MATCH(Note.title, Note.body)
    against = fn.AGAINST(NodeList(('word', SQL('with query expansion'))))
    return NodeList((match, against))

# in select
Note.select(Note, match('some word').alias('score'))

# in where
Note.select().where(match('some other words'))
```

http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#mysql-extensions
Issue: Long running processes using peewee eventually fail
Author: petersn
State: closed
Created: 2022-07-18 05:23:25

Hello folks, apologies if this issue is a duplicate. I've now seen several services I've written using peewee eventually fail after some time, varying from a few days to a couple months, with an error like:
```
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/peewee.py", line 3175, in execute_sql
    cursor = self.cursor(commit)
  File "/usr/local/lib/python3.8/site-packages/playhouse/postgres_ext.py", line 484, in cursor
    return self._state.conn.cursor()
psycopg2.InterfaceError: connection already closed
```
It appears that peewee never recovers -- after this error occurs all following attempts to use the database via peewee fail as above. But when I restart the long running process it immediately works fine, and postgres itself is working fine.

The connection itself is created as:
```
db = playhouse.postgres_ext.PostgresqlExtDatabase("postgres url", autorollback=True)

class BaseModel(peewee.Model):
    class Meta:
        database = db
```
... and then thereafter I use peewee in ways that should presumably be bog-standard.

peewee version: 3.14.10
python version: 3.8.12

Apologies that this isn't the most recent version of peewee. I'd try to reproduce this issue with the most recent version, but by its very nature (failing after months) it's hard to reproduce.

I realize that the above report is extremely vague and hard to work from, and quite plausibly this isn't peewee's fault. I can try to provide whatever information would be helpful to diagnose, if needed.

Thanks.

You need to understand how Peewee manages connections -- peewee creates a connection-per-thread and will not automatically reconnect if that connection should get closed by the server. Why? What if you were in the middle of a transaction or had uncommitted changes -- we don't want to silently lose those.

So what you need to do is ensure you are opening and closing your connections properly. Here's an example:

```python

db = PostgresqlExtDatabase(...)

def long_running_task():
    while True:
        db.connect()
        # do some work with the database
        db.close()

        # wait for more work to do.
```

My suggestions are: 

* open the connection for the short periods of time when you actually need it, close when done
* use a connection pool with the above if the overhead of opening/closing connections is an issue
Issue: docs: Fix a few typos
Author: timgates42
State: closed
Created: 2022-07-17 11:59:50

There are small typos in:
- docs/peewee/query_examples.rst
- peewee.py
- tests/model_save.py
- tests/postgres_helpers.py

Fixes:
- Should read `hierarchy` rather than `heirarchy`.
- Should read `existent` rather than `existant`.
- Should read `dictionary` rather than `dictioary`.
- Should read `denormalization` rather than `denormalisation`.



Semi-automated pull request generated by
https://github.com/timgates42/meticulous/blob/master/docs/NOTE.md
Issue: Default values for columns missing in table creation statement with sqlite
Author: SandaruKasa
State: closed
Created: 2022-07-12 23:12:26

Python: 3.10
peewee: 3.8.2, 3.15.1

```python
import peewee
import logging

logging.basicConfig(level=logging.DEBUG)


class Test(peewee.Model):
    test = peewee.IntegerField(default=1)

    class Meta:
        database = peewee.SqliteDatabase("test.sqlite3")


Test.create_table()
```

gives the following output:
```
DEBUG:peewee:('CREATE TABLE IF NOT EXISTS "test" ("id" INTEGER NOT NULL PRIMARY KEY, "test" INTEGER NOT NULL)', [])
```

But I'm pretty sure it should be <<  "test" INTEGER NOT NULL **DEFAULT 1**  >>

P.S.  Thank you for this amazing library


It seems you have not read this part of the documentation: http://docs.peewee-orm.com/en/latest/peewee/models.html#default-field-values

> Remember: when using the default parameter, the values are set by Peewee rather than being a part of the actual table and column definition.

Huh, a peculiar design choice.

Is there a way to propagate the default value to the table then?

>Link him the docs.
>
>Still doesn't read it.

@SandaruKasa ... come on.

>The database can also provide the default value for a field. While peewee does not explicitly provide an API for setting a server-side default value, you can use the constraints parameter to specify the server default:
>
>```
>class Message(Model):
>    context = TextField()
>    timestamp = DateTimeField(constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')])
>```
Issue: Snowflake Support
Author: sfc-gh-pmonteiro
State: closed
Created: 2022-07-07 17:46:48

I really like this ORM but it lacks Snowflake support. I'm a python seasoned developer and willing to help Peewee support Snowflake. What is the best way to support this project? Is there any documentation on adding support to new DBs?

http://docs.peewee-orm.com/en/latest/peewee/database.html#adding-a-new-database-driver

Looks like there's a driver with dbapi support so hopefully it will be rather easy.

https://docs.snowflake.com/en/user-guide/python-connector.html
Issue: Select-query side effect
Author: pylipp
State: closed
Created: 2022-07-06 10:19:50

In my application I have a function that accepts an optional `ModelSelect` argument. If it is not set, `model.select()` shall be used.

The line `selection or model.select()` causes an SQL query to be executed. Why is this (side) effect? I looked at the [docs](http://docs.peewee-orm.com/en/latest/peewee/querying.html#selecting-multiple-records) but could not find any hint.

```python
from peewee import SqliteDatabase, CharField, Model
import logging

logger = logging.getLogger("peewee")
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)

db = SqliteDatabase(":memory:")


class User(Model):
    name = CharField()

    class Meta:
        database = db


def some_logic(model, selection=None):
    return selection or model.select()


def main():
    db.create_tables([User])
    User.insert_many([{"name": n} for n in "abcdef"]).execute()

    logger.warning(0)
    selection = User.select()
    logger.warning(1)
    selection = some_logic(User, selection)
    logger.warning(2)


main()
```

The log output is
```
('CREATE TABLE IF NOT EXISTS "user" ("id" INTEGER NOT NULL PRIMARY KEY, "name" VARCHAR(255) NOT NULL)', [])
('INSERT INTO "user" ("name") VALUES (?), (?), (?), (?), (?), (?)', ['a', 'b', 'c', 'd', 'e', 'f'])
0
1
('SELECT "t1"."id", "t1"."name" FROM "user" AS "t1"', [])
2
```

When you write:

`return selection or model.select()`

Python needs to determine if `selection` is truth-y, so it calls its `__len__()` method to see if it's empty. This causes the query to be evaluated. The better way to do this is:

`return selection if selection is not None else model.select()`
Issue: Update database.rst
Author: inktrap
State: closed
Created: 2022-07-03 10:18:24

If the apsw **reference is rendered,** the text reads (emphasis mine):

> Peewee also comes with an alternate SQLite database that uses **apsw, an advanced sqlite driver**, an advanced Python SQLite driver.

I know that the idea is to describe the reference without relying on the self-description of the reference, but in this case they are (almost)identical. Another way would be to shorten the ref to just apsw, if restructured text permits that.

Thanks, I merged a roughly equivalent patch.
Issue: CockroachDB is not automatically setting server_version in all cases
Author: arel
State: closed
Created: 2022-06-27 14:19:03

I am occasionally seeing the error message below that is not especially self-explanatory.

```
    158 def atomic(self, system_time=None, priority=None):
--> 159     if self.server_version < NESTED_TX_MIN_VERSION:
    160         return _crdb_atomic(self, system_time, priority)
    161     return super(CockroachDatabase, self).atomic(system_time, priority)

TypeError: '<' not supported between instances of 'NoneType' and 'int'
```

I am using CockroachDB. The `server_version` is set when a connection is opened, which `atomic()` normally does. But in line 159 of `cockroachdb.py`, it's assumed `server_version` has already been set before calling `super(...).atomic()`.

https://github.com/coleifer/peewee/blob/49792e4eac5268c752375d34bbb63828708b592b/playhouse/cockroachdb.py#L159

To reproduce the error:

```python
from playhouse.db_url import connect

db = connect("cockroachdb://username:password@gcp-us-east1.cockroachlabs.cloud:26257/defaultdb...")

with db.atomic():  # raises error
    pass
```

I do not see this error if I use the `PostgresqlDatabase` class (e.g., when I change the schema of the datatbase url only).

```python
from playhouse.db_url import connect

db = connect("postgresql://username:password@gcp-us-east1.cockroachlabs.cloud:26257/defaultdb...")  # same other than schema

with db.atomic():  # no error
    pass
```

I am using peewee version `3.14.10`.

This is now resolve, thanks for the report. In the meantime you can alternatively use the database itself as a context-manager, which ensures a connection is created and wraps the block in a transaction (assuming this is for module-scope type initialization or whatever), e.g.

```python
with db:
    ...
```