akumoth/peewee-issues huggingface dataset:
Issue: Using .aggregate_rows() with .get() doesn't work correctly
Author: jturmel
State: closed
Created: 2014-12-17 16:38:01

It only returns one item on the back reference lists even if the item had more in the tables that were joined in.


`aggregate_rows()` was not really intended, when I wrote it, for fetching single instances via `.get()`. Can you explain your specific use case? I might be able to suggest some equally viable alternatives.


We have an alternative, we just don't use .get(), we just run the normal "list" query, check to see if there are any results, and pick off the first result, it's just not as elegant as using .get() and catching the peewee.DoesNotExist exception

Using an example from the docs: http://peewee.readthedocs.org/en/latest/peewee/quickstart.html

``` python
>>> subquery = Pet.select(fn.COUNT(Pet.id)).where(Pet.owner == Person.id).
>>> person = (Person
...          .select(Person, Pet, subquery.alias('pet_count'))
...          .join(Pet, JOIN_LEFT_OUTER)
...          .order_by(Person.name)
...          .where(Person.id == 1)
...          .aggregate_rows()
...          .get())

...     print person.name, person.pet_count, 'pets'
...     for pet in person.pets:
...         print '    ', pet.name, pet.animal_type
```


So the `.get()` helper works efficiently by calling `LIMIT 1`. `aggregate_rows()` does not work well with `LIMIT` because the data is de-duped and aggregated (hence aggregate_rows) on the python side. Also note the row**s** -- it is intended to work with multiple rows of data.

You can still achieve what you're going for in O(1) queries, you'll just need to make 2 instead of 1:

``` python
person = Person.get(Person.id == 1)
pets = Pet.select().where(Pet.owner == person)
# or
pets = person.pets
```

In addition to being roughly equivalent in terms of performance, this is probably quite a bit more efficient than `aggregate_rows()`.

Note: I edited your comment to fix the syntax highlighting.


Do you have any additional questions or thoughts? If not, I will go ahead and close the issue.


Definitely understand, that's fine on closing it.

It does seem like .get() could just be intelligent enough that if .aggregate_rows() is set on the query that it drops the LIMIT 1, that way aggregate rows still collects the records under the single record but the case where they don't use aggregate_rows() is still optimal.


Yeah, it would be possible, but I'm loth to introduce special-cases. Particularly because, in my mind, the use-cases for `.get()` and `.aggregate_rows()` are quite different. With `.get()` you have some piece of information to uniquely identify a row, so you're already looking at O(1). Whereas with `.aggregate_rows()` you're looking to grab multiple rows and avoiding the dreaded N+1 query problem. Since `.get()` already implies you don't have N+1, I think it's acceptable to avoid special-casing.

That said, I will update the docs to make this clear. Thanks for reporting the issue!
Issue: Issue with aggregate functions and mathematical operations
Author: goir
State: closed
Created: 2014-12-15 13:58:50

Hi,

We're having an issue with aggregate functions and mathematical calculations, this simple Test shows the issue:

```
from peewee import fn, Model

query = Model.select(
    fn.MAX(
        fn.IFNULL(1, 10) * 151,
        fn.IFNULL(None, 10)
    )
)
print query
```

This gives us:
<class 'peewee.Model'> SELECT MAX(IFNULL(?, ?) \* ?), IFNULL(?, ?) FROM "model" AS t1 [1, 10, 151, None, 10]

But it should be
SELECT MAX**(**(IFNULL(?, ?) \* ?), IFNULL(?, ?)**)** FROM "model" AS t1 [1, 10, 151, None, 10]
There is a bracket missing.

If you remove the `* 151` everything is fine. You can put in as many brackets as you like, they are getting ignored. I couldnt find a way to get this working.

Is this an issue or are we just using it wrong ? :)


> Is this an issue or are we just using it wrong ? :)

Yes, it appears to be! Thanks for bringing this to my attention. I'll look into fixing this tonight after work.
Issue: Fix error when aggregating results that JOIN many-to-many tables
Author: jturmel
State: closed
Created: 2014-12-11 07:14:54

Composite key is a list of primary keys in how the pk_value was being generated causing a crash when trying to be used as a dictionary key since a list isn't hashable.

Now it looks to see if it's a list, if it is, it casts the values to strings and concatenates them together with a dash.
Issue: sqlite ForeignKeyField column migration errors
Author: shamrin
State: closed
Created: 2014-12-08 17:56:08

- [x] ForeignKeyField add_column is incomplete - column is added, but without foreign key constraint
- [x] ForeignKeyField  drop_column fails: `peewee.OperationalError: unknown column "owner_id" in foreign key definition`
- [x] trying to drop non-existent column doesn't give any errors: e.g. `migrate(migrator.drop_column('device', 'owner'))`

Repeating the first two problems:

``` sh
git clone https://gist.github.com/8fe325d798c201e8e63b.git fk-bug
cd fk-bug
rm -f test.sqlite && python bug_models.py create && python bug_add.py && echo '.schema' | sqlite3 test.sqlite
rm -f test.sqlite && python bug_models.py create && python bug_drop.py
```

https://gist.github.com/shamrin/8fe325d798c201e8e63b


@coleifer Wow, thank you for super quick fix! :smiley: 

I've noticed adding `ForeignKeyField` produces `REFERENCES` that is inline with column definition:

```
"network_id" INTEGER REFERENCES "network" ("id")
```

vs what happens when you call `.create_table()`:

```
"network_id" INTEGER, FOREIGN KEY ("network_id") REFERENCES "network" ("id")
```

These two forms seem to be equivalent. Am I right?

I'm writing about it, because I didn't know about the first form. May be this thread would help people who could be confused by it :)


They are equivalent and unfortunately that is the only way to do it in one shot with SQLite. The way I'd prefer is with and `ALTER/ADD COLUMN`, then `ALTER/ADD CONSTRAINT`. Since SQLite does not support this, I just inlined the foreign key definition.


@coleifer Because I didn't know about inline `REFERENCES`, I've tried to implement `FOREIGN KEY` manipulation by creating a new table and copying data into it. (Reusing and extending `SqliteMigrator` `_update_column` method.) But my code quickly became ugly, I switched to other things. And on the next day you've fixed it with a much better approach :)
Issue: Fixed issue with aggregate_rows
Author: arrowgamer
State: closed
Created: 2014-12-01 11:04:37

Hi there.

Awesome job on this ORM!

I ran into an issue while using it last night, however, and here is my proposed solution. As not the most seasoned Python developer, your code is quite difficult for me to understand, so I don't know if this implemented correctly or if the change is even warranted. Perhaps I was using Peewee incorrectly.

My issue was as follows. I have ModelB with two foreign keys both to ModelA.

**Example:**

``` python
class ModelB(Model):
    manager = ForeignKeyField(ModelA, related_name='slaves')
    assistant = ForeignKeyField(ModelA, related_name='masters')
```

Using aggregate_rows to preload an instance of ModelA with its masters would place the results under the wrong related_field, slaves, for instance. I thought this might be because Peewee was handling this case incorrectly, and from what I understand about the code, the "rel_for_model" function accounts for this if you pass the field_obj argument, though that's not being passed through the calls to "reverse_rel_for_model", and so it's not really caring about the exact foreign key field and thus populating the wrong "related_name" field.

Does that make any sense?


Oh wow, nice catch!


Thanks a lot! ^_^ When do you think this will see the light of pip? =O
Issue: Save only dirty fields
Author: ricoboni
State: closed
Created: 2014-11-28 13:05:39

I don't use the `Model.save()` method in some of my models to avoid race conditions.

Actually I overwrite it for these specific models to have a default value to the `only` argument that makes sense.

Example:

```
In [1]: p1 = Project.get(id=21)

In [2]: Project.get(id=21).used_credit_count
Out[2]: 4669

In [3]: # Now, lets simulate another thread

In [4]: Project.update(used_credit_count=Project.used_credit_count + 1).where(Project.id == 21).execute()
Out[4]: 1L

In [5]: Project.get(id=21).used_credit_count
Out[5]: 4670

In [6]: p1.save()
Out[6]: 1L

In [7]: Project.get(id=21).used_credit_count
Out[7]: 4669
```

The release 2.2 introduced saving only dirty (modified) fields, what would be very useful in my application (and others, I hope). But when I took a look at the code, I noticed that the dirty fields only takes place when using CompositeKey:

``` python

    def save(self, force_insert=False, only=None):
        field_dict = dict(self._data)
        pk_field = self._meta.primary_key
        if only:
            field_dict = self._prune_fields(field_dict, only)
        if self._get_pk_value() is not None and not force_insert:

          # Here is #
           if not isinstance(pk_field, CompositeKey):
                field_dict.pop(pk_field.name, None)
            else:
                field_dict = self._prune_fields(field_dict, self.dirty_fields)
            rows = self.update(**field_dict).where(self._pk_expr()).execute()
        else:
            pk = self._get_pk_value()
            pk_from_cursor = self.insert(**field_dict).execute()
            if pk_from_cursor is not None:
                pk = pk_from_cursor
            self._set_pk_value(pk)  # Do not overwrite current ID with None.
            rows = 1
        self._dirty.clear()
        return rows

```

It seemed that its been used only to remove the fields that are part of the composite primary key, but IMHO its very useful on many scenarios.

Sorry if my English is not clear. Please don't hesitate to ask for clarifications.


I can see how that code appears confusing. The `only` parameter accepts a list of fields, and `model_instance.dirty_fields` returns a list of fields that have been modified. Using the two together, you can accomplish "save only the fields that changed":

``` python
project.save(only=project.dirty_fields)
```


Please let me know if this does not address your concern.


Yes, It solves the problem. I just thought the presence of a composite primary key were not a good criteria to save all fields or just the dirt ones.


Looking at d53415fb, I think that code is there to replace previous logic that popped the PK off the field dict when updating a model.
Issue: Why not validate the keyword before insertion?
Author: kamelzcs
State: closed
Created: 2014-11-28 04:46:09

```
class Person(BaseModel):
    name = CharField()
    birthday = DateField()
    is_relative = BooleanField()
uncle_bob = Person(name={1:2}, birthday="1910/10/11", is_relative="a", stupid="bas")
uncle_bob.save()
```

It would just work. Eventhough there is no `foo` field in the Model. 
The insert way is to ignore any `NOT defined` keywords before insert into table.  
It would make a big trouble if the keyword is the typo.

BTW1:  sqlalchemy would give 

```
TypeError: 'stupid' is an invalid keyword argument for Person
```

BTW2:
`Charfield` would accept all kinds of types other than `basestring`
`BooleanField` would accept all kinds of types other than `Boolean`
I just wondering is there any reason?
If the type checking is optional, then which interface to use?


> It would make a big trouble if the keyword is the typo.

Yes, that's a valid point.

> Charfield would accept all kinds of types other than basestring
> BooleanField would accept all kinds of types other than Boolean

The `python_value` and `db_value` functions on field ensure that the correct types are passed when reading rows from the database, and vice-versa.


Is it valuable to add in a keyword validation in case of typo as sqlalchemy?


A `KeyError` will be raised if you try to insert data into a non-existant field, though this happens at execution time rather than the time the insert is instantiated. I think this is sufficient and will leave the logic as-is.


But if birthday is optional field and when try to insert, a birthdays typo happened, this field would be ignored, but it should not be!

I can't find the keyerror in this case. 


https://github.com/coleifer/peewee/blob/706c9d8e67f22f8610142dcaf2e37829f3f9d138/peewee.py#L2556-L2582

Particularly 2560.


The code in my example would work in peewee, and would not give keyerror at all.
This keyerror will check the insert keyword?
Issue: .save() doesn't work when primary key is manually assigned
Author: scribu
State: closed
Created: 2014-11-24 00:07:25

Hello,

I have a model that looks something like this:

``` python
class Page(Model):
    class Meta:
        database = PostgresqlDatabase('example')
    url = CharField(primary_key=True)
    content = TextField()
```

If I manually create a Page instance, call `.save()` and then immediately query for that instance, I get a DoesntExist exception:

``` python
URL = 'http://example.com'

page = Page(
    url=URL
)

page.content = 'whatever'
page.save()

Page.get(Page.url == URL)  # raises peewee.PageDoesNotExist
```

What's going on?

Here's a full test script which reproduces the problem:

``` python
from peewee import *  # noqa
from playhouse.postgres_ext import *  # noqa

db = PostgresqlDatabase('example')


class Page(Model):
    class Meta:
        database = db
    url = CharField(primary_key=True)
    content = TextField()


def init():
    try:
        Page.drop_table()
    except ProgrammingError:
        db.rollback()

    Page.create_table()


init()

URL = 'http://example.com'

page = Page(
    url=URL
)

page.content = 'whatever'
page.save()

Page.get(Page.url == URL)
```

Details:
- Peewee 2.4.3
- psycopg2 2.5.4
- Python 3.4.1
- PostgreSQL 9.3.5
- OSX 10.10.1


After looking at how the `save()` method is implemented, it looks like it can't tell the difference between this:

```
page = Page.get(Page.url == 'http://example.com')
```

and this:

```
page = Page(url='http://example.com')
```

i.e. between a persisted object and one that exists only in Python.

Anyway, my workaround was to add an extra `if` and use `Page.create()`.

I'll leave this open in case it's deemed worth fixing.


As the documentation states, when using a non-auto-incrementing primary key, you must call `save()` on new instances by passing `force_insert=True`.

http://docs.peewee-orm.com/en/latest/peewee/models.html#non-integer-primary-keys-composite-keys-and-other-tricks
Issue: The table name on playhouse.migrate.migrate is partially case sensitive
Author: stenci
State: closed
Created: 2014-11-20 00:36:41

After executing the code below mytable2 misses one index.

The difference between the two migrate() lines is that one uses the case of the class and the other uses the case of the table name.

The function should be either case sensitive and fail when the case does not match (the class name or the column name?) or (better) not case sensitive and always work.

`````` Python
import peewee
import playhouse.migrate
db = peewee.SqliteDatabase('test.db', threadlocals=True)

class PeeweeModel(peewee.Model):
    class Meta:
        database = db

migrator = playhouse.migrate.SqliteMigrator(db)

class MyTable1(PeeweeModel):
    pk = peewee.CharField(primary_key=True)
    indexed = peewee.CharField(index=True)

class MyTable2(PeeweeModel):
    pk = peewee.CharField(primary_key=True)
    indexed = peewee.CharField(index=True)

MyTable1.drop_table(True)
MyTable2.drop_table(True)

MyTable1.create_table()
MyTable2.create_table()

try:
    playhouse.migrate.migrate(migrator.drop_column('MyTable1', 'missing_column'))
except:
    pass

try:
    playhouse.migrate.migrate(migrator.drop_column('mytable2', 'missing_column'))
except:
    pass```
``````
Issue: lastrowid not found
Author: hitman249
State: closed
Created: 2014-11-16 15:33:49

How to know the last added row id?

```
    meta = self.table.database
    cur = meta.get_cursor()
    last_insert_id = meta.last_insert_id(cursor=cur, model=Mt_files)
    print(last_insert_id)
```

AttributeError: 'Cursor' object has no attribute 'lastrowid'


The insert ID is returned on a `Model.save`. `Database.last_insert_id` takes a cursor _which was used to insert a row_ and returns the ID of the inserted row. By calling `Database.get_cursor`, your asking Peewee to open a _new_ database cursor, which will have no previously executed queries attached to it, and thus it cannot return a last-insert-id. Because this is a mostly undefined behavior, different error cases will happen for each DB-adapter.


@b1naryth1ef is correct, you would need to use the cursor you executed the `INSERT` with. If you are executing an `INSERT` statement, it will return the last row id automatically, and that can give you some insight into how to do it:

``` python
    def execute(self):
        if self._is_multi_row_insert and self._query is None:
            if not self.database.insert_many:
                last_id = None
                for row in self._rows:
                    last_id = InsertQuery(self.model_class, row).execute()
                return last_id
        return self.database.last_insert_id(self._execute(), self.model_class)
```
Issue: introspection fails in postgres when database contains table with same name in two different schemas
Author: Jokipii
State: closed
Created: 2014-11-13 07:42:22

I tried to introspector.metadata.get_columns('isrc') and got following error:

```
C:\Python27\lib\site-packages\playhouse\reflection.pyc in get_columns(self, table)
    230             columns[name] = Column(
    231                 name,
--> 232                 field_class=column_info['field_class'],
    233                 raw_column_type=column_info['raw_column_type'],
    234                 nullable=column_info['nullable'],

KeyError: 'field_class'
```

To duplicate error create PostgreSQL database 'testdatabase' with two schemas 'public' and 'music' and table 'isrc' with different columns in both schemas. Then run:

``` python
from peewee import *
from playhouse.reflection import Introspector

db = PostgresqlDatabase('testdatabase')
introspector = Introspector.from_database(db)
introspector.metadata.get_columns('isrc')
```

Reson for failure seems to be query from lines 192-198 

``` python
    def get_columns(self, table):
        # Get basic metadata about columns.
        cursor = self.execute("""
            SELECT
                column_name, is_nullable, data_type, character_maximum_length
            FROM information_schema.columns
            WHERE table_name=%s""", table)
```

It returns table columns from all schemas. I think that It should return only columns related on current schema...


Thank you for this issue, looking into it now.
Issue: Join doesn't work when a composite key is used
Author: bastula
State: closed
Created: 2014-11-11 19:07:52

Hello,

I have a table that has a primary key set as a Composite Key and I am unable to complete a join.

Here is the traceback:

``` Python
Traceback (most recent call last):
  File "localsync.py", line 79, in get_dates
    query = Image.select().join(Treatment).where(Image.date == Treatment.date)
  File "C:\Python27\lib\site-packages\peewee.py", line 257, in inner
    func(clone, *args, **kwargs)
  File "C:\Python27\lib\site-packages\peewee.py", line 2077, in join
    raise ValueError('A join condition must be specified.')
ValueError: A join condition must be specified.
```

and here are the models:

``` Python
class Image(Model):

    pid = CharField()
    date = DateField()
    time = TimeField()
    panel = CharField()

    class Meta:
        database = db
        primary_key = CompositeKey('date', 'time')

class Treatment(Model):

    id = PrimaryKeyField()
    pid = CharField()
    date = DateField()
    time = TimeField()
    field = CharField()

    class Meta:
        database = db
```


I tried a raw SQL query and it works fine:

``` SQL
SELECT * FROM image JOIN treatment WHERE image.date == treatment.date
```

The database backend is SQLite.


Have you tried explicitly setting the join condition?

``` python
predicate = (Image.date == Treatment.date) & (Image.time == Treatment.time)
 query = Image.select().join(
    Treatment,
    on=predicate)
```


That works by setting the join condition. I thought I could set it using where, but that's not the right place.

Thanks!
Issue: Feature request: Automatically generate table and column comments from Model and Field docstrings
Author: conqp
State: closed
Created: 2014-11-05 09:13:03

I wondered, whether it would be possible to implement the feature of generating table and column comments from the respective models' and fields' docstrings on table creation.
Thanks for considering it.

OT: The "schema" option for the cross-DB FK worked. Thanks for the hint.


Thanks for the suggestion. I think I'm going to pass on this, but if you would like to submit a pull-request with an implementation, I'd help review it.
Issue: Docs issue: Example with bad practices
Author: akaRem
State: closed
Created: 2014-11-03 12:36:01

http://peewee.readthedocs.org/en/unstable-2.0/peewee/models.html#Model.delete_instance

there is an example 

```
>>> user = User.get(User.username == username, User.password == password)
```

One could think that it's ok to store plain password in db.
Issue: improvement : Peewee Cookbook 
Author: akaRem
State: closed
Created: 2014-11-03 12:28:35

http://peewee.readthedocs.org/en/unstable-2.0/peewee/cookbook.html

there is example on use pwiz.py

```
python pwiz.py my_postgresql_database
```

I suggest to replace such things with 

```
python -m pwiz my_postgresql_database
```

It's better to explicitly search for python module in python libs than "some script" in system paths.
Also in my case it said evident thing:

```
$ python pwiz.py     
python: can't open file 'pwiz.py': [Errno 2] No such file or directory
```
Issue: Filtering by Count() in SQlite raises an error
Author: arski
State: closed
Created: 2014-11-01 21:11:10

What I'm trying to do is something like:

```
User.select(User, fn.Count(Tweet)).join(Tweet).where(fn.Count(Tweet) > 0)
```

Sadly, in SQlite, this throws an `OperationalError: misuse of aggregate: Count()` - which upon further investigation on SO turns out to be a specific SQlite issue where filtering by aggregates has to be done inside `HAVING` clause instead of `WHERE` - http://stackoverflow.com/questions/648083/sql-error-misuse-of-aggregate - any chance you could look into this please


Your query is incorrect.

``` python
(User
 .select(User, fn.COUNT(Tweet.id).alias('count'))
 .join(Tweet)
 .group_by(User)
 .having(fn.COUNT(Tweet.id) > 0))
```


When aggregating, you can only filter on aggregates in the `HAVING` clause. Additionally, you need to tell the database how to group the rows -- in this case, by user.


My bad, I thought I had filtered by SUM or COUNT in a WHERE clause in MySQL before.. but maybe that was an erroneous recollection. thanks for the help!
Issue: How to use functions like POSTGRES EXTRACT(YEAR FROM table.field)
Author: demongoo
State: closed
Created: 2014-11-01 20:52:48

This thing still doesn't let me sleep tight.
Can I somehow write the select field like:

```
EXTRACT(EPOCH FROM field)
```

Now I'm using `(fn.EXTRACT(SQL("EPOCH FROM \"t5\".\"ts\"")) * 1000).alias('session_id')` and the worst part is "t5". Is there any other solution? Or maybe some ideas how to let such constructions to be written?


You might try:

``` python
fn.EXTRACT(Clause(SQL('EPOCH FROM'), SomeModel.field))

# or
(fn.EXTRACT(Clause(SQL('EPOCH FROM'), SomeModel.field)) * 1000).alias('session_id)
```


For future reference it looks like you want to use `NodeList` now instead of Clause

This is built-in to the database object as of Peewee 3. You can use `db.extract_date('epoch', M.field)`.