akumoth/peewee-issues huggingface dataset:
Issue: ThreadSafeDatabaseMetadata in doc is not compatible with inheritance
Author: sherrrrr
State: closed
Created: 2021-09-09 03:05:37

[ThreadSafeDatabaseMetadata in doc](http://docs.peewee-orm.com/en/latest/peewee/database.html#thread-safety-and-multiple-databases)
```python
import threading
from peewee import Metadata

class ThreadSafeDatabaseMetadata(Metadata):
    def __init__(self, *args, **kwargs):
        # database attribute is stored in a thread-local.
        self._local = threading.local()
        super(ThreadSafeDatabaseMetadata, self).__init__(*args, **kwargs)

    def _get_db(self):
        return getattr(self._local, 'database', self._database)
    def _set_db(self, db):
        self._local.database = self._database = db
    database = property(_get_db, _set_db)


class BaseModel(Model):
    class Meta:
        # Instruct peewee to use our thread-safe metadata implementation.
        model_metadata_class = ThreadSafeDatabaseMetadata
```

when you try to use a baseModel
```python
class BaseModel(Model):
    class Meta:
        database = db
        model_metadata_class = ThreadSafeDatabaseMetadata

class User(BaseModel):
    id = BigAutoField(primary_key=True)
```

`User._meta.database` will not correctly inherit `BaseMoel._meta.database` because User's meta_options init in this way.
```
            for k in base_meta.__dict__:
                if k in all_inheritable and k not in meta_options:
                    meta_options[k] = base_meta.__dict__[k]
```
where `database` is not in `base_meta.__dict__` but `_database`.


This is expected, if somewhat non-obvious. The whole purpose of using the `ThreadSafeDatabaseMetadata` is for when you plan to switch the database at run-time in a multi-threaded application, so the assumption is that at multiple points during runtime you will be explicitly binding the models to a database, e.g.:

```python

# BaseModel declares that it uses ThreadSafeDatabaseMetadata

class A(BaseModel):
    pass

class B(BaseModel):
    pass

master = PostgresqlDatabase(...)
replica = PostgresqlDatabase(...)

# Explicitly bind the models.
master.bind([A, B])
```

With that said, it *should* still inherit the "default" or original Meta.database, so I will put together a patch for this.

b782657823a64fcdef6674cadccb2f2679933e6f
Issue: Multiple condition splicing
Author: Baloneo
State: closed
Created: 2021-08-26 03:36:40

Sometimes the front end will pass an empty string, if where condition like this:
```
ProductType.demand_side == ""
```
will not find results, so I used the following code

```python
name = current_schema_data.get('name')
demand_side = current_schema_data.get('demand_side')

expression = 1
if name:
    expression &= (ProductType.product_name == name)

if demand_side:
    expression &= (ProductType.demand_side == demand_side)

sql = ProductType.select().where(expression)
print(sql)
```

```
SELECT `t1`.`id`, `t1`.`create_time`, `t1`.`update_time`, `t1`.`product_name`, `t1`.`fk_organization_id`, `t1`.`unit`, `t1`.`type`, `t1`.`calc_type`, `t1`.`demand_side` FROM `product_type` AS `t1` WHERE (1 AND (`t1`.`demand_side` = '34')) LIMIT 100 OFFSET 0
```

It always has `Where(1 and FOO)` or `Where 1`, How to remove it?
I have try using `expression = None`, But the results it found are all empty


There are a couple ways, but probably the one I recommend for dynamically building up a query is to use reduce:

```python

exprs = []
if name:
    exprs.append(ProductType.name == name)
if other:
    exprs.append(ProductType.other == other)

if exprs:
    query = query.where(reduce(operator.and_, exprs))
```

Since you're using the same operator (AND) you can also just chain the calls:

```python

query = ProductType.select()
if name:
    query = query.where(ProductType.name == name)
if other:
    query = query.where(ProductType.other == other)
...
```

thanks!
Issue: UUIDField with bulk_update fails
Author: milesgranger
State: closed
Created: 2021-08-20 18:47:28

Summary
---
It appears using `Model.bulk_update` with a model using a `UUIDField` fails.

Minimal Example
---
```python
def test_uuid_bulk_update(db):

    n_rows = 1_000

    class Foo(peewee.Model):
        class Meta:
            database = db

        pk = peewee.IntegerField(primary_key=True)
        bar = peewee.UUIDField()

    if Foo.table_exists():
        Foo.drop_table()
    Foo.create_table()

    data = [
        {"pk": i, "bar": uuid.uuid3(uuid.NAMESPACE_URL, f"{i}")} for i in range(n_rows)
    ]

    with db.atomic():
        Foo.bulk_create((Foo(**kwargs) for kwargs in data))

    updates = [
        {"pk": item["pk"], "bar": uuid.uuid3(uuid.NAMESPACE_URL, f"{i*1.5}")}
        for i, item in enumerate(data[:100])
    ]


    # Saving them individually works fine.
    # for update in updates: 
    #  Foo(**update).save()
    
    #  This fails.
    Foo.bulk_update([Foo(**kwargs) for kwargs in updates], fields=["bar"])
```

Using a `TextField` instead will work fine in the code above.


Error
---
```
>         with __exception_wrapper__:
            cursor = self.cursor(commit)
            try:
               cursor.execute(sql, params or ())
E               psycopg2.errors.DatatypeMismatch: column "bar" is of type uuid but expression is of type text
E               LINE 1: UPDATE "foo" SET "bar" = CASE "foo"."pk" WHEN 0 THEN 'e0a687...
E                                                ^
E               HINT:  You will need to rewrite or cast the expression.
```


See #2308
Issue: Is there a module that implements Mock?
Author: ekiim
State: closed
Created: 2021-08-14 02:04:42

Hello, I'm trying to find out if there is a module for mocking for models.

I know it's a small project, I'm interested in finding one. 

No, mocking a sql database is an extremely complex affair. Most people use shallow mocks or just an in memory sqlite database for testing.
Issue: Update api.rst
Author: aparcar
State: closed
Created: 2021-08-08 00:12:24

The example is wrong since it doesn't split the `idlist`.

This is the same function as used in the test. However the example has a case with an empty list (`[]`) which won't work, since `None.split(",")` won't work. Any simple solutions here?

Actually the solution is as simple as `(idlist.split(",") if idlist else [])`

a643a6952372e22ce658c8ca9a23322bb4b2ab57
Issue: Insert Many\Upsert (PostgreSQL) doesn't work with M2M relations
Author: madMathematician971
State: closed
Created: 2021-08-06 10:51:31

Hello everyone,

Trying to utilize an upsert along with an insert_many query for a model with m2m relation. 

The error I get is `'ManyToManyField' object has no attribute '_sort_key'`


The code example is provided below. Trying to figure out if this is expected behavior due to the fact that ManyToManyField is not a usual peewee field in the default sense or if it's a bug.

```
class Region(_BaseModel):
    name = pw.CharField(unique=True)


class Website(_BaseModel):
    name = pw.CharField()
    url = pw.CharField(unique=True)
    regions = pw.ManyToManyField(Region, backref='websites')

WebsiteRegion = Website.regions.get_through_model()
```

(_Base model has a UUID4 id assigned to it)

However, when I'm trying to use an upsert which was implemented before M2M relation was added to Website Model and worked just fine, I get the error listed above ('ManyToManyField' object has no attribute '_sort_key')


The upsert setup:

```
query = Website.insert_many(self.data).returning(*self.__RETURNING).on_conflict(
             conflict_target=[Website.url],
             preserve=[Website.url],
             update={
                 Website.name: EXCLUDED.name,
                 Website.regions: EXCLUDED.regions,
                     }
                 )
```

where self.data is a valid list of pydantic models converted to dicts and self.__RETURNING is a valid list of `Website` model attributes desired to be returned.

Perhaps I am missing something required for m2m to work in the upsert?

I already have a workaround utilizing a loop with `website = Website.create(<data>)` and a default `website.regions.add(regions: List[UUID])` for populating m2m, but still having a working upsert for m2m would be great if possible, as create and update in a loop is less efficient than an upsert.


Anyone faced a similar issue with upsert and m2m before and what were your solutions \ workarounds?
Appreciate your time!


How is an upsert supposed to work across multiple tables? I think you do not understand how many-to-many actually works -- there is a junction table. There is no such thing as website.regions - that information is stored in the through table.
Issue: Problem with ended parentheses alias 
Author: wes9319
State: closed
Created: 2021-08-05 20:13:40

I had a problem trying to query data and assign an alias ended by a parentheses to each row, the data indeed is correct but the last parentheses of the alias gets cut off in the result data. Here's an example:
```
SUM(IF((((`t2`.`class` IN (%s, %s, %s)) AND (`t2`.`concept` != %s)) AND (`t3`.`cost_id` = %s)), IFNULL((`t2`.`value` * (`t3`.`value` / %s)), %s), %s)) AS `cost center (CCA)`, 
```
The result is:
```
'cost center (CCA': Decimal('139.704440000000')
```
The value is correct but the the alias is wrong due to the missing parentheses, is it possible to fix this in any way?

I don't know if I can change this logic without introducing a possible regression for users who select arbitrary sql functions and do not alias them. The current logic looks if there's a "." within the column name, takes the stuff to the right of it and strips `"` and `)`, e.g.:

UPPER("t1"."name") -> "name") -> name

The downside is that this logic is also being applied to explicitly-given aliases. Let me think about it.

The fix involved basically ensuring that user-provided aliases would be preserved, but at the same time I've tried to improve the logic when guessing at the intended destination column when it is not provided.
Issue: Accessing views via models not possible
Author: SilvanVerhoeven
State: closed
Created: 2021-08-03 21:38:10

I try to access a view in my SQLite database following the instructions from https://stackoverflow.com/questions/38707331/does-peewee-support-interaction-with-mysql-views. I figured that the article's `db_table` needs to be replaced with `table_name` in the current version of peewee. I used the following SQL statement to create the view: `db.execute_sql('CREATE VIEW IF NOT EXISTS foo_view AS SELECT UPPER(name) AS name FROM foo')`.

Unfortunately, even after this update my `FooView` model only returns `None` for the name. Printing the vars via `print([vars(fv) for fv in FooView.select(FooView.name)])` shows why:  
`[{'__data__': {}, '_dirty': set(), '__rel__': {}, 't1': 'HUEY'}, {'__data__': {}, '_dirty': set(), '__rel__': {}, 't1': 'MICKEY'}, {'__data__': {}, '_dirty': set(), '__rel__': {}, 't1': 'ZAIZEE'}]`

Compare this to the output of `print([vars(fv) for fv in Foo.select(Foo.name)])`:  
`[{'__data__': {'name': 'huey'}, '_dirty': set(), '__rel__': {}}, {'__data__': {'name': 'mickey'}, '_dirty': set(), '__rel__': {}}, {'__data__': {'name': 'zaizee'}, '_dirty': set(), '__rel__': {}}}]`

Interestingly enough, setting `Foo.Meta.table_name` to `foo_view` yields the same result as simply using `FooView`.

I wonder if this is a bug in peewee or whether I am doing something wrong. Help much appreciated!

What version of sqlite are you using?

The requirements.txt requires peewee 3.9.6

No, what version of sqlite.

I'm sorry. SQLite 3.31.1 (retrieved via `select sqlite_version()`)

This example code is working fine for me, I suppose you have some issue in your code or you can try updating your peewee if the code below does not work for you:

```python
from peewee import *

db = SqliteDatabase(':memory:')

class Foo(Model):
    name = TextField()
    class Meta: database = db

db.create_tables([Foo])
for name in ('huey', 'mickey', 'zaizee'):
    Foo.create(name=name)

db.execute_sql('create view foo_view as select upper(name) as name from foo')
class FooView(Foo):
    class Meta:
        primary_key = False
        table_name = 'foo_view'

print([row.name for row in FooView.select()])
```

Prints: `['HUEY', 'MICKEY', 'ZAIZEE']`
Issue: SQL from migrator
Author: konradkur
State: closed
Created: 2021-07-29 16:06:18

Hello my friends,

`migrate(
    migrator.add_column('some_table', 'title', title_field),
    migrator.add_column('some_table', 'status', status_field),
    migrator.drop_column('some_table', 'old_column'),
)`

how to get SQL code?

thanks, Konrad

This is not really exposed. Operations may be composed of multiple steps, so the migrations module uses an abstraction (`Operation`) to run potentially multiple queries. Additionally, steps may include validation that requires a previous step having been run (before the sql is generated). How do you get the sql for an operation in general, though (which may be composed of sub-operations)?

```python
# i think this should, generally, work
op = migrator.add_column('my_sqlite_table', 'new_col', TextField(default=''))
q = [op]
while q:
    item = q.pop()
    if isinstance(item, Operation):
        kw = item.kwargs.copy()
        kw['with_context'] = True
        q.append(getattr(migrator, item.method)(*item.args, **kw))
    elif isinstance(item, list):
        q.extend(item[::-1])
    elif isinstance(item, Node):
        print(migrator.make_context().sql(item).query())
    else:
        print(item.query())
```

Alternatively, you can attach a sql log handler:

```python
import logging
logger = logging.getLogger('peewee')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)
```
Issue: Dynamic table names refer to global variables
Author: Baloneo
State: closed
Created: 2021-07-16 09:06:40

```
GLOBAL_VAR_TABLE_NAME = "foo_table"
def make_table_name(model_class):
    model_name = model_class.__name__
    return GLOBAL_VAR_TABLE_NAME

class BaseModel(Model):
    class Meta:
        table_function = make_table_name

class User(BaseModel):
    # table_name will be "user_tbl".
 

```

update global variable
```
GLOBAL_VAR_TABLE_NAME = "bar_table"
```

but 
```
User().save()
```
still is foo_table, why?


table name is not a pointer, it is set and stored in the model class' `table_name` attribute. If you want to change it at run-time, then you need to call `ModelClass._meta.set_table_name()`.
Issue: This ORM doesn't seem to work anymore.
Author: Evert-Arends
State: closed
Created: 2021-07-15 18:38:26

I'm using Python 3.9.6, and the example provided in the readme is broken:

```
import datetime

import peewee

db = peewee.SqliteDatabase('my_database.db')


class BaseModel(peewee.Model):
    class Meta:
        database = db


class User(BaseModel):
    username = peewee.CharField(unique=True)


class Tweet(BaseModel):
    user = peewee.ForeignKeyField(User, backref='tweets')
    message = peewee.TextField()
    created_date = peewee.DateTimeField(default=datetime.datetime.now)
    is_published = peewee.BooleanField(default=True)


db.connect()
db.create_tables([User, Tweet])
charlie = User.create(username='charlie')
huey = User(username='huey')
huey.save()

# No need to set `is_published` or `created_date` since they
# will just use the default values we specified.
Tweet.create(user=charlie, message='My first tweet')
```

Accourding to the readme this code should be working? I would expect a database with 2 tables and some data in it. The db is created but empty.

I want to apologize. This worked like a charm. My database tool however is completely fucked. Sorry for the inconvenience.
Issue: Fixes bug in JSON IN query for iterables
Author: dustinrb
State: closed
Created: 2021-07-05 14:04:18

Resolves issue #2444

I don't like this patch for a couple of reasons, nor am I sure I understand the goal behind supporting such a query. Namely because it mutates the expression state when sql() is called, which is not done elsewhere in the codebase. Additionally, searching for an exact array within a json field seems problematic because there's no possibility of doing so efficiently from the database perspective.

I'm going to pass on this patch, but will look at the original issue and give it some thought.
Issue: IN queries on  fields with iterable Python representations
Author: dustinrb
State: closed
Created: 2021-07-05 14:03:13

When using arrays in a JSON field, the IN operator unpacks all the values into a single list. For example:

```python
itr_select_query = (KeyData
    .select()
    .where(KeyData.data.in_(
        [[1], [2], [1,2]]
    )))
```

will generate the SQL query:
```python
('SELECT "t1"."id", "t1"."key", "t1"."data" FROM "key_data" AS "t1" WHERE ("t1"."data" IN ((json(?)), (json(?)), (json(?), json(?))))', ['1', '2', '1', '2'])
```

instead of:
```python
('SELECT "t1"."id", "t1"."key", "t1"."data" FROM "key_data" AS "t1" WHERE ("t1"."data" IN ((json(?)), (json(?)), (json(?), json(?))))', ['[1]', '[2]', '[1, 2]'])
```

I see why you closed #2445;  it didn't make sense to single out the IN clause in that part of the code.

I think the broader issue is that you cannot use any field for an IN query where it's Python representation is a sequence. You might be able to get around this by calling

```python
...where(KeyData.data.in_(KeyData.data.db_value(i) in [[1], [2], [1,2]]))...
```
but that seems unintuitive from the user perspective. 

Perhaps the better solution would be to handle this in the `Field._in` with the understanding that if the user provides an iterable that each item has `Field.db_value` called on it before it's formed into an Expression.

I'm happy to work on a patch. I'm just too unfamiliar with codebase to architect this.

I've implemented a [fix](https://github.com/dustinrb/peewee/tree/json_in_query) that converts `multi_types` to Values in the Expression generated by the `.in_` function instead of mutating one SQL generation.

This process occurs outside of a Context block. Will that pose any issues the node tree when generating SQL? 

I looked at the patch and it has the same problem.

For now, you can always try:

```python
vls = [Value(l, unpack=False) for l in list_of_lists]
.where(KeyData.data.in_(vls))
```

Thanks for looking into this.

Maybe I'm not sure what the exact issue is. Your interim fix is essentially what my second patch does in the Field class which happens before SQL generation. Are you saying that query input should be untouched before it's handed over to the SQL generating code? In that case it feels like a better solution would be to add a flag to allow shallow unpacking for a single Expression and/or NodeList.

Thanks for letting me satisfy my curiosity. Let me know if you'd like me to keep working on a fix.

This is a bit of a strange one, so I'm going to close for now, but will revisit should it occur in another context.
Issue: how should  i use ModelSelect.select()?
Author: CooperWanng
State: closed
Created: 2021-07-02 07:52:58

I ran into a problem when I used select function of ModelSelect
>query = Auth.select(Auth.id)  # Model.select()
select t1.id FROM auth AS t1;
query = Auth.select(Auth.id).select()  # ModelSelect.select()
> select FROM auth AS t1;

how should i do?



If you are explicitly re-selecting columns, you need to specify which columns you want.

> If you are explicitly re-selecting columns, you need to specify which columns you want.

thanks,the usage of peewee differs between the two versions(3.6->3.14)
Issue: migrate leave foreignkey  out of consideration
Author: woshimanong1990
State: closed
Created: 2021-06-30 03:55:50

I drop a field to a tabel ,but not I success. Finally, I found, it's a bug.
you create a tmp table and then, drop old tabel. BUT, leave foreignkey  out of consideration
`      
        # Find any foreign keys we may need to remove.

        self.database.get_foreign_keys(table)
`

do nothing
I used sqlite.
maybe I should use `PRAGMA foreign_keys=OFF` first

The foreign keys are returned by `pragma foreign_key_list("...")`. I'm not sure I understand the issue you're describing.
Issue: migrator.add_column throws error on column with default value for sqlite
Author: binh-vu
State: closed
Created: 2021-06-30 03:13:57

Peewee version: `3.14.4`
SQLite version: `3.33.0`
When I run,
```
migrator = SqliteMigrator(db)

with db.atomic():
    migrate(
        migrator.add_column("table", 'is_deleted', BooleanField(default=True)),
    )
```

It throws the following error:
```
peewee.DatabaseError: database disk image is malformed
```
This behavior contradicts to the [documentation](https://docs.peewee-orm.com/en/latest/peewee/playhouse.html#supported-operations)

I investigated more and found that:

* When the table is empty, it executes this query: `ALTER TABLE "table" ADD COLUMN "is_deleted" INTEGER`
* However, then the table is not empty, it executes this query: `UPDATE "table" SET "is_deleted" = ?`. Manually executing the query on my sqlite database, it throws the error: `Result: no such column: is_deleted`. 
* I dump the content of the database to an sql file and re-import them on new database but still get the same error.
* Interestingly, `add_column` (nullable), then `apply_default` also doesn't work and I got the same error. Turns out the query `UPDATE ...` causing the issue. So I need to do it in one query `ALTER TABLE "table" ADD COLUMN "is_deleted" INTEGER NOT NULL DEFAULT 0`.

Closing the issue. I found the root of this problem.

I added the new field to my model definition with index. When I run my migration script, peewee saw a new field, it doesn't create the column. However, it creates the index. So the database has an index of an unknown column. A query to create column is fine, but an update query to that column throws error in python. Dropping the index and it works as expected.