akumoth/peewee-issues huggingface dataset:
Issue: peewee.IntegrityError: NOT NULL constraint failed
Author: umangapathak999
State: closed
Created: 2022-05-17 04:58:38

I want to set some fields to be nullable in User Model

Here is my base model:
```
db = SqliteDatabase(DBConfig.DB_NAME)

class BaseModel(Model):
    class Meta:
        database = db
```

Here is my User model:
```
class User(BaseModel):
    id = BigAutoField()
    name = CharField()
    email = CharField(unique=True)
    mobile_number = CharField(max_length=20, unique=True)
    avatar = CharField(null=True, default=None)
    two_factor_secret = CharField(null=True, default=None, unique=True)
    created_at = DateTimeField(default=datetime.now)
```

Here is what I tried to create a user  and where the said problem is occurring:
```
user = User.create(name="Test", email="test@email.com", mobile_number="9876543210")
```

If I pass avatar and two_factor_secret, the model works without any problem.

The error I get while not passing avatar and tow_factor_secret values:
```
peewee.IntegrityError: NOT NULL constraint failed: user.avatar
 ```

If any further detail is required, I am happy to provide it.

SOLVED!

I solved the problem. Nothing was wrong with the code. Just needed to remigrate my database. For those experiencing similar problem, script below tends to work:
```
db.connect()

models = [
    User
]

db.drop_tables(models)

db.create_tables(models)
```
`db` is an instance of peewee database initialization.
Issue: select_extend(): allow Model parameters
Author: morrissimo
State: closed
Created: 2022-05-05 17:40:24

My use case: incremental construction a query, usually via a subclass that leverages a superclass helper method. I'd like to keep the select() definition as built by the super class, but allow the subclass to extend the select targets (and JOINs, etc) _without_ having to resort to something like this:

```python

class Base:

    @classmethod
    def build_query(cls):
        return cls.select(cls).join_from(cls, SomeOtherClass)

class Subclass(Base):

    @classmethod
    def build_query(cls):
        qs = super().build_query()
        qs = qs.select_extend(
            *cls._meta.fields.values(),
            *YetAnotherClass._meta.fields.values(),
        ).join_from(cls, YetAnotherClass)
        return qs
```

With the proposed change to `select_extend()`, the above code would look like:
```python

class Base:

    @classmethod
    def build_query(cls):
        return cls.select(cls).join_from(cls, SomeOtherClass)

class Subclass(Base):

    @classmethod
    def build_query(cls):
        qs = super().build_query()
        return qs.select_extend(cls, YetAnotherClass).join_from(cls, YetAnotherClass)
```

Granted, I have no idea what the LOE looks like - but it _smells_ like a potential easy win for readability. Alternatively: there's a more correct way to do this and I've simply veered off the path ;)



Thanks, this was an accidental omission, and is now resolved.
Issue: Fast way to check if record exists
Author: ghost
State: closed
Created: 2022-04-27 18:56:06

First of all I am sorry in advance for the naive question.

I have an application which needs to iterate over a large number of entries and test each and every one against the sqlite peewee database to see if the value exists in a specific column.

Unfortunately this means tons of queries and this turns out to be slow.

I came across the index option and tried to index the desired column with a migration but I haven't seen any speed differences.

Is any special syntax required to make use of an index? I haven't been able to find an example.

You can add an index via `sqlite3 mydb-file` and adding it there. The sqlite docs have plenty of info on creating indexes. To do it via peewee you can:

* Main doc http://docs.peewee-orm.com/en/latest/peewee/models.html#indexes-and-constraints
* add it to the field definition, e.g. `my_field = IntegerField(index=True)`
* add it to the model class' Meta.indexes 
* use [modelindex apis](http://docs.peewee-orm.com/en/latest/peewee/api.html#ModelIndex)
* use `db.execute_sql()` directly to create the index

You can call `ModelClass._schema.create_indexes(safe=True)` after adding the index.

But for what you're trying to do specifically, a bloom filter is probably a better option.

If I understand you correctly:

Retrieve all rows from first_table that exist in the second_table, based on a shared value in common_column:
```
select * from first_table
where exists (
    select 1
    from second_table
    where first_table.common_column = second_table.common_column
)
```

In peewee:

    FirstTable.select().where(
        fn.exists(second_table.select().where(
            FirstTable.common_column == SecondTable.common_column
        ))
    )

You can change this to `not exists` to retrieve all rows from first_table that are missing in second_table. In peewee that would be `~fn.exists`
Issue: Count Filter is not Working
Author: weaverjess
State: closed
Created: 2022-04-27 15:11:13

Referencing: https://stackoverflow.com/questions/60067772/from-rows-to-columns-using-peewee-orm

Event:Booking is a 1:many relationship.

I have a situation where I want to pull all Events that are still bookable just to generate a list.

```python
n_bookings = peewee.fn.COUNT(BookingModel.id)
query = EventModel.select(EventModel, n_bookings.filter(BookingModel.status != 'cancelled').alias('count')).join(
    BookingModel,
    peewee.JOIN.FULL_OUTER,
    on=(BookingModel.event_id == EventModel.id)
).group_by(
    EventModel
).having(
    having
).where(
    select_where
).order_by(
    EventModel.start_time.asc(),
    EventModel.id.asc()
).limit(canary_limit)
```

While appending on a filter doesn't break anything, it also seems to do nothing, as it is still counting bookings with a status of "cancelled".

It's not `fn.filter()` -- you want `fn.COUNT(EventModel.id).filter(EventModel.status == 'cancelled')`

This is not a peewee issue, but rather an issue in incorrectly translating SQL to peewee APIs.

That's exactly what I have... except it's BookingModel, not EventModel. It doesn't work. Apologies for the title being slightly off. Updated.

@coleifer 

To clarify:

```python
peewee.fn.COUNT(BookingModel.id).filter(BookingModel.status != 'cancelled')
```
does not work.

Works fine for me:

```python
class Person(Model):
    name = TextField()
    class Meta:
        database = db

class Pet(Model):
    person = ForeignKeyField(Person)
    animal = CharField()
    class Meta:
        database = db

db.create_tables([Person, Pet])

p1 = Person.create(name='Cat Lady')
p2 = Person.create(name='Dog Owner')
p3 = Person.create(name='Animal Lover')
Pet.create(person=p1, animal='Cat 1-1')
Pet.create(person=p1, animal='Cat 1-2')
Pet.create(person=p2, animal='Dog 2-1')
Pet.create(person=p2, animal='Dog 2-2')
Pet.create(person=p3, animal='Cat 3-1')
Pet.create(person=p3, animal='Dog 3-1')
Pet.create(person=p3, animal='Dog 3-2')
Pet.create(person=p3, animal='Fish 3-1')

query = (Person
         .select(Person.name,
                 fn.COUNT(Pet.id).filter(Pet.animal.startswith('Cat')).alias('cats'),
                 fn.COUNT(Pet.id).filter(Pet.animal.startswith('Dog')).alias('dogs'),
                 fn.COUNT(Pet.id).alias('total_pets'))
         .join(Pet, JOIN.LEFT_OUTER)
         .group_by(Person.name))
for p in query:
    print(p.name, 'cats:', p.cats, 'dogs:', p.dogs, 'all:', p.total_pets)
```

Prints

```
Animal Lover cats: 1 dogs: 2 all: 4
Cat Lady cats: 2 dogs: 0 all: 2
Dog Owner cats: 0 dogs: 2 all: 2
```
Issue: peewee.OperationalError: (1061, "Duplicate key name 'fqcmodel_date_'") ?
Author: jekoie
State: closed
Created: 2022-04-23 08:36:06

I use peewee create a table and connect to mysql, but it run failed?
`peewee.OperationalError: (1061, "Duplicate key name 'fqcmodel_date_'")`

```python
class FQCModel(Model):
    date_ = DateTimeField(default=datetime.datetime.now, help_text="日期", verbose_name="日期", index=True, formats="%Y-%m-%d %H:%M:%S")
```

peewee crate table in safe mode don't work at all????
![image](https://user-images.githubusercontent.com/6836400/164888113-8776e544-9687-4f79-8245-fd8f7219d8da.png)


@jekoie You are supposed to do it like this:

```
class BaseModel(Model):
    class Meta:
        database = db

class FQCModel(BaseModel):
    date_ = DateTimeField(default=datetime.datetime.now, help_text="日期", verbose_name="日期", index=True, formats="%Y-%m-%d %H:%M:%S")

db.create_tables([FQCModel])
```


MySQL does not support create index if not exists, if I recall correctly. So you probably need to check yourself beforehand.

You can get the list of existing tables and indexes:

* http://docs.peewee-orm.com/en/latest/peewee/api.html#Database.get_tables
* http://docs.peewee-orm.com/en/latest/peewee/api.html#Database.get_indexes

@coleifer My Model is Ok, The real problem is `peewee` can't  judge if the table already exist, so in I call  `db.create_tables([FQCModel], safe=True)` again , it will be throw error.

You meant to reply to mkmoisen I believe.

Here is my comment, again:

MySQL does not support create index if not exists, if I recall correctly. So you probably need to check yourself beforehand.

And:

You can get the list of existing tables and indexes:

    http://docs.peewee-orm.com/en/latest/peewee/api.html#Database.get_tables
    http://docs.peewee-orm.com/en/latest/peewee/api.html#Database.get_indexes

@jekoie you can always write your own method that checks for existence before trying to create the table.
Issue: Index of Get Failed Row on insert_many
Author: weaverjess
State: closed
Created: 2022-03-31 01:54:54

Is there anyway to receive the index of a failed row on insert_many?

This could be very useful for helping have feedback to users for which row in a bulk request has failed, and thus caused the whole transaction to be thrown out.


Either your db will tell you or it won't. Beyond that it's not really something peewee can figure out.
Issue: Some issues about "(2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))"
Author: czf0613
State: closed
Created: 2022-03-25 05:01:17

I am using FastAPI as my backend framework.
And I also noticed this problem solution in chatpter "framework integration", which shows:

``` python
# This hook ensures that a connection is opened to handle any queries
# generated by the request.
@app.on_event("startup")
def startup():
    db.connect()


# This hook ensures that the connection is closed when we've finished
# processing the request.
@app.on_event("shutdown")
def shutdown():
    if not db.is_closed():
        db.close()
```

I followed this instruction but still get the same error, "peewee.OperationalError: (2006, "MySQL server has gone away (BrokenPipeError(32, 'Broken pipe'))")". I also get another error like "peewee.InterfaceError: (0, '')".

Did I make any mistakes? Maybe it's not compatible with connection pool?
Here is the piece of code that I configure the database:
```python
from playhouse.pool import PooledMySQLDatabase

db = PooledMySQLDatabase(
    'xxxx',
    max_connections=32,
    stale_timeout=60,
    user='xxxxxxxxxxxxx',
    password='xxxxxxxxxxx',
    host='xxxxxxxx',
    port=3306,
    charset='utf8'
)

class BaseModel(Model):
    class Meta:
        database = db
```

Looking forward to your reply, thanks a million!

Peewees pool and database implementation rely on connection-per-thread. This works well with regular python and gevent, but not with asyncio. Asyncio needs "connection per asyncio context" or behind a thread pool.

> Peewees pool and database implementation rely on connection-per-thread. This works well with regular python and gevent, but not with asyncio. Asyncio needs "connection per asyncio context" or behind a thread pool.

Well......is there any idea to avoid this problem? asyncio is really important in FastAPI......

@czf0613 If your particular use case really needs async, I think you'll have to use sqlalchemy instead of peewee. 

However, for the most applications, async python will not run faster than sync python. If your use case doesn't require async, you can use FastAPI in regular mode by not defining your functions as async def if I remember correctly.
Issue: Missing parametter `seen` in docstring
Author: Seluj78
State: closed
Created: 2022-03-22 12:53:24

As `seen` here (lol)

https://github.com/coleifer/peewee/blob/a6198b96f475a2ee9687f36eda1cbb539907c56a/playhouse/shortcuts.py#L14-L32

Cheers!

That's used internally to avoid cycles in graph when recursing. Ignore.

Could you maybe rename it `_seen` and note it in the docstring just in case ? :) So you don't have other questions ;)
Issue: Missing TableFunction
Author: AA-Stargazer
State: closed
Created: 2022-03-10 15:57:03

I'm trying to use the table function from "from playhouse.sqlite_ext import TableFunction"
but it gives this error: 
`Traceback (most recent call last):
  File ".../main5.py", line 46, in <module>
    from playhouse.sqlite_ext import TableFunction
ImportError: cannot import name 'TableFunction' from 'playhouse.sqlite_ext' (C:\Users\Enesu\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\playhouse\sqlite_ext.py)`

I tried to look at the sqlite_ext.py file and also couldn't find TableFunction. But I could find the class TableFunction in the _sqlite_ext.pyx file. I don't know how to solve this, so I thought it would be good to submit it here. If I've done something wrong, sorry for that, it's my first time.

Oh, and 1 more thing, in the sqlite_ext.py file, there is a try-except statement for importing. Maybe 1 of the files is the problem and it causes all other modules to not get imported
try:
    from playhouse._sqlite_ext import (
        backup,
        backup_to_file,
        Blob,
        ConnectionHelper,
        register_bloomfilter,
        register_hash_functions,
        register_rank_functions,
        sqlite_get_db_status,
        sqlite_get_status,
        TableFunction,
        ZeroBlob,
    )
    CYTHON_SQLITE_EXTENSIONS = True
except ImportError:
    CYTHON_SQLITE_EXTENSIONS = False


I imported traceback to sqlite3_ext.py, and added "traceback.print_exc()"  to except statement, this is what gave:
  File "C:\Users\Enesu\AppData\Local\Packages\PythonSoftwareFoundation.Python.3.9_qbz5n2kfra8p0\LocalCache\local-packages\Python39\site-packages\playhouse\sqlite_ext.py", line 22, in <module>
    from playhouse._sqlite_ext import (
ModuleNotFoundError: No module named 'playhouse._sqlite_ext'

You need to compile the c extension to use those.

the .pyx files?
is GCC works on them?

read the docs... http://docs.peewee-orm.com/en/latest/peewee/installation.html

Thank you very much for both solution and fast response.
Issue: PostgreSQL with pg8000?
Author: andy47
State: closed
Created: 2022-03-08 09:49:48

I'm developing on an Apple Silicon Mac. At the moment there isn't a way to install a binary wheel for psycopg2. So I'm looking at alternative PostgreSQL driver modules and the one that seems to work is [pg8000](https://github.com/tlocke/pg8000).

Is there a way in PeeWee to substitute psycopg2 with pg8000 as the underlying PostgreSQL db-api module?

I looked through the documentation but the only references I can find are to psycopg2.

I'm not familiar with pg8000, but you can consult the docs on adding a new database implementation:

http://docs.peewee-orm.com/en/latest/peewee/database.html#adding-a-new-database-driver

Thanks, I'll take a look.

Also I'm not too familiar with the new mac stuff but its probably fairly easy to compile psycopg2 and save yourself a lot of hassle. I've built it myself on linux many times.
Issue: Buggy SQL syntax is produced when trying to JOIN two fields from same model
Author: lapinvert
State: closed
Created: 2022-03-07 10:25:17

```
Adult(Model):
    name = CharField()

Children(Model):
    name = CharField()
    dad = ForeignKeyField(Adult, backref='children_as_dad')
    mom = ForeignKeyField(Adult, backref='children_as_mom')

Dad = Adult.alias()
Mom = Adult.alias()
query = (
    Children.select(Children, Dad, Mom)
    .join(Adult, on=(Children.dad == Dad.id))
    .switch(Children)
    .join(Adult, on=(Children.mom == Mom.id))
)
print(query)
```

I want to select all children along with their two parents in a single query, but the output query is really weird:
```
('SELECT "t1"."id", "t1"."dad_id", "t1"."mom_id", "t2"."id", "t2"."name", "t3"."id", "t3"."name", FROM "children" AS "t1" INNER JOIN "adult" AS "t4" ON ("t1"."dad_id" = "t2"."id") INNER JOIN "adult" AS "t4" ON ("t1"."mom_id" = "t3"."id")
```
executing it results in Exception, because of those `t4`s which should be `t2` and `t3` respectively.

I am using peewee `3.14.9` and sqlite.

Is that a bug, or what am I doing wrong?

You're almost there. Just join on the aliases themselves as opposed to .join(Parent...

```
Dad = Adult.alias()
Mom = Adult.alias()
query = (
    Children.select(Children, Dad, Mom)
    .join(Dad, on=(Children.dad == Dad.id))
    .switch(Children)
    .join(Mom, on=(Children.mom == Mom.id))
)
```
Issue: Alias should have a public `name` property
Author: mkmoisen
State: closed
Created: 2022-03-07 07:17:16

I believe that the only way to check the name of an `Alias` object is to access the private `._alias` attribute.

May we please add a public accessor to this?

I think it should be `name`, just like the `Field` API.

This is what I have to do now, which is a bit inconvenient:

```
columns = [
    Tweet.id,
    Tweet.content,
    User.username.alias('who')
]

# ...

for column in columns:
    if isinstance(column, Field):
        print(column.name)
    elif isinstance(column, Alias):
        print(column._alias)

```

---

It would be better if Alias had a `name`:

```
for column in columns:
    print(column.name)
```

Thank you

Added `name` property.

Hi @coleifer , thanks for adding these.

I'm writing a library that depends on peewee.

Do you think it would be sensible to simply require the new 3.14.10 version of peewee, and use the new `Alias.name`, `ModelSelect.selected_columns`, `ModelSelect.left_outer_join`, etc. directly? 

My only concern is if a user wants to use an older version of peewee and will not want to use my framework. I'm not sure if this is a valid concern today with the popularity of virtual environments. 

If it is a valid concern, is there anything wrong with my library simply monkey patching these attributes onto peewee?

I don't attempt to collect any stats on what versions people are running or downloading, so it's hard to say. I don't make any attempt to support people on 2.x, but as far as whether people use the latest and upgrade frequently, I couldn't say. I think monkeypatching is probably just fine if you insist on using the new apis.
Issue: ModelSelect public method to return list of columns in the SELECT
Author: mkmoisen
State: closed
Created: 2022-03-07 07:10:11

Unless I am mistaken, there is no public way to get the list of columns in the select.

The only way I see is to call the private `._returning` attribute.

Can we please add public access for this? SQLAlchemy has `selected_columns` in 1.3/1.4. In previous versions you could use the `.c` which returned a list of selected columns.

```
query = Tweet.select(
    Tweet.id,
    Tweet.content,
    User.username
).join(User)

selected_columns = query._returning

print(selected_columns)
```

Added `selected_columns` as a property.
Issue: Peewee Is there any performance benefit to using the Query Builder over the ORM's dict/nametuple functions?
Author: mkmoisen
State: closed
Created: 2022-03-06 23:52:08

I know that when the ORM returns python model objects, there is some overhead that we can eliminate by calling `.dicts()` or `.namedtuples`(). 

Is there any performance benefit to using the [query builder API](https://docs.peewee-orm.com/en/latest/peewee/query_builder.html) to return dicts/namedtuples, in comparison to returning dicts/namedtuples from the ORM API?

(I was going to ask this on stackoverflow but figured performance questions like this were not appropriate there).

Thank you.

You'll have to profile it yourself, as it probably depends quite a bit on what types of queries you are executing. If you are really concerned about performance, using your database's python driver directly is going to be the fastest, of course. But you're using Python, so it's probably a somewhat mis-placed effort, to try to micro-optimize at this layer.
Issue: Add SELECT columns to a preexisting query/ModelSelect 
Author: mkmoisen
State: closed
Created: 2022-03-06 18:09:36

If I already have a preexisting Peewee query (ModelSelect), is it possible to add a column to the SELECT statement of the query?

E.g:

```
query = Tweet.select(Tweet.id)
# query.add_select_column(Tweet.content)  # how to do this ?
```

I think it might be useful to have the `select` function of the ModelSelect return a query with a modified SELECT statement; the same thing that `where` and `join` do.

Today, the `select` function returns a new query from the base model, so it is not too useful:

```
>>> query = Tweet.select(Tweet.id)
>>> print(query.select(Tweet.contents).sql())
('SELECT "t1"."contents" FROM "tweet" AS "t1"', [])
```

Looking through the source code, I see I can do this work around by accessing a private attribute:

```
query = Tweet.select(Tweet.id)
query._returning.append(Tweet.content)
```

But I would rather not rely on the private attribute. 

Nevermind, I found it:

```
query = Tweet.select(Tweet.id)
query = query.select_extend(Tweet.content)
```
Issue: filter IN is not working
Author: tjengbudi
State: closed
Created: 2022-03-04 03:11:47


hello,
i try to use filter IN in peewee. but the generated code is not right
for example i want to select * from item where itemid in (abc, def)
but the peewee is create without "()"

so the peewee will create select * from item where itemid in abd, def
it will raised the error in mysql.

could you help me about this?

You also contacted me via my personal site. Then opened an issue on GH. Please in the future use stackoverflow for basic questions like this.

Works fine, by the way:

```python
from peewee import *

db = SqliteDatabase(':memory:')

class Reg(Model):
    key = TextField()
    class Meta:
        database = db

db.create_tables([Reg])

for i in range(10):
    Reg.create(key='k%s' % i)


query = Reg.select().where(Reg.key.in_(['k1', 'k3', 'k5']))
for row in query:
    print(row.key)
```

Output:

```
k1
k3
k5
```
Issue: query error
Author: wbzdssm
State: closed
Created: 2022-03-03 09:50:45

    I think I found a bug, when some fields do not exist in the model, I use the .select query to return None, but when I use scaler(as_tuple=Ture), it returns a row of correct data, I don't know if this is in line with expected；
 My table is not created with model, there is no table created with model, and the query is not returning any errors