akumoth/peewee-issues huggingface dataset:
Issue: Support for psycopg3
Author: elderlabs
State: closed
Created: 2021-10-14 05:38:37

Preemptive ticket here. The maintainer of psycopg2 has busied themself with its next iteration, [psycopg3](https://github.com/psycopg/psycopg). Are there plans to add support for the project; is there already native support by design? This ticket's more to see if the project's on your radar as it has officially released, and support for psycopg2 might be in decline fairly soon.

Thanks.

There are a number of psycopg3 changes that look to be backwards-incompatible (which is understandable) but will likely be a pain to identify and work around. Parameter binding, especially, looks like it has changed quite a bit.

It's definitely on my radar and will be added to Peewee, but I will probably wait a bit to add support. If anyone is interested in the meantime, feel free to implement this in a standalone/extension module.

Hello

I looked at your extension but I wondering, can I use this in conjuction with PostgresqlExtDatabase?

Any plans on built-in support for psycopg3? Considering is also adding it in their next release and it is a stable release..

Are there any specific features of `PostgresqlExtDatabase` you were intending to use - e.g. jsonb, arrays? I'll have to spend some time seeing what works and what doesn't, since we use some helpers from `psycopg2.extras` (e.g. `Json`).

I'm going through all my raw SQL code currently, but if I'm not mistaken I will have some jsonb-specifics.

I will use psycopg2 for now though, but it would be great to see support for 3.

No, I agree with you, things are going to be moving towards psycopg3 in the coming years so I'll want to be ready for that. Thanks for the ping on this, I'll be aiming to getting parity for psycopg3 with the current PostgresqlExtDatabase. No definitive timeline but it's a priority for me.
Issue: Model.bulk_create() ignores id of instances
Author: balloonz
State: closed
Created: 2021-10-14 00:50:11

It seems that Model.create() method respects id while Model.bulk_create ignores id of instances.

my model class:
```
class Book(Model):
    title = CharField(default="")
    author = CharField(default="")
    created_date = DateTimeField(default=datetime.now)

    class Meta:
        database = SqliteDatabase(":memory:")
```
The following code prints [(1, 'A'), (2, 'B'), (4, 'D'), (5, 'C')].
```
with db.atomic():
    Book.create(id=1, title="A")
    Book.create(id=2, title="B")
    Book.create(id=5, title="C")
    Book.create(id=4, title="D")
print([(book.id, book.title) for book in Book.select()])
```
The following code prints:
[(1, 'A'), (2, 'B'), (5, 'C'), (4, 'D')]
[(1, 'A'), (2, 'B'), (3, 'C'), (4, 'D')]
```
book_items = [
    Book(id=1, title="A"),
    Book(id=2, title="B"),
    Book(id=5, title="C"),
    Book(id=4, title="D"),
]
with db.atomic():
    Book.bulk_create(book_items, batch_size=50)
print([(book.id, book.title) for book in book_items])
print([(book.id, book.title) for book in Book.select()])
```
Also, the unsaved instances are kept unchanged and therefore inconsistent with the instances returned by Model.select().

Why are you manually specifying the ID for an auto-incrementing (opaque) primary key? The `bulk_create()` API is a shorthand for generating an `insert_many()`. Just use `insert_many()` if you really want to explicitly set these, I suppose.

I was importing some data into the database which involve relational data.
I thought `bulk_create()` as an alternative to `insert_many()` may also work.
If it is intended behavior, I will stick to `insert_many()`. 
Thanks for the quick response.  



If you want, you can disable the primary keys `auto_increment` attribute during bulk loading, but (especially if you're just creating the models to insert the data only) insert many is probably a fine choice.
Issue: Potential "list index out of range" in FunctionTable SQLite Extension
Author: ecpost
State: closed
Created: 2021-10-12 03:27:06

Hi,
I ran into an issue with this line in FunctionTable's pwBestIndex() function in _sqlite_ext.pyx:

```
        columns.append(table_func_cls.params[pConstraint.iColumn -
                                             table_func_cls._ncols])
```
The value of iColumn is some number less than _ncols, so the index into the list is always negative.  If it's _too_ negative, it goes past the start of params, causing an "IndexError: list index out of range" exception.

If you need some sample code demonstrating the problem, I'll put something together.

I'm happy to work on a fix and submit a pull request, but I'm having trouble figuring out the intended logic.  (I'm wondering why column positions are involved in indexing into the params list.  Although I do see that params are relevant here and in the filter step.)  Maybe it was partly to deal with the constraints of SQLite's virtual table API.  But anyway, if I can help at all, let me know.

The columns you can filter on are added as HIDDEN columns *after* the exported columns. So you would have:

```
table func: series(<start=0>, <stop=None>, <step=1>)

class Series(TableFunction):
    params = ['start', 'stop', 'step']
    columns = ['value']

...

CREATE VIRTUAL TABLE series(start, stop, step, value HIDDEN)
```

When looking up the constrained column, we subtract from it the number of "non-hidden" columns to get the index of the hidden/parameterized column.

I've added a check that this value does not drop below zero: 56fe72362a65199eb75bf25684eb290c57e3a37f

Ah, ok.  So that line is only meant to apply to those hidden columns, which would only be the non-negative cases.  Thanks for the quick response and fix!
Issue: UPDATE queries don't work because Value objects sqlify as ?
Author: Gaming32
State: closed
Created: 2021-10-04 17:35:00

Whenever I update a user's token using one of the two following pieces of code:
```python
# 1
user.token = token
user.save(only=['token'])
# 2
User.update({User.token: token}).where(User.unique_id == user_id).execute()
```
The following query is executed:
```sql
UPDATE "user" SET "token" = ? WHERE ("user"."unique_id" = ?)
```
All my arguments are dropped! As a result, the database is not updated. I've found that this appears to be because the `add_param` argument in `Context.value` is `True` by default. However, simply changing this to `False` does not work either (I didn't think it would, but I still tried it so I could say I did).

Detailed walkthrough of `Context.value`:
```python
    def value(self, value, converter=None, add_param=True):
        # add_param = True
        # converter = <bound method Field.db_value of <CharField: User.token>>
        # value = 'ba8a310b920ece30e4e28e6f67d40a53'
        if converter:
            value = converter(value)
        elif converter is None and self.state.converter:
            # Explicitly check for None so that "False" can be used to signify
            # that no conversion should be applied.
            value = self.state.converter(value)

        # value is still 'ba8a310b920ece30e4e28e6f67d40a53'
        if isinstance(value, Node):
            with self(converter=None):
                return self.sql(value)
        elif is_model(value):
            # Under certain circumstances, we could end-up treating a model-
            # class itself as a value. This check ensures that we drop the
            # table alias into the query instead of trying to parameterize a
            # model (for instance, passing a model as a function argument).
            with self.scope_column():
                return self.sql(value)

        self._values.append(value) # Values obtains the value 'ba8a310b920ece30e4e28e6f67d40a53'
        return self.literal(self.state.param or '?') if add_param else self # self.literal('?') is called, as add_param is True
```

The ? is the param placeholder. Parameters are bound the placeholders. This is to avoid sql injections.

It prevents the database from being updated though

How do I put a string into my database then?

Do you mean to imply that the fundamental operation of updating data is completely broken?

```python
db = SqliteDatabase(':memory:')

class User(Model):
    token = TextField()
    class Meta:
        database = db

User.create_table()

user = User.create(token='')
user.save()

user.token = 'new-token'
user.save()

user_db = User.get(User.id == user.id)
print(user_db.id, user_db.token)

User.update(token='new2').where(User.id == user.id).execute()

user_db = User.get(User.id == user.id)
print(user_db.id, user_db.token)
```

Prints

```
1 new-token
1 new2
```

Why was my comment deleted? I need legitimate help here.
Issue: Bulk update of all null DateTimeFields fails
Author: milesgranger
State: closed
Created: 2021-10-02 11:19:46

Hello there! :wave: 

I believe I've encountered a bug in an otherwise terrific library. 

Summary
---
When performing a bulk update of models, where the model has a nullable `DateTimeField` and every model in the list  to `bulk_update` has this field set to `None` will result in a an error complaining about it being of type timestamp but expression is of type text.

Example
---
```python
def test_bulk_update_of_all_null_datetime_fields(postgresdb):
    class Foobar(peewee.Model):
        class Meta:
            database = db

        col1 = peewee.IntegerField(primary_key=True)
        col2 = peewee.DateTimeField(null=True,)

    Foobar.create_table()

    models = [Foobar(col1=i, col2=datetime.now()) for i in range(2)]
    Foobar.bulk_create(models)

    models = [Foobar(col1=i, col2=None) for i in range(2)]
    try:
        Foobar.bulk_update(models, fields=["col2"])
    except:
        db.rollback()

    # Updating a single model in the list to non-null will work fine.
    models[0].col2 = datetime.now()
    Foobar.bulk_update(models, fields=["col2"])
```

This is the SQL all nulls generate
```sql
UPDATE "foobar"
SET "col2" = CASE "foobar"."col1" WHEN 0 THEN NULL WHEN 1 THEN NULL END
WHERE ("foobar"."col1" IN (0, 1))
```

Which will result in 
```bash
2021-10-02 11:07:19.084 UTC [60] ERROR:  column "col2" is of type timestamp without time zone but expression is of type text at character 30
2021-10-02 11:07:19.084 UTC [60] HINT:  You will need to rewrite or cast the expression.
2021-10-02 11:07:19.084 UTC [60] STATEMENT:  UPDATE "foobar" SET "col2" = CASE "foobar"."col1" WHEN 0 THEN NULL WHEN 1 THEN NULL END WHERE ("foobar"."col1" IN (0, 1))
```

manually modifying the query to cast the nulls to timestamps will work
```sql
UPDATE "foobar"
SET "col2" = CASE "foobar"."col1" WHEN 0 THEN NULL::timestamp WHEN 1 THEN NULL::timestamp END
WHERE ("foobar"."col1" IN (0, 1))
```

---

System ubuntu 18.04
peewee version 3.14.4
Python3.7
Postgresql 12

This is a Postgres limitation - for some reason Postgres does not behave well with certain data-types in these expressions. Related issues:

* #2457
* #2308
* #2292
* https://dba.stackexchange.com/questions/228046/why-do-i-need-to-cast-null-to-column-type/228058#228058

I cannot really offer a solution at this time, other than to suggest using individual updates / calls to `save()`. Doing updates in a loop inside a transaction may be just as fast (if not faster) than the `bulk_update` anyways.

Thank you, appreciate the information! :pray:
Issue: Question: Why Rollback do not work for Postgres?
Author: iamthen0ise
State: closed
Created: 2021-09-28 12:47:50

Hi!

Explicit rollback do not work. Moreover, it block all the transactions until the program end.

Peewee Versions is: 3.14.4

Code:
```python
from playhouse.pool import PooledPostgresqlExtDatabase
from playhouse.postgres_ext import (
    Model,
    CharField,
)

def get_db():
     return PooledPostgresqlExtDatabase(
        DB_NAME,
        user=DB_USER,
        password=DB_PASS,
        host=DB_HOST,
        port=DB_PORT,
        max_connections=32,
        autocommit=False,
        autorollback=False,
        stale_timeout=300,
    )

class ItemsModel(Model):
    name = CharField()


model = ItemsModel()

insert_items = []

for item in insert_items:
    db = get_db()
    with db.transaction():
        try:
            model.create(**item)
            db.commit()
        except Exception as e:
            db.rollback()
```

Any help is highly appreciated! 

Thank you!

You are using the transaction bit incorrectly I would say. Here's how I would do it:

```python

for item in insert_items:
    with db.atomic() as txn:
        try:
            model.create(**item)
        except IntegrityError:
            txn.rollback()
        else:
            txn.commit()
```

Still getting `current transaction is aborted, commands ignored until end of transaction block` using described method.

I'm sure you have some bug in your code. This works just fine:

```python
db = PostgresqlDatabase('peewee_test')

class Reg(Model):
    key = CharField(unique=True)
    class Meta:
        database = db

db.create_tables([Reg])
for k in 'abcadaec':
    with db.atomic() as txn:
        try:
            Reg.create(key=k)
        except IntegrityError:
            txn.rollback()
        else:
            txn.commit()

assert [r.key for r in Reg.select().order_by(Reg.key)] == list('abcde')
db.drop_tables([Reg])

```

Hmm, it works for soft inserts like `model.get_or_create()`, but causes block for strict `create` operation.

Don't know if it good by design, but it helps me and all working like magic now. Will check the sources for reference.

Thank you for your help! :)
Issue: Peewee cleanups all Model.dirty_fields with only params
Author: penja
State: closed
Created: 2021-09-24 08:54:36

For me was unexpected behavior with cleanup model dirty fields if we save model with `only` params. 
https://github.com/coleifer/peewee/blob/master/peewee.py#L6600-L6601
In my opinion, The saved field should be just removed from dirty fields.
Please see invalid behavior in the following code snippet.
```
import peewee
from peewee import *
import logging
import sys

log = logging.getLogger()
log.setLevel(logging.DEBUG)
handler = logging.StreamHandler(sys.stdout)
log.addHandler(handler)
print(peewee.__version__)

db = SqliteDatabase(":memory:")


class Example(Model):
    class Meta:
        database = db

    name = CharField()
    description = CharField(null=True)


db.create_tables([Example])

model = Example.create(name="foo", description="bar")
model.name = "bazz"
model.description = "example bar"
model.save(only=[Example.name])
#some other business logic
if model.dirty_fields:
    model.save()

model = Example.get_by_id(model)
print(model.name, model.description)
assert model.name == "bazz"
assert model.description == "example bar"
```
Output
```
3.14.4
('CREATE TABLE IF NOT EXISTS "example" ("id" INTEGER NOT NULL PRIMARY KEY, "name" VARCHAR(255) NOT NULL, "description" VARCHAR(255))', [])
('INSERT INTO "example" ("name", "description") VALUES (?, ?)', ['foo', 'bar'])
('UPDATE "example" SET "name" = ? WHERE ("example"."id" = ?)', ['bazz', 1])
('SELECT "t1"."id", "t1"."name", "t1"."description" FROM "example" AS "t1" WHERE ("t1"."id" = ?) LIMIT ? OFFSET ?', [1, 1, 0])
bazz bar
Traceback (most recent call last):
  File "dummy_sqlite.py", line 36, in <module>
    assert model.description == "example bar"
AssertionError
```


 

Thanks, this should be fixed now.

@coleifer Thank you for so hot fix.
Issue: UNSIGNED BIGINT AUTO_INCREMENT support in create_tables?
Author: sherrrrr
State: closed
Created: 2021-09-18 11:28:21

I am using mysql and use UNSIGNED BIGINT as primary key. According to as [issue542](https://github.com/coleifer/peewee/issues/542), I try to use BIGINT UNSIGNED like this
```python
class UnsignedBigAutoField(BigAutoField):
    field_type = 'BIGINT UNSIGNED'
```
and I try to use `ForeignKeyField` in models.
```
class User(Model):
    id = UnsignedBigAutoField(primary_key=True)

class Tweet(Model):
    id = UnsignedBigAutoField(primary_key=True)
    user = ForeignKeyField(User, backref='tweets')
```

I want to use peewee to create tables, later I realize that inherite `AutoField` with `auto_increment = True` does't affect sql, there is no AUTO_INCREMENT in create table sql. What's more, when try to create `Tweet`, user_id's type is BIGINT without UNSIGNED beacuse of ForeignKeyField's [field_type](https://github.com/coleifer/peewee/blob/master/peewee.py#L5213).

so I write this
```python
class UnsignedBigAutoField(IntegerField):
    auto_increment = True
    field_type = 'BIGINT UNSIGNED AUTO_INCREMENT'

    def __init__(self, *args, **kwargs):
        if kwargs.get('primary_key') is False:
            raise ValueError('%s must always be a primary key.' % type(self))
        kwargs['primary_key'] = True
        super(UnsignedBigAutoField, self).__init__(*args, **kwargs)
```

and this would cause `user_id` in Tweet have `AUTO_INCREMENT`.

So is there a solution to use UNSIGNED BIGINT as primary key and foreign key when I want to create table using peewee?

You overrode the pk, now just subclass `ForeignKeyField` and override the field_type property:

```python
class UnsignedBigAutoField(BigAutoField):
    field_type = 'BIGINT UNSIGNED AUTO_INCREMENT'

class UnsignedForeignKeyField(ForeignKeyField):
    field_type = 'BIGINT UNSIGNED'

class User(Base):
    id = UnsignedBigAutoField()

class Tweet(Base):
    id = UnsignedBigAutoField()
    user = UnsignedForeignKeyField(User)
```
Issue: Why store _database in ThreadSafeDatabaseMetadata?
Author: sherrrrr
State: closed
Created: 2021-09-16 04:00:42

```python
class ThreadSafeDatabaseMetadata(Metadata):
    """
    Metadata class to allow swapping database at run-time in a multi-threaded
    application. To use:
    class Base(Model):
        class Meta:
            model_metadata_class = ThreadSafeDatabaseMetadata
    """
    def __init__(self, *args, **kwargs):
        # The database attribute is stored in a thread-local.
        self._database = None
        self._local = threading.local()
        super(ThreadSafeDatabaseMetadata, self).__init__(*args, **kwargs)

    def _get_db(self):
        return getattr(self._local, 'database', self._database)
    def _set_db(self, db):
        self._local.database = self._database = db
    database = property(_get_db, _set_db)
```
I'm using peewee with gevent, and try to use `ThreadSafeDatabaseMetadata` to make sure every request can set its db to master/slave.
Here is how I define my model.
```python
class User(Model):
    id = BigAutoField(primary_key=True)
    class Meta:
        database = master
        model_metadata_class = ThreadSafeDatabaseMetadata
```
At first I thought every request is default to master, then I found that if one request switch to slave, `_database` in `User` became slave, then if gevent switch to another greenlet with a new request, then User's database is default to slave.
Of course this can be solved by every request first call something like `master.bind([ALLMODELS])`, but if that so, why is `_database` necessary? Can I remove `_database` in `ThreadSafeDatabaseMetadata` and use like this?
```python
class ThreadSafeDatabaseMetadata(Metadata):
    def __init__(self, *args, **kwargs):
        # database attribute is stored in a thread-local.
        self._local = threading.local()
        super(ThreadSafeDatabaseMetadata, self).__init__(*args, **kwargs)

    def _get_db(self):
        return getattr(self._local, 'database', master) # default use master
    def _set_db(self, db):
        self._local.database = db
    database = property(_get_db, _set_db)
```
And I think `ThreadSafeDatabaseMetadata`'s behavior is a bit confusing, and didn't find it in the document.

>At first I thought every request is default to master, then I found that if one request switch to slave, _database in User became slave, then if gevent switch to another greenlet with a new request, then User's database is default to slave.

Yeah, I think you're right actually, and the implementation needs a change. Originally the idea I had was to just make sure that binding was a thread-safe operation, and that within a running thread, a different thread cannot alter the db. But, as you indicate, it also makes sense to me to preserve the "default" db.

You can use your patch or you can try out this commit: 00498bfc073569a63cb30fa43d706fdcc3e14dc0
Issue: Help on select() and get() methods
Author: Sonotoki-da
State: closed
Created: 2021-09-12 13:11:21

I've already searched for solutions in the Issues, but couldn't find an answer, nor understand properly :')
OS: Windows
Python: 3.6.8, 3.9.6
IDE: VSCode
Tested purposely in 3.6.8 (saw the peewee is developed with that version). Thought maybe I can get the code work.
The ```TgUser.select(TgUser.subscribed).where(TgUser.user_id == id)``` line is just doesn't work properly. It should be returning True (as in the db, the subscribed record is set to True for that user)
Here's my rough code:
`queries.py`
```python
from datetime import date

import peewee as p

db = p.SqliteDatabase('database.db')


class TgUser(p.Model):
    user_id = p.IntegerField(unique=True)
    username = p.CharField(null=True)
    first_name = p.CharField()
    last_name = p.CharField(null=True)
    msg_count = p.IntegerField(default=0)
    subscribed = p.BooleanField()
    joined = p.DateField()
    bot_status = p.CharField(default='member')

    class Meta:
        database = db


def create_db():
    db.create_tables([TgUser])
    return db

def register_user(values: dict, joined: date, subscribed: bool) -> bool:
    user = {}
    for x in ['user_id', 'username', 'first_name', 'last_name']:
        user.update({x: values.get(x, None)})
    data, exist = TgUser.get_or_create(**user, joined=joined, subscribed=subscribed)
    return exist

def get_user_status(user_id: int) -> bool:
    q = TgUser.select(TgUser.subscribed).where(TgUser.user_id == user_id)
    return q.get()
```
`test.py`
```python
import queries as q
from datetime import datetime

db = q.create_db()

values = {
    'user_id': 123456,
    'username': '@some_user',
    'first_name': 'SomeUser',
}
q.register_user(values, joined=datetime.now(), subscribed=True)
if q.get_user_status(user_id=123456) is True:
    print("Subscribed")
else:
    print("Not subscribed yet")
```

Out:
`Not subscribed yet`

Please help, any suggestion is appreciated! I just wanted to stop writing raw SQL queries

The problem is here. Calling `query.get()` returns a model instance (if one exists) or raises a `DoesNotExist` if not. You are comparing the return value to `is True` -- and while the result (a model instance) is "truthy", it is *not* `True` - it is a model instance.

```python
if q.get_user_status(user_id=123456) is True:
    print("Subscribed")
else:
    print("Not subscribed yet")
```

You should instead write:

```python
try:
    user = q.get_user_status(user_id=123456)
except q.TgUser.DoesNotExist:
    # user with id=123456 does not exist
    print('user does not exist')
else:
    if user.subscribed:
        print('subscribed')
    else:
        print('not subscribed')
```

omg... Thank you very much! > < It's working now!
And sorry for taking your time to this kind of question :') I'll study further!