akumoth/peewee-issues huggingface dataset:
Issue: Unexpected results when using insert_many
Author: tornikenats
State: closed
Created: 2019-02-27 21:09:50

When you try to insert_many, different first objects lead to different results. See below:

```python
#models.py
from peewee import (
    MySQLDatabase,
    Model,
    CharField
)
from playhouse.db_url import connect

db_url = 'mysql://root@localhost/test'
db = connect(db_url)

class TestAccount(Model):
    email = CharField()
    first_name = CharField(null=True, default=None)

    class Meta:
        database = db
```
```python
#app.py
from models import TestAccount, db

# Result: all first_name are set to null
#1	temp@hotmail.com	NULL
#2	temp2@hotmail.com	NULL
users_1 = [
    {
        'email': 'temp@hotmail.com'
    },
    {
        'email': 'temp2@hotmail.com',
        'first_name': 'John'
    },
]
# Result: ValueError: Missing value for "<CharField: TestAccount.first_name>".
users_2 = [
    {
        'email': 'temp@hotmail.com',
        'first_name': 'John'
    },
    {
        'email': 'temp2@hotmail.com'
    },
]

db.create_tables([TestAccount])

TestAccount.insert_many(users_1).execute()
TestAccount.insert_many(users_2).execute()
```

Yes, this is expected. You need to provide dictionaries with the *same* keys and values (or tuples of the same length). When you issue a SQL bulk insert, you list the expected columns, then the tuples of values, e.g.:

```sql
insert into tbl_name (cola, colb, colc) values (?, ?, ?), (?, ?, ?), (?, ?, ?)
```

So you list that you're inserting into "tbl_name" (columns "a", "b" and "c"). You need to provide values for all 3 columns for each row inserted.

If columns are not explicitly specified when doing a bulk insert with Peewee, then Peewee inspects the first row being inserted to derive the list of columns being inserted. Your second example, Peewee would assume you are inserting values for columns "email" and "first_name".

So you're trying to do:

```sql
insert into testaccount (email, first_name) values (?, ?), (?, <error! no value>)
```

Which doesn't work.

Just make sure your dictionaries all contain the same fields *or* explicitly specify the fields you wish to insert.

http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.insert_many
Issue: [PostgreSQL - Connection pooling] No results to fetch at restart of application
Author: edebernis
State: closed
Created: 2019-02-27 13:43:13

Hello,

I am using Peewee in combination with Bottle web framework, uWSGI and Docker.
I have 10 uWSGI workers running in a Docker container. Each worker connects to a PostgreSQL database using connection pooling :
```
from playhouse.pool import *
database = PooledPostgresqlDatabase(None)
database.init(db_name, max_connections=2, stale_timeout=300, **db_args)
```

Each worker opens DB connection before request and closes it afterwards :
```
@webapp.hook('before_request')
def before_request():
    database.connect()

@webapp.hook('after_request')
def after_request():
    if not database.is_closed():
        database.close()
```

I am also using an exit handler to close all idle connections when gracefully stopping uWSGI worker :
```
import atexit
atexit.register(exit_handler, database)

def exit_handler(database):
    database.close_idle()
    sys.exit(0)
```

The issue I have is when restarting Docker container. During approx 5/10 minutes, I get a lot of following PostgreSQL errors (`psycopg2.ProgrammingError: no results to fetch`) on many peewee requests (not all).

And on database side, I get many warnings like these :
```
WARNING:  there is no transaction in progress
WARNING:  there is already a transaction in progress
```

However, when looking at active connections to DB from app using netstat, all connections are correctly dropped when stopping container.

Any ideas about this one ?

Thanks for your help !

So if you are exiting/terminating your Python process, there's no need to call `close_idle()` because the process will exit anyways, closing everything.

I'm not sure what's going on as far as when the subprocesses are getting forked (before or after the connection pool is initialized? hopefully before). Or what the shutdown/restart behavior is... Or what kinds of queries would trigger the errors you're observing... So many questions. Could this just a timing issue where your db isn't ready when you're attempting to connect/query?

If you can reproduce this reliably without requiring docker or uwsgi or bottle web framework (e.g., just peewee and postgres) I'll reopen.
Issue: Dropping foreign key constraints
Author: arel
State: closed
Created: 2019-02-25 18:21:13

I have a situation with [circular foreign key dependencies](http://docs.peewee-orm.com/en/latest/peewee/models.html#circular-foreign-key-dependencies), and I followed the approach of using a `DeferredForeignKey`. I am using MySQL.

As mentioned in the docs, in order to use this approach I need to explicitly create foreign key constraints:

> To create the tables and the foreign-key constraint, you can use the `SchemaManager.create_foreign_key()` method to create the constraint after creating the tables

However, once they are created, _how do I drop foreign key constraints in peewee?_ Does it make sense to add a `drop_foreign_key()` method? Otherwise, if I drop a table during development, for example, I get a `peewee.InternalError`:

```
peewee.InternalError: (3730, "Cannot drop table 'image' referenced by a foreign key constraint 'fk_user_profile_image_id_refs_image' on table 'user'.")
```


For reference (for anyone googling this) if you want to drop a table in MySQL without regard to its foreign key constraints, you can temporarily [`SET FOREIGN_KEY_CHECKS = 0`](https://stackoverflow.com/a/8538716/2438538). Something like this:

```python
# disable foreign key checks (lasts for this session only)
db.execute_sql("SET FOREIGN_KEY_CHECKS=0")

# drop any tables as usual
db.drop_tables([ModelA, ModelB])

# (optionally) re-enable foreign key checks
db.execute_sql("SET FOREIGN_KEY_CHECKS=1")
```

This is a consequence of using an ill-advised database schema.

Peewee's migrations module (`playhouse.migrate`; ([docs](http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#migrate)) contains helpers for dropping foreign-key constraints, but in the case of a circular FK you'll need to disable the constraint check like you have in your example.
Issue:   _in_use size exceed Maximum connections despite calling db.close_idle(),  db.manual_close() or  db.close_stale(60)
Author: ernest-andela
State: closed
Created: 2019-02-25 04:26:48

Calling the manual_close() method always return False because of this line in the code https://github.com/coleifer/peewee/blob/master/playhouse/pool.py#L194 . Where can l find the method is_closed() on this line https://github.com/coleifer/peewee/blob/master/playhouse/pool.py#L194? 

**Below is my initials for the creation of the connection**
```db = PooledPostgresqlDatabase(max_connections=30, timeout=300, stale_timeout=1, **db_args)```

**However l am having the following issues**
1. db.is_closed() is always True
2. self.self._in_use quickly gets to 30 causing Maximum Connection error

**Calling any of the following does not help:**
1. db.close_idle()
2. db.manual_close()
3. db.close_stale(60)

I am connecting to the database: **before_request** and closing the database **teardown_request**


`is_closed` is implemented on `peewee.Database` in `peewee.py`. What it does is check whether there is a connection already open (for the current thread, if you're using multiple threads).

The logic of `manual_close` could be described in this way:

1. does the current thread have a connection open? If not, there's nothing to do - return `False`
2. get a reference to the currently-open connection
3. remove the connection from the list of connections currently-in-use by the pool
4. call the `Database.close()` method, which resets the connection state and internally calls `._close()`, which is overridden by the pool subclass.
5. (inside `_close()`) this is overridden by the pool subclass to recycle the conn, which will not happen as the conn is no longer marked as being in-use
6. call `_close()` again, this time with a flag indicating that yes -- we really want to close the connection -- at which point it will be closed.

The symptoms you are describing do not make a lot of sense to me... Are you using a multi-threaded application? If so, then `is_closed()` is with respect to the current thread, and maybe you're calling it from a different thread.

Something is definitely weird in your app or your setup. For instance, I use the peewee pooled database on a handful of flask apps and it is quite well-behaved -- doesn't leak conns, etc. Your stale timeout of 1 second seems odd, but shouldn't be causing the issue.

You can always turn on logging:

```python
import logging
logger = logging.getLogger('peewee.pool')
logger.addHandler(logging.StreamHandler())
logger.setLevel(logging.DEBUG)
```

Also note the pool is unit-tested.

If you can post some more information indicating this is a bug, or a minimal script that reproduces the issue, I will gladly reopen the issue.
Issue: Travis CI recommends removing the sudo tag
Author: cclauss
State: closed
Created: 2019-02-24 09:33:12

[Travis are now recommending removing the __sudo__ tag](https://blog.travis-ci.com/2018-11-19-required-linux-infrastructure-migration).

"_If you currently specify __sudo: false__ in your __.travis.yml__, we recommend removing that configuration_"

So this change completely fucks the 3.8 tests and the solution is to mark it as "allow failures"? Nope.

OK, I owe you an apology actually, as the psycopg2 driver installation seems to be failing on 3.8 regardless of any other changes. Sorry about that.
Issue: CentOS 6 support
Author: goooroooX
State: closed
Created: 2019-02-22 14:52:50

Hi,

It seems peewee module is lack of CentOS 6 support. I have tried it in docker container and normal CentOS 6.8/6.9 installation, and in all cases I have the same exception (Python 2.6/2.7, x86/x64):

`  File "/app/base.py", line 233, in feed
    LogSourceModel.insert_many(batch).execute()
  File "/app/libs/peewee.py", line 1625, in inner
    return method(self, database, *args, **kwargs)
  File "/app/libs/peewee.py", line 1696, in execute
    return self._execute(database)
  File "/app/libs/peewee.py", line 2358, in _execute
    return super(Insert, self)._execute(database)
  File "/app/libs/peewee.py", line 2121, in _execute
    cursor = database.execute(self)
  File "/app/libs/peewee.py", line 2727, in execute
    return self.execute_sql(sql, params, commit=commit)
  File "/app/libs/peewee.py", line 2721, in execute_sql
    self.commit()
  File "/app/libs/peewee.py", line 2512, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/app/libs/peewee.py", line 2714, in execute_sql
    cursor.execute(sql, params or ())
OperationalError: near ",": syntax error`

CentOS 7 is OK.
I've executed in-build tests, results are attached - they also contains the same exceptions.
[peewee_test_results.txt](https://github.com/coleifer/peewee/files/2894498/peewee_test_results.txt)

Can you please comment on this? Is there any easy way to make in work on CentOS 6?
Thank you!

Those test failures are likely due to your using an absolutely ancient version of sqlite. This ancient version of sqlite does not seem to support bulk insert. That is the problem. Upgrade sqlite to a newer version.
Issue: Update multiple rows by id
Author: gurland
State: closed
Created: 2019-02-21 03:08:03

I have list of dicts `[{"pk": 1, "value": 12}, ...]`, how could I update all rows by their `id`? 

Only way I found is:
```
m = MyModel.get(id=pk)
m.value = value
m.save()
```

But that is very slow

If you're using Postgres or a recent-ish sqlite, you can try to use the `ValuesList` with a common table expression in an UPDATE query. Postgres also supports UPDATE FROM which could also work. You can see an example here, as I've added a testcase: c1b6e5f8c5a2b6799f24b2df8788f43a0ba8811d
Issue: UnboundLocalError when doing join().join_from()
Author: Defman21
State: closed
Created: 2019-02-20 11:40:16

I have a query that looks like this:

```
ModelAModelB
.select(ModelA, ModelB)
.join(ModelC, on=(ModelC.some_field == ModelAModelB.some_field))
.join_from(ModelAModelB, ModelA) # Exception occurs on this line
.join_from(ModelAModelB, ModelB)
```

For some reason, today I got an error:

```

2019-02-20T11:33:07.005118570Z   File "/usr/local/lib/python3.7/site-packages/peewee.py", line 6061, in join_from
2019-02-20T11:33:07.005130799Z     return self.join(dest, join_type, on, src, attr)                            
2019-02-20T11:33:07.005142575Z   File "/usr/local/lib/python3.7/site-packages/peewee.py", line 606, in inner             
2019-02-20T11:33:07.005154599Z     method(clone, *args, **kwargs)                                                        
2019-02-20T11:33:07.005166359Z   File "/usr/local/lib/python3.7/site-packages/peewee.py", line 6050, in join             
2019-02-20T11:33:07.005178361Z     on, attr, constructor = self._normalize_join(src, dest, on, attr)
2019-02-20T11:33:07.005190257Z   File "/usr/local/lib/python3.7/site-packages/peewee.py", line 6008, in _normalize_join  
2019-02-20T11:33:07.005202328Z     return (on, attr, constructor)                                                        
2019-02-20T11:33:07.005214005Z UnboundLocalError: local variable 'constructor' referenced before assignment
```

Is there something I'm doing wrong? I thought about changing `join` to `join_from(ModelAModelB, ModelC)`, but I'm not sure that's the reason of this error.

Nevermind, I made a mistake in my code and `ModelAModelB` was not `ModelAModelB`.
Issue: PostgreSQL support for on_conflict with partial indexes
Author: iamyohann
State: closed
Created: 2019-02-17 00:24:57

I'm attempting to upsert multiple records into a PostgreSQL table containing a partial index.

The insert statement looks something like this in plain SQL

```
INSERT INTO abc (a, b, c, value1, value2) VALUES (1, 1, null, '0.2'::numeric, '0.02'::numeric)
ON CONFLICT (b, c) where a is null
DO UPDATE
SET value1 = '0.555'::numeric, value2 = '0.555'::numeric;
```
The table `abc` has a partial unique index on `(b, c) where a is null`.

However `OnConflict` does not appear to support PostgreSQL `index_predicate` as specified in the above query (`ON CONFLICT (b,c) where a is null`).

There is a `where` in `OnConflict` but that seems to be for the `conflict_action` section, not for the `conflict_target` section as specified here https://www.postgresql.org/docs/10/sql-insert.html

This results in a error being raised
```
peewee.ProgrammingError: there is no unique or exclusion constraint matching the ON CONFLICT specification
``` 

Is it possible to add support for `index_predicate` with `OnConflict` or am I missing something here?

----------------------------------------
Peewee version = `3.8.2`
PostgreSQL version = `PostgreSQL 10.5 on x86_64-apple-darwin18.0.0, compiled by Apple LLVM version 10.0.0 (clang-1000.10.43.1), 64-bit`
Python version = `3.6.x`

Yes you're right, I didn't think about partial indexes when designing the API. To fix this, I've added a new parameter, `conflict_where`, which should take the expression(s) that comprise the WHERE clause of the partial index.

That was quick! Cheers

@coleifer 
Is there a way to ignore duplicates that contains partial index with multiple conditions. 
Postgres Version: psql (10.17 (Ubuntu 10.17-1.pgdg20.04+1))

**Partial Index** 
` "participant_event_id_user_id_unique" UNIQUE, btree (event_id, user_id) WHERE deleted = false AND event_id IS NOT NULL AND role <> 'test-admin'::text`.

**Query**
`insert into participant (role, user_id, created_by, event_id) VALUES ('proctor', 21937, 'com.talview.27585f36-4b68-4bbf-b41f-20a6c4a37d83', 2463) on conflict(user_id, event_id) where ("role" != 'test-admin') DO Nothing;`

**Error**
ERROR:  there is no unique or exclusion constraint matching the ON CONFLICT specification

For it to match the partial index it must contain the same where clause conditions, as I understand it.
Issue: Isssue with varchar fiels in postgres
Author: dorel14
State: closed
Created: 2019-02-16 18:59:03

Hello
I change my database from sqlite to postgres and varchar column are converted in array fields so all the new text i insert are embraced with brackets 
How can i deactivate this ?

THX

That doesn't make any sense to me. Peewee doesn't automatically convert anything to ArrayField. People use their model classes with both SQLite and Postgres (ie Sqlite for tests, Postgres for production), and I've never heard of such an issue.

If you can provide instructions on how to reproduce this, I'll take a look. But you have not given me any information that I can use...other than you seem to be confused.

ok faullt of my code  excuse me 
i discover peewee and it's not very simple
Issue: For Update in Subquery Fails with PostgreSQL
Author: href
State: closed
Created: 2019-02-15 20:58:06

I'm trying to use SELECT .. FOR UPDATE SKIP LOCKED in a subquery to delete some records concurrently, and I think I stumbled upon a bug. Usually, my FOR UPDATE selects work fine in my Postgres Database, but this query cannot be rendered by Peewee.

When I run this example code:
```python
from peewee import Model
from peewee import AutoField
from peewee import TextField

class User(Model):
    id = AutoField()
    name = TextField()

User.delete().where(User.id.in_(User.select(User.id).for_update())).sql()
```

I get the following error:
```
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Users/denis/.virtualenvs/centime/lib/python3.7/site-packages/peewee.py", line 1692, in sql
    return context.parse(self)
  File "/Users/denis/.virtualenvs/centime/lib/python3.7/site-packages/peewee.py", line 600, in parse
    return self.sql(node).query()
  File "/Users/denis/.virtualenvs/centime/lib/python3.7/site-packages/peewee.py", line 567, in sql
    return obj.__sql__(self)
  File "/Users/denis/.virtualenvs/centime/lib/python3.7/site-packages/peewee.py", line 2374, in __sql__
    ctx.literal(' WHERE ').sql(self._where)
  File "/Users/denis/.virtualenvs/centime/lib/python3.7/site-packages/peewee.py", line 567, in sql
    return obj.__sql__(self)
  File "/Users/denis/.virtualenvs/centime/lib/python3.7/site-packages/peewee.py", line 1295, in __sql__
    if op_in and Context().parse(self.rhs)[0] == '()':
  File "/Users/denis/.virtualenvs/centime/lib/python3.7/site-packages/peewee.py", line 600, in parse
    return self.sql(node).query()
  File "/Users/denis/.virtualenvs/centime/lib/python3.7/site-packages/peewee.py", line 567, in sql
    return obj.__sql__(self)
  File "/Users/denis/.virtualenvs/centime/lib/python3.7/site-packages/peewee.py", line 2084, in __sql__
    raise ValueError('FOR UPDATE specified but not supported '
ValueError: FOR UPDATE specified but not supported by database.
```

This happens on PostgreSQL (not that I have specified that yet in this example), even though PostgreSQL supports FOR UPDATE. My other FOR UPDATE queries work out fine and when I remove the check leading up to the ValueError the query executes correctly as well.

Well, the reason your specific example is bad is because you need to associate `User` model with a Postgres database instance. But the error is the same even after adding the database, just for different reasons. Fixed.

Thanks for fixing this so super quickly 🙂
Issue: Passing single model to bind_ctx causes uninitialized Proxy error
Author: MushuEE
State: closed
Created: 2019-02-15 16:47:53

I was trying to make a fixture to do some unit testing for my Peewee methods when I hit this issue:
```
@pytest.fixture()
def test_db():
    """Test to check db."""
    _db = SqliteDatabase(":memory:")
    dbs = (Testbed)
    with _db.bind_ctx(dbs):
        _db.create_tables(dbs)
        yield _db
        _db.drop_tables(dbs)
```
Will trigger
```
    @pytest.fixture()
    def test_db():
        """Test to check db."""
        _db = SqliteDatabase(":memory:")
        dbs = (Testbed)
>       with _db.bind_ctx(dbs):

    def __getattr__(self, attr):
        if self.obj is None:
>           raise AttributeError('Cannot use uninitialized Proxy.')
E           AttributeError: Cannot use uninitialized Proxy.
```
That sent me down a bit of a rabbit hole. 
The issue ended up being that I needed `[Testbed]`, in other fixtures I had `(Model1, Model2, Model3)` which is happy because it is iterable. If there is a way to pass a single model or add a catch, I think it might be helpful. Thanks

`(Testbed)` is not a tuple.

`(Testbed,)` is a tuple.

This is your problem.

Yes, I mentioned that, I opened because the Error message received was misleading. Hopefully others might find this helpful if they hit the same issue, thanks.
Issue: Bulk Upsert
Author: takehaya
State: closed
Created: 2019-02-12 19:54:48

hi. I want to use update and preserve with bulk upsert.

I would like to run the following code. How should I do?
```
Book.insert_many(books).on_conflict(preserve=[Book.disposal], update=books).execute()
```

Questions like this belong on StackOverflow.

The documentation in peewee:

* http://docs.peewee-orm.com/en/latest/peewee/querying.html#upsert
* http://docs.peewee-orm.com/en/latest/peewee/api.html#Insert.on_conflict

Consult the manual for the SQL database you are using to get a better idea of how to write such a query.

sorry.I looked at StackOverflow and read the document, but there was no example.

List can not be taken with" on_conflict (update = books) "
How to pass "list" to "on_conflict(update=*)"?

I'm suggesting that you don't understand how insert with "on conflict" clause works. And that you should familiarize yourself with the way it is used.
Issue: v3 multiple databases support
Author: tyfncn
State: closed
Created: 2019-02-12 14:28:45

First thank you for such a great software. I've been using it for almost 3 years now. My issue is missing "Using()" context manager. It was very useful while backing up to a new sqlite database of same models.  It seems it is deprecated in v3. Is there any replacement for this?
Old docs was here: http://docs.peewee-orm.com/en/2.10.2/peewee/database.html#using-multiple-databases

My use case is somewhat like this:
```python
new_db = SqliteDatabase("backup_2019.db")
data_list = []
for rec in Record.select():
	data_list.append({"name": rec.name, "desc": rec.desc})

with Using(new_db, [Record]):
	for rec in data_list:
		Record.create(**data)
```
Would like to update to latest version but this particular use case holds me back. Any help would be greatly appreciated

Yes, you can use `database.bind_ctx()`, or `Model.bind_ctx()`:

* http://docs.peewee-orm.com/en/latest/peewee/api.html#Database.bind_ctx
* http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.bind_ctx