akumoth/peewee-issues huggingface dataset:
Issue: Introspector changes Integer field which is primary key to Serial type (AutoField) in dynamically-generated model from Postgres Database
Author: AlexeyYuNovikov
State: closed
Created: 2021-02-05 09:22:39

When I'm trying  to get dynamically-generated model from existing table in Postgres database, I've found that fields with type Integer become fields with type AutoField in generated model if those fields are primary key.
This fields in my DB have unique values which is really key values. I'm getting them from another remote source.
Query for creation of the test table 
![image](https://user-images.githubusercontent.com/45177856/107012561-3c60a500-67aa-11eb-8f87-494a44c8b5b7.png)

Result of the execution of the code for generating model for this table interactively in python console in PyCharm:
 
```python
    >>introsp_pub = Introspector.from_database(pg_db, schema='public')
    >>proba_dynamic_model = introsp_pub.generate_models(table_names=['proba'])['proba']
    >>print_model(proba_dynamic_model)
    proba
      proba_int AUTO PK
      proba_txt VARCHAR
    >>print_table_sql(proba_dynamic_model)
      CREATE TABLE IF NOT EXISTS "public"."proba" (
        "proba_int" SERIAL NOT NULL PRIMARY KEY,
        "proba_txt" VARCHAR(255)
      ) 
```

This is result afer using pwiz
```
    class BaseModel(Model):
      class Meta:
          database = database

    class Proba(BaseModel):
      proba_int = AutoField()  # that is not right!!!! It should be IntegerField() !!!
      proba_txt = CharField(null=True)

    class Meta:
        table_name = 'proba'
        schema = 'public'
```

And as addition, size of varchar was detected incorrectly. The size of field "proba_txt" is 10 symbols in the database and 255 symbols in the generated model. 

The reflection stuff is not perfect and uses a heuristic in some cases. If the primary-key is a single integer column, then the reflection assumes it is auto-increment. Similarly, we don't pull the character-length for varchar column types - although this may change in a future version. These are both known limitations.
Issue: Usage in a replicated Postgres cluster
Author: whisust
State: closed
Created: 2021-02-04 18:47:12

Hi, I'm using Peewee in prod and recently we set up a read replica.

As far as I've seen in the issues and the doc, there is no support to specify a `database` that could be used for read-only queries.

I'm using this piece of code for dynamic database binding at run-time since a few months, but under some load (gevent + gunicorn), I get errors linked to Inserts/Updates being done in a Read Only connection if I have a read replica:

```python
from playhouse.pool import PooledPostgresqlExtDatabase
leader_db = PooledPostgresqlExtDatabase(...)

if config.db.read_replica is not None:
    read_replica = PooledPostgresqlExtDatabase(...)
    


def using_db(db, *models):
    """Decorator used to specify the db to use, binds the models to the given db before querying"""

    def using_db_decorator(f):
        @wraps(f)
        def wrapper(*args, **kwargs):
            [m.bind(db) for m in models]
            try:
                db.connect(reuse_if_open=True)
                return f(*args, **kwargs)
            finally:
                db.close()

        return wrapper

    return using_db_decorator
```

And I'm using the `using_db` as a decorator over my persistence functions:
```python
class AuthToken(Model):
    id = AutoField(primary_key=True)
    value = CharField(null=False, index=True)
    created_at = DateTimeField(null=False, default=datetime.now)
    last_used_at = DateTimeField(null=False, default=datetime.now)

@using_db(leader_db, AuthToken)
def update(id: int, last_used_at: datetime) -> Optional[AuthToken]:
    return AuthToken.update(last_used_at=last_used_at).where(AuthToken.id == id).returning(AuthToken).execute()[0]
```


It _can_ trigger this kind of error:
```
ReadOnlySqlTransaction
cannot execute UPDATE in a read-only transaction
```

So, Do you have guidance on how to use Peewee with a replicated Postgres system?

Not really, no, it's kind-of up to you how you want to handle that. It's not something that is "officially" supported, although there are ways you can do it, as you allude to. I'm not sure what would be making it sporadically fail under-load, either - that seems odd if it works correctly under general circumstances. I don't have enough information to really debug if this even points to the existence of a bug.
Issue: Different processing of datetime in different databases
Author: ztj1993
State: closed
Created: 2021-02-03 10:02:36

I'm writing something related to statistics. I don't want to limit the database. Different databases handle different dates and times.

I tested extract_ However, different databases need to be dealt with separately, resulting in the following problems:

1.How to get the database type of the model;
2.If I want to be compatible with all the databases supported by peewee, which date function is better;

Here are some code snippets:
```
from peewee import *

database_proxy = DatabaseProxy()

database = SqliteDatabase(':memory:')


class StatisticsDataModel(Model):
    mid = IntegerField()
    d_t = DateTimeField()
    val = IntegerField()

    class Meta:
        table_name = 'statistics_data'
        database = database_proxy

def sum_format(mid, start, end):
    format_d_t = fn.Year(StatisticsDataModel.d_t).alias('format_d_t')
    query = StatisticsDataModel.select(
        format_d_t,
        fn.SUM(StatisticsDataModel.val),
    ).where(
        StatisticsDataModel.mid == mid,
        StatisticsDataModel.d_t >= start,
        StatisticsDataModel.d_t <= end,
    ).group_by(format_d_t)
    return query.execute()
```

------------

The above is translated by software!

Peewee datetime field exposes properties like `.year` which use a db-specific extract (or equivalent) function. So you can write:

```python

query = (DataModel
         .select(DataModel.d_t.year, fn.SUM(DataModel.val))
         .where(...)
         .group_by(DataModel.d_t.year))
```

Relevant docs: http://docs.peewee-orm.com/en/latest/peewee/models.html#datetimefield-datefield-and-timefield
Issue: ImproperlyConfigured: MySQL connector not installed!  
Author: thisnugroho
State: closed
Created: 2021-02-02 07:54:52

hello, first I wanna thank you guys who made this, it's very helpfull for me who just start learning python, migrating from PHP 😁,
I got an error like in this picture below,

![image](https://user-images.githubusercontent.com/49790011/106567870-73964280-656d-11eb-8420-9603d1908171.png)

it's said I don't have mysql.connector installed,
then i try to import it, but there is no error

![image](https://user-images.githubusercontent.com/49790011/106568309-18188480-656e-11eb-9ba2-3c57a6ed6dd2.png)

so I confused about what is happening...

can someone explain to me, did i make mistake?

I'm trying point number 4 from this answer which is with `playhouse.mysql_ext.MySQLConnectorDatabase`  from this:
https://github.com/coleifer/peewee/issues/1569#issuecomment-484128525
and the code is looklike this:
```python
from peewee import * 
from playhouse.mysql_ext import MySQLConnectorDatabase 

dbc = {
    "db_name":"xxx",
    "host":"127.0.0.1",
    "user":"xxx",
    "password":"xxxx",
    "port":3306
}

db = MySQLConnectorDatabase(dbc['db_name'], user=dbc['user'], password=dbc['password'], host=dbc['host'])
```

I'm using:
python:3.8.5
flask:1.1.2
OS: ubuntu 20.04

It looks like you're using a virtual environment. You may need to be sure mysql-connector is installed in your venv. Whatever is happening, the problem is that peewee cannot import mysql.connector.

You can verify this yourself in your script. Place this at the top:

```python
import mysql.connector
```

> It looks like you're using a virtual environment. You may need to be sure mysql-connector is installed in your venv. Whatever is happening, the problem is that peewee cannot import mysql.connector.
> 
> You can verify this yourself in your script. Place this at the top:
> 
> ```python
> import mysql.connector
> ```

thank you so much for your response,
Solved it by doing:
- Install Python wsgi
I started by installing wsgi  `sudo apt-get install libapache2-mod-wsgi` but still got the error because i just know it that previous command is installing wsgi for python2, so it's running on python2 that's why my pymysql or other mysql modules that i tried doesn't work, so I run this to fix it `sudo apt-get install libapache2-mod-wsgi-py3` install wsgi for python3
- checking python-path in apache config
adding this line in my app.py.
```python
import sys
print(sys.path)
```
and make sure both python paths are equals ( in apache config and from script output )

and now my apache conf looklike this:

```
python-path=/home/pg/:/usr/lib/python38.zip:/usr/lib/python3.8:/home/pg/venv/lib/python3.8/site-packages:/home/pg/venv/lib/python3.8/site-packages/gevent-21.1.2-py3.8-linux-x86_64.egg
```

that's it, in case someone had the same trouble,thank you~😊
Issue: distinct() on a ModelCompoundSelectQuery raises an error
Author: arel
State: closed
Created: 2021-01-30 02:07:53

I am using peewee version 3.14.0. A see that `ModelCompoundSelectQuery` does not have a `distinct()` method, and calling it raises the error:

```
AttributeError: 'ModelCompoundSelectQuery' object has no attribute 'distinct'
```

For example:

```python
o1 = User.select().where(User.id < 5)
o2 = User.select().where(User.id > 1)
o3 = o1 | o2

query1 = o1.distinct()  # OK
query2 = o3.distinct()  # Raises error
```

I would expect a compound query to have the same API as any query. If this expected behavior, how should one select distinct rows in a compound query?


This is because the convention is different when using UNION and UNION ALL. From which side of the union would you be selecting "distinct" from, in your example? You could put distinct on both the left- and right-half of the query, but I think what you want is actually just the regular behavior of UNION.

See this answer: https://stackoverflow.com/questions/49925/what-is-the-difference-between-union-and-union-all

Or https://www.w3schools.com/sql/sql_ref_union.asp
Issue: How to set postgres cursor.itersize from peewee?
Author: qacollective
State: closed
Created: 2021-01-29 07:59:57

I am using Peewee with PostgreSQL and server side cursors as per [the documentation](http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#server-side-cursors) and realise that the default row fetch size is 2000.  I've verified this by looking at the queries being sent to the server.

It works really well, my code is receiving around 200,000 rows per second. However, I am reading hundreds of millions of rows and am aware that the default psycopg2 cursor itersize is 2000 records. I'd like to experiment with changing this default to see if I get a higher throughput of records.

Is there a way to set the psycopg2 server side itersize value from Peewee?

Can I do this?

```
db = PostgresqlExtDatabase('dbname', user='user', password='pass', host='localhost', port=5432, server_side_cursors=True)
db.cursor().itersize = 50000
data = Record.raw("SELECT ...").dicts().iterator(database=db)
```

Looking at the queries sent to the server, I'm still seeing 

```
FETCH FORWARD 2000 FROM "64a756a8-61f1-11eb-9154-ac220bbe7188"
```

Any assistance would be greatly appreciated.

You can try overriding the `cursor()` method used by Peewee to create the db-api 2 cursor:

```python

class PGDatabase(PostgresqlExtDatabase):
    def cursor(self, commit=None):
        cursor = super(PGDatabase, self).cursor(commit=commit)
        cursor.itersize = 50000
        return cursor

db = PGDatabase('dbname', ...)
```

Please let me know if this does not work, as I didn't test it myself.

Just writing in to confirm that @coleifer's advice above does work.  In my pgAdmin4 console, I'm seeing peewee clients execute statements like:

    FETCH FORWARD 500000 FROM "dfb9d910-7187-11eb-9f92-ac220bbe7188"

This has helped me to drastically reduce memory consumed by multiple processes retrieving half a billion rows each. :+1:
Issue: `generate_models` returns empty `dict`
Author: vsheg
State: closed
Created: 2021-01-28 21:49:11

Hi,
I want to see tables in my predefined database (it was created with `SQLAlchemy` and modified with GUI Beekeeper Studio)

1. When I use `peewee` I get noting:

```python
from peewee import PostgresqlDatabase
from playhouse.reflection import generate_models

db = PostgresqlDatabase('postgres', user=getenv('DB_USER'),
                                    password=getenv('DB_PASSWORD'),
                                    host='localhost',
                                    port=5432
                        )

print( generate_models(db) )
# {}
```

2. But when I use `SQLAlchemy` (or GUI Beekeeper Studio), I can see all my tables:
```python
from sqlalchemy import create_engine

engine = create_engine(getenv('DB_URI'))
print( engine.table_names() )
# ['sources', 'channels', 'persons', 'posts']
```
What's wrong?

<details>
  <summary>Versions</summary>
  

- Python 3.9.1
- pip 21.0 
- peewee 3.14.0
- psycopg2-binary 2.8.6
- macOS Big Sur 11.1

</details>

I have no idea what might be going on in your setup. This code is covered with unit tests so it is not fundamentally broken:

https://github.com/coleifer/peewee/blob/master/tests/reflection.py#L81

I'd suggest debugging or providing more information. Possibly some mismatch between what sqlalchemy db connection string you're using and how you're initializing the peewee db?

Peewee has a `db.get_tables()` method you can use to quickly see if it can detect the tables.
Issue: How to repeat use query?
Author: mouday
State: closed
Created: 2021-01-25 03:05:36

I want to use peewee query like:

```python
# query list 
query =UserModel.select()

if name:
    query = query.where(UserModel.name == name)
if age:
    query = query.where(UserModel.age == age)

lst = query.dicts()
```

```python
# query count
query =UserModel.select()

if name:
    query = query.where(UserModel.name == name)
if age:
    query = query.where(UserModel.age == age)

count = query.count()
```

i found this code is repeat， and i want to combine it
```python
query =UserModel.select()

if name:
    query = query.where(UserModel.name == name)
if age:
    query = query.where(UserModel.age == age)
```

what can i do? do you any suggestion?


This is a great thing to ask on stackoverflow. The issue tracker is for issues w/peewee, while questions should go on stackoverflow or the #peewee irc on freenode.

```python
# Did you try this?
query =UserModel.select()

if name:
    query = query.where(UserModel.name == name)
if age:
    query = query.where(UserModel.age == age)

count = query.count()
lst = query.dicts()
```

thank you !
Issue: How to specify some fields when using the function of replace()?
Author: vba34520
State: closed
Created: 2021-01-23 02:50:19

How to specify some fields when using the function of [`replace()`](http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.replace)?

1. http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.replace
2. http://docs.peewee-orm.com/en/latest/peewee/querying.html#upsert

I give a primary key to  [`replace()`](http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.replace) and just want to modify one of the field.

```python
from peewee import *

db = SqliteDatabase('test')


class Product(Model):
    product_id = CharField()
    product_name = CharField()

    class Meta:
        database = db


db.connect()
db.create_tables([Product])
id = Product(product_id='0001', product_name='apple').save()
Product.replace(id=id, product_name='banana').execute()  # specify some fields
print(id)
```

it will cause peewee.IntegrityError: NOT NULL constraint failed.

Looking forward to your reply!

Replace will delete the row and replace it with the data you provide.

I think what you want is "update()".
Issue: Allow deferring of foreign keys when lazy_load is disabled
Author: conqp
State: closed
Created: 2021-01-22 16:57:42

I just encountered a problem when disabling lazy loading on foreign keys.

```python
class Referenced(Model): pass

class Referencing(Model):
     ref = ForeignKeyField(Referenced, lazy_load=False)

referenced = Referenced()
referencing = Referencing()
referenging.ref = referenced
referenced.save()
referencing.save()
```
The above code will result in a `DoesNotExist` exception, because the current `NoQueryForeignKeyAccessor` does not attempt to check `instance.__rel__`
I think this should work, since it does not require a query.
I will make a pull request, that solves this issue and also gets rid of the `NoQueryForeignKeyAccessor`.
This has the beautiful side effect, that changing a `ForeginKeyField`'s `lazy_load` attribute during runtime actually has an effect on the behaviour. Currently, `lazy_load`  is only evaluated during `ForeignKeyField.__init__` and then unnecessarily stored on the instance, since subsequent changes to it will not have any effect.

Thanks for this - I've merged an equivalent patch which includes a regression test as well: b34e601
Issue: Add model information to DoesNotExist
Author: conqp
State: closed
Created: 2021-01-21 12:29:30

Currently it is hard to extract model context from generic `DoesNotExist` exceptions.
It would be nice, if the `DoesNotExist` class would have information about the model, so I can do something like this:

```python
from flask import Flask
from peewee import DoesNotExist, Model, ModelBase

APPLICATION = Flask('myapp')

@APPLICATION.errorhandler(DoesNotExist)
def no_such_model(exception: DoesNotExist):
    return (f'Model {exception.model} does not exist.', 404)
```

Currently I have to write such a handlers for every single `Model.DoesNotExist` to have the context of which model was not found.

This seems of somewhat questionable utility as it seems more likely you would want to catch- and handle those exceptions rather than bubbling them all the way up to the web framework to handle generically. I'm going to pass on this for now, but will keep an eye out for other requesters/use-cases for this and re-evaluate then.

Fair enough. My current code is currently working with separate handlers for each model's exception.
I just see this as a means of reducing duplicated code.
Issue: callback on record insert, delete and update
Author: sugizo
State: closed
Created: 2021-01-13 19:25:45

# Objective
before insert table can  execute python function to convert data 
**e.g.** 
user insert Field intervals in list [2, 2, 1, 2, 2, 2, 1]
then base on list above automatically convert and insert into Field intervals_name with [W, W, H, W, W, W, H] on the same table

**or** 

after insert table can send email to user, base on field email stored on another table 
**e.g.**
```
def after_insert_nav(f, id):
    query_account_0 = (db.account.product == f['product'] )
    rows_account = db(query_account_0).iterselect()
    for row_account in rows_account:
        if row_account.investor.notification:
            mail_to = row_account.investor.email
            subject = 'NAV %s' % row_account.product.name
            messages = response.render('templates/mail/nav.html', 
                                               dict(row = row_account, row_nav = row_nav) )
            attachments = ''
            insert_mail_queue(mail_to, subject, messages, attachments)
```

# question
is it possible to use callback on record insert, delete and update in peewee ?

**ref**
http://web2py.com/books/default/chapter/29/06/the-database-abstraction-layer#callbacks-on-record-insert-delete-and-update

**n.b.**
objective above can be achieve in [pydal](https://github.com/web2py/pydal)

thanks

No, this is not really supported by Peewee. Rather, you should probably be implementing your callbacks in your application rather than as part of core peewee (along with your validation, or whatever else you need to do). You can, of course, override the `save()` method and do things in there if you wish:

```python

class User(Model):
    first_name = TextField()
    last_name = TextField()
    full_name = TextField()

    def save(self, force_insert=False, only=None):
        self.full_name = self.first_name + ' ' + self.last_name
        return super(User, self).save(force_insert, only)
```

The above will work when you use the model-level APIs like save() / create() -- but will **not** when you use methods like `insert()` / `update()`.

Another way you can handle converting values to database-specific types is by using a custom field class and implementing the `db_value()` / `python_value()` methods. Here are the notes on custom fields: http://docs.peewee-orm.com/en/latest/peewee/models.html#creating-a-custom-field
Issue: Sponsor project?
Author: milesgranger
State: closed
Created: 2021-01-13 14:38:51

Hi,

Just want to say how much I appreciate this project and how tightly maintained it is! :clap: 
Would you be willing to enable sponsorship for this project and/or for yourself as a developer?

Thanks, @milesgranger. I don't seek sponsorships (I'm gratefully employed at this time), but I'm happy to encourage you to donate to a charitable cause.

Should have clarified this was on behalf of an organization which uses peewee extensively and wanted to "give back" a bit. However, I'll take your suggestion you'd rather it go to a charitable cause. 👌
Issue: Many-to-many relations and their entries
Author: thmsklngr
State: closed
Created: 2021-01-07 15:00:47

Hi,

currently I'm switching an application from PonyORM to Peewee, which is somehow a challenge (both ORMs ;) ). One thing which is difficult to me, is the handling of many-to-many relations. I'd like to avoid the ManyToManyField, so I try to implement it with the suggested default solution.

Selecting from the intermediate table is easy and also the joins, but I'm struggeling with inserting data to it. I usually treat the data to be unique as set, means, that user1 and his relation to tweet1 is unique and can't be inserted a second time. A relation from user1 to tweet2 is ok.

As far as I remember, PonyORM handles this differently and holds combinations as unique (only one user1->tweet1) without any developer intervention. So, how to avoid this with peewee? Or is it necessary to query first the intermediate table if that combination exists or not?

Many thanks in advance! Regards, Thomas

You would want to use a unique constraint on the two foreign-keys in your intermediate table.

Then use the `insert into ... on conflict ignore` (or the equivalent) when inserting, to tell the database "if this insert would violate the unique constraint, then discard it". Sqlite, MySQL and Postgres all support this:

* http://docs.peewee-orm.com/en/latest/peewee/api.html#Insert.on_conflict_ignore
* http://docs.peewee-orm.com/en/latest/peewee/querying.html#upsert
Issue: Query results to json?
Author: jet10000
State: closed
Created: 2021-01-06 15:11:25

Is there a convenient way to directly convert the query result into json, or into an orjson object?

* http://docs.peewee-orm.com/en/latest/peewee/querying.html#retrieving-row-tuples-dictionaries-namedtuples - specifically `.dicts()`
* http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#shortcuts
* http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#model_to_dict

Please post questions to stackoverflow. The issue tracker is for tracking issues.