akumoth/peewee-issues huggingface dataset:
Issue: TimestampField type is different if accessed via query as opposed to directly
Author: vsajip
State: closed
Created: 2018-03-17 20:08:22

If I have a model which has a timestamp field:
```
class MyModel(BaseModel):
    ...
    expiry = TimestampField(utc=True, index=True, default=None)
    ...
```
If I then create an instance using e.g.
```
instance = MyModel.create(..., expiry=time.time() + 86400, ...)
```
then the type of `instance.expiry` is a `float`. However, if I access via a query, then it's a `datetime`:
```
instance = MyModel.select().first()
```
Is there a good reason for this inconsistency, and how is it best avoided?


`DateTimeField` uses a "datetime" column-type for the backend data-storage. In SQLite in particular, which does not have a date/time column type, it can be much more efficient to store the data as an integer timestamp. The `TimestampField` exists to allow you to work with Python `datetime` objects but store them in the database as integers.

If you are simply trying to store a floating-point timestamp, just use a `FloatField` or a `DoubleField`.

> and how is it best avoided?

I have been working through this issue as I was inputting a timestamp from an API and just as you described, I would get a float field and a datetime in the different scenarios.

For anyone else coming across this issue, the solution for me was to convert the timestamp into a datetime object before creating the instance.

In the example by OP, you could use:
`instance = MyModel.create(..., expiry=datetime.utcnow() + timedelta(seconds=86400), ...)`
or similar, depending upon whether you need offset aware dates or not.
Issue: Make connecting signals operation idempotent
Author: bmwant
State: closed
Created: 2018-03-16 17:05:28

## Rationale
Attaching receivers should not crash a program. 

## Use cases
1. Module has been reloaded
`m.py`
```
from peewee import *
from playhouse import signals

class A(signals.Model):
    a = TextField(default='')

state = []

@signals.post_save(sender=A)
def post_save(sender, instance, created):
    state.append((sender, instance, instance._pk, created))
```
and `test.py`
```
import m

try:
    from importlib import reload
except ImportError:
    pass

reload(m)
```
will raise `ValueError`.

2. We should allow assigning same receiver multiple times (it should be idempotent) to handle cases like this
`test2.py`
```
from peewee import *
from playhouse import signals


class A(signals.Model):
    a = TextField(default='')


database = SqliteDatabase(':memory:')
A._meta.set_database(database)
A.create_table()

state = []


@signals.post_save(sender=A)
def post_save(sender, instance, created):
    state.append((sender, instance, instance._pk, created))


def ensure_receivers_are_connected():
    # I am not sure whether `flush` was invoked or not at some point
    signals.post_save(sender=A)(post_save)


ensure_receivers_are_connected()

a = A(a='my text')
a.save()
assert len(state) == 1
```
But I guess we still need to prohibit binding in a such way function to different sender. In case you intentionally want to do such a thing you have `disconnect` method.

@coleifer could you review this one?

I don't think this is exactly a bug, and I don't love the solution proposed in this patch. I don't really think of registering a signal as an idempotent operation, and registering the same combination of receiver/name/sender seems like a user mistake.

I've tried to address some of the quirks of connect/disconnect in two commits:

* 8992c69ed8708d89e4f888d97b997a0232098141
* 2d421a9db9ddc6223c35972291afe37bc2b20c68

Registering a handler twice for the same combination of receiver/name/sender will result in a `ValueError`. I'm not clear on how this would come up in practice, since when a module is imported once it continues to remain in the sys module cache. If you want to avoid side-effects you can do your registration in an init function that only runs once.
Issue: update_or_create is missing ?
Author: elonzh
State: closed
Created: 2018-03-16 06:46:54

I'm curious that why the model method `update_or_create` is not provided.
https://github.com/coleifer/peewee/commit/073364ad89a8de48f4a25bbf6106bf42050d34fa

Because it's hard to correctly write a generic version of these methods that isn't vulnerable to race conditions. The proper way to implement this is very short, and as a result, I leave it up to users to implement their own method for create-or-get (similar workflow for insert-or-update):

```python
# create-or-get
try:
    with db.atomic():
        return SomeObj.create(...)
except IntegrityError:
    return SomeObj.get(...)
```
Issue: db.batch_commit() mode
Author: HarryR
State: closed
Created: 2018-03-14 16:09:38

```python
db = SqliteDatabase(':memory:')
with db.batch_commit(10):
    for n in range(0, 1234):
        Model.create(**...)
```

Every N'th model creation commits the transaction, the equivalent Python without `batch_commit` is:

```python
with db.manual_commit():
    db.begin():
    for n, row in enumerate(...):
        Model.create(**row)
        if n % 100 == 0:
            db.commit()
            db.begin()
    db.commit()
```

Having a `batch_commit` would make it easier/simpler to make batch inserts faster.

Alternatively there is `Model.insert_many`, but to create multiple linked models at the same time the `batch_commit` method could be useful.

Using `db.atomic()` works unless you have a large number of items to insert, in that case periodic commits are used to ensure data is synchronised to disk, e.g.:

```python
with db.atomic():
    for row in data:
        row.update(defaults)
        Model.create(**row)
```

it's probably easier to do it using a batch iterator instead of modifying peewee:

```python
for work in batch(rows, n=1234):
    with db.atomic():
        for row in work:
            Model.create(**row)
```

Feel free to close, I answered own question...

I originally thought that the `10` in the call to batch_commit() was the total number of batches, but on reading again I think you mean to imply a batch size of 10, or, a commit every 10 rows?

Something like that should be do-able, but it is probably cleaner to pass an iterator, e.g.:

```python

for row in db.batch_commit(list_of_data, 100):
    Model.create(**row)
```

Continuing that thought, the `batch_commit` would be a generator that would:

```python

# Helper.
def chunked(iterable, n):
    marker = object()
    for group in (list(g) for g in izip_longest(*[iter(iterable)] * n,
                                                fillvalue=marker)):
        if group[-1] is marker:
            del group[group.index(marker):]
        yield group

# method on Database
def batch_commit(self, it, n):
    for obj_group in chunked(it, n):
        with self.atomic():
            for obj in obj_group:
                yield obj
```

I didn't test this yet, so I'm not sure if it works. Just thinking out loud.

Added in 3d4e6e4.
Issue: Introspector does not preserve capitalized columns name
Author: ketozhang
State: closed
Created: 2018-03-14 10:41:03

Given a simple table like

```python
class Table(Model):
    employee = CharField(unique=True)
    Dept = TextField()
```

On the attempt in trying to introspect this table `models = Introspector.from_database(db).generate_models()`, the keys in `models['Table'].__dict__` are all lowercase:

```python

>>> models['Table'].__dict__
# psuedo output
mappingproxy({...,
'employee': <peewee.FieldDescriptor at 0x7f480108c748>,
'dept': <peewee.FieldDescriptor at 0x7f480108ceb8>, # want 'Dept' got 'dept'
...})
```

** Is there any way to preserve capitalized object names?**

---

The reason why I need this is because of such an insert statement:

```python
data = {
'employee' : 'John Smith'
'Dept' : 'Accounting'
}
Table.insert(**data) # or Table.create(**data) getting two different errors weirdly.
```


The introspector "python-izes" the column names by default. So if you have such a table, the actual field definition will look something like:

```python
dept = TextField(column_name='Dept')
```

In this way, the actual underlying column-name is still preserved and used, it's just that the accessor for it conforms to a Pythonic naming convention.

If you want to use the column-names as-is, you can pass `literal_column_names=True` to the `introspect()` or `generate_models()` methods.
Issue: how to get sql when save
Author: visonforcoding
State: closed
Created: 2018-03-14 10:19:17

can i get the sql when i use save function .  i don`t know why i can`t save.

You can turn on query logging: http://docs.peewee-orm.com/en/latest/peewee/database.html#logging-queries

```python
# Print all queries to stderr.
import logging
logger = logging.getLogger('peewee')
logger.setLevel(logging.DEBUG)
logger.addHandler(logging.StreamHandler())
```

Just a guess, but if you're not able to save...are you explicitly specifying the primary key? In other words, is your primary key something other than an auto-incrementing integer? If so, check out:

http://docs.peewee-orm.com/en/latest/peewee/models.html#id3
Issue: ProgrammingError during get_or_create
Author: jonathan-s
State: closed
Created: 2018-03-10 13:30:28

I'm running Postgres with peewee 2.10.2 

When running the following code: 

```
Author.get_or_create(
    id=author['id'],
    defaults=author
)
```

I get syntax error at or near "AS" `LINE 1: ...t1"."country" FROM "author" AS t1 WHERE ("author" AS t1 AND ...` It happens for the second AS. The full query is the following.

```
SELECT "t1"."id", "t1"."ctime", "t1"."mtime", "t1"."name", "t1"."gender", "t1"."country" 
FROM "author" AS t1 
WHERE ("author" AS t1 AND ("t1"."id" = %s)) [16667]
```

I'm using peewee-async so it may have to do with that library. Checking..
Issue: Flat tuples
Author: jonathan-s
State: closed
Created: 2018-03-10 12:42:21

It would be nice if there was an option to return tuples that were flat. Much like the django queryset `values_list(flat=True)`. 

You can easily write a little transform:

```python

import operator
flat_transform = lambda query: map(operator.itemgetter(0), query)

x = [(1,), (2,), (3,), (4,)]
list(flat_transform(x))  # [1, 2, 3, 4]
```
Issue: Support prefetching ManyToManyFields
Author: iBelieve
State: closed
Created: 2018-03-09 18:17:35

Previously, prefetching a relationship exposed in a ManyToMany field would only expose the through model’s prefetched data. Now, the many to many field correctly exposes the prefetched data via the through field’s prefetched results, rather than requerying what has already been prefetched.

Interesting... Since the `prefetch` API requires the specification of the intervening models, it *does* seem a bit un-intuitive to access the related instances through the many-to-many field. Or maybe it is intuitive? I'm not sure...ManyToManyField is such a hack!

At least for my use case, it feels intuitive, as I'm trying to fetch a list of users and their teams. I'm using prefetch as an optimization to prevent an N+1 situation for fetching teams for each user, so it feels most intuitive to access prefetched teams through the ManyToManyField.

Basically, I'd like to go from this:

```
for user in User.select():
    print(user.teams)
```

to the optimized version:

```
for user in prefetch(User.select(), TeamMember, Team):
    print(user.teams)
```

Without this, I'd have to do something like:

```
for user in prefetch(User.select(), TeamMember, Team):
    teams = [through.team for through in user.teammember_set]
    print(teams)
```

Which feels un-intuitive and requires that I treat prefetched queries differently from regular queries. Of course, the alternative is do not use ManyToManyField and just have a property on user that fetches teams which also works with prefetched, but for my use case ManyToManyField is simple and meets my needs.

Agreed, thanks for the patch!
Issue: Why ForeignKeyAccessor.__set__() doesn't update instance._dirty?
Author: TwoAnts
State: closed
Created: 2018-03-09 16:02:52

I use `=` to set a `ForeignKeyField` and call `save()` method, but it doesn't take effect.

When I read the source code in peewee.py, I found that `ForeignKeyAccessor.__set__()` seems not to update the `instance._dirty` . This leads to a failure to update `ForeignKeyField` by calling `save()` method.

Please fix it, if it is a bug. 


Fixed, thanks for reporting.
Issue: Clarify comment that sounded like create_tables() in Peewee 3 will dr…
Author: bryhoyt
State: closed
Created: 2018-03-08 23:05:32

…op your table if it exists.

This scared me on first read. I had to read it a couple times to figure out that the second part of the parenthetical comment was talking about the drop_tables() method. And I had to read the source code to be fully sure.

I like the updated functionality, though. It's a great improvement, thanks!

Cheers!
Issue: Postgres Match returns only one result
Author: metakermit
State: closed
Created: 2018-03-04 12:22:45

Hi, I'm using the `Match` functionality to access Postgres' [full-text search](http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#pg-fts) (`from playhouse.postgres_ext import Match`), but I only get 1 result.

For comparison, using the same search query (just a single word) the `contains` query returns more results. How can I access all the results from a `Match`?

```
ipdb> len([m for m in Act.select().where(Act.content.contains(query))])
66
ipdb> len([m for m in Act.select().where(Match(Act.content, query))])
1
```

Not sure what the issue might be, but the `Match` function is tested and returns multiple results where appropriate. Perhaps there's an issue with the query you're providing?

Example ipython notebook showing it working:

https://gist.github.com/coleifer/cee34b9db777c76de95a0c717aadd030
Issue: Documentation for `Case` Shortcut
Author: r0mille
State: closed
Created: 2018-03-01 17:06:25

https://github.com/coleifer/peewee/blob/master/playhouse/README.md
mentions a section for `Shortcuts/helpers` where CASE statements can be constructed. 

However there is nothing in Peewee docs that would help a user figure out how to construct `CASE` statements.
http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#shortcuts

Anything would be appreciated! Thanks in advance!

I forgot that file existed, and so it hasn't been updated yet for 3.x.

You can find the `Case` helper here, as it's now part of "peewee.py": http://docs.peewee-orm.com/en/latest/peewee/api.html#Case
Issue: Aggregation result as list
Author: ak4nv
State: closed
Created: 2018-03-01 05:28:14

Hi!
First of all my congratulations with new major release! I've been using peewee several years and I love it!

This code below returns aggregated field as string. Is there another way to get result as list?
```python
    qs = (Model
          .select(Model.group, fn.array_agg(Model.name).alias('name'))
          .group_by(Model.group)
          .order_by(Model.group))
```
Thanks a lot!

I'm going to assume the `name` field has a string as Python equivalent. By default, the result of the query is coerced to the same type of the original field, a string in this case. You can prevent this by doing:

``` python
    qs = (Model
          .select(Model.group, fn.array_agg(Model.name, coerce=False).alias('name'))
          .group_by(Model.group)
          .order_by(Model.group))
```
This should return a list!

@Karel-van-de-Plassche Thanks a lot!
I've found solution for 2 version of peewee:
```python
f = fn.array_agg(Model.name)
f._coerce = False
qs = (Model
        .select(Model.group, f)
        .group_by(Model.group)
        .order_by(Model.group))
```

@ak04nv Great! Be aware that `_coerce` is part of the private API, and might change without notice. If this solution works for you, feel free to close the issue.

Did @Karel-van-de-Plassche's suggestion of passing `coerce=False` into the function work for you @ak04nv ?

@coleifer yes and thank you guys again!
Issue: Returns on inserts
Author: justin-richert
State: closed
Created: 2018-02-28 00:41:49

I'm having a little trouble with peewee's insert query. 

It would be ideal if  `<ModelInstance>.create()` would allow for returning all columns. I know it returns primary keys automatically, but I have a model that has server side defaults besides the primary key that I need to render in a response after inserting a row.

However, given that implementation may not be likely (or at least not soon), I turned to `<ModelInstance>.insert().returning(<column_list>).execute()`. I don't pass anything to insert yet because I'm testing on a table with two columns, one being of type bigserial, one being of type timestamp with tz, both with values generated on the db server side.

I've tracked through a significant portion of the code executed from that line of code, and in doing so, I came across `last_insert_id`, a function that checks the `query_type` param. In the case of the code I executed for the insert, `query_type` is `Insert.SIMPLE`, which is equal to 0. Then, `last_insert_id` executes this line: `return cursor if query_type else cursor[0][0]`, which scraps the result set I had achieved by invoking the returning function and just returns the id of the inserted row. I can't tell if that is a bug and this line should read `return cursor if query_type is not None else cursor[0][0]`, or if that was intended. If it is intended to affect the `Insert.SIMPLE` query type, it seems as though it should also check if `.returning` was used.

The code gets a bit difficult when it's trying to figure out what to return for insert queries when the db supports returning clauses. By default we want to select the table's primary key and return the last-inserted ID...but if the user specifies something else, we probably should return a cursor over the returned results.

In the current code, you should be able to do this:

```python

class ServerDefault(Model):
    timestamp = DateTimeField(constraints=[SQL('default (now())')])

query = ServerDefault.insert().returning(ServerDefault.id, ServerDefault.timestamp)
last_id = query.execute()
new_rows = list(query)
new_id, new_timestamp = new_rows[0]
```

I made a small fix, which will cause `execute()` to return the cursor if the returning clause is non-default, so in master you would write:

```python
query = ServerDefault.insert().returning(ServerDefault.id, ServerDefault.timestamp)
cursor = query.execute()
new_id, new_timestamp = cursor[0]
```

Thanks for bringing this to my attention -- it highlighed a fragile API. The API is still more fragile than I would like, but hopefully it's more obvious now... If you specify a returning clause, you'll get a cursor back -- whether inserting one row or multiple rows.

It didn't occur to me to check the query object after execution for some reason. Thanks for pointing that out.

I really appreciate your help with this! And thanks for the awesome project.

No worries, glad to help. Once I push a new release (3.1.2) the query will directly return a cursor object so it won't be quite so confusing hopefully.
Issue: Cursor count attribute returns 0, when shouldn't
Author: vikt0rs
State: closed
Created: 2018-02-27 08:27:23

For some reasons, when I try to check if request returns some data with the "count" attribute, the check fails because it always returns 0. Please, suggest.
PS - this also worked in 2.X

```
>>> docs.select().execute()
<peewee.ModelObjectCursorWrapper object at 0x1089b80f0>
>>> docs.select().execute().count
0
>>> len(docs.select().execute())
308
```

The `count` attribute of the cursor wrapper is something different than the `count()` method of a `Select` query:

```python

create_3_users()
User.select().count() == 3  # True

qr = User.select().execute()
qr.count == 0  # True

[x for x in qr]
qr.count == 3  # True
```

The `CursorWrapper.count` attribute tracks how many rows have been read from the cursor. It's a private API and has a different meaning than `Select.count()`.

Make sense?

Suggestion: Maybe it can be renamed to _count to signify the private status?

It's not documented, so I'm not even sure what would lead someone to using it?

Thanks for the explanation, but it is the part of public API, so I was pretty sure, that I can use it. Please, inform what's wrong in this idea? :)

> it is the part of public API

Can you please share where this is documented?

@coleifer You are right, I was misinterpreting PEP8
@vikt0rs As per PEP8 https://www.python.org/dev/peps/pep-0008/#public-and-internal-interfaces:
> All undocumented interfaces should be assumed to be internal.


Agreed, this wasn't documented, sorry for my note
Issue: Alias does not preserve schema
Author: Karel-van-de-Plassche
State: closed
Created: 2018-02-26 10:03:17

Using peewee 3.1.0. I'm trying to use an alias in a self-join to create a common table expression. However, I get an error that the aliased table name does not exists. I think this is because alias does not preserve the schema. Minimal example:


``` Python
netAlias = Network.alias('net')
cte = Network.select(Network.id, netAlias.networks).from_(Network, netAlias).cte('nets')
query = net.select().with_cte(cte)
query.sql()

('WITH "nets" AS (SELECT "t1"."id", "net"."networks" FROM "develop"."network" AS "t1", "network" AS "net") SELECT "t2"."id", "t2"."feature_names", "t2"."target_names", "t2"."recipe", "t2"."networks" FROM "develop"."network" AS "t2"',
 [])

query.get()

ProgrammingError: relation "network" does not exist
LINE 1: ...net"."networks" FROM "develop"."network" AS "t1", "network" ...
```

I would expect from_ in the cte to generate `"develop"."network" AS "net"`, not `"network" as "net"`. However, I'm an SQL noob and might be using cte's wrong.

Maybe related `netAlias.select().get()` fails too with the same error, although `netAlias.get()` works.

Fixed in d7f0350, thanks so much for reporting.
Issue: why does rawquery not support iterator
Author: m358807551
State: closed
Created: 2018-02-26 06:25:22

I want to execute some codes like:
`Article.raw("select * from article").iterator()`.
However, I got:
`{AttributeError}'RawQuery' object has no attribute 'iterator'`

Is there a very compelling reason for using `raw()` in your case? The above could simply be written as `Article.select()`.

Raw queries *do* support iterator in 3.x:

```python

query = Article.raw('select * from article')
for article in query.iterator():
    # do something with article.
```

In 3.x raw queries support `iterator()`.

Thank you.
Issue: Add `column_exists` method to `Database` class
Author: stanislavkozlovski
State: closed
Created: 2018-02-25 16:28:23

Currently, when running migrations, I'd like to check if a column exists. My current solution is the following:
```python3
column_name = 'name'
table_name = 'users'
if any(column_data.name == column_name for column_data in db.get_columns(table_name)):
  pass
```
This also produces the following query (PostgreSQL) which is overkill:
```sql
SELECT column_name, is_nullable, data_type
            FROM information_schema.columns
            WHERE table_name = 'users' AND table_schema = 'public'
            ORDER BY ordinal_position
```

What I'd like to have is:
```python3
column_name = 'name'
table_name = 'users'
if db.column_exists(table_name, column_name):
  pass
```

And it could produce something like the following query:
```sql
EXISTS (SELECT 1
             FROM information_schema.columns
             WHERE table_name = 'users' AND table_schema = 'public' AND column_name='name')
```

I could implement this myself, I'd just like somebody to approve it.

I don't plan on adding that API, you can very easily use the `get_columns()`, as you've indicated, to achieve  equivalent results.