akumoth/peewee-issues huggingface dataset:
Issue: peewee.ProgrammingError: syntax error at or near "RETURNING"
Author: ernest-andela
State: closed
Created: 2019-06-16 01:50:51

I am getting the error below and l don't know what is causing this error 

I am saving this into a model using Redshift Postgres Database: 


```
  etl_dic = { "email": "email"  }


  File "/Users/ernestappiah/Desktop/FlaskApplications/AirflowETL/terravion/service/subscriptions/operations/subscription.py", line 220, in dump_android_to_db
    SubscriptionETLModel.insert(etl_dic).execute()
  File "/Users/ernestappiah/Desktop/FlaskApplications/Environments/env_terravion/terravion_env/lib/python3.6/site-packages/peewee.py", line 1785, in inner
    return method(self, database, *args, **kwargs)
  File "/Users/ernestappiah/Desktop/FlaskApplications/Environments/env_terravion/terravion_env/lib/python3.6/site-packages/peewee.py", line 1856, in execute
    return self._execute(database)
  File "/Users/ernestappiah/Desktop/FlaskApplications/Environments/env_terravion/terravion_env/lib/python3.6/site-packages/peewee.py", line 2572, in _execute
    return super(Insert, self)._execute(database)
  File "/Users/ernestappiah/Desktop/FlaskApplications/Environments/env_terravion/terravion_env/lib/python3.6/site-packages/peewee.py", line 2318, in _execute
    cursor = self.execute_returning(database)
  File "/Users/ernestappiah/Desktop/FlaskApplications/Environments/env_terravion/terravion_env/lib/python3.6/site-packages/peewee.py", line 2325, in execute_returning
    cursor = database.execute(self)
  File "/Users/ernestappiah/Desktop/FlaskApplications/Environments/env_terravion/terravion_env/lib/python3.6/site-packages/peewee.py", line 2962, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/Users/ernestappiah/Desktop/FlaskApplications/Environments/env_terravion/terravion_env/lib/python3.6/site-packages/peewee.py", line 2956, in execute_sql
    self.commit()
  File "/Users/ernestappiah/Desktop/FlaskApplications/Environments/env_terravion/terravion_env/lib/python3.6/site-packages/peewee.py", line 2732, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/Users/ernestappiah/Desktop/FlaskApplications/Environments/env_terravion/terravion_env/lib/python3.6/site-packages/peewee.py", line 183, in reraise
    raise value.with_traceback(tb)
  File "/Users/ernestappiah/Desktop/FlaskApplications/Environments/env_terravion/terravion_env/lib/python3.6/site-packages/peewee.py", line 2949, in execute_sql
    cursor.execute(sql, params or ())
peewee.ProgrammingError: syntax error at or near "RETURNING"
LINE 1: ...ic"."subscription_etl" ("email") VALUES ('email') RETURNING ...
                                                             ^

```

Fixed: I set primary_key = False

It appears to me that Redshift should support the Postgresql `RETURNING` clause, as it is not listed among the "unsupported postgresql features":

https://docs.aws.amazon.com/redshift/latest/dg/c_unsupported-postgresql-features.html
Issue: Strange Operational Error
Author: BOBONA
State: closed
Created: 2019-06-12 17:46:57

```
model_class = MyModel
model_class.create(
      **stuff
)
```

Try this code^
This doesn't work and I get an error that says that 'mymodel' is not a table.
Is this a glitch or is it intended? I can't figure out how to fix it by myself.

Is the solution to only use lowercase names? :)

Nevermind, I just realized that uppercase table names are generally an issue.

You have complete control over the model class name and/or the table name (same w/fields and columns).

http://docs.peewee-orm.com/en/latest/peewee/models.html#table-names
Issue: Could I get more exactly error of IntegrityError?
Author: fy0
State: closed
Created: 2019-06-12 05:15:24

Hello, I wrote some code to pick out `duplicate key` and `NOT NULL` exception from IntegrityError, by text match.

```python

class PeeweeContext:
    def __init__(self, db):
        self.db = db

    def __enter__(self):
        pass

    def __exit__(self, exc_type, exc_val, exc_tb):
        db = self.db
        if isinstance(exc_val, peewee.IntegrityError):
            db.rollback()
            if exc_val.args[0].startswith('duplicate key'):
                raise AlreadyExists()
            elif exc_val.args[0].startswith('NOT NULL constraint failed'):
                raise NotNullConstraintFailed()
        elif isinstance(exc_val, peewee.DatabaseError):
            db.rollback()
            logger.error("database error", exc_val)
            raise ResourceException("database error")
```


But the code not work on a server with PG 11.3, because exception info was printed by chinese.

```
peewee.IntegrityError: 错误:  重复键违反唯一约束"domain_user_id_name"
DETAIL:  键值"(user_id, name)=(\x5ab0d481cbc00000, 'test')" 已经存在
```

```
postgres@server:~$ locale
LANG=zh_CN.UTF-8
LANGUAGE=zh_CN:zh
LC_CTYPE="zh_CN.UTF-8"
...
```

So could I get more exactly error to avoid text match?
Thanks
Issue: delete signal hooks with delete_instance in recurse mode
Author: Kruptein
State: closed
Created: 2019-06-10 14:31:27

Hi,

I have a couple of signal hooks that act on pre_delete. As mentioned in the docs these signals only work with delete_instance.  I'm however under the impression that this does not work in combination with the recurse=True field of delete_instance. (i.e. the signals do not trigger for dependencies).

take for example these signals:

```python
@pre_delete(sender=Room)
def on_room_delete(model_class, instance):
    logger.warning("DELETE ROOM")

@pre_delete(sender=Location)
def on_location_delete(model_class, instance):
    logger.warning("DELETE LOCATION")
```

Where a `Room` consists of multiple `Location`s.  When calling `some_room.delete_instance(True)` both the room and related locations are removed from the database as expected, however only the DELETE ROOM signal is triggered.

Any suggestions what I can do to work around this ?

That's correct, using the `recurse` functionality issues efficient bulk deletes for any dependent objects. As such, the `delete_instance()` methods of the deps are not called.

It is done this way for efficiency in terms of the database.

(Better yet is to just specify the `on_delete='CASCADE'` option when declaring foreign-keys, and let the database handle everything).

But as to your problem, you'll need to do it manually if you want to do some special processing for each deleted dependent object.

Yes I have the proper on cascade things, as ultimately the database is correctly wiped. It's just that I need to do some extra work outside of the database that I hoped to simplify with signals. But I understand the performance reasons, thanks for your work.
Issue: Support raising custom DoesNotExist exceptions in models
Author: onyb
State: closed
Created: 2019-06-10 13:18:18

### Why do we need this?

- The current way of raising a custom `DoesNotExist` exception is to override model methods like `.get_by_id()`, catch `DoesNotExist` and re-raise with a custom exception class. This is too much boilerplate for something that should be doable with something like:

    ```py
    class BaseModel(Model):
        DoesNotExist = MyCustomException
    ```
- Django ORM supports it, and so do most other ORMs.

@coleifer Could you please explain the reason why you closed this PR? Is raising custom `DoesNotExist` exceptions already possible with PeeWee, or perhaps there is something you'd like me to improve?
Issue: Mysql 1093 error
Author: dzpt
State: closed
Created: 2019-06-09 21:23:20

I got this error after trying to delete a record which has dependency like this with mysql 5.7:

```
class Topic(BaseModel):
    class Meta:
        table_name = 'forum_topic'
    id = AutoField()

class Post(BaseModel):
    class Meta:
        table_name = 'forum_post'
    id = AutoField()
    reply_to: Post = ForeignKeyField(
        'self', null=True, default=None, backref='child_posts')
    topic = ForeignKeyField(Topic, null=True, default=None, backref='posts')
```

> peewee.InternalError: (1093, "You can't specify target table 'forum_post' for update in FROM clause")

generated query:

``('DELETE FROM `forum_post` WHERE (`forum_post`.`reply_to_id` IN (SELECT `forum_post`.`id` FROM `forum_post` AS `forum_post` WHERE (`forum_post`.`topic_id` = %s)))', [2])`


1093 error: In MySQL you can't delete from the same table you are selecting from.
https://dev.mysql.com/doc/refman/8.0/en/subquery-restrictions.html

Found it there #1609 

`on_delete='CASCADE'`.

Doesn't work on mysql 5.7.
i have to upgrade to mariadb 10.3.15 to have `UPDATE Statements With the Same Source and Target`
https://mariadb.com/kb/en/library/update/#update-statements-with-the-same-source-and-target

MySQL has supported ON DELETE CASCADE for ages. Here it is documented in the 5.5 manual:

> CASCADE: Delete or update the row from the parent table, and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported.

MariaDB has similarly supported this for ages.

There is also of course the option to ON DELETE SET NULL.
Issue: Add support (or docs) for how to create jsonb indexes for Postgres
Author: jboothatgranular
State: closed
Created: 2019-06-07 21:34:58

I'm trying to create a gin index on a postgres jsonb column.

```
=> \d layer
                               Table "public.layer"
         Column          |          Type          | Collation | Nullable | Default 
-------------------------+------------------------+-----------+----------+---------
 data                    | jsonb                  |           | not null | 
...
```

I can do in psql via `CREATE INDEX idx_data_foo ON layer USING gin ((data->'foo'));`

I can't seem to get it to go with the migrations support in playhouse. I have passing the whole `(data->'foo')` as a string, but it complains that isn't a column name. I've tried passing the column/JsonLookup `Layer.data["foo"]` but that isn't a string.

Is there a simple way this is expected to work that I'm missing? Needs documented? Needs implemented? Pointers if its needs implemented? Should I be doing the query a different way? (I'm trying to accelerate a `WHERE data->'foo'::text ?| '{a,b,c}'::text[]` type query)

Don't be a hero:

```python
db.execute_sql("create index idx_data_foo ON layer USING gin (data->'foo');")
```
Issue: delete_instance with recursive=True only works with one level
Author: dzpt
State: closed
Created: 2019-06-06 14:29:44

I have a table with foreign key to itself

```
class Post(BaseModel):
    reply_to: Post = ForeignKeyField(
        'self', null=True, default=None, backref='child_posts', on_delete='CASCADE')
```

Then in db if i have
```
id reply_to_id
1 nulll
2 1
3 2
4 3
5 4
```
If i `delete_instance(True, True)` record `id=2`, it deletes ID `2,3` only and leave ID `4,5`.
It means it doesn't delete recursively (only works for 1 level).

Did i miss something or i have to deploy delete recursively by myself?

Oh, yeah - I don't think it correctly handles foreign-keys to self. The best way is to lean on your database to handle this with an `on_delete=...` in your `ForeignKeyField()` declaration.

@coleifer Oh, so what's the purpose of recursive flag, it only works for 1 level?

https://github.com/coleifer/flask-peewee/issues/31
I've read this post and thought peewee can do delete recursively.

Self-reference is needed to be use on multiple level items like categories, posts.
I use mysql, so any other way to do delete recursively? Or i have to override `delete` method to implement it myself?

If you have a row with a deep dependency graph of foreign-keys, it will correctly handle dependent rows without leaving orphaned data. That is the purpose.

The reason it does not support hierarchical data (i.e. self-referential foreign-keys) is to avoid cycles in the dependency graph.

> I use mysql

Doesn't innodb support ON DELETE constraints?

> avoid cycles in the dependency graph.

Can we check the type of foreign key before deleting, it would avoid circular delete?
I prefer to do delete on code rather than depend on db.





I remembered that the other - in fact, main - reason for not supporting this is that we don't know how *deep* into the graph to go without resolving foreign-keys at every step.

So with a non-self-referential graph of foreign-keys, we just make sure to issue a query for each node in the graph (each foreign-key). With self-referential, we would have to issue *n* deletes where *n* is the maximum depth of any row's relations. But we don't know how far to go without resolving at each step. So peewee doesn't do this, but you could very easily write it yourself.

Or just use the database. The database knows how to delete a self-referential collection of rows. That is why the `ON DELETE` constraint exists. Use it!

> I prefer to do delete on code rather than depend on db.

Why? To my thinking that's 100% backwards.
Issue: Pandas DataFrame as peewee backend
Author: isthisthat
State: closed
Created: 2019-06-05 12:50:59

Hi @coleifer, for some reason sqlite writes and reads are painfully slow (10x slower) on my system through the python interface. System commands to sqlite are fast as expected. I've been experimenting replacing the sqlite back-end with a pandas DataFrame. I had some success using peewee Model select `.sql()` and then `pandasql.sqldf(sql)` to query a DataFrame. However that still loads data to an sqlite db behind the scenes so was taking up twice the memory. Is a pandas DataFrame something you might consider supporting as a back-end natively? I guess the missing link is a translation from sql to pandas query syntax as outlined [here](https://pandas.pydata.org/pandas-docs/stable/getting_started/comparison/comparison_with_sql.html). Thanks!

> sqlite writes and reads are painfully slow (10x slower) on my system through the python interface

What driver are you using?

How are you executing these queries?

Compared to what? C? The sqlite3 shell?

> Is a pandas DataFrame something you might consider supporting as a back-end natively?

Sorry, definitely not.

> What driver are you using?

I've tried `SqliteDatabase`, `SqliteExtDatabase` and `APSWDatabase` with all the `PRAGMA` tips and tricks I could find.

> How are you executing these queries?

Using e.g. list(Model.select(...).where(...).dicts())

> Compared to what?

The sqlite3 shell
Issue: replace instance delete method with model delete method
Author: fenngwd
State: closed
Created: 2019-06-04 11:21:16

The pitfalls of delete method in instance are numerous. It may cause unintended consequences, such as truncating table.

I want to disable delete method call and raise warning in instance. After reading the code, I found that delete_instance use delete to do the actual dirty work in instance. Because of this, disabling delete method is difficult without changing delete_instance method. I have to copy delete_instance 's code and fix it to workaround this.

If code is changed as below, we can still call delete method in Model Object without any change, and call delete_instance method in Model's instance correctly, and at the same time, we can override delete method in instance to disable usage in instance for avoiding unexpected consequences.

Thanks! This makes sense to me.

Another option would be for me to move those methods (delete, insert, select, update) to the metaclass instead. That would expose them as class-methods on model classes, but prevent you from accidentally calling them on a model instance. Might be worth doing in the near future.
Issue: alias function partly broken in peewee 3.9.4 and 3.9.5
Author: anne17
State: closed
Created: 2019-06-03 14:16:33

I just upgraded from peewee 3.9.3 to 3.9.5 and suddenly I am getting the following error:

```  File "/home/anne/projects/recapi/recapi/views/recipe_data.py", line 39, in get_recipe_data
    User, pw.JOIN.LEFT_OUTER, on=(User.id == recipemodel.Recipe.created_by).alias("a")
  File "/home/anne/projects/recapi/venv/lib/python3.7/site-packages/peewee.py", line 698, in inner
    method(clone, *args, **kwargs)
  File "/home/anne/projects/recapi/venv/lib/python3.7/site-packages/peewee.py", line 6603, in join
    on, attr, constructor = self._normalize_join(src, dest, on, attr)
  File "/home/anne/projects/recapi/venv/lib/python3.7/site-packages/peewee.py", line 6534, in _normalize_join
    attr == fk_field.object_id_name and not is_backref:
AttributeError: 'AutoField' object has no attribute 'object_id_name'
```

The same error occurs in peewee 3.9.4 but everything is running fine in 3.9.3. The code piece that breaks looks like this:

```Changed = User.alias()
recipes = recipemodel.Recipe.select(
    recipemodel.Recipe, User, Changed, pw.fn.group_concat(tagmodel.Tag.tagname).alias("taglist")
).where(
    recipemodel.Recipe.published == published
).join(
    User, pw.JOIN.LEFT_OUTER, on=(User.id == recipemodel.Recipe.created_by).alias("a")
).switch(
    recipemodel.Recipe
).join(
    Changed, pw.JOIN.LEFT_OUTER, on=(Changed.id == recipemodel.Recipe.changed_by).alias("b")
).switch(
    recipemodel.Recipe
).join(
    tagmodel.RecipeTags, pw.JOIN.LEFT_OUTER, on=(tagmodel.RecipeTags.recipeID == recipemodel.Recipe.id)
).join(
    tagmodel.Tag, pw.JOIN.LEFT_OUTER, on=(tagmodel.Tag.id == tagmodel.RecipeTags.tagID)
).group_by(recipemodel.Recipe.id)
```

It's the `.alias("a")` and `.alias("b")` bits that seem to be causing the trouble.

Please let me know if there is any relevant information lacking from this bug report.

Thanks, I've been able to replicate this and am working on a fix.

Fixed, pushing a new release 3.9.6

See also #1914

Sorry to be bugging you again, but will you be pushing the new release to pypi?

I thought I had, 3.9.6? https://github.com/coleifer/peewee/releases/tag/3.9.6

Oh, guess not, done.

Great, thank you!
Issue: replace instance delete method with model delete method
Author: fenngwd
State: closed
Created: 2019-06-03 07:39:05

The pitfalls of `delete` method in instance are numerous. It may cause unintended consequences, such as truncating table.

I want to disable `delete` method call and raise warning in instance. After reading the code, I found that `delete_instance` use `delete` to do the actual dirty work in instance. Because of this, disabling `delete` method is difficult without changing `delete_instance` method. I have to copy `delete_instance` 's code and fix it to workaround this.

If code is changed as below, we can still call `delete` method in Model Object without any change, and call `delete_instance` method in Model's instance correctly, and at the same time, we can override `delete` method in instance to disable usage in instance for avoiding unexpected consequences.
Issue: Peewee 3.9.5 throws TypeError on Windows running Python 3.7
Author: sn0opy
State: closed
Created: 2019-05-29 09:26:30

I'm getting the following stack trace running on Windows with Python 3.7. The exact same code runs fine on Linux with Python 3.5 up to 3.7.

Is there anything, that has to be tweaked in my own code to run on Windows properly or is there an actual issue in Peewee's code? The Flask version being used on Linux and Windows is 1.0.3.

```
Traceback (most recent call last):
  File "C:\Python37\lib\site-packages\flask\app.py", line 2311, in wsgi_app
    response = self.full_dispatch_request()
  File "C:\Python37\lib\site-packages\flask\app.py", line 1834, in full_dispatch_request
    rv = self.handle_user_exception(e)
  File "C:\Python37\lib\site-packages\flask\app.py", line 1737, in handle_user_exception
    reraise(exc_type, exc_value, tb)
  File "C:\Python37\lib\site-packages\flask\_compat.py", line 36, in reraise
    raise value
  File "C:\Python37\lib\site-packages\flask\app.py", line 1830, in full_dispatch_request
    rv = self.preprocess_request()
  File "C:\Python37\lib\site-packages\flask\app.py", line 2106, in preprocess_request
    rv = func()
  File "C:\Python37\lib\site-packages\playhouse\flask_utils.py", line 181, in connect_db
    self.database.connect()
  File "C:\Python37\lib\site-packages\playhouse\pool.py", line 108, in connect
    return super(PooledDatabase, self).connect(reuse_if_open)
  File "C:\Python37\lib\site-packages\peewee.py", line 2882, in connect
    self._state.set_connection(self._connect())
  File "C:\Python37\lib\site-packages\playhouse\pool.py", line 125, in _connect
    ts, conn = heapq.heappop(self._connections)
TypeError: '<' not supported between instances of 'Connection' and 'Connection'
```

What-the-what? Can you share how you're setting up and tearing down your connections? This seems like it should only happen if two conns are created at the *exact same timestamp*.

The code is open source. I'm linking the diff to the python2 to 3 migration. Unfortunately, I', not really that much into peewee and flask, but I hope the following diff helps anyway

https://github.com/cecpk/OSM-Rocketmap/commit/79539952592f7fb78c061373dabb664f533354d0#diff-541e2b217ba4a7d7859dae2cb443b6b7

Not sure, can you provide a script to replicate?

Closing as I don't anticipate this being likely, but will gladly reopen if you can do so.
Issue: Handle missing record for ForeignKeyField
Author: dzpt
State: closed
Created: 2019-05-25 06:24:35

`edited_by_user = ForeignKeyField(User, null=True)`

After the record has been deleted but still has reference from other table, i'll get `*DoesNotExist: instance matching query does not exist error if i access to record.edited_by_user`

How do i get rid of it like the record is null then return None like `get_or_none`?

This case can be annoying to None object model in template.

It's impossible to use `try/except` in template? Then i have to write another method to check if the field is getable or not

```
def get_edited_by_user()
    try/except ....
```

Then assign it to the template.
That's so annoying. It should be another argument to determine return None for empty/missing ForeignKeyField

> After the record has been deleted but still has reference from other table, i'll get *DoesNotExist: instance matching query does not exist error if i access to record.edited_by_user

What do you mean, you have orphaned `User` instances? e.g., the `edited_by_user` column contains a user id for which no corresponding user row exists?

If this is the case, then google "referential integrity". Your database should be enforcing these kinds of constraints, updating orphaned foreign-keys to NULL is supported by many databases. Peewee implements this as well via the `delete_instance(recursive=True, delete_nullable=False)`.