akumoth/peewee-issues huggingface dataset:
Issue: Syntax for using json_each in subquery
Author: brandond
State: closed
Created: 2019-05-24 21:08:17

I'm trying to do something like this:

```sql
DELETE FROM tweet WHERE tweet.user_id IN (SELECT value FROM json_each('[1,2,3]'))
```

I have tried something like this, but it ends up selecting the literal string 'value' instead of the value column:

```python
inactive_users = peewee.Select(from_list=[peewee.fn.json_each('[1,2,3]')], columns=['value'])
dq = (Tweet
      .delete()
      .where(Tweet.user.in_(inactive_users)))
dq.execute()
```

In reality the JSON list is actually sourced from another query that's using the `json_group_array` aggregation to output a lengthy list of IDs that I am doing some post-processing on. If I simply pass the Python list in directly, it binds a host parameter for each list entry, which ends up overrunning the sqlite SQLITE_MAX_VARIABLE_NUMBER limit if the list is large; I'm trying to work around this by serializing the list back to a JSON string and then using the table-valued `json_each` function in a subquery to split it back out.

Hmm, I think I found something that works, although I'm not sure if it's the best way to do it:

```python
tve = peewee.fn.json_each('[1,2,3]').alias('tve')
inactive_users = peewee.Select(from_list=[tve], columns=[tve.c.value]).cte('inactive_users')
dq = (Tweet
      .delete()
      .where(Tweet.user_id << inactive_users)
      .with_cte(inactive_users))
dq.execute()

```

I guess the proper way to do this would be to have `json_each` registered as a TableFunction, but right now we only get to use it through `JSONField.children()`, unless I'm missing something.

Whoa, whoa... Slow down!

```python

id_list = [1, 2, 3]
id_json = json.dumps(id_list)

subq = Select(from_list=fn.json_each(id_json), columns=[Entity('value')])
query = Tweet.delete().where(Tweet.id.in_(subq))
```

This works fine for me.

I think the crux of the problem is that your "column" is being treated as a sql parameter rather than a column name. Hence I use `columns=[Entity('value')]` to indicate this is a name.

Thanks - that'll work! I couldn't figure out how to get it to recognize 'value' as a column name without binding it to something.

@coleifer - For the record, when I try this as shown in your example on a Select query I get the following error:
`TypeError: object of type 'Function' has no len()`

I think it should be:
```python
subq = Select(from_list=[fn.json_each(id_json)], columns=[Entity('value')])
```


Oh right, yep.
Issue: python 3.6.8  DateTimeField formats 无效
Author: itmeryn
State: closed
Created: 2019-05-23 06:09:26

 
![image](https://user-images.githubusercontent.com/18156931/58228949-d8424700-7d62-11e9-9da3-52b2f3186a78.png)
![image](https://user-images.githubusercontent.com/18156931/58229447-594e0e00-7d64-11e9-8333-1846d7f37ea0.png)
![image](https://user-images.githubusercontent.com/18156931/58229453-5d7a2b80-7d64-11e9-83db-02ae52daee7c.png)


Is there a question? Under certain conditions and depending on your database driver, the date/time may be returned from the database cursor as a string. Similarly if you're loading csv or json into your date/time-field, it may be a string.

按照截图的逻辑 就会直接返回value  字段中配置的format 并没有生效

English. Still not a clue what this issue is about.

According to the logic of the screenshot, the format directly returned in the value field does not take effect.
Issue: Auto change IntegerField to ForeignKeyField?
Author: dzpt
State: closed
Created: 2019-05-22 20:16:04

I have a model like this

```
class AdminUser(BaseModel):
    class Meta:
        table_name = '_admin_user'
    id = AutoField()
    group_id = IntegerField()
    group: AdminGroup = ForeignKeyField(AdminGroup)
```

After checking  type of `group_id` field by using `type(getattr(model, 'group_id')`, it becomes `ForeignKeyField `, not `IntegerField` any more.

But after moving group_id under group field, it won't happen any more, `group_id` type will be `IntergerField`
```
    group: AdminGroup = ForeignKeyField(AdminGroup)
    group_id = IntegerField()
```

Why is that and how do i preserve / get the correct field type of `*_id`?

When you write:

```python
group = ForeignKeyField(AdminGroup)
```

Peewee will add a column `group_id` which stores the related `AdminGroup.id` value. So you're stepping on your own toes.

Additionally, every `ForeignKeyField` by default will add a "<field_name>_id" descriptor to the model, which provides access to the underlying `AdminGroup.id` value (e.g., the integer, not an `AdminGroup` instance).

Just use `group = ForeignKeyField(AdminGroup)`. Peewee automatically creates an equivalent integer field for you at `group_id`.

Thank you, but i need to declare `group_id` for using on other part of my code.
Any chance to get the real type before bind that to `ObjectIdAccessor` or disable this feature?

So `group` and `group_id` are totally different values, then?

In that case you can either:

- set the `column_name` parameter of the `group` foreign key field
- set the `object_id_name` parameter of the `group` foreign key field to something other than `group_id` so it doesn't conflict
Issue: Rawquery with table name doesn't work?!
Author: dzpt
State: closed
Created: 2019-05-20 09:16:48

Mysql 5.7
Peewee 3.9.5
-

I use this code
`db.execute_sql("select count(*) from %s", ('_admin_group',))`

and got this
`peewee.ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''_admin_group'' at line 1")`

but this one works

`db.execute_sql("select * from _admin_group where id=%s", (1,))`

Is there any problem while working with table name?

You can't put the table-name in as a parameterized query with your MySQL driver. Simple as that. For instance, with SQLite, this comes up in other places, too -- like if you're creating an INDEX on an expression, you can't use parameter placeholders for the expression values.
Issue: How to generate sql to carry database name?
Author: ztj1993
State: closed
Created: 2019-05-15 10:26:05

model:
```
class AttrModel(Model):
    id = PrimaryKeyField(help_text='id')

    class Meta:
        table_name = 'attrs'
        database = MySQLDatabase(database='silo')
```
generate sql:
```
sql = AttrModel.select().sql()
print(sql)
```
result:
```
('SELECT `t1`.`id` FROM `attrs` AS `t1`', [])
```

I want result:
```
('SELECT `t1`.`id` FROM `attrs` AS `silo`.`t1`', [])
```

Can it be realized?



Yes, you can use the `schema` parameter:

```python
class AttrModel(Model):
    id = AutoField()  # NOTE: use AutoField, not PrimaryKeyField, which is deprecated.

    class Meta:
        table_name = 'attrs'
        schema = 'silo'

# AttrModel will now be queried like "select * from silo.attrs as t1".
```

@coleifer Thank you!
Issue: Get initial value of dirty fields.
Author: lalit-lintel
State: closed
Created: 2019-05-15 08:35:37

Peewee has a feature that manages dirty fields. 
It managing the list of fields name, which is changed, but not the old/initial value of those fields.

It is great if peewee also manages the old/initial value of fields when it gets changed.

There anything is available which can help to get the inital value of fields?

> It is great if peewee also manages the old/initial value of fields when it gets changed.
>
> There anything is available which can help to get the inital value of fields?

You need to manage this yourself, unfortunately.

Peewee has very simplistic tracking for when fields are assigned, but this was only designed to simplify the use-case of when you have a lot of columns but only need to save a subset of them.

If you need to find original values, you can always query the DB for the current state of the row data.

Another option would be to mess around with overriding `Model.__init__` to add extra logic for saving the initial data the model is instantiated with.

```python
class MyModel(Model):
    field1 = TextField()
    ... etc ...

    def __init__(self, **kwargs):
        super(MyModel, self).__init__(**kwargs)
        self._initial_data = self.__data__.copy()
```

> ```python
> class MyModel(Model):
>     field1 = TextField()
>     ... etc ...
> 
>     def __init__(self, **kwargs):
>         super(MyModel, self).__init__(**kwargs)
>         self._initial_data = self.__data__.copy()
> ```

Thanks for this hint, I got the expected result with this.
Issue: bulk_create throws NotNullViolation when DEFAULT constraint is present
Author: ymrzkrrs
State: closed
Created: 2019-05-14 14:22:39

I have the following model:
```
class Summary(Model):
    
    start_date = DateField(formats=['%Y-%m-%d'])
    end_date = DateField(formats=['%Y-%m-%d'], constraints=[SQL('DEFAULT CURRENT_DATE')])
    
    class Meta:
        database = db
        db_table = 'test_table'
```

If I insert using:
```
summary = Summary(start_date=date(2019,4,29))
summary.save()
```
it works. The debugging logs show:
```
2019-05-14 16:13:52 DEBUG  peewee:peewee:2928 - ('INSERT INTO "test_table" ("start_date") VALUES (%s) RETURNING "test_table"."id"', [datetime.date(2019, 4, 29)])
```

However if I try inserting using bulk_create:
```
Summary.bulk_create([summary], batch_size=100)
``` 
It throws:  
```
psycopg2.errors.NotNullViolation: null value in column "end_date" violates not-null constraint
```
The debugging logs show that the bulk_create query is different, which is causing the error.
```
2019-05-14 16:20:25 DEBUG  peewee:peewee:2928 - ('INSERT INTO "test_table" ("start_date", "end_date") VALUES (%s, %s) RETURNING "test_table"."id"', [datetime.date(2019, 4, 29), None])
```
I'm using PostgreSQL.

I switched to using `default=datetime.today` with bulk_create for this use case, as it fills the field value before inserting.

Peewee is not exactly aware of defaults implemented on the server. It can work with them, but it's not the same as using the `default=` python-side implementation.

To get bulk create w/a server-side default, use the `insert_many()` API instead:

```python
start_dates = [
    datetime.date(...),
    datetime.date(...),
    ...
]

Summary.insert_many([(date,) for date in start_dates], fields=[Summary.start_date]).execute()
```
Issue: Fix empty bulk_create error
Author: vicrac
State: closed
Created: 2019-05-13 16:40:40

I noticed that attempt to insert an empty bulk to database (sqlite in my case) is causing following error:
```
Traceback (most recent call last):
  File "/home/vicrac/peewee/peewee.py", line 2946, in execute_sql
    cursor.execute(sql, params or ())
sqlite3.OperationalError: incomplete input

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "/home/vicrac/peewee/tests/base.py", line 240, in inner
    method(self)
  File "/home/vicrac/peewee/tests/models.py", line 176, in test_empty_bulk_create
    User.bulk_create([])
  File "/home/vicrac/peewee/peewee.py", line 6016, in bulk_create
    res = cls.insert_many(accum, fields=fields).execute()
  File "/home/vicrac/peewee/peewee.py", line 1785, in inner
    return method(self, database, *args, **kwargs)
  File "/home/vicrac/peewee/peewee.py", line 1856, in execute
    return self._execute(database)
  File "/home/vicrac/peewee/peewee.py", line 2571, in _execute
    return super(Insert, self)._execute(database)
  File "/home/vicrac/peewee/peewee.py", line 2320, in _execute
    cursor = database.execute(self)
  File "/home/vicrac/peewee/peewee.py", line 2959, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/home/vicrac/peewee/peewee.py", line 2953, in execute_sql
    self.commit()
  File "/home/vicrac/peewee/peewee.py", line 2729, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/home/vicrac/peewee/peewee.py", line 183, in reraise
    raise value.with_traceback(tb)
  File "/home/vicrac/peewee/peewee.py", line 2946, in execute_sql
    cursor.execute(sql, params or ())
peewee.OperationalError: incomplete input
```
It took me some time to figure it out, so I fixed it the simplest possible way - return from method if list is empty. I added also a simple test for it (which currently passess).
Issue: SQLite: Check constraint to check if value is in list causes infinite loop
Author: HashFail
State: closed
Created: 2019-05-10 19:30:58

Maybe this isn't supported, but it seems like a parse error to me. 

Minimal example:

```python
from peewee import SqliteDatabase, CharField, Check
from playhouse.signals import Model

class Test(Model):
    database = SqliteDatabase('test.db')
    database.connect()
    test = CharField(constraints=Check("test in ('http','smb')"))

def main():
    database = SqliteDatabase('test.db')
    database.connect()
    Test._meta.database = database
    database.create_tables([Test])

if __name__ == '__main__':
    main()
```

Running this code causes an infinite loop in `create_tables`. The following works just fine in SQLite: 

```sql
create table test(test varchar CHECK(test in ('http','smb')));
insert into test values ('abc');
>> Error: CHECK constraint failed: test
```

EDIT: Version numbers are Python 3.6.8, peewee 3.9.5

Try

`constraints=[Check("test in ('http', 'smb')")]`

Oh, derp. Thanks!
Issue: user-defined function from int to string gives ValueError when printed
Author: isthisthat
State: closed
Created: 2019-05-07 10:58:40

Setup:

    from enum import Enum
    from peewee import *
    from playhouse.sqlite_ext import SqliteExtDatabase
    DNA = Enum('DNA', {"N":-1, "A":1, "T":2, "C":3, "G":4})
    db = Proxy()

    def toDNA(n):
        return DNA(n).name.upper()

    class Base(Model):
        abase = IntegerField(default=-1) # DNA
        class Meta:
            database = db

    def setupDB():
        dbs = SqliteExtDatabase("foo")
        db.initialize(dbs)
        db.connect(reuse_if_open=True)
        db.create_tables([Base], safe=True)
        db.register_function(toDNA, "to_dna", 1)
        # add dummy data
        Base.create(abase="-1")
        Base.create(abase="4")

    def query():
        return list(Base.select(fn.to_dna(Base.abase)).dicts())

    def query2():
        return list(Base.select(fn.to_dna(Base.abase).cast('NONE')).dicts())

If I run `query()` I get `invalid literal for int() with base 10: 'N'`
Then I tried casting as blob but I get zeros. `query2()` gives `[{'abase") AS NONE)': 0},`...

I basically want to save space by storing an int instead of a char but then be able to print it back easily. Any advice? Thank you.


Peewee looks at the field type when converting values coming back from the DB. What version of Peewee are you using, by chance? In more recent versions, the conversion logic was made a little bit more forgiving, allowing fallback to the original value.

This works, for ex:

```python
db = SqliteDatabase(':memory:')

class Num(Model):
    val = IntegerField()
    class Meta:
        database = db

@db.func()
def to_s(n):
    return {
        0: 'zero',
        1: 'one',
        2: 'two'}.get(n, 'too many')

Num.create_table()
Num.insert_many([(i,) for i in range(4)]).execute()

query = (Num
         .select(fn.to_s(Num.val).alias('val'))
         .order_by(Num.val))
for row in query:
    print(row.val)
# zero
# one
# two
# too many
```

For older versions, you can try:

```python
fn.to_dna(Base.abase, coerce=False)
```

> Then I tried casting as blob but I get zeros. query2() gives [{'abase") AS NONE)': 0},...

You might note that the key of the dict is all messed up. Sqlite does not give very helpful column names when you are selecting a function or something and do not provide an alias. You probably want to do:

```python
fn.to_dna(Base.abase).alias('dna')
```

So you can access it using a nice name.

> I basically want to save space by storing an int instead of a char but then be able to print it back easily. Any advice? Thank you.

An integer takes up 8 bytes (sqlite uses 64-bit integers). A char takes up a single byte. So you might rethink that as well...because your decision is 8x more wasteful.

Oh dear! thanks a lot for the advice.
Issue: SQL Syntax Error in Model.save() when row is empty
Author: 0az
State: closed
Created: 2019-05-06 07:47:21

When `save()` is called on a row with nothing to save, there is a SQL syntax error caused by a lack of fields to update: `('UPDATE "foo" SET  WHERE ("foo"."foo" = ?)', [0])`.

<details><summary>MCVE</summary>

    from peewee import IntegerField, Model, SqliteDatabase
    
    db = SqliteDatabase(':memory:')    
    
    
    class Foo(BaseModel):
        foo = IntegerField(null=True)

        class Meta:
            database = db
    
    
    db.create_tables([Foo])
    
    foo = Foo.create()
    
    # peewee.OperationalError: near "WHERE": syntax error
    # ('UPDATE "foo" SET  WHERE ("foo"."foo" = ?)', [0])
    foo.save()

</details>

This seems contrived and I'm wondering how it even would come about in practice. Your model definition does not match the UPDATE statement you've shared, either.

When you instantiate a model, it has an empty data dict. If you call `.create()` without any kwargs, this is effectively the same as instantiating w/no data. So when you go to save this model in a subsequent call, having not set any values, there's nothing going on there. I've chosen to raise a `ValueError` in this case, but I do believe that in practice it would take some rather contrived workflow to actually encounter this.

It came about because I misread docs, and called `.save` immediately after `.create`. The lack of a useful error (now fixed) is why I brought it up, as it took an hour and a fresh set of eyes to figure out why.
Issue: Replace deprecated PrimaryKeyField with AutoField in docs
Author: 0az
State: closed
Created: 2019-05-06 07:20:59

<details>
<summary>Grep after change</summary>

    > rg -i 'PrimaryKeyField'
    CHANGELOG.md
    885:Re-add a shim for `PrimaryKeyField` (renamed to `AutoField`) and log a
    
    tests/fields.py
    450:class TestCompositePrimaryKeyField(ModelTestCase):
    
    peewee.py
    124:    'PrimaryKeyField',  # XXX: Deprecated, change to AutoField.
    4346:class PrimaryKeyField(AutoField):
    4348:        __deprecated__('"PrimaryKeyField" has been renamed to "AutoField". '
    4351:        super(PrimaryKeyField, self).__init__(*args, **kwargs)
    
    docs/peewee/changes.rst
    56:* :py:class:`PrimaryKeyField` has been renamed to :py:class:`AutoField`

</details>

Danke.
Issue: Foreign Key creation during create_table does not use reference field length
Author: geonyoro
State: closed
Created: 2019-05-03 14:38:06

I have a table using InnoDB engine in MySQL. The field I want to reference is a varchar of 51. 
Using the create_table or create_tables([Table]) syntax to create it, the created foreign key field is varchar of 255, leading an error #150.

What exactly is the error you are encountering? The linked ticket does not strike me as being relevant.

How is this varchar foreign-key field being declared? How is the field it is pointing to declared?

```python
class ReleaseASINMap(BaseModel):
    release_id = peewee.CharField(max_length=51, unique=True, null=True)

class ReverseStringMap(BaseModel):
    release_map = peewee.ForeignKeyField(ReleaseASINMap, field="release_id", 
            backref='reverse_map',
            on_delete="CASCADE")

peewee.InternalError: (1005, u"Can't create table 'repricer.reverse_string_map' (errno: 150)"
```
Maybe I'm on to nothing here. It did work on my local, not in production, and by googling I see the #150 error is related to foreign keys.

Note: please format your code in the future. For fuck's sake it is not hard.

The errno: 150 is coming from MySQL - not Peewee...

So if I look at the generated SQL for the CREATE TABLE query looks OK to me:

```sql
CREATE TABLE IF NOT EXISTS `reverse_string_map` (
  `id` INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, 
  `release_map_id` VARCHAR(51) NOT NULL, 
  FOREIGN KEY (`release_map_id`) 
      REFERENCES `release_asin_map` (`release_id`) 
      ON DELETE CASCADE)
```

You may want to check your MySQL manual, verify the storage engine you are using (you need InnoDB), etc, etc. This SO answer may help:

https://stackoverflow.com/questions/1457305/mysql-creating-tables-with-foreign-keys-giving-errno-150

The following code works fine for me and does not produce any error, so I'll suggest you've got some problem on your side and that this isn't a bug in Peewee:

```python
from peewee import *

db = MySQLDatabase('peewee_test')

class BaseModel(Model):
    class Meta:
        legacy_table_names = False
        database = db

class ReleaseASINMap(BaseModel):
    release_id = CharField(max_length=51, unique=True, null=True)

class ReverseStringMap(BaseModel):
    release_map = ForeignKeyField(ReleaseASINMap, field="release_id",
            backref='reverse_map',
            on_delete="CASCADE")

db.create_tables([ReleaseASINMap, ReverseStringMap])
db.drop_tables([ReleaseASINMap, ReverseStringMap])
```

Noted the issue. It was with the collations difference. Database itself is on latin1 collation, some tables are on utf8. Thanks for that.
Issue: Documentation: playhouse.reflection module does not contain generate_models
Author: erezmarmor
State: closed
Created: 2019-04-30 12:50:00

**peewee version=3.6.4**
both these pages (there might be more, hadn't checked):
- http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#reflection
- http://docs.peewee-orm.com/en/latest/peewee/interactive.html#interactive

the documentation provides this import as an example:
`from playhouse.reflection import generate_models`

however this function is in fact a method of Introspector class (i.e. not available from the module scope).

the proper statement might look like:
```
from playhouse.reflection import Introspector
db = PostgresqlDatabase('my_app')
models = Introspector.from_database(db).generate_models()
```

False.

```python
In [1]: from playhouse.reflection import generate_models

In [2]: from peewee import PostgresqlDatabase

In [3]: db = PostgresqlDatabase('my_app')

In [4]: models = generate_models(db)  # models is now a dict of table name -> model class.
```

![s1556631726 02](https://user-images.githubusercontent.com/119974/56965970-df8b8180-6b23-11e9-8640-a12b2d1266de.png)


![image](https://user-images.githubusercontent.com/16885498/57012343-a7ad3880-6c0e-11e9-8356-7587bd0cd580.png)
@coleifer , are there known compatibility issues with python3?

No, sorry I didn't see you had an older version. The generate_models helper was added here:

https://github.com/coleifer/peewee/commit/23dad105456bf4034d3ee21c6d29e56a7e2b9c7a