akumoth/peewee-issues huggingface dataset:
Issue: Using ESCAPE with Sqlite
Author: mtihlenfield
State: closed
Created: 2021-01-06 13:37:19

I'm essentially trying to do the same exact thing as #348, but the solution there recommends using `Clause`, which doesn't seem to exist any longer. There no longer seems to be a way to execute a query like:

`select * from symbol where name LIKE "%\_printf" ESCAPE "\"`

on an sqlite database without just resorting to `.execute_sql`. Is there a new way to do this, or is `.execute_sql` the way to go?



Solved this just using `SQL()` like so:
```
Symbol.select().where(SQL("name LIKE ? ESCAPE '\\'", (sym_name, )))
```

Peewee now automatically escapes when you use `.contains()`, `.startswith()` or `.endswith()`. So your example would be:

```python

Symbol.select().where(Symbol.name.endswith('_printf'))
```
Issue: Could you tell me how to operating the content of the update only a data?
Author: BarryYBL
State: closed
Created: 2021-01-04 12:40:27

user.update(username='tudou').where(id='20')
There is no effect！
He put all the table data keys are updated

You need to put `.execute()` when you are finished creating your update query.

```python
User.update(username='foo').where(id=20).execute()
```

Please do not post simple questions on the issue tracker. Use stackoverflow for questions.

But he has updated my  all username key value！
How do I update only the content with ID =76？
![image](https://user-images.githubusercontent.com/46100874/103543108-f24d8080-4ed8-11eb-9f9d-016fda57edd2.png)


You need to write `.where(User.id == 20)`

Has been solved！ OK，thankyou！
Issue: KeyError when iterating over query from subquery with CTE
Author: Neirop
State: closed
Created: 2021-01-03 20:34:48

Database: Postgresql 12
Peewee version: 3.14.0

Model:
```
class BaseModel(Model):
    class Meta:
        database = database


class Channel(BaseModel):
    channel_id = IntegerField(primary_key=True)
    channel_name = TextField()

    class Meta:
        table_name = 'channel'


class UserMessage(BaseModel):
    message_id = UUIDField(index=True, primary_key=True)
    channel_id = ForeignKeyField(column_name='channel_id', field='channel_id', model=Channel, on_delete='CASCADE',
                                 lazy_load=False, index=False)
    user_id = IntegerField()
    timestamp = IntegerField()

    class Meta:
        table_name = 'user_message'
        indexes = (
            (('channel_id', 'timestamp'), False),
        )
```
Query:
```
UserMessageAlias = UserMessage.alias()
    subquery = UserMessageAlias \
        .select(UserMessageAlias, fn.RANK().over(order_by=[UserMessageAlias.timestamp.desc()])
                .alias("rank")) \
        .where(UserMessageAlias.channel_id == 26) \
        .order_by(UserMessageAlias.timestamp.desc()) \
        .limit(200)

    users = set()
    for i in range(100):
        users.add((random.randint(0, 999), random.randint(0, 150)))

    user_cte = ValuesList(users).cte("user_streamer", columns=["uid", "limit"])
    query = UserMessage \
        .select(subquery.c.message_id, subquery.c.channel_id, subquery.c.user_id,
                subquery.c.timestamp, subquery.c.rank) \
        .from_(subquery) \
        .join(user_cte, on=(
            (subquery.c.user_id == user_cte.c.uid) &
            (subquery.c.rank <= user_cte.c.limit))) \
        .with_cte(user_cte)

    for mes in query:
        print(mes)
```
I got this error:

```
Traceback (most recent call last):
  File "test.py", line 111, in <module>
    get_messages()
  File "test.py", line 100, in test_bug
    for mes in query:
  File "/tmp/test/venv/lib/python3.8/site-packages/peewee.py", line 4361, in next
    self.cursor_wrapper.iterate()
  File "/tmp/test/venv/lib/python3.8/site-packages/peewee.py", line 4280, in iterate
    result = self.process_row(row)
  File "/tmp/test/venv/lib/python3.8/site-packages/peewee.py", line 7548, in process_row
    instance = objects[key]
KeyError: <peewee.ModelSelect object at 0x7fbee0c78d90>
```


But if I use dicts() or tuples(), it works


Thanks for the succinct report. This is now resolved.
Issue: 'SqliteDatabase' object has no attribute 'create_index' error when calling create_index() method in Table class
Author: ldconejo
State: closed
Created: 2020-12-28 19:29:38

Operating System: MacOS
Python version: 3.9
Peewee version: 3.14.0

It seems the _create_index()_ method in SQLite was deprecated, in turn breaking the _create_index()_ method in the Table class. The method in _dataset.py_ works as a wrapper:

```
    def create_index(self, columns, unique=False):
        self.dataset._database.create_index(
            self.model_class,
            columns,
            unique=unique)
```

I'm trying the following code:

db = DataSet('sqlite:///my_database.db')
table = db['UsersTable']
table.create_index(['test'])

Which returns this error:

```
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/Library/Frameworks/Python.framework/Versions/3.9/lib/python3.9/site-packages/playhouse/dataset.py", line 210, in create_index
    self.dataset._database.create_index(
AttributeError: 'SqliteDatabase' object has no attribute 'create_index'
```



Thank you for the report. I've pushed a fix for this.

Thank you so much!
Issue: DateTimeField should return a datetime instance
Author: yuriescl
State: closed
Created: 2020-12-27 17:25:30

`DateTimeField` should return a `datetime` instance when accessed instead of a `str` instance. I imagine one of the main reasons to use a `DateTimeField` instead of `CharField` is to work with the field as a `datetime` instance without worrying about converting to/from `str`.

Model:
```
class Entry(Model):
    created_at = DateTimeField(default=datetime.now(timezone.utc))
```

Expected behavior:
```
entry = Entry.get()
print(type(t.created_at))
<class 'datetime.datetime'>
```

Actual behavior:
```
entry = Entry.get()
print(type(t.created_at))
<class 'str'>
```

You are probably using sqlite and storing the datetimes in some unrecognizable format. If you look at the code you can see what format are supported out of the box. Using these formats is recommended because they allow for lexicographic ordering.

This is also explained in detail in the documentation:

http://docs.peewee-orm.com/en/latest/peewee/api.html#DateTimeField

@coleifer Thanks for the quick response. This is how I'm setting the value:
```
In [2]: from models import Entry
In [3]: entry = Entry()
In [4]: from datetime import datetime, timezone
In [5]: entry.created_at = datetime.now(timezone.utc)
In [6]: entry.save()
Out[6]: 1
In [7]: entry.id
Out[7]: 3
In [8]: entry = Entry.get(id=3)
In [9]: entry.created_at
Out[9]: '2020-12-27 20:33:38.411859+00:00'
```

I'm setting it directly as a `datetime` object and `save()` works, but isn't it inconsistent to allow receiving a `datetime` and returning a `str` later?

I'm using SQLite, yes.
Peewee converts `datetime` to a string when saving to SQLite, why not convert it back as well?

Don't store tz info. Just use naive datetimes in utc.

That worked, thanks.
There should be an error thrown when trying to save with tzinfo saying it's not supported.
Issue: When a where clause query has any param as None, the query errors at _escape_like_expr
Author: bsodhi
State: closed
Created: 2020-12-23 06:06:52

When the params (such the `dept_name, org_id, fname, lname, role` in the following example) are `None`, then the queries like the following (see failing code below) are failing with the error shown next. This code used to work prior to upgrading to 3.14.0 version of peewee.

Though it looks a bit ugly, for now I'm thinking to perform the None checks prior to the failing query statement. However, I would like to seek your suggestions on how to fix it properly.

Thanks in advance!

**Failing code in application:**
```
dept_name, org_id, fname, lname, role = ... 
query = query.where(
            ((dept_name is None) | (Person.dept_name.contains(dept_name)))
            &
            ((org_id is None) | (Person.org_id.contains(org_id)))
            &
            ((role is None) | (User.role == role))
            &
            ((fname is None) | (User.first_name.contains(fname)))
            &
            ((lname is None) | (User.last_name.contains(lname)))
        ).order_by(-User.id)
```

**Error details:**
```
File "/Users/MyUser/.venv/APPS/lib/python3.8/site-packages/peewee.py", line 1190, in _escape_like_expr
    if s.find('_') >= 0 or s.find('%') >= 0 or s.find('\\') >= 0:
AttributeError: 'NoneType' object has no attribute 'find'
```

Peewee doesn't support "`<x is None>`" expressions, for starters. This may have worked in previous versions, but it boils down to concatenating "%" with NULL and then performing a LIKE query - which is just gross.

I suggest that you clean up your code and refactor those expressions. I think I'll mark this wontfix for now.
Issue: Can't update backref record?
Author: dzpt
State: closed
Created: 2020-12-15 08:53:09

```
class Post(BaseModel):
    content = CharField()
    topic = ForeignKeyField(Topic, null=True, backref='Posts')


class Topic(BaseModel):
    @property
    def post(self):
        return self.posts.get()
```
Then if i do update, it won't work
```
t = Topic.get_by_id(420426)
t.post.content = 'test' # post content won't be updated
t.post.save(only=[Post.content]) # returns 0
```

Since `self.posts.get()` returns the the ref record, it supposed to work
    


But if i do this , it works.
```
t = Topic.get_by_id(420426)
p = t.post
p.content = 'test'
p.save(only=[Post.content])
```

Every time you access `topic.post` using that property you are re-fetching a new instance (and the one you modified is discarded).
Issue: Model dependencies list with auto-id field and relations
Author: mrkovalchuk
State: closed
Created: 2020-12-12 19:25:57

Good day.

Model.dependencies method bring some exception for me. 

Python 3.7
Peewee 3.14.0

Example:

```python
from peewee import Model, CharField, ForeignKeyField


class A(Model):
    text = CharField()


class B(Model):
    a = ForeignKeyField(A, backref='bbbs')


list(A().dependencies())
```

Error:
```
Traceback (most recent call last):
  File "/some-path/some-project/models/tmp.py", line 12, in <module>
    list(A().dependencies())
  File "/some-path/Envs/some-project/lib/python3.7/site-packages/peewee.py", line 6528, in dependencies
    node = (fk == self.__data__[fk.rel_field.name])
KeyError: 'id'
```

What happens: `A()` object doesn't have a value for ID field. And `Model().__data__` don't return this field. Looks like a bug.

Right now, I don't know, what way we should choose for resolve this issue. I'd be happy to create PR with fix, after talk about solution. 

Thanks.

An un-saved model, by definition, will not have any dependencies -- so this does not strike me as a bug.
Issue: MySQL ([Errno 111] Connection refused) running inside Docker
Author: sriveros95
State: closed
Created: 2020-12-11 14:18:09

Hello and thanks for the amazing work.

I'm trying to use Peewee inside Docker but I keep receiving `peewee.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' ([Errno 111] Connection refused)")`

DB name, user, port host are all correct (I'm using 127.0.0.1 as host). 

I don't think the problem is MariaDB's bind-address as I'm able to connect to the DB from other docker containers running GoLang. 

Any idea? 

Running Python 3.7.9, peewee 3.14.0

The error indicates that you cannot connect to the server, so I fail to see how this is a peewee issue. Probably some docker networking issue on your end.

Can you connect to the mysql server using the mysql cli on the same host as the code is running? That'd be the best place to start debugging. But yeah -- this does not sound at all like a peewee bug to me. You might also try asking of stackoverflow if you need help debugging your docker configs.

Yeah tried with just pymysql and still erroring, sorry for the trouble, thank you @coleifer !
Issue: Validation in Custom Field Classes?  Question - NOT an Issue
Author: jvickroy
State: closed
Created: 2020-12-03 23:54:04

A custom field to store/retrieve folder/directory path names is:
```
class FolderField (TextField):
    """ custom peewee field type for folder (directory) path name """
    def db_value (self, name :str) -> Path:
        """ database string to Python Path converter """
        if not ( path := Path (name) ) .is_dir() : raise ValueError ('retrieved {name=} is not an existing folder')
        return path
    def python_value (self, path :Path) -> str:
        """ Python Path to database string converter """
        if not path.is_dir() : raise ValueError ('submitted {path=} is not an existing folder')
        return str (path)

```
which performs validation to ensure the str|Path name refers to an existing directory.

Is this bad practice?  Would it be better to perform this validation in a `check` function associated with the tables using the `FolderField`?

Thanks for this great library!

Putting it in a check constraint means needing to use a database-provided function. Unless you're using sqlite and will always have such a user-defined function available, I'm not sure how you would do this.

Peewee does not really do validation in the `db_value()` or `python_value()` methods. This should be handled at the layer that is accepting user input, e.g. a web form. The `db_value()` and `python_value()` methods are solely intended for converting between python-native types and whatever the underlying storage engine driver returns. For example, converting a `Path` object to a string instance and vice-versa.

The handling of the validation would be better done somewhere up the stack, in my experience, where you will have a better chance of informing the user or caller of the problem and allowing them to fix the issue.

Thanks for your clarification.
I will always and only be using SQLite for the back-end.  These are small projects for my personal use.  Based on your feedback, I will move these Path validations to `check` functions managed by `@pre_init` decorators for the tables.
Issue: Missing timeout on a select() to a crashed MariaDB
Author: davnat
State: closed
Created: 2020-12-02 11:09:33

Hi,
I'm connecting to a crashed MariaDB 10.5, the log says:
```Dec 01 20:31:29 db2 mysqld[683]: max_threads=102
Dec 01 20:31:29 db2 mysqld[683]: thread_count=3
Dec 01 20:31:29 db2 mysqld[683]: It is possible that mysqld could use up to
Dec 01 20:31:29 db2 mysqld[683]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 760299 K  bytes of memory
Dec 01 20:31:29 db2 mysqld[683]: Hope that's ok; if not, decrease some variables in the equation.
Dec 01 20:31:29 db2 mysqld[683]: Thread pointer: 0x7f5d9c5cb308
Dec 01 20:31:29 db2 mysqld[683]: Attempting backtrace. You can use the following information to find out
Dec 01 20:31:29 db2 mysqld[683]: where mysqld died. If you see no messages after this, something went
Dec 01 20:31:29 db2 mysqld[683]: terribly wrong...
Dec 01 20:31:29 db2 mysqld[683]: stack_bottom = 0x7f5da0132d58 thread_stack 0x49000
```
In this situation, a Model.select() seems to block indefinitely.

I'd like to be able to set a timeout, so that my application could point out that something bad happened to the DB.

I investigated a bit on this, and found that maybe I could use something like this: https://stackoverflow.com/questions/492519/timeout-on-a-function-call/494273#494273 , but I'm not sure how I could use this approach, maybe a decorator?

Or maybe I'm missing something here, I'm definitely not an expert in DB handling.

Thank you, cheers

Ok, I found that using mysqlclient instead of PyMySQL I can pass a `connect_timeout` option to `MySQLDatabase` that works as expected.

>I can pass a connect_timeout option to MySQLDatabase that works as expected.

Perfect.
Issue: Does peewee currently support json fields?
Author: zhenzi0322
State: closed
Created: 2020-11-30 04:07:59

Does peewee currently support json fields?

Read the manual

* http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#pgjson
* http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#mysql-ext
* http://docs.peewee-orm.com/en/latest/peewee/sqlite_ext.html#sqlite-json1
Issue: Dynamically select Postgres schema
Author: dhnchandan
State: closed
Created: 2020-11-30 00:27:22

I am thinking about changing Postgres schema dynamically. Every user will have a schema name in the user database and when he will login his data will be queried from his own named schema.
```
class PublicBaseModel(peewee.Model):
    class Meta:
        database = sql_db

class BaseModel(peewee.Model):
    class Meta:
        database = sql_db
        schema = 'some_name' # this will be set dynamically according to logged in user
```

I know about dynamically select [database](http://docs.peewee-orm.com/en/latest/peewee/database.html#dynamically-defining-a-database). But is it possible in case of schema?

That data is not stored thread-local and you are advised that it is a bad idea. It's probably better to dynamically generate a model-class with the desired schema than to try swapping it around. Generally this is not going to be well-supported.
Issue: Unexpected column names when using fn and .dicts
Author: albatros69
State: closed
Created: 2020-11-26 11:15:47

Consider the following example (I tried to shave it to a minimum):
```python
class Test(Model):
    start_date = DateTimeField(null=True)
    end_date = DateTimeField(null=True)

>>> list(Test.select(fn.MIN(Test.start_date), fn.MAX(Test.end_date)).dicts())
[{'start_date")': datetime.datetime(2020, 9, 11, 9, 56), 'end_date")': datetime.datetime(2020, 11, 29, 16, 0)}]
```

See the extra quote and parenthesis for the column names. One would expect something more like:
```python
>>> list(Test.select(fn.MIN(Test.start_date), fn.MAX(Test.end_date)).dicts())
[{'start_date': datetime.datetime(2020, 9, 11, 9, 56), 'end_date': datetime.datetime(2020, 11, 29, 16, 0)}]
```
I'm just proposing here to strip the colum names from the extra right parenthesis.

I'm OK with adding this, but you should *really* be explicitly aliasing these so that it is not database-dependent and fragile. I believe, furthermore, this example is specific to sqlite - postgres behaves differently (I believe the columns will be named "min" and "max", at least that's how it behaves on my laptop with postgres 13), and older versions of sqlite behave even differently if I recall correctly.

I've implemented a similar patch which also adds a test-case and merged.

Thanks a lot for the answer. You're absolutely right about the aliasing necessity. I discovered this issue as part of some quick&dirty debugging, where i didn't take the time to alias (which I usually do). As it was quite straight forward to correct, I thought it was better to consolidate that in a PR.
Issue: Select by foreign key doesn't works as expected
Author: penja
State: closed
Created: 2020-11-25 20:20:53

Hello. We have the foreign keys with related field that is not equal the primary key to the related model.
The following code snippet shows an invalid SELECT query when we use in the select query related model object instead of just identifier.
```
from peewee import *

db = SqliteDatabase(':memory:')


class Base(Model):
    class Meta:
        database = db


class A(Base):
    id = AutoField()
    a_id = CharField(unique=True, max_length=24)


class B(Base):
    id = AutoField()
    a = ForeignKeyField(A, field="a_id")


db.create_tables([A, B])

a = A.create(a_id="example")
b = B.create(a=a)
print(B.select().where(B.a == b.a).sql())
print(B.select().where(B.a == b.a_id).sql())
```
Output:
```
('SELECT "t1"."id", "t1"."a_id" FROM "b" AS "t1" WHERE ("t1"."a_id" = ?)', [1])
('SELECT "t1"."id", "t1"."a_id" FROM "b" AS "t1" WHERE ("t1"."a_id" = ?)', ['example'])

```
Thanks

Thanks for the succinct issue report. For now I've made a provisional fix which correctly delegates to the `ForeignKeyField.db_value()` method when a model instance is converted. The fix is not perfect since we still want a way to convert model instances when a converter is present (but is not able to handle the given model instance), so I am catching possible `TypeError` or `ValueError`. I believe this should work in practice and have put some new tests in place.

Many thanks.
Issue: migrator.rename_table doesn't work
Author: tres45
State: closed
Created: 2020-11-23 11:30:24

Hi, I use peewee 3.14.0 and can't rename table: peewee.ProgrammingError: relation "test_table_new" does not exist

`migrator.rename_table('test_table', 'test_table_new')`
```migration                   |   File "/usr/local/lib/python3.8/site-packages/peewee_migrate/router.py", line 183, in run
migration                   |     self.run_one(mname, migrator, fake=fake, force=fake)
migration                   |   File "/usr/local/lib/python3.8/site-packages/peewee_migrate/router.py", line 155, in run_one
migration                   |     migrator.run()
migration                   |   File "/usr/local/lib/python3.8/site-packages/peewee_migrate/migrator.py", line 129, in run
migration                   |     op.run()
migration                   |   File "/usr/local/lib/python3.8/site-packages/playhouse/migrate.py", line 164, in run
migration                   |     self._handle_result(method(*self.args, **kwargs))
migration                   |   File "/usr/local/lib/python3.8/site-packages/playhouse/migrate.py", line 172, in inner
migration                   |     return fn(self, *args, **kwargs)
migration                   |   File "/usr/local/lib/python3.8/site-packages/playhouse/migrate.py", line 455, in rename_table
migration                   |     pk_names = self._primary_key_columns(old_name)
migration                   |   File "/usr/local/lib/python3.8/site-packages/playhouse/migrate.py", line 444, in _primary_key_columns
migration                   |     cursor = self.database.execute_sql(query % tbl)
migration                   |   File "/usr/local/lib/python3.8/site-packages/peewee.py", line 3136, in execute_sql
migration                   |     self.commit()
migration                   |   File "/usr/local/lib/python3.8/site-packages/peewee.py", line 2902, in __exit__
migration                   |     reraise(new_type, new_type(exc_value, *exc_args), traceback)
migration                   |   File "/usr/local/lib/python3.8/site-packages/peewee.py", line 185, in reraise
migration                   |     raise value.with_traceback(tb)
migration                   |   File "/usr/local/lib/python3.8/site-packages/peewee.py", line 3129, in execute_sql
migration                   |     cursor.execute(sql, params or ())
migration                   | peewee.ProgrammingError: relation "test_table_new" does not exist
migration                   | LINE 5:                 pg_class.oid = 'test_table_new'::regclass...
migration                   |                                        ^
migration                   | 
migration exited with code 1
```

Pseudo table structure:
```
test_table:
    id,
    a_id fk on table_a,
    b_id fk on table_b,
    unique index(a_id, b_id)
```

This code is covered by unit-tests and I believe it is working properly.

https://github.com/coleifer/peewee/blob/efb41a944e926ea201d120e50d3d864028fbf899/tests/migrations.py#L392-L420