akumoth/peewee-issues huggingface dataset:
Issue: Using fn.* on select() : can't rebuild model object
Author: thibaultmeyer
State: closed
Created: 2014-03-04 10:21:46

Hello,

When I use fn.MIN(Transaction.date), MySQL return an column named "fn.MIN(Transaction.date)" intead of "date"... So all Transaction items returned have attribute "date" == None.

It would be nice to have the possibility to do something like that

```
                       fn.MIN(Transaction.date, AS="date")
```

Test:

```
lstTransaction = Transaction.select().where(Transaction.vendor ==
1).distinct().group_by(Transaction.client).order_by(Transaction.amount)

OK
print lstTransaction[0].date
==> 2014-02-01 12:21:45


lstTransaction = Transaction.select(Transaction.id, Transaction.vendor, 
Transaction.amount, Transaction.client, Transaction.date).where(
Transaction.vendor == 1).distinct().group_by(Transaction.client)
.order_by(Transaction.amount)

OK
print lstTransaction[0].date
==> 2014-02-01 12:21:45

lstTransaction = Transaction.select(Transaction.id, Transaction.vendor,
Transaction.amount, Transaction.client, fn.MIN(Transaction.date))
.where(Transaction.vendor == 1).distinct().group_by(Transaction.client)
.order_by(Transaction.amount)

KO --> All Transaction items on list have attribute date == None
print lstTransaction[0].date
==> None
print lstTransaction[0].__dict__
==> {'_data': {'client': 1, 'vendor': 1, 'amount': 10.0, 'Min(t1.`date`)': datetime.datetime(2014, 2, 24, 10, 52, 32),}}
```


I write a little hack to add support of "AS" in the function fn.MIN. It works, but I'm not sure that is the best way to proceed.

peewee.py - Class Func(Node)

``` python
 406     def __getattr__(self, attr):                                                               
 407         def dec(*args, **kwargs):                                                              
 408             _as = kwargs.pop('AS', None)                                                       
 409             if _as is None:                                                                    
 410                 return Func(attr, *args, **kwargs)                                             
 411             else:                                                                              
 412                 return Clause(Func(attr, *args, **kwargs), SQL('AS'), _as)                     
 413         return dec
```


That already exists actually, just call `.alias()` instead of as, e.g.:

``` python
fn.MIN(SuchAndSuch.date).alias('min_date')
```


**BUT** I admire your enthusiasm for fixing this! Moar patches :)
Issue: Added a debug feature for the Database class
Author: pydeveloper94
State: closed
Created: 2014-02-22 06:08:21

I changed the Database class so that you can choose to enable logging. For example,

```
db = SqliteDatabase('data.sqlite3', debug=True)
```

will enable the logger to log messages onto the console while the python interpreter is running.


I'm not sure I understand these changes.


http://peewee.readthedocs.org/en/latest/peewee/cookbook.html#logging-queries


The developer is not able to choose whether or not they want to disable the logger providing debug output. You gave a solution here, https://github.com/coleifer/peewee/issues/254, to write

```
import logging
logger = logging.getLogger('peewee')
logger.addHandler(logging.NullHandler())
```

but the person who raised this issue stated that the code provided did not work. When I added these lines to my code base, it did not fix the logger problem.

I think it would be favorable to enable logging while instantiating the database object by changing the debug keyword argument, which I added.

When I first came across the log message being printed into the console, I thought there was an error with my code, but it was just because logging is not disabled by default.


> You might try then peewee_logger.handlers = []. The logging docs are pretty helpful.

From my final comment in the issue you referenced.


The log framework has multiple log levels so you can subscribe to the ones you care about. Adding another layer of API in-between seems redundant, so I'm going to close this.
Issue: How to reference specific field with ForeignKeyField
Author: wcbeard
State: closed
Created: 2014-02-13 06:21:20

I'm trying to find a way to use a `ForeignKeyField` that refers to a specific field of another model, rather than the entire model itself. I believe in Django's ORM it's the [`to_field`](https://docs.djangoproject.com/en/dev/ref/models/fields/#django.db.models.ForeignKey.to_field) attribute.

Looking at the [source code](https://github.com/coleifer/peewee/blob/master/peewee.py#L826), there doesn't appear to be any option for this, but rather a check to make sure that `related_name` doesn't conflict with any of the model's field names (`if self.related_name in self.rel_model._meta.fields`...).

If there is no way to currently specify the field, would it be a feasible future enhancement for peewee?


I've got a commit or two towards making this work, almost there.


Added. http://peewee.readthedocs.org/en/latest/peewee/api.html#ForeignKeyField
Issue: refactored _parse to run isinstance less.
Author: danthedeckie
State: closed
Created: 2014-02-12 07:13:19

I was importing some data, and found it running slower than I thought it should. I profiled, and found that the `_parse` function was taking the longest, and that `isinstance` was getting called 259579 times, which was taking a good chunk of it.  I've (very naively) refactored the `QueryCompiler._parse_node` function to call out to per-object methods (which for now is called `_parse_me`, but that's easily changed).

This reduces the calls to `isinstance` down to 97122, and shaves a 10% off my data import time.

Is this a line of optimising worth pursuing?


I had shied away from this previously because I didn't want to tangle up `Node` objects with their SQL representations, but this has actually caused some pain when wanting to add new Node types (i.e. the `unknown` grossness). Maybe this is a good path forward, I certainly have been aware that the recent refactorings would hurt performance a bit, but figured it would be negligible since most time is spent in the DB anyways... but I always like fast.

Thank you very much for this work, I think it's a good start and I'll investigate a similar approach.


Cool! :-) I really like peewee, I love how simple the whole thing is for library users, and only being one file, etc.  Thanks very much for your work on it.


I did some profiling (ran the benchmark suite with cProfile). Here are some baseline results:

https://gist.github.com/coleifer/9da9e7683530342b7de2


Closing this and have marked it as a to-do item.
Issue: request: reflect major changes in the source code tarball in the ChangeLog
Author: pitchforks
State: closed
Created: 2014-02-12 05:06:26

I have noticed that the source code tarball for 2.2.1 is much smaller. Apparently it's because 2.2.1 doesn't ship docs in HTML form anymore. There wasn't anything in the ChangeLog about it so I've only noticed it when my build script failed and I had a closer look at what's going on.

This is a low priority request, since I'm only packaging peewee for myself, so feel free to close the issue as soon as you read it. While the main purpose of the ChangeLog is to cover functional changes in the software, covering up details about such major source code tarball changes will be helpful to packagers.

Now, since one has to build the docs himself, Sphinx becomes a build dependency if docs are needed, it will be nice to add this somewhere, perhaps a line in README?


This was an oversight on my part. I re-pushed the 2.2.1 release so hopefully that fixes things.  Thank you for bringing this to my attention.
Issue: Metaclass conflict
Author: soasme
State: closed
Created: 2014-02-08 03:56:54

Are there any suggestions to solve the metaclass conflict problem?

``` python

from peewee import Model, SqliteDatabase, CharField

db = SqliteDatabase('/tmp/conflict.db')

class UserMeta(type):
    def __new__(mcs, name, bases, dct):
        return super(UserMeta, mcs).__new__(mcs, name, bases, dct)

class User(Model):
    __metaclass__ = UserMeta
    name = CharField()
    class Meta:
        database = db

User.create_table()
user = User.create(name='bob')
print user

import os
os.remove('/tmp/conflict.db')
```

```

Traceback (most recent call last):
  File "a.py", line 10, in <module>
    class User(Model):
  File "a.py", line 8, in __new__
    return super(UserMeta, mcs).__new__(mcs, name, bases, dct)
TypeError: Error when calling the metaclass bases
    metaclass conflict: the metaclass of a derived class must be a (non-strict) subclass of the metaclasses of all its bases
```


it's more of a python problem rather than a peewee one.

I think letting `UserMeta` inherit `BaseModel` should resolve this issue
Issue: CREATE INDEX on ForeignKey crashes during table creation
Author: giohappy
State: closed
Created: 2014-02-06 22:46:36

When doing MyModel.create_table(), MyModel containing a ForeignKeyField, the creation of the related index doesn't work, because it complains that the relation is missing (MyModel's table)

I've also notices that the CREATE INDEX sql misses the table schema. I've tried fixing it at line https://github.com/coleifer/peewee/blob/master/peewee.py#L1321 :

tbl_name = ('.').join([model_class._meta.schema,model_class._meta.db_table])

but this doesn't solve the problem. Maybe the table hasn't been created yet when the create index is issued? Am I doing something wrong?


Fix https://github.com/coleifer/peewee/pull/304
Issue: Update/Refresh Field List
Author: bndr
State: closed
Created: 2014-02-03 21:04:23

Hi!

I would like to suggest implementing some sort of refresh_table method, which will add/delete Fields of the Model. 

For example I have a model:

```
class Pets(Model):
  name = CharField()
```

Then I do `Pets.create_table()` now I have the relevant table in my database, after some time (usually during development) I decide that I need another field:

```
class Pets(Model):
  name = CharField()
  ownerName = CharField()
```

But now the Model isn't in sync with the table in the database, and I get Errors like:
`"Unknown column 't1.ownerName' in 'field list'"`

It would be quite useful have some sort of refresh/update/alter_table method that would update the relevant fields, instead of adding the fields manually or dropping and recreating the table.

Thoughts?


This would be a great feature but if you take a look at some of the existing schema migration tools you might be surprised how tricky this is.

Currently peewee has a helper in `playhouse.migrate` but i've only tested it with postgres. Schema migrations in sqlite are tricky because it only supports adding columns. For now these are the best options.
Issue: How to check
Author: kramer65
State: closed
Created: 2014-02-03 18:08:33

I'm trying to write a query which should return all names of Users which have at least one message associated with them. My models look like this:

```
class User(db.Model):
    name = CharField()

class Message(db.Model):
    user = ForeignKeyField(User, related_name='messages')
    text = TextField()
```

and I currently try this as follows:

```
usersWithAtLeastOneMessage = User.select().where((fn.Count(User.messages) > 0)
for u in usersWithAtLeastOneMessage: 
    print u.name
```

Unfortunately, this gives me an error saying `OperationalError: misuse of aggregate function Count()`. 

Does anybody know how I can get all users which have at least one Message? All tips are welcome!


This isn't really appropriate for GH issues. Try the [peewee mailing list](groups.google.com/group/peewee-orm) or irc (freenode #peewee).


Since you're stuck, though, you might try something like this:

``` python

(User
 .select()
 .where(fn.Exists(
     Message.select().where(Message.user == User.id))))
```
Issue: pwiz foreign key regex for sqlite
Author: coleifer
State: closed
Created: 2014-02-01 17:43:32

Recent changes to the definition of foreign keys has caused pwiz to break when introspecting peewee-created sqlite tables.

Here is the table definition from peewee 2.1.6:

``` sql
CREATE TABLE "fkpk" (
    "col_types_id" INTEGER NOT NULL PRIMARY KEY REFERENCES "coltypes" ("f11") 
);
```

Here it is for 2.2.0, broken:

``` sql
CREATE TABLE "fkpk" (
    "col_types_id" INTEGER NOT NULL PRIMARY KEY, 
    FOREIGN KEY ("col_types_id") REFERENCES "coltypes" ("f11")
);
```
Issue: Cascade doesn't seem to exist anymore?
Author: b1naryth1ef
State: closed
Created: 2014-01-30 07:55:36

I'm trying to figure out if I'm going crazy, or if the docs are out of date. Having any ForeignKeyField with cascade=True, errors out with the following:

Code:

``` python
ForeignKeyField(Category, cascade=True)
```

Throws:

``` python
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 823, in __init__
    super(ForeignKeyField, self).__init__(*args, **kwargs)
TypeError: __init__() got an unexpected keyword argument 'cascade'
```

Looking at source, it seems this doesn't exist anymore (or at least not anywhere I can find it in the 2.2 branch of things). Was this supposed to be removed? Am I missing something obvious (very likely)?

I'm trying to find the right way to drop a table w/ cascade (sense that also seems like it was removed, or at least is unusable).


Well you're right, that arg is replaced with `on_delete` and `on_update`. I need to push a documentation update. Neither is responsible for dropping a table with the cascade option. I think you're right that, while I see the code for this the important part is unreachable.  I'll have two fixes coming.


This should now be fixed. To drop a table w/cascade you can call:

``` python
ModelClass.drop_table(cascade=True)
```
Issue: Add version information
Author: c4urself
State: closed
Created: 2014-01-30 00:15:35

This is super useful for debugging purposes, seeing as peewee  is just one big file it's nice to open it and see the VERSION information.


OMG, this change is so amazing; please have my babies.


`__version__` is better than `VERSION`, I think.


**version** added. Thanks for bringing this up!


:thumbsup: thanks!
Issue: How to select a field with DB function?
Author: giohappy
State: closed
Created: 2014-01-30 00:11:15

This is the inverse of https://github.com/coleifer/peewee/issues/292
In this case I need to obtain a field with a DB function.
Eg

SELECT ST_AsText(geom)


Ok, I found it out:

MyModel.select(fn.ST_AsText(MyModel.geom).alias('point'))
Issue: Git database driver
Author: grimnight
State: closed
Created: 2014-01-29 20:55:10

Is it possible to add [Git-ORM](https://pypi.python.org/pypi/git-orm) like functionality to peewee using database drivers?
Since peewee has such a collection of [addons](http://peewee.readthedocs.org/en/latest/peewee/playhouse.html), I was wondering if that was possible.
[Git-ORM](https://pypi.python.org/pypi/git-orm) uses [Pygit2](https://pypi.python.org/pypi/pygit2) but [Dulwich](https://pypi.python.org/pypi/dulwich) is pure Python, so both would be good choices.
I ask this because [Git-ORM](https://pypi.python.org/pypi/git-orm) is Python 3 only and it has seen little development lately (and it doesn't support remove).

What I am asking is, is it even possible or would it be too difficult?


I don't see any reason not to just use Git-ORM.

If someone wanted to build a dp-api 2.0 driver for git, then something like that would be possible with peewee, but until then just stuck w/git-orm.  Cool project, btw, hadn't heard of it.
Issue: Added an itemselector for hstore fields
Author: zdxerr
State: closed
Created: 2014-01-29 09:52:02

I added an itemgetter to the hstore field, which returns an expression the selects the corresponding key from the hstore. Now you can do stuff like:

```
Test.select(Test.data['key'])
```

and

```
OP_MATCH = 'match'

def match(left, right):
    return Expression(left, OP_MATCH, Param(right))

PostgresqlExtDatabase.register_ops({
    OP_MATCH: '~*',
})

Test.select().where(match(Test.data['key'], 'some regular expression.*'))
```


Nice!!  Thank you.  Also thank you for the test case.
Issue: How to define a field wich requires to call DB function? 
Author: giohappy
State: closed
Created: 2014-01-28 23:06:27

I would need to manage GIS data creation, given the DB support it (eg PostGIS for Postgresql, MySQL a bit by default, Spatialite for Sqlite). 
The minimal requirement is to define a field which can wrap the value with a DB function. 
Eg, to save a point with PostGIS, I could call "St_PointFromText(string_value)", and "St_AsText" to retrieve it. 
I can't figure out how this could be done with a custom field... 


Yeah that's a bit tricky, but it should be possible. Did you try something like:

``` python
class PointField(Field):
    db_field = 'point'  # Assuming you would hook this up to a field type registered w/the db.

    def db_value(self, value):
        if value:
            return fn.St_PointFromText(value)
```

**EDIT** nevermind, that won't work as it's only called on parameters that are being 'interpolated' into the query.


I'm not sure the best way to make this DRY, but you could try:

``` python
class PointField(Field):
    db_field = 'point'

def pft(value):
    return fn.St_PointFromText(value)

Coord.insert(point=pft('34.40032,10.1010101'))
Coord.update(point=pft('35.303,10.101')).where(Coord.id == 3)
Coord.select().where(
    Coord.point == pft('35.303,10.101'))
```


Here's something that hopefully helps:

``` python
from peewee import *

class PointField(Field):
    db_field = 'point'

db = SqliteDatabase(':memory:')
db.register_fields({'point': 'POINT'})

class Coord(Model):
    point = PointField()

    class Meta:
        database = db

def pft(v):
    return fn.St_PointFromText(v)

coords = '38.9717,95.2353'
print Coord.insert(point=pft(coords))
print Coord.update(point=pft(coords)).where(Coord.point == pft('39.0,96.0'))
print Coord.select().where(Coord.point == pft(coords))
```

---

```
<class '__main__.Coord'> INSERT INTO "coord" ("point") VALUES (St_PointFromText(?)) ['38.9717,95.2353']
<class '__main__.Coord'> UPDATE "coord" SET "point" = St_PointFromText(?) WHERE ("point" = St_PointFromText(?)) ['38.9717,95.2353', '39.0,96.0']
<class '__main__.Coord'> SELECT t1."id", t1."point" FROM "coord" AS t1 WHERE (t1."point" = St_PointFromText(?)) ['38.9717,95.2353']
```


Thanks! I will give it a try asap. The trick was the use of the fn stuff ;)


It works perfectly!
Issue: Added db schema support
Author: giohappy
State: closed
Created: 2014-01-28 00:11:56

Added db_schema do Model meta options. I've refactored Entity to skip empty strings (e.g. when db_schema is empty or None)


I opted for a slightly different refactoring which you can see in cfc7c3db6987cfe1ad087d645477bd1cb8c864e8 and 7a831badaa43347fa717718c9669f617bb5949e4. The end result should be the same as what you have here.

I also chose `schema` instead of `db_schema`. `db_table` is a holdover from Django, but going forward I'd like to switch it to just `table`.

Thanks for your work. Additionally, if you'd like to submit code in the future, please shoot for pep8 and 79-char lines.


I didn't receive your comment. I will try to follow the 79-char lines rule ;) (I already tend to respect PEP8)
Issue: Sub-select in from clause
Author: coleifer
State: closed
Created: 2014-01-24 01:20:14

I'm using peewee2.1 with python3.3 and an sqlite3.7 database.

I want to perform certain SELECT queries in which:

I first select some aggregate (count, sum), grouping by some id column; then
I then select from the results of (1), aggregating over its aggregate. For example, I want in some cases to count the number of rows in (1) that have each aggregated value.
My database has an 'Event' table with 1 record per event, and a 'Ticket' table with 1..N tickets per event. Each ticket record contains the event's id as a foreign key. Each ticket also contains a 'seats' column that specifies the number of seats purchased. (A "ticket" is really best thought of as a purchase transaction for 1 or more seats at the event.)

Below are two examples of working SQLite queries of this sort that give me the desired results:

``` sql
SELECT ev_tix, count(1) AS ev_tix_n FROM 
(SELECT count(1) AS ev_tix FROM ticket GROUP BY event_id)
GROUP BY ev_tix
```

``` sql
SELECT seat_tot, count(1) AS seat_tot_n FROM
(SELECT sum(seats) AS seat_tot FROM ticket GROUP BY event_id)
GROUP BY seat_tot
```

I can execute these through peewee as raw queries, but I don't know how to do them using peewee directly. Specifically, I don't know how to select on the inner query's aggregate (count or sum) when specifying the outer query. I can of course specify an alias for that aggregate, but it seems I can't use that alias in the outer query.

Is it in fact possible to do these selects without relying on raw queries? If so, how?

Note that I ran into a separate problem conducting these through peewee as raw queries when I updgraded from peewee 2.1.5 to 2.1.7. You can see my separate post about that: https://groups.google.com/forum/#!topic/peewee-orm/ZIXEJzTFxxY.

Thanks for your help!


peewee's APIs are geared towards selecting from a table. `Model.select()` implies that you are selecting from the table represented by `Model` and want to be returned `Model` instances (excepting special cases like `.scalar()`, `.aggregate()`, `.tuples()`, `.dicts()`).

I haven't really put much effort into making it work with a sub-select in the from clause, partially because it doesn't fit very well into the rubric of returning model instances.  I see the value of sub-selects, just haven't built it out yet.

I think it'd be a good feature so I'll look into how to handle this.


I believe the commit I just pushed 22ce07c will make it possible to achieve the queries you've listed.  Take a look at the included testcase.  I'm still not sure this is going to be a flexible enough solution to handle some of the other cases that come to mind, though. Particularly to select from multiple tables and join in the where clause.
Issue: simple functional way to create models
Author: leliel12
State: closed
Created: 2014-01-21 01:42:54

I actually working on tool for generate "dimension tables" for pentaho. I write this simple snippet for create models in more functional way.

``` python
#!/usr/bin/env python
# -*- coding: utf-8 -*-

from peewee import *

def create_model(model_name, supercls=(Model,), meta={}, **fields):
    """Create a model in functional way

    This 2 codes are equivalent

    >>> db = SqliteDatabase("foo.db")
    >>> class MyModel(Model):
    ...     name = TextField()
    ...     flag = BooleanField()
    ...        
    ...     class Meta:
    ...         database = db

    >>> db = SqliteDatabase("foo.db")
    >>> MyModel = create_model("MyModel",
    ...     meta={"database": db},
    ...     name=TextField(),
    ...     flag=Booleanfield()
    ...)


    """
    fields["Meta"] = type("Meta", (), meta)
    model = type(model_name, supercls, fields)
    return model
```

Maybe it will be useful for another developers to merge inside peewee (i replace sqlalchemy with this fragment of code) 


Thanks for your interest in peewee and for sharing your code.  I'm not sure this belongs as a github issue, as I use this to track bugs and new features, as opposed to snippets.