akumoth/peewee-issues huggingface dataset:
Issue: Aggregating by month: "user-defined function raised exception"
Author: piotrek-k
State: closed
Created: 2018-01-30 15:46:14

Hi,
I'm trying to extract month from `date` column in my database. [Just as it's shown here.](http://docs.peewee-orm.com/en/latest/peewee/models.html#datetimefield-datefield-and-timefield)

Here is my code:
```
 query = (dc.Message.select(dc.Message.date.month.alias('month')).limit(5))
    
    for q in query:
        print(q.month)
```
And Message model:
```
class Message(BaseModel):
    text = CharField(null = True)
    created_by = ForeignKeyField(User)
    posted_in = ForeignKeyField(Chat)
    date = DateTimeField()
```

When I try to execute my code, I get this message in debug console:

> peewee.OperationalError: user-defined function raised exception

Do you have any ideas how to fix that?

What is the raw data stored in the Message.date column?

Executing this code
```
    query = (dc.Message.select(dc.Message.date).limit(5))
    
    for q in query:
        print(q.date)
```
Gives me this response:

> 2016-08-31 15:39:00+02:00
> 2016-08-31 15:39:00+02:00
> 2016-08-31 15:25:00+02:00
> 2016-08-31 14:43:00+02:00
> 2016-08-31 14:31:00+02:00 

The problem is that the datetime strings contain timezone information, which is not supported by these methods. If using SQLite, which does not contain a native datetime type, dates are stored in a text column in a format that ensures lexicographic ordering (so sorting and comparing datetimes works as you'd expect). You have the format correct (Y-m-d H:M:S) but the inclusion of the timezone will make ordering not work correctly (unless all values have the same timezone, which, if the case, means you can probably do without storing it anyways?). So to use the helper functions with SQLite, you'll want to store all datetimes as naive datetimes. If you use timezones, just convert to UTC before storing and convert back to local-time before using them in your application.

@coleifer thanks, indeed it started to work after I removed timezone information.

There is still a little problem with `dc.Message.date.month` though. Ordering algorythm treats it as if it was string. So the order now looks like: "1", "10", "11", "12", "2" etc. Is there some quick workaround?

You should be able to cast as an integer. [Sqlite Cast docs](https://sqlite.org/lang_expr.html#castexpr):

```python
MyModel.select().order_by(MyModel.timestamp.month.cast('INTEGER'))
```
Issue: Peewee 3.0.2, what happened to `playhouse.test_utils.test_database`?
Author: cLupus
State: closed
Created: 2018-01-30 11:39:12

Hi,
The method `test_database` seem to have disappeared, and I can't find any references to it in the new documentation (or in the changes made).

Is there a new way of doing the same, or has the functionality ben removed for good?

The functionality has been removed. You can instead use:

* http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.bind
* http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.bind_ctx

These methods allow you to bind a model (and optionally its relations) to a database.

The bind() method will not create the tables in the same way test_database did, as it is more generally useful for binding a graph of models to a different database, which is presumed to exist already.

Thanks 😄
Issue: Peewee 3.0.2, what happened to `Selectquery.naive`?
Author: csytan
State: closed
Created: 2018-01-30 08:51:59

Here are the docs from 2.7.0, but I see no mention of it in the 3.0.2 change log.

http://docs.peewee-orm.com/en/2.7.0/peewee/api.html#SelectQuery.naive

Thanks!

Ah, thanks! I'll correct the oversight.

In 3.x you can use `.objects()` instead.
Issue: Field name case sensitivity bug
Author: rene-dev
State: closed
Created: 2018-01-30 03:12:20

I have a field name which is uppercase, and the model looks like this:
`foo = IntegerField(column_name='Foo', null=True)`
for some reason the field is always None in the query result.
however, if I change the model to lowercase:
`foo = IntegerField(column_name='foo', null=True)`
it works as expected.
I ran the sql output manually, and it contained numbers instead of null.
the sql query reflects the case of column_name string, and both queries work.
according to the mysql docs field names are never case sensitive:
https://dev.mysql.com/doc/refman/5.6/en/identifier-case-sensitivity.html

the model was created with pwiz, and column_name reflects the case of the database, which I think is correct.
as both queries work, there must be some bug in the result handling in peewee.

Im using PyMySQL and python3.

Which peewee version are you using? Sorry--I suppose it's clear that you're using 3.x.

I've replicated this locally using Postgres. Has to do with some bug in the code assigning column values to instance attributes. Will update and push a fix as soon as I have more info.

Thank you so much for taking the time to report this issue, as this was a serious bug!! What happened was the code for constructing model instances was not translating the underlying column name back to the appropriate field name.

I've pushed 3.0.3 to remedy the issue.

Thanks! I was using 3.0.2
Issue: Add base class rename
Author: aconz2
State: closed
Created: 2018-01-29 19:42:53


Cheers. This wasn't exactly a public API, but I guess it doesn't hurt to include it?

Yea I just ran into this while upgrading some code so figured I would try to give others a heads up. I have some scripts that do consistency checks that totally reach in where they're not supposed to.

Thank you for your work!
Issue: Oracle
Author: darena-mdsol
State: closed
Created: 2018-01-24 18:20:47

Is it possible for configure cx_Oracle for accessing for oracle DB?

Yep, peewee works well with DB-API 2.0 drivers. See docs:

http://docs.peewee-orm.com/en/latest/peewee/database.html#adding-a-new-database-driver
Issue: Feature request: Get by primary key shorthand.
Author: conqp
State: closed
Created: 2018-01-24 14:17:23

I often want to get instances of models by the respective primary key value.  
It would be nice to have a short-hand for:

    def by_pk(pk_value):
        return Model.get(Model._meta.primary_key == pk_value)

Hence, I suggest adding `__getitem__` to `BaseModel`:

    def __getitem__(self, pk_value):
        return self.get(self._meta.primary_key == pk_value)

So we can use:

    try:
        record_with_pk_12 = Model[12]
    except DoesNotExist:
        print('No record with primary key 12.')

This is already implemented in 3.0a.
Issue: Trouble compiling and running peewee with speedups
Author: cLupus
State: closed
Created: 2018-01-22 23:26:04

Hi,
After building peewee with python and the SQLite extension speed ups, I get the following error when running the tests.

```
FAIL: test_regexp (playhouse.tests.test_fields.TestFieldTypes)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/***/build/peewee-2.10.2/playhouse/tests/test_fields.py", line 377, in test_regexp
    assertValues('^ab', 'abcdefg', 'abcd')
  File "/***/build/peewee-2.10.2/playhouse/tests/test_fields.py", line 375, in assertValues
    self.assertEqual(values, list(expected))
AssertionError: Lists differ: [] != ['abcdefg', 'abcd']

Second list contains 2 additional elements.
First extra element 0:
'abcdefg'

- []
+ ['abcdefg', 'abcd']

----------------------------------------------------------------------
Ran 383 tests in 5.393s

FAILED (failures=1)
```

Any tips?

Not sure... you might try Peewee 3.0a?

Tests passed fine for me, for what it's worth.

I've now tried the same with 3.0a (0914cee).
The test above now succeeds, however now there are two new failures:


```
Unable to import sqlite C extension tests, skipping.
Unable to import postgres extension tests, skipping.
Unable to import SQLCipher extension tests, skipping.
Unable to import sqlite extension tests, skipping.
...........................................................................................................................................................................................................................................................................FF...........................................................................................................................
======================================================================
FAIL: test_assert_query_count_ctx_mgr (tests.test_utils.TestQueryCounter)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/***/build/peewee-3.0a/tests/test_utils.py", line 89, in test_assert_query_count_ctx_mgr
    self.assertRaises(AssertionError, will_fail)
AssertionError: AssertionError not raised by will_fail

======================================================================
FAIL: test_assert_query_count_decorator (tests.test_utils.TestQueryCounter)
----------------------------------------------------------------------
Traceback (most recent call last):
  File "/***/build/peewee-3.0a/tests/test_utils.py", line 77, in test_assert_query_count_decorator
    self.assertRaises(AssertionError, will_fail_under)
AssertionError: AssertionError not raised by will_fail_under

----------------------------------------------------------------------
Ran 392 tests in 6.292s

FAILED (failures=2)
```

In both cases the command
```
$PYTHON setup.py build_ext \
 && $PYTHON setup.py build \
 && $PYTHON setup.py install \
 && $PYTHON runtests.py
```

was run
Issue: multi query slow
Author: m358807551
State: closed
Created: 2018-01-22 05:10:57

I have thousands of ids for a table,and I want to query model one by one by use `xxx.select().filter(id==id).first()` in a for loop(not used `>>` operator because if the given id not exist in the table, I want to get None), but this way need thousands of db query and the huge total time() is unacceptable.
I need the query result is in order and be None if the given id not found in the table, of course as fast as possible, anyone has some good ideas? 

This is the most ludicrous and unrealistic ticket I've seen in a long time. There's no reason you should write your code in this way, in my opinion.

If you want to get `None` for the missing values, then you could do something like this:

```python
query = ModelClass.select().order_by(ModelClass.id)

last_id = current_id = 0
missing_ids = []
for obj in query:
    current_id = obj.id
    if current_id != last_id + 1:
        missing_ids.extend(range(last_id, current_id))
    last_id = current_id
```

Does that make sense?

Thanks for reply.
Your suggestion would work in most cases, but in my case, the table has millions of rows, and I do not think putting all of them in memory is a good idea.
What I need is a function like `def get_models_by_ids(ids): ...`, and when the given id is not found in the table, get a `None` instead.
And I finally found a not elegant way:
```
def get_models_by_ids(given_ids):
    query = ModelClass.select().where(ModelClass.id << given_ids)
    id2model = {model.id: model for model in query}
    models = [id2model.get(id_) for id_ in given_ids]
    return models
```
It is ugly but it really worked. Does peewee have a better way to do things like that? May be it's ludicrous, but I really need a function like that.

Peewee can be made to use less RAM when iterating over large result-sets by chaining a call to `.iterator()` in your query:

```python
query = ModelClass.select().order_by(ModelClass.id).iterator()

last_id = current_id = 0
missing_ids = []
for obj in query:
    current_id = obj.id
    if current_id != last_id + 1:
        missing_ids.extend(range(last_id, current_id))
    last_id = current_id
```

The solution you've commented should work fine, too, of course.

Peewee doesn't include anything like what you're asking for, no.

Thanks for your explanation
Issue: Default index for Fields
Author: tomako
State: closed
Created: 2018-01-20 07:04:10

Most of the Fields have `index=False` by default. But there are few exceptions in postgres_ext package like ArrayField, BinaryJSONField, HStoreField, TSVectorField where this changed and they get `index=True` silently.
I'm saying silently because I haven't seen in the doc and noticed that only later. Maybe our case is not a regular case because we don't need index for a BinaryJSONField field. But it was generated and was huge.
Though it seems logical to add index to these fields but changing the default behavior (comparing to other Fields) can be confusing. Maybe a highlighted notice in the doc could help preventing such cases.

Fixed, thanks for bringing this to my attention (and sorry for the confusion it caused).
Issue: SQLite datetime field is str after save
Author: lucasrc
State: closed
Created: 2018-01-19 14:49:57

class Customer(BaseModel):

    date_test = DateTimeField()

Customer(date_test=now()).save()
customer = Customer.get()
customer.date_test  # is a str now not datetime obj

I don't know what you're talking about:

```python
In [1]: from peewee import *

In [2]: db = SqliteDatabase(':memory:')

In [3]: class Test(Model):
   ...:     date = DateTimeField()
   ...:     class Meta:
   ...:         database = db
   ...:         

In [4]: Test.create_table()

In [5]: t = Test(date=datetime.datetime.now())

In [6]: t.save()
Out[6]: 1

In [7]: Test.get().date
Out[7]: datetime.datetime(2018, 1, 19, 9, 5, 6, 364642)
```

Sorry I found the error: Its a timezone. In postgress works very well

import datetime

from peewee import SqliteDatabase, Model, DateTimeField

from pytz import timezone

eastern = timezone('US/Eastern')
db = SqliteDatabase(':memory:')


class Test(Model):

    class Meta:
        database = db

    date = DateTimeField()


Test.create_table()
Test(date=datetime.datetime.now(tz=eastern)).save()

test = Test.get()
assert isinstance(test.date, datetime.datetime)

Traceback (most recent call last):
  File "test_peewee.py", line 24, in <module>
    assert isinstance(test.date, datetime.datetime)
AssertionError

A-ha, thanks @lucasrc ... So SQLite does not have a native datetime column type (just text, blob, int, float and null). Because of this datetimes are stored in lexicographically-sortable strings (YYYY-mm-dd HH:MM:SS.ffffff by default). When Peewee pulls data out of the db, it attempts to convert the string into a datetime using a list of supported formats.

When a timezone is included, the format becomes YYYY-mm-dd HH:MM:SS.ffffff-ZH:ZM -- which is not among the supported formats. Failing to convert to datetime, peewee just returns the string.

To support timezones with SQLite, you might (untested, beware):

```python
class DateTimeTZField(DateTimeField):
    def python_value(self, value):
        if value is None: return
        datetime_str, zone = value.rsplit(' ', 1)  # Expected YYYY-mm-dd HH:MM:SS.ffffff ZZZZ
        val = datetime.datetime.strptime(datetime_str, '%Y-%m-%d %H:%M:%S.%f')
        if zone.startswith('-'):
            mult = -1
            zone = zone[1:]
        else:
            mult = 1
        zh, zm = int(zone[:2]), int(zone[2:])
        offset = FixedOffset(mult * (zh * 60 + zm))
        return val.replace(tzinfo=offset)

    def db_value(self, value):
        return value.strftime('%Y-%m-%d %H:%M:%S.%f %z') if value else None
```

I think in 3.2 you can use %z with strptime, but since Peewee supports 2.7 I don't think that behavior can be relied upon.

As I was thinking -- you know, using timezones will **break** lexicographic sorting. **You're best off converting everything to UTC and storing it as a naive datetime.**

Thanks man for explanaition
Issue: Cause a error when using join
Author: cfk1996
State: closed
Created: 2018-01-19 03:58:28

## These are my models:
```python
class MyBaseModel(Model):
    class Meta:
        database = db
class Category(MyBaseModel):
    category_name = CharField(null=False, unique=True)
class CsArticle(MyBaseModel):
    title = CharField(null=False, unique=True)
    md = TextField(null=False)
    html = TextField(null=False)
    date = DateField(null=False)
    category_id = IntegerField(null=False)
    number = IntegerField(null=False, default=0)
```

## When I do this query:
```python
datas = ( CsArticle
                .select(CsArticle, Category)
                .join(
                    Category,
                    on=(CsArticle.category_id == Category.id)).alias('cate')
                .order_by(CsArticle.date.desc()))
response.append({'title': data.title,
                             'category': data.cate.category_name,
                             'date': data.date,
                             'id': data.id, 'number': data.number})
```
## It's caused such error:
`File "/python-code/kunkun_blog/reader_request/article_list.py", line 44, in _get_cs_list
    'category': data.cate.category_name,
AttributeError: 'CsArticle' object has no attribute 'cate'`



Why is `category_id` not a `ForeignKeyField`?

```python
class CsArticle(MyBaseModel):
    ...
    category = ForeignKeyField(Category)  # This will work instead of category_id field.
```

After that, your ".alias()" needs to apply to the "on" expression...not the query as a whole:

```python
.join(
    Category,
    on=(CsArticle.category_id == Category.id).alias('cate'))  # Note parentheses.
```
Issue: pwiz does not generate related_name argument
Author: simopal6
State: closed
Created: 2018-01-18 10:37:42

As in the title, pwiz does not seem to use the `related_name` attribute on `ForeignKeyField`s. Is this expected? And is there a way to force it to do it?

The related_name (backref in 3.0) is a property that only exists in python/peewee. It's not an actual database thing, just a convenient way to reference the set of referenced objects. What would you expect to happen?

Sorry, I wasn't very clear. I have two tables, `video` and `frame` (as in "a video has many frames"), with a foreign key constraint defined on `frame.videoid`.

When I run `pwiz`, it creates the models and adds the `ForeignKeyField` to the generated `Frame` class. However, no `related_name` argument is passed to `ForeignKeyField`, so I'm unable to easily access the frames associated to a given video from a `Video` model instance.

Of course I can easily modify the generated code and add the `related_name` argument myself, but I was wondering how come `pwiz` doesn't do it by itself.

The `related_name` isn't explicitly defined because, if the user wants to use a non-default `related_name` pwiz has no way of anticipating what that might be.

The default related name/backref is "modelname_set", so in your case it would be `Frame.video_set` by default.

If you want something different then you'll of course need to edit the generated code.

Ok, I see now. I didn't know that a default attribute on the "other" model would be created even without specifying `related_name` (I'm not sure this is documented). Thank you for your help!
Issue: Compute COUNT of a RawQuery
Author: vadimkantorov
State: closed
Created: 2018-01-16 19:11:52

I have a complex raw query with joins, but I'm interested only in the number of rows in the result set.

For now my workaround is: `len(list(MyModel.raw('SELECT obj.id FROM MyModel obj ...')))` but it's not very elegant and allocates objects uselessly.

Attempts like `MyModel.raw('SELECT COUNT(*) FROM MyModel obj ....').scalar()` (always returns 1) or `MyModel.raw('SELECT obj.id FROM MyModel obj ....').count()`, or `len(MyModel.raw('SELECT obj.id FROM MyModel obj ...'))` have failed.

Thanks!

What about:

```python
sql = "my crazy query"
count_sql = "select count 1 from %s as _" % sql
count, = db.execute_sql(count_sql).fetchone()
```

Closing due to no response.
Issue: [Proposal] Decoratorsfor fields conversion
Author: gugadev
State: closed
Created: 2018-01-16 14:14:42

First, congratz to the team for your excelent work. Should be nice have the option to convert automatically a field when the ORM fetchs data or call some property. For example, this model have a BLOB field:

```python
class User(BaseModel):
    @to_base64 # this fn converts the binary to base64 on property call
    photo = BlobField()

```

When you get an user and call `.photo` property, the decorators is executed and you get the base64 representation. This could be useful when serialize a model to JSON, for example.

Could this be feasible?

Thanks for reading.

@gugadev -- That's invalid syntax.

Your example `to_base64` decorator -- do you intend that to be implemented in Python or is that a function provided by your database? Is this feature intended to be bi-directional, i.e.:

```python
with open('image-file.jpg', 'rb') as fh:
    raw_data = fh.read()

user = User()
user.photo = raw_data
user.save()  # Is the photo data converted to base64 before sending it to the database?

user_from_db = User.get(User.id == 1)
data = user.photo  # When accessing the property for reading, is the raw data returned, or base64?
```

I don't think your proposal is very well thought out.

If you're looking to apply a function to the data before writing/after reading, please check out this document:

http://docs.peewee-orm.com/en/latest/peewee/models.html#creating-a-custom-field
Issue: cast not supported
Author: ra-esmith
State: closed
Created: 2018-01-12 19:22:59

Hello,

Peewee rocks!  Thank you for a great ORM!

I am porting unto 3.0 and ran into an error that
cast was not supported.  
How might I do the following

from playhouse.shortcuts import cast
                expr = (cast(column, 'text').contains(str((request_values[key_search]))))

Thanks,
Evan

Cast is now just a method call:

```python

expression = column.cast('text').contains(...)
```