akumoth/peewee-issues huggingface dataset:
Issue: Unable to create model with ManyToManyField
Author: skopz356
State: closed
Created: 2018-12-19 13:13:06

Hi, I am using mysql version 5.7.24 and when i try to create Model with ManyToMany relations i got 
error:
```


    raise errorclass(errno, errval)
peewee.IntegrityError: (1048, "Column 'chat_id' cannot be null")
```

Here is my code:

```
class User(BaseModel):
    name = peewee.CharField()
    surname = peewee.CharField()
    email = peewee.CharField()

    def __str__(self):
        return self.surname

lass Message(BaseModel):
    text = peewee.CharField()
    send_by = peewee.ForeignKeyField(User)

    def __str__(self):
        return self.text


class Chat(BaseModel, ManyToManyModel):
    message = ManyToManyField(Message, backref="chats")
    user = ManyToManyField(User, backref="users")


u =  User.get(id=1)
m = Message.get(id=1)
Chat.create(message=m, user=u)
```

I have created tables chat_message_through and chat_user_through.
Thanks for help


Read the docs: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#manytomany

And: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#manytomanyfield

I read the docs many times, but i still did not find what am i doing bad?
Issue: .on_conflict() update parameter does not adapt dicts (Postgres)
Author: lohrmann
State: closed
Created: 2018-12-19 00:06:54

When a record includes dictionary data for a JSONB column in postgres, the data is correctly adapted in insert or update statements, but fails to be adapted as part of the .on_conflict() clause's update parameter.

The following (simplified)

    record = {'someid': 'textid', 'tstp': datetime(2018, 12, 12, 15, 30, 30), 'somecolumn': 'text', 'data': {}}
    (SomeTable.insert(record)
     .on_conflict('update', conflict_target=[s.someid, s.tstp], update=record))

sends the following statement

    ('INSERT INTO "someschema"."sometable" ("someid", "tstp", "somecolumn", "data") VALUES 
        (%s, %s, %s, %s) 
    ON CONFLICT ("someid", "tstp") 
    DO UPDATE SET 
        "someid" = %s, "tstp" = %s, "somecolumn" = %s, "data" = %s 
    RETURNING "session"."id"'
    , ['textid', 
       '2018-12-12 15:30:30', 
       'text', 
       <psycopg2._json.Json object at 0x11215bd30>,
       'textid', 
       '2018-12-12 15:30:30', 
       'text', 
       {}])

resulting in a `peewee.ProgrammingError: can't adapt type 'dict'`.

I am using peewee 3.8.0. Is this intended behaviour? ~~As a workaround I'm using a try/except clause in combination with an insert statement (the more common operation), but this of course means two queries to the database in case of conflict. Are there any more efficient workarounds, e.g. manually pre-converting the dict into a psycopg2 json object?~~

Edit: Using `from psycopg2.extras import Json`, the dict can successfully be pre-converted and the .on_conflict() clause executes successfully. 
    

As a temporary workaround, try using the actual field objects instead of the field-names as strings, e.g.:

```python
record = {
    Session.someid: 'textid',
    Session.tstp: datetime.datetime(2018, 12, 12),
    Session.data: {},
}
```

Fixed by f59ee6d
Issue: Slower insert_many performance relative to sqlite3 executemany
Author: rpadmanabhan
State: closed
Created: 2018-12-18 17:10:08

Thank you for developing peewee, it is a great lightweight ORM !

I am inserting batches in peewee like this : 
```python
import peewee

db = peewee.SqliteDatabase('test_db.sqlite3',pragmas={'synchronous': 0,' 'journal_mode':'wal'})

with db.atomic(): # 10000 rows in this transaction
    for batch in peewee.chunked(to_insert,249):
        FooTable.insert_many(batch, fields=[Bar.seq, Bar.params_in, Bar.results_out]).on_conflict_ignore(True).execute()
```
I am able to insert twice as fast using sqlite3's executemany function. I am using the same PRAGMA options in both. The table has primary key on a string column seq.
 
``` python
import sqlite3

insert_query = ''' INSERT OR IGNORE INTO foo_table (seq, params_in, results_out) VALUES (?, ?, ?) '''
conn = sqlite3.connect('test_db.sqlite3')
conn.execute('PRAGMA synchronous = OFF')
conn.execute('PRAGMA journal_mode = wal')
conn.commit()

conn.executemany(insert_query, to_insert) # 10000 rows
conn.commit()
```

While the inserts are reasonably fast using peewee (~ 30000 inserts a sec) , I wanted to know why it is ~ 2x slower than using sqlite3, or is this much slowdown expected because of the abstraction layer provided by the ORM ?


Only 2x is really not as bad as I might have thought.

Yes, it's because peewee contains logic for converting python data-types to data-types that can be handled by the database. For example, if you're using `TimestampField` you work with `datetime` objects in Python code, but when it comes to interacting with the DB we convert this to an integer. So when you are inserting objects, peewee is efficient, but it still must make sure to apply all conversions to the application data before sending it to the db. Same applies when reading from a cursor.

The code in peewee is optimized and uses efficient algorithms, but its doing a lot for you to make life easier and there's a performance cost.

At the end of the day remember that it's python. It's slow compared to C. You want to go fast? Use C.

Thanks for the reply. For anyone else who stumbles on this. I was able to get similar performance as sqlite's executemany using using peewee's execute_sql like this. Not ideal, but is worth it for me because of large inserts i need to do and since this table has pretty simple data types. 

I like to use peewee for the other nice features like datetime you mentioned.

``` python
insert_query = ''' INSERT OR IGNORE INTO foo_table (seq, params_in, results_out) VALUES (?, ?, ?) '''
with db.atomic():
    for row in to_insert:
        db.execute_sql(insert_query, row)
```



For select queries, when you are iterating over lots of rows, here are some docs that may be helpful:

http://docs.peewee-orm.com/en/latest/peewee/querying.html#iterating-over-large-result-sets

There's also [sweepea](https://github.com/coleifer/sweepea), a sqlite query-builder I wrote using Cython that may be faster.
Issue: setdefault added to playhouse kv, for dict like setdefault behavior
Author: ascourge21
State: closed
Created: 2018-12-13 21:20:12

To make KeyValue behave more like the regular python dict, a setdefault is also added.
This is a feature that we regularly use in our team when the db isn't initialized with any value, especially for settings and config items.

Thnx.
Issue: Broken DeferredForeignKey inheritance
Author: NicolasCaous
State: closed
Created: 2018-12-12 20:54:40

This is quite a complicated issue. I will try to do my best to give as much information as possible.

## The code

```
from peewee import *
from models.base_model import BaseModel, BaseHistory

class Company(BaseModel):
    teste = TextField()
    user = DeferredForeignKey('User', null=True)

class CompanyHistory(Company, BaseHistory):
    pass
```

```
from peewee import *
from util.server import SERVER
import datetime

class BaseModel(Model):
    id = BigAutoField()
    created_at_utc = DateTimeField(default = lambda: datetime.datetime.utcnow())
    created_at_local = DateTimeField(default = lambda: datetime.datetime.now())

    class Meta:
        database = SERVER['database']
        legacy_table_names = False

class BaseHistory(Model):
    updated_at_utc = DateTimeField(default = lambda: datetime.datetime.utcnow())
    updated_at_local = DateTimeField(default = lambda: datetime.datetime.now())
```
## The problem

When trying to create `CompanyHistory` table inside the database, the following error message appears:

> 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 'NOT NULL)' at line 1")

"What? SQL Syntax? I don't control that, it must be peewee." I thought. So I dig it through:

Using pdb I was able to retrieve the parsed SQL query from peewee:

![screenshot from 2018-12-12 18-28-05](https://user-images.githubusercontent.com/24411365/49896869-b9e33180-fe3b-11e8-892a-f0bc28daad3a.png)

raw:
```
'CREATE TABLE IF NOT EXISTS `company_history` (`id` INTEGER AUTO_INCREMENT NOT NULL PRIMARY KEY, `created_at_utc` DATETIME NOT NULL, `created_at_local` DATETIME NOT NULL, `updated_at_utc` DATETIME NOT NULL, `updated_at_local` DATETIME NOT NULL, `teste` TEXT NOT NULL, `user`  NOT NULL)'
```

pretty:
```
CREATE TABLE IF NOT EXISTS `company_history` (
  `id` BIGINT AUTO_INCREMENT NOT NULL PRIMARY KEY,
  `created_at_utc` DATETIME NOT NULL,
  `created_at_local` DATETIME NOT NULL,
  `updated_at_utc` DATETIME NOT NULL,
  `updated_at_local` DATETIME NOT NULL,
  `teste` TEXT NOT NULL,
  `user`  NOT NULL
)
```

The type of the user column is missing. 

## Conclusion

Field inheritance is broken for `DeferredForeignKey`

## Workaround

redefine the field:
```
from peewee import *
from models.base_model import BaseModel, BaseHistory

class Company(BaseModel):
    teste = TextField()
    user = DeferredForeignKey('User', null=True)

class CompanyHistory(Company, BaseHistory):
    user = DeferredForeignKey('User', null=True)
    pass
```


Thanks for the report, I will look into it.

I would like to contribute in a solution as well, but it's hard to understand how the project is structured. Do you have any tips for where to begin?

The code's only a few thousand lines, definitely readable in one sitting. You can ignore everything except `peewee.py` for getting started.
Issue: Different result with 'or' - '|'
Author: BreX900
State: closed
Created: 2018-12-12 09:50:20

I'm not very good at English. I will show the error with two examples. The same examples should show the same result but it is not...

`#first example
f = (Product.asin_product=="NameOne")
f = f | (Product.asin_product=="NameTwo")
Product.select().where(f).execute()
#peewee - DEBUG - ('SELECT "t1"."asin_product" FROM "product" AS "t1" WHERE (("t1"."asin_product" = %s) OR ("t1"."asin_product" = %s))', ['NameOne', 'NameTwo'])

#Second example
s = (Product.asin_product == "NameOne")
s = s or (Product.asin_product == "NameTwo")
Product.select().where(s).execute()
#2018-12-12 10:46:28,754 - peewee - DEBUG - ('SELECT "t1"."asin_product" FROM "product" AS "t1" WHERE ("t1"."asin_product" = %s)', ['NameOne'])`

They should return the same query as a result, but it is not

Read the docs:

http://docs.peewee-orm.com/en/latest/peewee/query_operators.html
Issue: DeferredManyToManyField
Author: albireox
State: closed
Created: 2018-12-07 06:44:54

I hit a case in which I'd have liked to be able to use a `DeferredManyToManyField` with a `DeferredThroughModel`. In my case I was able to fix it rearranging the order of the model classes so that the `ManyToManyField` would not need to point to a deferred model, but I wonder if that will always be the case.

Ultimately, and to make things a bit cleaner, I wonder if it'd possible to add a `through_model` keyword to both `ForeignKeyField` and `DeferredForeignKey` which, if set, would make the foreign key behave as a `ManyToManyField`.

As you noticed, there's no such thing as a `DeferredManyToManyField`, nor do I think there needs to be. Remember that a many-to-many is just a junction table with foreign-keys to the table(s) being related via many-to-many. To use my favorite example,

```python

class Student(Model):
    name = TextField()
    # courses = ManyToManyField(Course) ??? there's no need to do this.

class Course(Model):
    name = TextField()
    students = ManyToManyField(Student)  # do this instead. many-to-many is symmetrical.

# Or, better yet, don't use ManyToManyField at all and just:
class CourseStudent(Model):
    course = ForeignKeyField(Course)
    student = ForeignKeyField(Student)
```

> I wonder if it'd possible to add a `through_model` keyword to both `ForeignKeyField` and `DeferredForeignKey` which, if set, would make the foreign key behave as a `ManyToManyField`.

Zen of Python:

> Special cases aren't special enough to break the rules.
> ...
> There should be one-- and preferably only one --obvious way to do it.
Issue: ManyToManyField and backref='+' fails
Author: albireox
State: closed
Created: 2018-12-07 01:28:07

It seems that if you have a `ManyToManyField` that uses a through model in which the foreign keys are defined with `backref='+'` it fails when trying to access the many-to-many field because it still tries to access the backref. The error is

```python
~/.pyenv/versions/3.7.1/Python.framework/Versions/3.7/lib/python3.7/site-packages/peewee.py in __get__(self, instance, instance_type, force_query)
   4609     def __get__(self, instance, instance_type=None, force_query=False):
   4610         if instance is not None:
-> 4611             if not force_query and isinstance(getattr(instance, self.src_fk.backref), list):
   4612                 return [getattr(obj, self.dest_fk.name) for obj in getattr(instance, self.src_fk.backref)]
   4613             else:

AttributeError: 'Target' object has no attribute '+'
```

From the [documentation](http://docs.peewee-orm.com/en/latest/peewee/relationships.html#manytomany):

> Peewee provides a field for representing many-to-many relationships, much like Django does. This feature was added due to many requests from users, but I strongly advocate against using it, since it conflates the idea of a field with a junction table and hidden joins. It’s just a nasty hack to provide convenient accessors.

Regarding the backref ... I'm not sure I completely understand, but it appears to me that disabling the backref on the ManyToManyField works correctly:

```python
class Student(Model):
    name = TextField()

class Course(Model):
    name = TextField()
    students = ManyToManyField(Student, backref='+')

CourseStudent = Course.students.get_through_model()

# ...
# It seems to work fine to use the `course.students` many-to-many accessor:
math = Course.create(name='math')
s = Student.create(name='s')
math.students.add(s)  # Works.
list(math.students)  # Works, returns [<Student: 1>]
```

And, as it would suggest, there is no backref on the `Student` model, e.g. there's no `student.courses` backref.

-------------------------

If you're talking about manually declaring a through-model with backrefs disabled on both sides...I have to ask: what are you even bothering with using `ManyToManyField` for?

I'm not saying there's not a bug in the code. I am, however, suggesting that you're making a foolish choice to try and use ManyToManyField for anything but the most trivial many-to-many implementations.
Issue: AttributeError: 'ValuesList' object has no attribute '_hash'
Author: lephuongbg
State: closed
Created: 2018-12-06 09:20:29

I'm trying to filter valid ids from a list of ids like this:

```python
ids = [(1,), (2,), (3,)]
values = ValuesList(ids, ('id',), alias='ids')
list(Users.select(values.c.id)
      .join(values, p.JOIN.RIGHT_OUTER, cls.id == values.c.id)
      .where(cls.id.is_null()))
```

Produced SQL is OK but as soon as the query returns any results:

```
Traceback (most recent call last):
  File "<input>", line 1, in <module>
  File "/var/task/.venv/src/peewee/peewee.py", line 3726, in __len__
    self.fill_cache()
  File "/var/task/.venv/src/peewee/peewee.py", line 3767, in fill_cache
    iterator.next()
  File "/var/task/.venv/src/peewee/peewee.py", line 3823, in next
    self.cursor_wrapper.iterate()
  File "/var/task/.venv/src/peewee/peewee.py", line 3739, in iterate
    self.initialize()  # Lazy initialization.
  File "/var/task/.venv/src/peewee/peewee.py", line 6555, in initialize
    self._initialize_columns()
  File "/var/task/.venv/src/peewee/peewee.py", line 6485, in _initialize_columns
    if isinstance(node, Column) and node.source == table:
```


What's the actual error? "ValuesList has no attribute source"?

edit: nevermind, replicated "AttributeError: 'ValuesList' object has no attribute '_hash'"
Issue: Support order by in aggregate function
Author: lephuongbg
State: closed
Created: 2018-12-05 07:29:52

PostgreSQL support ORDER BY in aggregate function (e.g `json_agg`, `array_agg`, etc.) like this:

```SQL
SELECT array_agg(
    "t1"."name" ORDER BY "t1"."name" ASC, "t1"."created_at" DESC
) AS "users" 
FROM "users" AS "t1"
```

This PR extend `fn` API to support this kind of query.

```
fn.array_agg(User.name).order_by(User.name.asc(), User.created_at.desc())
```

My own sense is that this is a niche usage and not widely supported.

Rather than implement a special-case for this, I'll suggest using `NodeList` and `SQL` to compose the appropriate "ORDER BY" clause.

Could you give an example on how to use NodeList with `fn`? Because for me:
+ `fn` wraps its arguments with parens and,
+ I couldn't figure out how to put `NodeList` as `fn` argument. 

```sql
SELECT array_agg(
    "t1"."name" ORDER BY "t1"."name" ASC, "t1"."created_at" DESC
) AS "users" 
```

```python
name_asc = NodeList((User.name, SQL('ORDER BY'), User.name.asc()))
...select(fn.array_agg(name_asc, User.created_at.desc()).alias('users'))
```

That's much harder to look at. Guess I just have to maintain a subclass in my code.

By the way, what do you think are the cons of supporting this in `fn`?
Issue: how  to select a random row with a group by clause in peewee
Author: Colaplusice
State: closed
Created: 2018-12-05 01:07:55

like this: select object_id, name, image_path
from
  (SELECT images.image_path AS image_path, objects.id AS object_id, objects.name
  FROM objects LEFT JOIN images ON images.object_id = objects.id
  ORDER BY RAND()) as z
group by z.object_id, z.name

the question has been asked in stackoverflow : [link](https://stackoverflow.com/questions/28514736/how-to-select-a-random-row-with-a-group-by-clause) ,but i don't know how to translate it to peewee. please help me...
i am stuck in there for a long time.


It can be done like this:

```python
objects_tbl = Table('objects')
images_tbl = Table('images')
subquery = (objects_tbl
            .select(images_tbl.c.image_path,
                    objects_tbl.c.id.alias('object_id'),
                    objects_tbl.c.name)
            .order_by(SQL('RAND()'))
            .join(images_tbl, join_type=JOIN.LEFT_OUTER, on=images_tbl.c.object_id == objects_tbl.c.id)
            .alias('z'))
query = (Select().select(subquery.c.object_id, subquery.c.name,
                         subquery.c.image_path)
         .from_(subquery)
         .group_by(subquery.c.object_id, subquery.c.name))
```

Output:
```SQL
--- query.sql()
SELECT "z"."object_id", "z"."name", "z"."image_path" 
FROM (
    SELECT "t1"."image_path", "t2"."id" AS "object_id", "t2"."name" 
    FROM "objects" AS "t2" 
    LEFT OUTER JOIN "images" AS "t1" ON ("t1"."object_id" = "t2"."id") 
    ORDER BY RAND()
) AS "z" GROUP BY "z"."object_id", "z"."name"
```

yeah, thank you very much, i solved the problem about 30 mininutes ago。 I found the answer in [charlesleifer.com/blog](http://charlesleifer.com/blog/querying-the-top-n-objects-per-group-with-peewee-orm/)
my code is this:

```
        random_query = (
            Sentence.select(Sentence, Level.difficulty)
            .join(Level, on=(Sentence.level_id == Level.id))
            .order_by(pw.fn.Rand())
            .where(
                Sentence.status == ResourceStatus.PUBLISHED,
                Level.status == ResourceStatus.PUBLISHED,
            )
        )
        sentences = (
            Sentence.select(
                random_query.c.status,
                random_query.c.content,
                random_query.c.id,
                random_query.c.level_id,
            )
            .from_(random_query)
            .group_by(random_query.c.difficulty)
        )
```

thank you very much~




That's great. Then we should also close the issue.

Good stuff, yep that example looks good.
Issue: Introspector: Don't strip _id unless foreign key
Author: cognifloyd
State: closed
Created: 2018-11-30 22:27:17

This avoids stripping _id from a field name when introspecting a table,
unless that column is a foreign key.

Resolves #1799 

This is a quick hack and should probably also change the interface of `introspect()` and `generate_models()` to include some new parameter.

Closing as a roughly equivalent patch was merged instead.
Issue: Specify charset on CharField
Author: tarlogic-osint
State: closed
Created: 2018-11-30 13:07:55

Peewee doesn't allow to specify the charset of CharField.

 I want to specify an utf8 charset in some name fields for which I don't need the utf8mb4 charset used by default in Mysql. This also generates problems with Unique indexes generated cause the length of varchars (utf8mb4 makes a char be 4 bytes in size). It throws the error `Specified key was too long; max key length is 767 bytes`.

I would be nice if that option could be included.




I'd typically think it would be better to use UTF8 when creating the database, e.g.:

```sql
CREATE DATABASE foobar CHARACTER SET utf8 COLLATE utf8_unicode_ci;
```

I understand the storage concerns, though. You should be able to add the charset as peewee supports arbitrary constraints:

```python
field = CharField(constraints=[SQL('CHARACTER SET utf8 COLLATE utf8_unicode_ci')])
```

First, thanks for the suggestion.

I tried to apply the next definition:
```python
name = CharField(unique=True, constraints=[SQL('CHARACTER SET utf8 COLLATE utf8_unicode_ci')])
```

But there was an SQL syntax error due the generated SQL , that was something similar to:

```SQL
CREATE TABLE foo (field VARCHAR(255) NOT NULL CHARACTER SET utf8 COLLATE utf8_unicode_ci);
```

The problem comes from the `NOT NULL`  which was put before of  `CHARACTER SET`. To solve this was necessary to set `NOT NULL` after of charset declaration, in this way:

```SQL
CREATE TABLE foo (field VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL);
```

To achieve the previous sentence from peewee I had to write the next declaration, which it is a bit tricky:

```python
field = CharField(unique=True, null=True, constraints=[SQL('CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL')])
```

So even if it is possible to reach the desire SQL statement this problem should be revised since my solution is just an ugly hack.


I just realize that `CHARACTER SET` is not available in SQLite, therefore if you put that in constraints parameter the table creation fails in SQLite (due to the generated SQL statement). Thus this is not a valid solution because don't allow to use both SQLite and Mysql in a transparent way. 

I encourage to solve this problem cause I believe that peewee it's a nice tool with a really nice interface, but this kind of issue could make them unusable in several cases.


No, I'm pretty sure this is just a MySQL thing. In postgres you set the encoding on the database. Sqlite is just utf8, as far as I know.

Typically I think its unusual to specify the charset on just a single column. Usually one would set a default charset on the database or maybe(?) the table.