akumoth/peewee-issues huggingface dataset:
Issue: Rollback doesn't work inside of Jupyter Notebook
Author: NAN-CForadas
State: closed
Created: 2022-06-27 13:53:27

I keep getting
```cursor.execute(sql, params or ())
psycopg2.errors.InFailedSqlTransaction: current transaction is aborted, commands ignored until end of transaction block
```
even though I have queries inside try: except: blocks
```
def get_random_user():
    with db.atomic() as txn:
        query_failed = False
        try:
            user = User.select().order_by(fn.Random()).limit(1).execute()[0]
        except: # catch *all* exceptions
            e = traceback.format_exc()
            print( "<p>Error: %s</p>" % e)
            txn.rollback()
            query_failed = True

        if query_failed:
            print('Get random user failed')
        else:
            return user

print(get_random_user())
```
The only way to reset the database from not issuing the error is to restart the Jupyter Notebook kernal and start from scratch.

Any thoughts or ideas, I think I have the error trapping/rollback setup properly?

Thank you

Your error handling code looks good to me. I cannot think of what might be causing this, as it seems to be working fine on my side:

![scr1656348894883](https://user-images.githubusercontent.com/119974/175994871-73e0a6a4-ebbc-4470-a085-b67210e402ae.png)

It also works when swapping `PooledPostgresDatabase`.

moving **txn.rollback()** to the first line under the **execpt:** fixed it for me.
```
try:
    pass
except Exception as exc:
   txn.rollback()
   e = traceback.format_exc()
   print(f"<p>Error: {exc}</p>{e}")
   query_failed = True
```
Issue: Weird query operator behavior
Author: wpnbos
State: closed
Created: 2022-06-26 13:21:17

<img width="482" alt="image" src="https://user-images.githubusercontent.com/33165624/175815958-5a702699-c45e-457b-9399-68daeff3c4d8.png">
<img width="476" alt="image" src="https://user-images.githubusercontent.com/33165624/175815980-dba5b463-a9c0-45d8-ae7c-f9e183621754.png">

It would seem the query operators here just don't work properly. 

I'm using a Postgres database, both fields are of type `real`.

I have no idea what these screenshots are supposed to show. Post a self-contained, self-explanatory code snippet.

Possibly this is to do with floating point precision. You need to use numeric / decimal field for prices.

Confirmed this is floating point stuff.

```
In [1]: from peewee import *

In [2]: db = PostgresqlDatabase('peewee_test')

In [3]: class K(Model):
   ...:     price = FloatField()
   ...:     name = TextField()
   ...:     class Meta:
   ...:         database = db
   ...: 

In [4]: db.create_tables([K])

In [5]: K.create(price=123.45, name='12345')
Out[5]: <K: 1>

In [6]: K.create(price=123.44, name='12344')
Out[6]: <K: 2>

In [7]: K.create(price=123.46, name='12346')
Out[7]: <K: 3>

In [8]: K.select().where(K.price < 123.45).tuples()[:]
Out[8]: [(1, 123.45, '12345'), (2, 123.44, '12344')]

In [9]: K.select().where(K.price >= 123.45).tuples()[:]
Out[9]: [(3, 123.46, '12346')]
```

Next time submit code like ^^

> 

Thanks a lot! I changed my FloatFields to DecimalFields and I can confirm that this remedied my issue. 

My sincerest apologies for the lazy issue. I'll make sure to post self-contained, self-explanatory code snippets next time.
Issue: Possible bug with distinct count
Author: sayanarijit
State: closed
Created: 2022-06-20 07:18:41

I think we encountered a bug with the `.count()` method when applied on distinct column.

To reproduce:

```python
actual_qry = Post.select(Post.author_id).distinct()
buggy_qry = Post.select(Post.author_id.distinct())

actual_qry.sql() == buggy_qry.sql()
# True

actual_qry.count() == buggy_qry.count()
# False
```

This is because Peewee wraps any query that executes a `.count()`, so the actual sql that peewee ends up executing looks like:

```sql
-- Post.select(Post.author_id).distinct().count()
SELECT COUNT(1) FROM (
  SELECT DISTINCT "t1"."user_id" FROM "tweet" AS "t1"
) AS "_wrapped"
```

The second example, however, Peewee does not infer that there is important information it is supposed to capture inside the selected columns, and so we end up with:

```sql
-- Post.select(Post.author_id.distinct()).count()
SELECT COUNT(1) FROM (SELECT 1 FROM "tweet" AS "t1") AS "_wrapped"
```

So what you should do is either:

* `Post.select(Post.author_id).distinct().count()`
* `Post.select(fn.COUNT(Post.author_id.distinct())).scalar()`
Issue: How to return one to many records on single query
Author: duc-codecomplete
State: closed
Created: 2022-06-17 04:51:27

I have users and tweets table, 1 user has many tweets, 1 tweet belongs to an user
```
class User(BaseModel):
    class Meta:
        db_table = 'users'
    uuid = UUIDField(primary_key=True)
    email = CharField()


class Tweet(BaseModel):
    class Meta:
        db_table = 'tweets'
    uuid = UUIDField(primary_key=True)
    content = TextField()
    user = ForeignKeyField(User, backref='tweets', related_name='tweets')
```

For get Tweet, it"s return with user, no problem

`tweets = (Tweet.select().join(User))
`
```
[
    {
        "uuid": "74dbadc6-7fa9-44cf-92cc-236f4605721f",
        "content": "meow",
        "user": {
            "uuid": "5bb50b91-c7da-4b25-8f9c-2ffb39d4235b",
            "email": "duc.nguyen@codecomplete.jp"
        }
    },
    {
        "uuid": "a4da5282-6f5f-4ad9-aec6-d8813ba1f2e8",
        "content": "hiss",
        "user": {
            "uuid": "5bb50b91-c7da-4b25-8f9c-2ffb39d4235b",
            "email": "duc.nguyen@codecomplete.jp"
        }
    },
    {
        "uuid": "556748ee-9ac8-4ed0-9dac-0b760ae4349b",
        "content": "purr",
        "user": {
            "uuid": "5bb50b91-c7da-4b25-8f9c-2ffb39d4235b",
            "email": "duc.nguyen@codecomplete.jp"
        }
    },
    {
        "uuid": "e8ba2c18-59ac-4968-98e0-e4ce58ee5094",
        "content": "woof",
        "user": {
            "uuid": "6835d471-acd9-46d4-b73c-c6ccab91d547",
            "email": "duc.nguyen1@codecomplete.jp"
        }
    },
    {
        "uuid": "7584c092-906d-41e3-84a0-ee6b8f3d88f9",
        "content": "whine",
        "user": {
            "uuid": "6835d471-acd9-46d4-b73c-c6ccab91d547",
            "email": "duc.nguyen1@codecomplete.jp"
        }
    }
]
```

but when I try to get all user with tweets, it"s not working
`users = User.select(User, Tweet).join(Tweet)
`
```
[
    {
        "uuid": "5bb50b91-c7da-4b25-8f9c-2ffb39d4235b",
        "email": "duc.nguyen@codecomplete.jp"
    },
    {
        "uuid": "5bb50b91-c7da-4b25-8f9c-2ffb39d4235b",
        "email": "duc.nguyen@codecomplete.jp"
    },
    {
        "uuid": "5bb50b91-c7da-4b25-8f9c-2ffb39d4235b",
        "email": "duc.nguyen@codecomplete.jp"
    },
    {
        "uuid": "6835d471-acd9-46d4-b73c-c6ccab91d547",
        "email": "duc.nguyen1@codecomplete.jp"
    },
    {
        "uuid": "6835d471-acd9-46d4-b73c-c6ccab91d547",
        "email": "duc.nguyen1@codecomplete.jp"
    }
]
```

my expect:
```
[
    {
        "uuid": "5bb50b91-c7da-4b25-8f9c-2ffb39d4235b",
        "email": "duc.nguyen@codecomplete.jp",
        "tweets": [
            {
                ...
            },
            {
                ...
            }
        ]
    },
    ....
]
```

This is fundamental to how relational databases operate, I'm not sure what to tell you, it's not magic. The docs describe some approaches, however, which you may check out: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#avoiding-the-n-1-problem
Issue: How get inserted row count from Model.insert_many() with postgres?
Author: MichaelLan
State: closed
Created: 2022-06-10 22:56:28


This is a relatively recent addition:

http://docs.peewee-orm.com/en/latest/peewee/api.html#Insert.as_rowcount

```python
iq = User.insert_many([(u,) for u in ('u1', 'u2', 'u3')])
iq = iq.returning()
assert iq.as_rowcount().execute() == 3
```

I tried it, but not work, I am using postgress and peewee 3.14.10
![image](https://user-images.githubusercontent.com/9300072/173393554-5092c491-5c94-4182-ab62-862c22043f3c.png)



>This is a relatively recent addition:
Issue: whether `conn` was append to PooledDatabase._connections?
Author: chmberl
State: closed
Created: 2022-06-05 02:54:19

where to `conn` append to `self._connections`?
Why is _connections always empty after I set max_connections?

my db url is
postgresqlext+pool://xxx:xxx@localhost:5432/netket?max_connections=5&stale_timeout=300


found the reason


I made a mistake
Issue: run test for MySQL 8.0 failed 
Author: siddontang
State: closed
Created: 2022-06-03 17:59:29

Hi, I tried to run the test for MySQL 8.0 as `python3 ./runtests.py -e mysql --mysql-user=root --mysql-port=3306`, but met some test failed, like:

```
ERROR: test_for_update_nested (tests.models.TestForUpdateIntegration)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/Users/tangliu/eng/show/peewee/peewee.py", line 3160, in execute_sql
    cursor.execute(sql, params or ())
  File "/opt/homebrew/lib/python3.9/site-packages/pymysql/cursors.py", line 148, in execute
    result = self._query(query)
  File "/opt/homebrew/lib/python3.9/site-packages/pymysql/cursors.py", line 310, in _query
    conn.query(q)
  File "/opt/homebrew/lib/python3.9/site-packages/pymysql/connections.py", line 548, in query
    self._affected_rows = self._read_query_result(unbuffered=unbuffered)
  File "/opt/homebrew/lib/python3.9/site-packages/pymysql/connections.py", line 775, in _read_query_result
    result.read()
  File "/opt/homebrew/lib/python3.9/site-packages/pymysql/connections.py", line 1156, in read
    first_packet = self.connection._read_packet()
  File "/opt/homebrew/lib/python3.9/site-packages/pymysql/connections.py", line 725, in _read_packet
    packet.raise_for_error()
  File "/opt/homebrew/lib/python3.9/site-packages/pymysql/protocol.py", line 221, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/opt/homebrew/lib/python3.9/site-packages/pymysql/err.py", line 143, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1093, "You can't specify target table 'users' for update in FROM clause")

......

Ran 891 tests in 32.490s

FAILED (failures=4, errors=3, skipped=105)
```

That's alright, we test using MariaDB.
Issue: Question: How to make the default value of two different datetime fields match?
Author: jonathanmach
State: closed
Created: 2022-06-01 14:36:01

Hi all!

How to make the default value of two different datetime fields match?

Say I have the following `BaseModel`:
```python
class BaseModel(Model):
    id = CharField(primary_key=True, default=lambda: shortuuid.uuid())
    created_at = DateTimeTZField(default=lambda: dt.now(tz))
    updated_at = DateTimeTZField(default=lambda: dt.now(tz))
```

Now, when the object is created/instantiated, the values in `created_at` and `updated_at` will have slightly different timestamps (which is completely understandable).

**What would be the ideal approach to make sure they match 100% when an object is first created?**

I initially tried using `DateTimeTZField(constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')])` instead, but when creating a new record (eg: `BaseModel.create()`), Peewee will try to insert  `created_at` and `updated_at` as `NULL`, which will override the DB server default.
Then I thought that maybe I could use `pre_init` or `pre_save` signals to explicitly set `instance.updated_at = instance.created_at`, which works, but I thought there might be a better way to accomplish this.

Thank you in advance!


Probably the easiest way is to override the `save()` method:

```python
class BaseModel(...):
    def save(self, *args, **kwargs):
        self.updated_at = dt.now(tz)
        if not self.created_at: self.created_at = self.updated_at
        return super(BaseModel, self).save(*args, **kwargs)
```

Thanks!
Apparently, this won't work when using `Model.insert()`, since it would require a database-level default (`DEFAULT CURRENT_TIMESTAMP`).
But in order to have a database-level default, we would need to get rid of the Peewee default value, which then would break the logic for `Model.create()`.

>which then would break the logic for Model.create().

That doesn't sound correct to me.

You are right, sorry!
I think what broke my tests was that `db.evolve()` won't detect the new `constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')]` param.
Is it expected? I just checked [the docs](https://docs.peewee-orm.com/en/latest/peewee/models.html?highlight=CURRENT_TIMESTAMP#some-fields-take-special-parameters) but didn't find anything stating it wouldn't be detected by `db.evolve()`.

I don't know what evolve is. It's not part of this library. Database-provided defaults work just fine with peewee, as do python-side defaults.

Sorry @coleifer, I thought peewee db evolve was part of this library. Thanks very much for your help!
Issue: The index is created with the wrong name
Author: daveusa31
State: closed
Created: 2022-06-01 11:18:54

The index is created with the wrong name

```python
import peewee
from playhouse.migrate import make_index_name


class CryptoTransaction(peewee.Model):
    currency = peewee.CharField(default="usdt", index=True, max_length=255)
    hash = peewee.CharField(unique=True)

    class Meta:
        db_table = "crypto_transaction"
        database = peewee.SqliteDatabase(':memory:')


for model in [CryptoTransaction]:
    model.create_table()


print(CryptoTransaction._meta.table_name)  # crypto_transaction
print(make_index_name(CryptoTransaction._meta.table_name, [CryptoTransaction.hash.column_name]))  # crypto_transaction_hash
print([index.name for index in CryptoTransaction._meta.database.get_indexes(CryptoTransaction._meta.table_name)])  # ['cryptotransaction_currency', 'cryptotransaction_hash']
```

There is such a code. Using the make_index_name function, it is expected that the index name is crypto_transaction_hash. And when creating a model, the index name is generated in its own way




Try this:

```python
class CryptoTransaction(peewee.Model):
    currency = peewee.CharField(default="usdt", index=True, max_length=255)
    hash = peewee.CharField(unique=True)

    class Meta:
        db_table = "crypto_transaction"
        database = peewee.SqliteDatabase(':memory:')
        legacy_table_names = False
```

This gives the following output:

```
crypto_transaction
crypto_transaction_hash
['crypto_transaction_currency', 'crypto_transaction_hash']
```

`make_index_name` doesn't know about model classes or whether they use old or new-style table names, so it just uses the table name itself. I cannot change the default index-name generation without breaking compatibility.
```
Issue: Problems using both Window() and dicts() in window functions
Author: aox-lei
State: closed
Created: 2022-05-30 23:36:22

version: 3.14.10
in the documentation example: http://docs.peewee-orm.com/en/latest/peewee/querying.html#reusing-window-definitions
```
win = Window(order_by=[Sample.id])
query = Sample.select(
    Sample.counter,
    Sample.value,
    fn.LEAD(Sample.value).over(win),
    fn.LAG(Sample.value).over(win),
    fn.SUM(Sample.value).over(win)
).window(win)  # Include our window definition in query.

for row in query.tuples():
    print(row)

# counter  value  lead()  lag()  sum()
# 1          10.     20.   NULL    10.
# 1          20.      1.    10.    30.
# 2           1.      3.    20.    31.
# 2           3.    100.     1.    34.
# 3         100.    NULL     3.   134.
```
If using dicts(), the field name becomes `value") OVER w`
If namedtuples() is used, raise ValueError: Type names and field names must be valid identifiers: 'value") OVER w'

If alias is specified for each field, it can be used normally

Yes, aliases must be used when you want to have Python identifiers or dict keys. Peewee tries to determine the column name to use based on whatever the cursor indicates, but when you have an expression (like `SUM("t1"."value") OVER "w"`) then Peewee's column-name code can't really figure out what to call it.
Issue: Return dict from scalar
Author: strmwalker
State: closed
Created: 2022-05-30 17:34:46

I have some analytical queries that look like this:
```python
import peewee as pw

query = Model.select(
    pw.fn.COUNT(Model.field).alias('name')
)
```
and because I am usually passing result further as dict, I have to do it like this:
```python
import peewee_async

manager = peewee_async.Manager()

result = (await manager.execute(query.dicts())[0]
```
What I'd like to have is and option to write something like this:
```python
result = await manager.execute(query.scalar(as_dict=True))
```

I'd be happy to contribute the PR if this is a valid request
Issue: Getting Type object has no attribute _meta. on create_tables
Author: Waayway
State: closed
Created: 2022-05-29 14:08:04

Th error i'm getting is,
`AttributeError: type object 'Chat' has no attribute '_meta'. Did you mean: 'Meta'?`
Where Chat is random between the tables i'm making. My code is
```
import peewee
from peewee import *
from .database import db

class baseModel(peewee.ModelBase):
    class Meta:
        database = db


class User(baseModel):
    id = peewee.CharField(unique=True)
    username = peewee.CharField()
    email = peewee.CharField(unique=True)
    password = peewee.CharField()
    server_ids = peewee.ManyToManyField(model=None)
    friend_ids = peewee.ManyToManyField(model=None)

class Server(baseModel):
    id = peewee.CharField(unique=True)
    title = peewee.CharField()
    icon = peewee.CharField()
    chat_ids = peewee.ManyToManyField(model=None)

class Chat(baseModel):
    id = peewee.CharField(unique=True)
    title = peewee.CharField()
    description = peewee.CharField()
    server_id = peewee.ForeignKeyField(model=None)
    user_ids = peewee.ManyToManyField(model=None)
    message_ids = peewee.ManyToManyField(model=None)

class Message(baseModel):
    id = peewee.CharField(unique=True)
    content = peewee.CharField()
    send_time = peewee.DateTimeField()
    sender_id = peewee.ForeignKeyField(model=None)
    chat_id = peewee.ForeignKeyField(model=None)

def create_tables():
    with db:
        db.create_tables([User,Server,Chat,Message])
```
And it always fails on the last line.
Version of Peewee i'm running is 3.14.10
And Python 3.10


You're extending peewee.ModelBase for some reason: `class baseModel(peewee.ModelBase):`

Change that to `class baseModel(peewee.Model)` and you'll be set.

It now gives me a slightly different error
```
AttributeError: 'NoneType' object has no attribute '_meta'
```

Found the issue, i had the manytomany fields model=None
Issue: peewee 连接 postgresql 问题
Author: MataSong
State: closed
Created: 2022-05-27 14:09:17

peewee 在连接 postgresql 数据库时，如何选择 schema 模式呢？

http://docs.peewee-orm.com/en/latest/peewee/models.html#model-options-and-table-metadata

```
class BaseModel(Model):
    class Meta:
        schema = 'myschema'
```
Issue: 使用peewee连接postgresql数据库
Author: MataSong
State: closed
Created: 2022-05-27 13:34:59

定义表名不生效。

`class PostgresCustomTableDataInfo(Model):
    """
    custom_table_data 表
    """
    _id = CharField(primary_key=True)
    activity_id = CharField()
    custom_table_id = CharField()
    mapping_custom_table_chinese_name = CharField()
    data_list = ArrayField()

    class Mete:
        database = DatabaseProxy()
        table_name = 'custom_table_data'`


`class PostgresCustomTableDataDao(PostgresDao):
    """
    pg数据库 custom_table_data 表
    """

    def __init__(self, db_name: str):
        super().__init__()
        self.custom_table_data_db = PostgresqlDatabase(db_name, **self.postgres_instance)

    def create_tables(self):
        self.custom_table_data_db.connect()
        self.custom_table_data_db.bind([PostgresCustomTableDataInfo])
        self.custom_table_data_db.create_tables([PostgresCustomTableDataInfo])`

`PostgresCustomTableDataDao('test').create_tables()`

创建表后得到 PostgresCustomTableDataInfo 表名，而不是 custom_table_data 表名
Issue: pwiz doesn't find installed psycopg (peewee.ImproperlyConfigured: Postgres driver not installed!)
Author: NAN-CForadas
State: closed
Created: 2022-05-26 12:54:28

System Software Overview:
  macOS Monterey
  System Version:	macOS 12.3.1 (21E258)
  Kernel Version:	Darwin 21.4.0

  Python 3.10.4

```% python -m pwiz -H $HOSTNAME -p 5432 -e postgresql -v $DATABASE
Traceback (most recent call last):
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/runpy.py", line 196, in _run_module_as_main
    return _run_code(code, main_globals, None,
  File "/Library/Frameworks/Python.framework/Versions/3.10/lib/python3.10/runpy.py", line 86, in _run_code
    exec(code, run_globals)
  File "pee-wee-testing/venv/lib/python3.10/site-packages/pwiz.py", line 222, in <module>
    introspector = make_introspector(engine, database, **connect)
  File "pee-wee-testing/venv/lib/python3.10/site-packages/pwiz.py", line 52, in make_introspector
    return Introspector.from_database(db, schema=schema)
  File "pee-wee-testing/venv/lib/python3.10/site-packages/playhouse/reflection.py", line 501, in from_database
    metadata = PostgresqlMetadata(database)
  File "pee-wee-testing/venv/lib/python3.10/site-packages/playhouse/reflection.py", line 264, in __init__
    cursor = self.execute('select oid, typname, format_type(oid, NULL)'
  File "pee-wee-testing/venv/lib/python3.10/site-packages/playhouse/reflection.py", line 165, in execute
    return self.database.execute_sql(sql, params)
  File "pee-wee-testing/venv/lib/python3.10/site-packages/peewee.py", line 3175, in execute_sql
    cursor = self.cursor(commit)
  File "pee-wee-testing/venv/lib/python3.10/site-packages/peewee.py", line 3159, in cursor
    self.connect()
  File "pee-wee-testing/venv/lib/python3.10/site-packages/peewee.py", line 3113, in connect
    self._state.set_connection(self._connect())
  File "pee-wee-testing/venv/lib/python3.10/site-packages/peewee.py", line 3812, in _connect
    raise ImproperlyConfigured('Postgres driver not installed!')
peewee.ImproperlyConfigured: Postgres driver not installed!
```
```
(venv) (base) pee-wee-testing % pip freeze
click==8.1.3
Flask==2.1.2
itsdangerous==2.1.2
Jinja2==3.1.2
MarkupSafe==2.1.1
peewee==3.14.10
psycopg==3.0.14
Werkzeug==2.1.2
```

Sorry, I installed psycopg not psycopg2.  Please excuse me!

You probably want to use `psycopg2` or `psycopg2-binary` as those are better supported by Peewee and can coexist alongside your install of `psycopg==3`.