akumoth/peewee-issues huggingface dataset:
Issue: sqlite3 random rows by seed
Author: nisanb
State: closed
Created: 2020-04-22 17:53:35

With using Python, my tool has a randomized seed for each run.
Upon placing the same seed, I would like to receive the same randomized results.

I tried externally-implementing it, but that causes performance degradation for two reasons:
1. I need to build a list out of ModelSelect objects
2. I need to return all rows for a given where() query, even if I know I only need 5 randomized out of them.

The overhead is a pain and the performance penalty is high.

Could peewee provide an interface as such?
Or, is there a better way of doing it, other than what I tried?

Sqlite allows you to write arbitrary user-defined functions. There are examples:

http://docs.peewee-orm.com/en/latest/peewee/database.html#sqlite-user-functions
Issue: Slow attribute access in 2.10.2
Author: ParthGandhi
State: closed
Created: 2020-04-22 10:01:26

We're on version `2.10.2`, with no plans to upgrade in the short term.

From our profiling we've found that `__get__ peewee.py:915` takes a really long time in some of our hot code paths. Any idea why that `FieldDescriptor` is so slow? It seems to be doing just a `get` on a dict. I tried accessing the underlying `._data` directly and thats about 2x the speed.

Any idea why its that slow? I know "python is slow" answers some of it, but not _all_ of it.

Any suggestions to improve attribute access? Would upgrading to `3.x` help? I went through the changelog but found nothing specifically about this.

Python is slow. Typically the time spent querying the DB will be the bulk of the time spent in any peewee code path... usually orders of magnitude. If attribute access is hurting you, use the tuples method on the query to have the cursor return tuples instead of model instances.
Issue: Error with bulk_update and UUID primary keys
Author: spl0k
State: closed
Created: 2020-04-21 09:27:37

Hello,

It seems peewee doesn't serialize primary keys properly in `bulk_update()` when they're `UUIDField`s.

This code
```python
import logging
import uuid

from peewee import Model, UUIDField, IntegerField, SqliteDatabase

database = SqliteDatabase(":memory:")


class TestModel(Model):
    pk = UUIDField(primary_key=True, default=uuid.uuid4)
    i = IntegerField()

    class Meta:
        database = database


database.create_tables((TestModel,))

m1, m2 = [TestModel.create(i=i) for i in (1, 2)]
m1.i = 11
m2.i = 22

logger = logging.getLogger("peewee")
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)

TestModel.bulk_update((m1, m2), (TestModel.i,))
```

produces the following
```
('UPDATE "testmodel" SET "i" = CASE "testmodel"."pk" WHEN ? THEN ? WHEN ? THEN ? END WHERE ("testmodel"."pk" IN (?, ?))', [UUID('cec16f95-6bbe-4cb7-a4b7-89373dc7c895'), 11, UUID('046b94f6-154b-4f68-b9e8-4e447fd568a1'), 22, 'cec16f956bbe4cb7a4b789373dc7c895', '046b94f6154b4f68b9e84e447fd568a1'])
Traceback (most recent call last):
  File "E:\projects\battledawn-server\env\lib\site-packages\peewee.py", line 3090, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.InterfaceError: Error binding parameter 0 - probably unsupported type.

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "bulk.py", line 27, in <module>
    TestModel.bulk_update((m1, m2), (TestModel.i,))
  File "E:\projects\battledawn-server\env\lib\site-packages\peewee.py", line 6345, in bulk_update
    .where(cls._meta.primary_key.in_(id_list))
  File "E:\projects\battledawn-server\env\lib\site-packages\peewee.py", line 1877, in inner
    return method(self, database, *args, **kwargs)
  File "E:\projects\battledawn-server\env\lib\site-packages\peewee.py", line 1948, in execute
    return self._execute(database)
  File "E:\projects\battledawn-server\env\lib\site-packages\peewee.py", line 2433, in _execute
    cursor = database.execute(self)
  File "E:\projects\battledawn-server\env\lib\site-packages\peewee.py", line 3103, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "E:\projects\battledawn-server\env\lib\site-packages\peewee.py", line 3097, in execute_sql
    self.commit()
  File "E:\projects\battledawn-server\env\lib\site-packages\peewee.py", line 2864, in __exit__
    reraise(new_type, new_type(exc_value, *exc_args), traceback)
  File "E:\projects\battledawn-server\env\lib\site-packages\peewee.py", line 183, in reraise
    raise value.with_traceback(tb)
  File "E:\projects\battledawn-server\env\lib\site-packages\peewee.py", line 3090, in execute_sql
    cursor.execute(sql, params or ())
peewee.InterfaceError: Error binding parameter 0 - probably unsupported type.
```

Using peewee version 3.13.2

Fixed in 3.13.3
Issue: Error when issuing bulk_create with FK to non-primary-key rel field
Author: coleifer
State: closed
Created: 2020-04-20 22:04:54

        u1 = BCUser.create(username='u1')
        u2 = BCUser.create(username='u2')
        BCTweet.bulk_create([
            BCTweet(user='u1', content='t%s' % i)
            for i in range(4)])

Throws a foreign-key mismatch error.

So the `bulk_create()` method just pulls field data off each model and lines it up for insert. When the model being created has a foreign-key, then peewee is pulling off the related model instance. When doing the insert, we don't realize that we want the related model attr that corresponds to the FK and are just blindly grabbing the PK.

Fixed by 2944cd21f76282306dc034432d4b9270553883a4
Issue: ManyToMany field no longer has .add, .remove, .clear methods
Author: pylakey
State: closed
Created: 2020-04-16 16:21:36

I suppose that http://docs.peewee-orm.com/en/latest/peewee/relationships.html#manytomanyfield is outdated. 

Example in docs doesn't work with latest peewee version because of AttributeError

EDIT: This issue is related to ManyToManyField with through_model set. 

I am sorry for opening this issue. Already found a stupid mistake

The example code runs fine for me on the latest version of Peewee.

https://gist.github.com/coleifer/8dd15da06ed5e390064ff99ae8ef9378

The methods you mentioned *are* present. They are exposed on the backref accessor, not the field itself - this is how it has worked for a very long time.

@coleifer, thanks for your reply, it was my mistake)
Issue: Pivot functionality?
Author: bravegag
State: closed
Created: 2020-04-09 14:44:32

I think a really good feature in Peewee would be pivoting data which is supported up to some extent by different database vendors. Having pivot support would be a welcome abstraction to Peewee for a far common problem. Note that pandas implements pivoting see: https://pandas.pydata.org/Pandas_Cheat_Sheet.pdf

Pivoting means to turn data stored in long (or narrow) format, into wide format i.e. closer to a dataframe. Why saving the data in long format in the first place? because it's more extensible requiring less potential schema updates to extend.

See a relevant SO question here:
[How to pivot in SQLite or i.e. select in wide format a table stored in long format?](https://stackoverflow.com/questions/1237068/how-to-pivot-in-sqlite-or-i-e-select-in-wide-format-a-table-stored-in-long-form)

I don't think this is a good fit for a library like Peewee, but I would encourage you, if you think it would be useful, to create your own module.
Issue: [q] Run-time model schema
Author: ak4nv
State: closed
Created: 2020-04-09 11:10:51

Hi there!
I'm trying to make a multitenancy application (flask+peewee). I think it possible to change the schema for all models in runtime but can't find the way.
Any example, pls (without context managers)

Thanks!

You can use the `SubclassAwareMetadata` implementation, which tracks all models declared in your app. From that list of models, you should be able to easily iterate through them and change each model's `._meta.schema`.

This is exactly covered in this test-case:

https://github.com/coleifer/peewee/blob/4919f5b6ea56969e56abaa5f4e276452e96bc9c9/tests/models.py#L3255-L3287
Issue: OperationalError: PRIMARY KEY missing on table 
Author: bravegag
State: closed
Created: 2020-04-08 19:22:04

I get the OP title error while creating a table and setting the Meta to `primary_key = False` see the following MCVE sample:

```
from peewee import *

db = SqliteDatabase('market_data.db')


class BaseModel(Model):
    """
    Base reusable model.
    """
    class Meta:
        database = db
        legacy_table_names = False


class MarketInstrument(BaseModel):
    """
    Market instrument model.
    """
    id = BigAutoField(primary_key=True)
    name = CharField(max_length=20, null=False, index=True)


class MarketInstrumentData(BaseModel):
    """
    Market instrument time series data.
    """
    market_instrument = ForeignKeyField(MarketInstrument, backref='data')
    timestamp = DateTimeField(null=False, index=True)
    value = DecimalField(null=False, decimal_places=10)

    class Meta:
        """
        Doesn't require a PK
        """
        primary_key = False
        without_rowid = True


db.create_tables([MarketInstrument, MarketInstrumentData])
```

This leads to the error:
```
peewee.OperationalError: PRIMARY KEY missing on table market_instrument_data`
```

Indeed, I'm explicitly setting the `MarketInstrumentData` table not to have a PK since it isn't only unnecessary but also ER design-wise incorrect. It's a weak entity and it contains a FK to the strong entity `MarketInstrument` and that's all is needed. 

When does that operational error occur? Without the full traceback it's impossible to tell what happened. Operational error typically is raised by your driver - you're violating a constraint implied by something in your schema.

I believe, looking at the sqlite source code, that it's the use of "without rowid" and primary-key = false that are the issue. If you have no primary key, but you are also telling sqlite "without rowid" then it's going to complain, in other words. Give it a primary key or remove the "without rowid" bit and you should be OK.

Oh yes you're totally right. Thanks!
Issue: [Question] Return FKs with an _id suffix.
Author: dsmurrell
State: closed
Created: 2020-04-08 09:43:46

Right now, even though foreign keys are stored as field names in the database with an `_id` suffix, they are added to the dictionary outputted by `model_to_dict` without the `_id` suffix.

I was wondering, if I want all foreign keys to appear in the output dict of `model_to_dict` with a `_id` suffix, do I have to write custom code to achieve this, or is there a setting in peewee that can achieve this.

The reason I ask is because on the client side, the expectation is to receive the `_id` suffix for FKs and right now I'm adding them on the client side for all keys that match some list of FKs. This is a brittle solution and I'd like to improve it.

For example, if a `post` has a `user` FK, then I'd like `model_to_dict(post)` to output something like:

```
{
  id: "xyz",
  title: "This is a post",
  user_id: "abc"
}
```

`model_to_dict` is simply a shortcut, provided for convenience. If you need something different, then just implement a simple serialization function.

I understand that and have read that elsewhere... just checking that something didn't exist already that I was missing. Thanks!
Issue: mysql default sql_mode setting
Author: amchii
State: closed
Created: 2020-04-08 03:15:12

Hi,

peewee set the sql_mode to `'PIPES_AS_CONCAT'` when use `MySQLDatabase`, but this will remove the default setted values `'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'`, 

```python
import peewee

pwdb=peewee.MySQLDatabase('test',user='root',password='password')

r=pwdb.execute_sql('select @@session.sql_mode')

r.fetchall()
Out[4]: (('PIPES_AS_CONCAT',),)

from MySQLdb import _mysql

db=_mysql.connect(user='root',passwd='password',db='test')

db.query('select @@session.sql_mode')

res=db.use_result()

res.fetch_row()
Out[9]: ((b'ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION',),)
```
and this lead to when insert a `not null` field without value(also no default), no error throw, incorrect insert(for normal) see https://github.com/coleifer/peewee/issues/2031
```python
from peewee import *


db = MySQLDatabase(DB_NAME, user=DB_USER, password=DB_PASSWORD)

class M(Model):
    value = IntegerField(default=None, null=False)
    class Meta:
        database = db

db.create_tables([M])

m = M.create()
m_db = M[M.id]

print(m_db.value)

# output:
# /home/bukinpk/.local/lib/python3.7/site-packages/pymysql/cursors.py:170: Warning: (1364, "Field 'value' doesn't have a default value")
#   result = self._query(query)
# 0
```

i think the sql_mode setting should append `'PIPES_AS_CONCAT'` to the default setting instead of replace



You're welcome to specify whatever sql mode you wish by passing it in the constructor.
Issue: no function fts_bm25
Author: Dawoodkhorsandi
State: closed
Created: 2020-04-06 15:49:04

Hi

I tried to use sqlite3 full-text search with peewee when I run this code 
```Python
Poets.select().
            join(PoetsIndex, on=(Poets.id == PoetsIndex.rowid)).
            where(PoetsIndex.match(phrase))
            order_by(PoetsIndex.bm25())
```
it's throws error:

    cursor.execute(sql, params or ())
    peewee.OperationalError: no such function: fts_bm25

the match function works well, I tried raw SQL too, and it was good, so probably my version contains FTS3 and FTS4, why that error occurs?

Thank you for peewee



Peewee ships with some user-defined functions which provide the bm25 ranking (when using fts3/4). You need to ensure that you're using `SqliteExtDatabase` as your database class and you should be good-to-go, as it will register the rank functions by default.

You can register them yourself, too, just check out the code in `playhouse.sqlite_ext` (and the optional C extension) if you're curious.
Issue: foreign key exist 
Author: JPilarr
State: closed
Created: 2020-04-06 14:41:47

hi, 
would like check if foreign key exist do something.
```
  if self.printer_type:
        result['type_id'] = self.printer_type.id
        result['type_name'] = self.printer_type.name
```
like this. I think the example doesn't work

Questions go on stackoverflow. http://docs.peewee-orm.com/en/latest/peewee/contributing.html#questions
Issue: Comparison operations for subqueries
Author: EugeneRymarev
State: closed
Created: 2020-04-01 13:30:11

Hi, @coleifer!
Again with same problem, but not for **EQ** or **NE** operations.
Other operations not work too. Like **LT** or **GT**.
In _HashableSource class need add more operations from **OP**.

https://github.com/coleifer/peewee/issues/2118#issuecomment-593150353_

4919f5b6ea56969e56abaa5f4e276452e96bc9c9
Issue: SqliteDatabase + DateTimeField not working
Author: COM8
State: closed
Created: 2020-04-01 12:20:21

When you run the following example, it creates a DB and the `wnstoken` table but without the `expires` field.
```python
from peewee import *
from datetime import datetime, timezone

DB_PATH: str = "peeweeTest.db"
db: SqliteDatabase = SqliteDatabase(DB_PATH)

class WNSToken(Model):
    token: CharField = CharField(unique=True)
    expires: DateTimeField(default=datetime.now(timezone.utc))

    class Meta:
        database = db

print("Initalizing the DB...")
db.connect()
db.create_tables([WNSToken])
print("DB initialized.")
```

It looks like it's ignoring `DateTimeField`s. I tested this with multiple different combinations of fields and tables. As soon as a table includes a `DateTimeField`, it won't be created.

Inserting works, but also completely ignores the `DateTimeField`.

### Environment
* peewee: `3.13.2`
* Python3: `3.7.6`

### Resulting DB
![Screenshot from 2020-04-01 14-21-18](https://user-images.githubusercontent.com/11741404/78136556-206ed300-7424-11ea-843a-5fd65116b69f.png)


Too much typing...
```Python
expires: DateTimeField(default=datetime.now(timezone.utc))
```
Should be:

```Python
expires: DateTimeField = DateTimeField(default=datetime.now(timezone.utc))
```
Issue: database.execute_sql('PRAGMA foreign_keys = 1;') not working
Author: ldconejo
State: closed
Created: 2020-03-28 05:52:20

Hi,

I teach a Python class in which we use Peewee for implementing SQL databases in Python, given how straightforward it is to use.

One of my students recently pointed out that the foreign key constraints in one of the class examples were not working.

How to reproduce:

1. Run _personjob_learning_v3_p1.py_: Initializes the database with _person_ and _job_ tables. Populates the _person_ table.

2. Run _personjob_learning_v5_p1.py_: Populates the _job_ table.

3. Run _personjob_learning_v5_p5.py_: Deletes _person_ "Andrew" from the _person_ table, which should fail since there are entries with "Andrew" as a foreign key in the _job_ table. However, the deletion goes through.

The code uses this line to activate foreign key constraints after every _database.connect()_:

`database.execute_sql('PRAGMA foreign_keys = 1;')`

I think the command above no longer works. A workaround is replacing the line above in _person_model.py_ (line 22) with the following:

`database.pragma('foreign_keys', 1, permanent=True)`

I'm selecting _permanent=True_ so that I only need to make the change there, otherwise it needs to be made after every _database.connect()_

[peewee_issue_example.zip](https://github.com/coleifer/peewee/files/4396687/peewee_issue_example.zip)

Regards,

Luis


So some database pragmas have to be executed once, before any tables are created (journal_mode for example). Other pragmas, like "foreign_keys", have to be issued at the start of every connection. The recommended way to do this with peewee is to declare your pragmas as part of your db constructor:

```python

# Ensure foreign_keys pragma is set on every connection.
db = SqliteDatabase('my_app.db', pragmas={'foreign_keys': 1})
```

In my testing the pragma works just fine when executed manually via `execute_sql()`. See:

```python

from peewee import *

db = SqliteDatabase(':memory:')

class User(Model):
    username = TextField()
    class Meta:
        database = db

class Tweet(Model):
    user = ForeignKeyField(User, backref='tweets', on_delete='cascade')
    content = TextField()
    class Meta:
        database = db

db.execute_sql('pragma foreign_keys=0')
db.create_tables([User, Tweet])

u1 = User.create(username='u1')
u2 = User.create(username='u2')
Tweet.create(user=u1, content='u1-t1')
Tweet.create(user=u2, content='u2-t1')

invalid_user = User(username='u3', id=999)
Tweet.create(user=invalid_user, content='invalid-user')

# Deleting a user will not delete the referenced tweets.
User.delete().where(User.username == 'u1').execute()
assert Tweet.select().count() == 3

db.execute_sql('pragma foreign_keys=1')

User.delete().where(User.username == 'u2').execute()
assert Tweet.select().count() == 2

try:
    Tweet.create(user=invalid_user, content='this fails')
except IntegrityError:
    pass
else:
    assert False, 'should not be able to create invalid fk ref'
```
Issue: Python 3.8.0 : reduce is not defined
Author: caryfitzhugh
State: closed
Created: 2020-03-20 20:41:26

Running with python 3.8.0 I run into errors where 'reduce is not defined'.

In looking at it peewee.py assumes that if you can 
```python
from collections.abc import Callable
```
then you don't need to import reduce.

In my environment at least, that is not accurate.

At the top of peewee.py (around line 192 in my latest version) - could we drop in this snippet?
```python
try:
    reduce
except NameError:
    from functools import reduce
```

> Running with python 3.8.0 I run into errors where 'reduce is not defined'.
> 
> In looking at it peewee.py assumes that if you can
> 
> ```python
> from collections.abc import Callable
> ```
> 
> then you don't need to import reduce.
> 
> In my environment at least, that is not accurate.
> 
> At the top of peewee.py (around line 192 in my latest version) - could we drop in this snippet?
> 
> ```python
> try:
>     reduce
> except NameError:
>     from functools import reduce
> ```
in python 3,reduce are moved to functools module.you can use this snippet


You must be using an old version of Peewee. For quite a long time it's had `from functools import reduce`.

https://github.com/coleifer/peewee/blob/master/peewee.py#L172

apologies.. I checked I was running the latest code, but - in debugging something else in that file I think my auto-save in vim reformatted some imports, and "fixed" the indentation on that one line without me knowing... Apologies again.  Thanks - all seems to work now.
Issue: Question on INSERT ON DUPLICATE KEY UPDATE
Author: eely22
State: closed
Created: 2020-03-18 17:44:24

This is more of a question than an issue as I am unsure of how to do something. I have a simple table like this:

```
class CarrierRateModel(Model):

    age = DoubleField(null=True)
    rate = DoubleField(null=True)
    rate_set_id = CharField(null=False, index=True)
    updated = BooleanField(default=False, index=True)

    class Meta:
        db_table = 'rate'
        # each rate should be unique to the rate set and age. this allows us to update rates later
        primary_key = CompositeKey('age', 'rate_set_id')
```

I am using the insert function to then upsert into this table. What I want to do is insert many rows and if the rate column changes when there is a duplicate, set the updated flag to True. In SQL, I would do it as such:

```
INSERT INTO rate (age, rate, rate_set_id, updated) 
VALUES (1.0, 3.0, 'abc123', 0) 
ON DUPLICATE KEY UPDATE updated = (rate != VALUES(rate) ), rate=VALUES(rate);
```

This works. It will insert into the table, if there is a duplicate it sets the rate to the new rate and the update flag to true if the new rate didn't equal the old one.

How would I do this with peewee? Reading through the docs, I had thought the [EXCLUDED helper](http://docs.peewee-orm.com/en/latest/peewee/api.html#EXCLUDED) was the way to go, so I tried this:

```
CarrierRateModel.insert(age=rate.age, rate=rate.rate, rate_set_id=rate.rate_set_id).on_conflict(
                update={
                    CarrierRateModel.rate: EXCLUDED.rate,
                    CarrierRateModel.updated: CarrierRateModel.rate != EXCLUDED.rate
                }
            ).execute()
```

But I just get the error:

```
pymysql.err.InternalError: (1054, "Unknown column 'EXCLUDED.rate' in 'field list'")
```

I am not quite sure what the EXCLUDED helper is supposed to do or how it works. Am I using this correctly? Is this not the right approach?

I even tried to be more literal and use fn.VALUES, but this just throws a SQL syntax error: 

```
CarrierRateModel.insert(age=rate.age, rate=rate.rate, rate_set_id=rate.rate_set_id).on_conflict(
                update={
                    CarrierRateModel.rate: fn.VALUES(rate),
                    CarrierRateModel.updated: CarrierRateModel.rate != fn.VALUES(rate)
                }
            ).execute()
```

How would I replicate that SQL? If I try to use any of the conflict_* parameters in on_conflict, I just get this: 

```
MySQL does not support the specification of where clauses or conflict targets for conflict resolution.
```

I am using MariaDB 10.3.20 and peewee 3.13.1  

Ok, nevermind, I figured it out. I just had the syntax wrong, this is it: 

```
CarrierRateModel.insert_many([model_to_dict(r) for r in rates[i:i + bulk_size]]).on_conflict(
                update={
                    CarrierRateModel.updated: (CarrierRateModel.rate != fn.VALUES(CarrierRateModel.rate)),
                    CarrierRateModel.rate: fn.VALUES(CarrierRateModel.rate)
                }
            ).execute()
```