akumoth/peewee-issues huggingface dataset:
Issue: How do I union queries where the model contains a JSON field?
Author: dsmurrell
State: closed
Created: 2021-06-29 16:35:24

If I have two or more queries to join (say q1, q2 and q3) and I'm using set operations on them like: 

`query = q1 & q2` or `query = q2 | q3` or `query = q1 - q3`

I know that there is a limitation that the selected columns for these should be the same, but I'm finding that I get the errors:

`peewee.ProgrammingError: could not identify an equality operator for type json`
`psycopg2.errors.UndefinedFunction: could not identify an equality operator for type json`

whenever all fields are selected and the model includes a `JSONField` from `playhouse.postgres_ext`

Is this any way to resolve this? My use case is that I'm passing a list of queries through to another function where I want to union them and so I don't have the luxury of adding `.where(xyz)` statements onto an exiting query to limit the selection using multiple where filters. Also, the set operations seem more powerful in general as passing a list of where params is quite limiting.

Doing a bit of searching, it looks like the UNION operation is trying to take out duplicates and needs to compare the JSON content... is there any way in the union from peewee that I can specify that I'd like only apply distinct on say `id`?

Yes, you need to use UNION ALL if you don't want duplicates removed (which is why you're getting that error). You can use `.union_all()` or the addition operator overload.

Thanks, I came across that option in my searches.

So you're saying that if I am concerned about duplicates, I can `.union_all()`, and then use `.distinct(model.id)`?

I was typing this out below before you responded but then I got consumed by something else:

"Trying out `BinaryJSONField` instead of `JSONField` now and this seems to work (using union and BinaryJSONFields), but I'm not sure what the implications are of changing our whole schema to use `BinaryJSONField` instead of `JSONField` everywhere. Are these supposed to be interchangeable or will changing the schema change the data in some way?"

I'd imagine the we don't want to be doing JSON equality operations anyway to filter distinct records so I'll try out using distinct after union_all. Thanks again for the help!

This is a part of the SQL specification, that UNION must perform some type of equality to remove duplicates, whereas UNION ALL is not constrained in that way.
Issue: Some obstacles when trying to add a driver for ClickHouse 
Author: pm5
State: closed
Created: 2021-06-28 03:38:57

I saw #912 and tried to add a driver for [ClickHouse](https://clickhouse.tech/) using [clickhouse-driver](https://clickhouse-driver.readthedocs.io/en/latest/features.html#python-db-api-2-0) but there seems to be some obstacles:

- The [documentation](http://docs.peewee-orm.com/en/latest/peewee/database.html#adding-a-new-database-driver) seems outdated.  `Database._connect` is no longer taking arguments and one has to use `foodb.connect(self.database, **self.connect_params)` instead.
- The DB API 2.0 implemented by clickhouse-driver specifically uses Python extended format codes for SQL parameters, i.e. `%(name)s`, which is [allowed](https://www.python.org/dev/peps/pep-0249/#paramstyle).  I've found `Database.param` in peewee, but it seems a bit hard to get it work with this format.

Any suggestions?

I've fixed the first item, thank you for bringing that to my attention.

The second item will not be so easy, since it implies that you are passing the parameters as a `dict`. Every other driver I know of just uses a tuple to represent the query parameters, so there is probably some work involved in getting this working. It looks like this driver is implemented very idiosyncratically compared to the others, e.g.

```
>>> cursor.executemany('INSERT INTO test (x) VALUES', [[200]])
```

What the heck is going on here? Is the driver inserting placeholders?

I'd suggest taking it up with the clickhouse implementer(s) to see about getting this resolved upstream, as it will likely be rather tricky with Peewee.
Issue: time generation on linux vs windows
Author: naamlev
State: closed
Created: 2021-06-27 14:19:49

Hey ,
I am using peewee 3.13.2
Part of the project include using join over multiple tables and retrieving foreign keys using prefetch.
I've recently added to the join new table that has many to many relationship and prefetch to retrieve it key.
**example:**

_old version:_
 X.select(X)
        .join_from(X, Y, peewee.JOIN.LEFT_OUTER)
        .join_from(X, Z, peewee.JOIN.LEFT_OUTER)
        .where(query)
        .prefetch(Y, Z)

_new version:_
 X.select(X)
        .join_from(X, Y, peewee.JOIN.LEFT_OUTER)
        .join_from(X, Z, peewee.JOIN.LEFT_OUTER)
        .join_from(Z, T, peewee.JOIN.LEFT_OUTER)
        .switch(X)
        .where(query)
        .prefetch(Y, Z,T)
**questions**
1. is there a better why (lower time complexity) to model this behavior without limiting the query that the user can give?
2. why do i get better time complexity in windows?
    * The same branch (and same test) get on windows: 340 sec and in linux:870 sec
    * Just for comparison before the change i've got windows:200 sec linux: 250 sec 

I think you are misusing `prefetch()`. In your first example, you shouldn't need prefetch at all if each X has only one (or zero) Y / Z. Just write:

```python
(X
 .select(X, Y, Z)
 .join_from(X, Y, JOIN.LEFT_OUTER)
 .join_from(X, Z, JOIN.LEFT_OUTER))
```

For your new version, since a Z can have many T's (if I'm reading this correctly), then yes you would probably be best off to use `prefetch()`. Again, though, you may be able to reuse the above snippet for the X, Y, and Z portions and just use prefetch for the T's.

I cannot really advise on how to optimize this, it depends so much on how many rows of X, Y, Z and T you are getting, what database you are using, etc, etc. It might be possible to use an aggregation to roll-up the related T's and skip using prefetch altogether.

Are you able to express the entire query in a single SQL SELECT (using plain old sql)? If yes, then we can probably optimize this. If no, then it will be hard to say.

I'll reopen this if there is a bug, but feel free to respond and I will do my best to help you.
Issue: Prefetching from multiple models
Author: jonathanmach
State: closed
Created: 2021-06-26 15:54:47

Hi all! Hoping everyone is safe!

I've read the documentation about N+1 queries and prefetch, but I'm facing a strange behavior.
Let's say I have the following models:

```
Users

Orders
     user (FK)
     approved_by_user (FK)

Kits
     order (FK)
     claimed_by_user (FK)
     cancelled_by_user (FK)
```
```python
    # I can query for Kits and prefetch Users:
    for kit in prefetch(Kits.select(), Users):
        print(kit.claimed_by_user)
        print(kit.cancelled_by_user)
    # 👍 Works fine, no N+1 queries 🎉

---
    # or query for Kits and prefetch Orders:
    for kit in prefetch(Kits.select(), Orders):
        print(kit.order)
    # 👍 Works fine, no N+1 queries 🎉

---
    # But I can't prefetch both Users and Orders:
    for kit in prefetch(Kits.select(), Orders, Users):
        print(kit.claimed_by_user)
        print(kit.cancelled_by_user)
        print(kit.order)  # It generates N+1 queries here❗️ 😨😨
```
I imagine I'm missing some small detail here.

My main goal is to respond to a REST request with a JSON in the following format:
```
[
      Kits
            claimed_by_user : {...}
            cancelled_by_user : {...}
            order : {user: {...}, approved_by_user: {...}} 
]
```

Thanks in advance!

You don't need prefetch. Prefetch is for when the direction of traversal is reversed, e.g. you have a list of users and you want to get all their kits as well.

Just do joins (you'll need aliases because you reference User multiple times):

```python

OrderUser = User.alias()
OrderApprover = User.alias()
KitClaimer = User.alias()
KitCanceller = User.alias()
q = (Kit.select(Kit, Order, OrderUser, OrderApprover, KitClaimer, KitCanceller)
     .join_from(Kit, Order)
     .join_from(Order, OrderUser, JOIN.INNER, on=Order.user)
     .join_from(Order, OrderApprover, JOIN.INNER, on=Order.approved_by_user)
     .join_from(Kit, KitClaimer, JOIN.LEFT_OUTER, on=Kit.claimed_by_user)
     .join_from(Kit, KitCanceller, JOIN.LEFT_OUTER, on=Kit.cancelled_by_user))
```
Issue: Question: Is there a way to use one field to set another field when the create method is used?
Author: dsmurrell
State: closed
Created: 2021-06-23 20:40:16

There are multiple uses of the `Users.create(email=X)` method in our codebase. Is there any way to modify the Users class or override the create method to make a field `email_lower` get set to another field that is always passed in (`email`)?

I've tried:

```
def create(self, **kwargs):
        super().create(email_lower=kwargs["email"].lower(), **kwargs)
```

but I think that this doesn't work because the object isn't instantiated before the `create` method on the subclass is called.

I also tried this:

```    
def __init__(self, **kwargs):
        super(Users, self).__init__(email_lower=kwargs["email"].lower(), **kwargs)
```

but it didn't work either.

I think I got there:

```
  @classmethod
  def create(cls, **query):
      query['email_lower'] = query["email"].lower()
      return super(Users, cls).create(**query)
```

@coleifer, is there any reason to use signals over this way of doing it?

The proper way to do this would probably be to just create an index on the lower-case version of the email, since I assume you only are using it for uniqueness or querying.

```sql
create unique index "user_email_lower" on users(lower(email))
```

If you really want to have it in its own field, then I'd override the `save()` method.

```python
def save(self, *args, **kwargs):
    self.email_lower = self.email.lower()
    return super(...).save(*args, **kwargs)
```
Issue: [Feature request] Add more items to the log output
Author: iHalt10
State: closed
Created: 2021-06-13 21:55:20

Hello, this is a small feature request, but I'd like to add more items to the log output.

```py
from peewee import *
import logging
logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)

a_db = SqliteDatabase('a.db')
b_db = SqliteDatabase('b.db')
class A(Model):
    name = TextField()
    class Meta:
        database = a_db

class B(Model):
    name = TextField()
    class Meta:
        database = b_db

a_db.create_tables([A])
b_db.create_tables([B])

# Output:
# ('CREATE TABLE IF NOT EXISTS "a" ("id" INTEGER NOT NULL PRIMARY KEY, "name" TEXT NOT NULL)', [])
# ('CREATE TABLE IF NOT EXISTS "b" ("id" INTEGER NOT NULL PRIMARY KEY, "name" TEXT NOT NULL)', [])
```
In the above, it is difficult to understand "which database was executed", can change it to the following code?

```py
...
    def execute_sql(self, sql, params=None, commit=SENTINEL):
        logger.debug((sql, params, self.database))
# Output:
# ('...', [], 'a.db')
...
```
> https://github.com/coleifer/peewee/blob/master/peewee.py#L3146

Or, I would like to have a function that can extend the log output more items, in addition to the "(sql, params)" items.
```py
...
class Database(_callable_context_manager):
    ...
    def get_log_items(self, sql, params, db):
        return (sql, params)
    def execute_sql(self, sql, params=None, commit=SENTINEL):
        logger.debug(self.get_log_items(sql, params, self))
...
```
myself code
```py
...
def my_get_log_items(sql, params, db):
    return (sql, params, db.database)

db = SqliteDatabase('a.db')
db.get_log_items = my_get_log_items
db.execute_sql(...)
# Output:
# ('...', [], 'a.db')
```
Umm...

If you have time, I'd appreciate your opinion.


I'm going to pass on this for now.
Issue: create_table / drop_table implementation for MySQLMigrator and other engines
Author: krokwen
State: closed
Created: 2021-06-12 22:09:25

It's a required functionality for cases when you have global schema changes,
Use case:
day 1: Model A. Dataset A
day 2: Model B. Dataset B
day 3: Model B updated. Dataset A patrially moved to model B
day 4: Model A is no longer needed and removed from code.

And i have to apply these changes on late environment that on  'day 1'

So, in this case it's impossible to use model's create and drop methods, because at the moment of migration run the old model will be already removed from code, but i have to apply all the changes in migrations.

Peewee has a schema migrations module: http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#schema-migrations

I don't quite understand the issue, so I don't know what to tell you, but this doesn't seem to me a peewee-specific issue.

I'm talking about same module.
It's not able to create/drop table by itself.
For now you can create or drop table only with existing model class. But how to do it when model is no longer exists?

Or i have to define model class in migration script every time? It's a huge crutch...


I don't really understand the problem. If you have the model class, just import it into the migration and call `create_table()` - without the model definition Peewee has no idea what columns to add or anything. For dropping a table, I also don't see why this shouldn't be possible by using a model class imported from your app -- run the migration(s), then remove the model code.

You can also stub out a model class if all you need to do is drop it, or just run `database.execute_sql('DROP TABLE...')`.
Issue: Cannot reset ForeignKeyField to None
Author: orsinium
State: closed
Created: 2021-06-11 11:24:44

```python
import peewee

class A(peewee.Model):
    pass

class B(peewee.Model):
    a = peewee.ForeignKeyField(A, null=True)

b = B(a=A())

# works as expected:
b.a
# <A: None>

# unexpected:
b.a = None
b.a
# <A: None>
```

Expected behavior: when `None` is assigned to the ForeignKeyField, the field value is None.
Actual behavior: assigning `None` to the ForeignKeyField has no effect.

How to reset a foreign key to None?

This was happening because peewee only cleared the related object reference (the A instance on B) if the actual value of the FK changed. Since it was `NULL` to start with (e.g. A.id is None), peewee did not register that there was anything to do. In practice I don't believe this would lead to any inconsistency in the database, but I can see how it might be confusing.

Fixed.

Thank you for the answer! I think I understand now: setting b.a to None was interpreted as setting b.a.id to None, which is already true. In my application, I distinguish between "the A record is not created yet" and "there is no A record related to this B record". So you're right, it was confusing. I found a workaround, though, by implementing `A.__bool__`.
Issue: peewee create and use id field for deferred foreign key with primary_key=true 
Author: penja
State: closed
Created: 2021-06-07 14:12:07

Hello. We have the deferred foreign key that used like primary key in new table. In this case peewee ignore `primary_key=True` param in column declaration and try to use `id` like identifier in all sql queries.
Please see invalid behavior in following code snippet.
```
import peewee
from peewee import *
import logging
import sys

log = logging.getLogger()
log.setLevel(logging.DEBUG)
handler = logging.StreamHandler(sys.stdout)
log.addHandler(handler)

print(peewee.__version__)

db = SqliteDatabase(":memory:")


class Base(Model):
    class Meta:
        database = db


class B(Base):
    a = DeferredForeignKey("A", primary_key=True)
    x = CharField(null=True)


class A(Base):
    id = AutoField()

db.create_tables([B, A])
```
Output 
```
root@87a4315fc6f4:/vagrant# python dummy_sqlite.py 
3.14.4
('CREATE TABLE IF NOT EXISTS "a" ("id" INTEGER NOT NULL PRIMARY KEY)', [])
('CREATE TABLE IF NOT EXISTS "b" ("id" INTEGER NOT NULL PRIMARY KEY, "a_id" INTEGER NOT NULL PRIMARY KEY, "x" VARCHAR(255))', [])
Traceback (most recent call last):
  File "/usr/local/lib/python3.8/site-packages/peewee.py", line 3144, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: table "b" has more than one primary key

During handling of the above exception, another exception occurred:
```
Thanks in advance for your response and probably for the fix.

P.S There is workaround by in my opinion this is not obvious!
```
class B(Base):
    class Meta:
        primary_key = False

    a = DeferredForeignKey("A", primary_key=True)
    x = CharField(null=True)
#or
class B(Base):
    class Meta:
        primary_key = peewee.CompositeKey("a")
    a = DeferredForeignKey("A")
    x = CharField(null=True)
```
Issue: Syntax error when selecting in VALUES
Author: Hippopotas
State: closed
Created: 2021-06-05 06:27:19

Peewee version: 3.4.14
SQLite version: 3.35

When running code of the form (as recommended in issues [2344](https://github.com/coleifer/peewee/issues/2344), [2411](https://github.com/coleifer/peewee/issues/2411), [2414](https://github.com/coleifer/peewee/issues/2414)):
```python
foo = [(1, 2), (3, 4)]
bar = ValuesList(foo, columns=('col1', 'col2'))
baz = EnclosedNodeList([bar])
t1.select().where(Tuple(t1.col1, t1.col2).in_(baz))
```

I am getting a different kind of error: `OperationalError: near ",": syntax error`

The query it is attempting to execute is apparently (in (query, param) form)
```
('SELECT "t1"."col1", "t1"."col2" FROM "t1" WHERE (("t1"."col1", "t1"."col2") IN (VALUES (?, ?), (?, ?)))',
 [1, 2, 3, 4])
```

The same occurs on
```python
t1.raw("""SELECT col1 FROM t1 WHERE ((col1, col2) IN (VALUES (1, 2))""").execute()
```

After some tinkering, it appears that the problematic syntax is the comma between `col1, col2`
I am unsure why this is happening. I played around with the parentheses a bit, but could not figure out a fix.

This does not occur on non-row-values queries, e.g.
```
t1.select().where(t1.col1 << [1, 2, 3])

('SELECT "t1"."col1", "t1"."col2" FROM "t1" WHERE ("t1"."col1" IN (?, ?, ?))',
 [1, 2, 3])
```

This works fine for me:

```python
db = SqliteDatabase(':memory:')

class KV(Model):
    key = TextField()
    value = IntegerField()
    class Meta:
        database = db

db.create_tables([KV])

KV.insert_many([('k1', 1), ('k2', 2), ('k3', 3), ('k4', 4), ('k5', 5)]).execute()

vl = ValuesList([('k1', 1), ('k3', 3), ('k5', 5)])
vl_e = EnclosedNodeList([vl])

query = (KV.select()
         .where(Tuple(KV.key, KV.value).in_(vl_e)))
for row in query:
    print(row.key, row.value)
```

Prints:

```
k1 1
k3 3
k5 5
```

Hm. I ran your example code, but got the same error. Could I ask what the sql being generated is?

Edit: never mind, ignore me. I reinstalled everything, and now it miraculously works. Sorry for the bother.
Issue: Multi-encoding support in playhouse DataSet 
Author: JarneVerhaeghe
State: closed
Created: 2021-06-04 09:16:26

It appears the DataSet module from playhouse does not support other encodings such as 'latin-1'. 

I locally solved it by adjusting the open_file, thaw, and freeze function to have an extra argument: 
Line 21-22
```
def open_file(f, mode,encoding_='utf8'):
        return open(f, mode, encoding=encoding_)
```

Line 152-156
```
def freeze(self, query, format='csv', filename=None, file_obj=None, encoding='utf8',
               **kwargs):
        self._check_arguments(filename, file_obj, format, self._export_formats)
        if filename:
            file_obj = open_file(filename, 'w',encoding)
```

Line 164-168
```
 def thaw(self, table, format='csv', filename=None, file_obj=None,
           strict=False, encoding='utf8', **kwargs):
      self._check_arguments(filename, file_obj, format, self._export_formats)
      if filename:
          file_obj = open_file(filename, 'r',encoding)
```

This issue is more of a suggestion for multi-encoding support. I did not want to open a pull request for this.
Issue: Create tables from ModelSelect outside the public schema
Author: albireox
State: closed
Created: 2021-05-31 18:43:49

I think there is a bug or limitation in the `ModelSelect.create_table()` method. The way it works right now, you can only create tables in the public schema. This is because `Entity` expects multiple arguments, one for each part of the path, but `create_table` can send only one. If you pass the whole path as a string (e.g., `sandbox.test_table`) it gets quoted as `"sandbox.test_table"` which still points to the public schema.

I think the solution is either to allow to pass a tuple to `create_table()` (which will break compatibility) or to change the function signature so that it's more greedy getting the arguments so that you can pass `create_table('sandbox', 'test_table')` and all those arguments make it to the `Entity` instance.

I've chosen to support this by allowing a tuple for the `table_name` parameter. So:

```python

query = SomeModel.select()...
query.create_table('my_new_table')

# OR
query.create_table(('schema', 'my_new_table'))
```
Issue: Preferred way to do `database.execute_sql` ?
Author: andersentobias
State: closed
Created: 2021-05-28 12:26:05

We're using a DB connection pool with peewee 2.8.0.
We needed to optimize a query by writing handwritten SQL to our PostgreSQL db.

What is the preferred way of doing such a query?
We're using peewee together with a Flask app, if that helps.

Right now, the code is:

```python
database = PooledPostgresqlExtDatabase(...)
# Lots of code...
def func():
    cursor = database.execute_sql('SELECT ...')  # More code obviously.
    result = cursor.fetchall()
    # ...
```

Note that we're not closing the db cursor manually.

Peewee has been on 3.0 for about 3 years now, so you might want to upgrade as there are many improvements.

That said, as long as you're opening the pool connection before the request starts, and closing it (returning it back to the pool) after the request, then your code should work just fine.
Issue: How to get BaseModel Fields?
Author: beucismis
State: closed
Created: 2021-05-27 09:35:26

I want to get the defined `Fields` as a list.

```
class User(BaseModel):
    id = BigIntegerField(primary_key=True)
    nick = CharField(unique=True)
    age = SmallIntegerField(unique=True)

    class Meta:
        database = db
```

Post questions on stackoverflow or IRC. This is for tracking issues.

```python

User._meta.fields  # dict of field name -> field instance
User._meta.sorted_fields  # list of field instances in order
User._meta.sorted_field_names  # Just the names
```

Thanks.