akumoth/peewee-issues huggingface dataset:
Issue: Genarated query problem
Author: ck196
State: closed
Created: 2016-04-19 03:16:08

I do deleted query on Relationship table

`
query = Relationship.delete().where(Relationship.video == video)
`

<class 'libs.basemodel.Relationship'> DELETE FROM "relationship" WHERE ("video_id" = (SELECT "t2"."id" FROM "video" AS t1 WHERE ("t1"."name" = ?) ORDER BY "t1"."add_date" DESC)) [u'v5.mp4']

Problem:
OperationalError: no such column: t2.id

My models very simple:

```
class BaseModel(Model):
    class Meta:
        database = database

class User(BaseModel):
    username` = CharField(unique=True)
    password = CharField()
    email = CharField()
    join_date = DateTimeField()

    class Meta:
        order_by = ('username',)

class Relationship(BaseModel):
    video = ForeignKeyField(User, related_name='relationships')
    to_user = ForeignKeyField(User, related_name='related_to')
    class Meta:
        indexes = (
            # Specify a unique multi-column index on from/to-user.
            (('video', 'to_user'), True),
        )

class Video(BaseModel):
    name = CharField(unique=True)
    width = IntegerField()
    height = IntegerField()
    step = IntegerField()
    frame_count = IntegerField()
    add_date = DateTimeField()

    class Meta:
        order_by = ('-add_date',)
```


Please fix the formatting of code.


Sorry, i've updated code formatting.


To fix this I believe if you explicitly select the Video ID column you'll be set:

``` python
video = Video.select(Video.id).where(Video.name == 'v5.mp4')
query = Relationship.delete().where(Relationship.video == video)
print query
```

Outputs:

``` sql
DELETE FROM "relationship" 
WHERE ("video_id" = (
  SELECT "t1"."id" 
  FROM "video" AS t1 
  WHERE ("t1"."name" = ?) 
  ORDER BY "t1"."add_date" DESC
))
```

If you've already got the query from somewhere else and want to modify the SELECT clause, you can do:

``` python
query = Relationship.delete().where(Relationship.video == video.select(Video.id))
```

---

An unrelated suggestion:

Never ever ever specify a default ordering for your models. It's a code smell and I regret building it into Peewee's API (the idea comes from Django originally, and the Django community seems to feel the same way about it). If you want a default ordering that's easily accessible, just do something like:

``` python
class Video(BaseModel):
    # ... fields ...

    @classmethod
    def all(cls):
        return cls.select().order_by(cls.add_date.desc())

# ------ elsewhere in your code ------ #

videos = Video.all()
```
Issue: Please support cockroachdb
Author: jet10000
State: closed
Created: 2016-04-18 10:07:37

https://www.cockroachlabs.com/


Peewee should work with any Python DB-API 2.0 driver. It looks like work has started on adding one for cockroachdb:

https://github.com/cockroachdb/cockroach-python/pull/12

After that, follow the instructions here to integrate with Peewee:

http://docs.peewee-orm.com/en/latest/peewee/database.html#adding-a-new-database-driver
Issue: cache result?
Author: daoiqi
State: closed
Created: 2016-04-12 06:39:42

``` python
class MyRetryDB(RetryOperationalError, MySQLDatabase):
    pass


database = MyRetryDB(DB_CFG['db'],
                     **{'host': DB_CFG['host'],
                        'password': DB_CFG['passwd'],
                        'port': DB_CFG['port'], 'user': DB_CFG['user']})


class UnknownField(object):
    pass


class BaseModel(Model):
    class Meta:
        database = database
        only_save_dirty = True

class TVm(BaseModel):
    uuid = CharField(primary_key=True)
    cpu = IntegerField()
    mem = IntegerField()
    disk = IntegerField()
    createTime = DateTimeField()

    class Meta:
        db_table = 't_vm'
```

``` python
while True:
        uuid = '8d443fb7-3e30-4149-bb32-89ee5532eddc'
        a = TVm.select().where(
            TVm.uuid == uuid)
        for x in a:
            print x.createTime, x.mem
        time.sleep(5)
```

when I (or another process) update db data,  this always print old data.
can fetch realtime data, not cache in memory?


Yes, try wrapping your select in an atomic block:

``` python
with database.atomic():
    a = TVm.select()...
```


``` python
while True:
        uuid = '8d443fb7-3e30-4149-bb32-89ee5532eddc'
        a = TVm.select().where(
            TVm.uuid == uuid)
        for x in a:
            print x.createTime, x.mem
        database.commit()     # it will get realtime data.
        time.sleep(5)
```


Yes. You can also enable commit on all selects:

database.commit_select = True


see https://github.com/coleifer/peewee/commit/6087199320a3ad8968add5d50f6c3a047767afdd

I use 2.7.3 so every select not commit.    upgrade to 2.8.0 to fix it. thanks.
Issue: create new record can't get the database default value.
Author: Dongin
State: closed
Created: 2016-04-11 05:42:49

```
class MyModel(Model):
    create_at = DateTimeField(constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')],
                              formats='%Y-%m-%d %H:%M:%S', verbose_name=‘Created time')
    other_fields = xxx

inst = MyModel.create(other_fields=xxx)
print inst.create_at is None
```

The output is **True**.


Correct, because peewee is not magical, it will not magically retrieve a value that was created by the database server.

You look to be using MySQL, which AFAIK does not support INSERT ... RETURNING. So you will need to re-fetch the record or alternatively create the timestamp in Python and send it to the server.
Issue: Update playhouse.rst
Author: aaronwp
State: closed
Created: 2016-04-08 17:26:49

I think read_slaves needs to be a list not a tuple. I was getting `TypeError: object of type '....' has no len()` until I made this change.


Hmm, but `tuple` does have `len`...

``` python
In [1]: x = (1,2,3)

In [2]: len(x)
Out[2]: 3
```


See my sample code below. For some reason I was thinking it was just a documentation issue, but perhaps it's a bug...?  

```
mysql_db = PooledMySQLDatabase('dbname',
                               host=os.environ['DB_HOST'],
                               user=os.environ['DB_USER'],
                               passwd=os.environ['DB_PASS'],
                               max_connections=32,
                               stale_timeout=300)  # 5 minutes.

mysql_read_db = PooledMySQLDatabase('dbname',
                               host=os.environ['DB_READ_HOST'],
                               user=os.environ['DB_USER'],
                               passwd=os.environ['DB_PASS'],
                               max_connections=32,
                               stale_timeout=300)  # 5 minutes.

def _db_close(exc):
    databases = [mysql_db,mysql_read_db]
    for database in databases:
        if not database.is_closed():
            database.close() 

class BaseModel(ReadSlaveModel):
    class Meta:
        database = mysql_db
        read_slaves = [mysql_read_db] // <--- if (mysql_read_db) then i get error.                            

```


A tuple with only one item needs a trailing comma.

(db,)
Issue: pskel not included in install
Author: charleschen
State: closed
Created: 2016-04-07 21:10:12

installing latest version of peewee doesn't include pskel.  was there a reason not to include it in the setup.py?


Not sure what happened...It doesn't look to have been in any release, so I guess I just spaced out. It will be included in the next release to pypi, which should be in the upcoming week or two.


thanks!
Issue: NoneType error when deleting an instance with multiple null foreign keys
Author: nomorepanic
State: closed
Created: 2016-04-06 15:05:39

peewee raises a NoneType error when deleting an instance with multiple null foreign keys, e.g.:

```
Schools(Base):
    id = PrimaryKeyField(primary_key=True)
    name = CharField()

Parent(Base):
    id = PrimaryKeyField(primary_key=True)
    name = CharField()

Children(Base):
    id = PrimaryKeyField(primary_key=True)
    parent = ForeignKeyField(Parents, null=True)
    school = ForeignKeyField(Schools, null=True)
    age = IntegerField()
```

Then

```
somechild = Children(age=12)
somechild.save()
...
somechild.delete_instance()
```

Will result in this:

```
 def delete_instance(self, recursive=False, delete_nullable=False):
    if recursive:
        dependencies = self.dependencies(delete_nullable)
        for query, fk in reversed(list(dependencies)):
            model = fk.model_class
            if fk.null and not delete_nullable:
                model.update(**{fk.name: None}).where(query).execute()
            else:
                model.delete().where(query).execute()
    >return self.delete().where(self._pk_expr()).execute()
    TypeError: 'NoneType' object is not callable
```

I was going to post this on Stackoverflow, but then I've seen that getattr(somechild, 'delete') returns None.

I am using peewee 2.8.0 on python 3.4.3


Pushed a testcase... It passed locally on py2.7 with sqlite, so we'll see what's up.


I found what's happening. My model has a nullable 'delete' field and that overwrites the delete method. 
Thank you for prompt reply but it was my distraction :)

You could consider renaming the delete method to _delete, but I do not know whether this would make sense with peewee.
Issue: Removed side effects while cleaning prefetch query
Author: kammala
State: closed
Created: 2016-04-02 15:55:10

There was a strange behaviour: then you do prefetch on complex query with some grouping, you will have some kind of exception or incorrect result. The reason of this is simple: `clean_prefetch_query` removes any information about grouping from query before placing it as subquery in prefetch queries, but it is done on original query instead of cloned one. 

This pr fixes this little bug(in a single code line ^_^).


Nice pull request, and nice catch. Thanks!
Issue: Polymorphic relationships
Author: smilledge
State: closed
Created: 2016-04-02 11:07:41

Does peewee support polymorphic relationships?

Would be awesome to have something similar to what is provided by Laravel and Rails:
https://laravel.com/docs/5.1/eloquent-relationships#polymorphic-relations
http://guides.rubyonrails.org/association_basics.html#polymorphic-associations


It's named Generic Foreign Keys. Check the Playhouse docs.
On Apr 2, 2016 2:07 PM, "Sam Milledge" notifications@github.com wrote:

> Does peewee support polymorphic relationships?
> 
> Would be awesome to have something similar to what is provided by Laravel
> and Rails:
> https://laravel.com/docs/5.1/eloquent-relationships#polymorphic-relations
> 
> http://guides.rubyonrails.org/association_basics.html#polymorphic-associations
> 
> —
> You are receiving this because you are subscribed to this thread.
> Reply to this email directly or view it on GitHub
> https://github.com/coleifer/peewee/issues/903


Didn't see that, thanks!


Thanks @arikfr ! @smilledge feel free to reply if you have follow-up questions.


I've made a simple generic foreign key implementation: https://gist.github.com/wonderbeyond/0e1f402b7595e0b4f9f653260fed029c#file-index-md
Issue: Migration to change CharField max_length
Author: josefdlange
State: closed
Created: 2016-03-29 22:19:01

The title says the most of it. I'm using `arnold` for migrations on my PostgreSQL database, and I think I'd want to do something like: 

```
def up():
    config.DB.execute_sql("SOME arbitrary SQL to MODIFY column;")

def down():
    config.DB.execute_sql("OPPOSITE of ABOVE SQL code;")
```

Does that sound about right? I don't see any first-class features for changing the size of a Postgres VARCHAR.


Indeed, for that you will need to probably consult the postgres manual for the correct SQL.  Peewee handles most things, but this is one of the ones it doesn't.
Issue: Can I define a default ORDER BY for a relationship?
Author: maaaks
State: closed
Created: 2016-03-28 14:16:07

I want something like this:

``` python
child = ForeignKeyField(Parent, related_name="children", default_order_by="birthday")
```


Your example foreign key definition doesn't make much sense...why would the foreign key be called "child" when it points at a Parent? I'm just going to assume this is a meaningless example...

At any rate, you can always just do:

``` python
class Category(Model):
    parent = ForeignKeyField("self", related_name="children_backref")

    @property
    def children(self):
        return self.children_backref.order_by(Category.name)
```
Issue: Is it possible to do a coalesce with peewee?
Author: KrisSiegel
State: closed
Created: 2016-03-27 04:42:03

I have 3 fields that I want to return as one field (2 of the 3 will _always_ be NULL so I only want the one with a value) but I haven't been able to find any documentation that shows a way to handle this in PeeWee. Am I missing something?


Yep, it's possible.

e.g.:

``` python
SomeModel.select(fn.COALESCE(SomeModel.field_a, SomeModel.field_b).alias('the_value))
```


Thanks, didn't see that in the documentation anywhere.


It's described here:

http://docs.peewee-orm.com/en/latest/peewee/querying.html#sql-functions-subqueries-and-raw-expressions


That doesn't actually mention coalesce only that you can _"use arbitrary SQL functions"_ but how do I know which functions are supported through PeeWee? Or do they _only_ map directly back to the database I have setup? For instance not every DB has ISNULL.

If the documentation was a little more clear either way it would be helpful. I think it would be nice to list all functions available across all databases versus what's available for individual ones. Or at least the verified working through PeeWee functions.


Literally the third sentence:

> To use arbitrary SQL functions, use the special fn() object to construct queries:

You can call literally any function, whether it exists or not. It's just syntactic sugar, read the code to get an understanding of how it works. You can nest calls to functions, you can do whatever.

> I think it would be nice to list all functions available across all databases versus what's available for individual ones.

That's stupid. Why would I do that? Read your database documentation, no sense in duplicating it.


> Literally the third sentence:

You and I both made reference to that passage and it doesn't address my specific statement so I'm not sure what you gain by reiterating a point we've both talked about. Regardless...

> That's stupid. Why would I do that?

So my comment already addressed the why. But if you think the suggestion is stupid I can't tell if you just ignored my 'whys' or you want other 'whys' without addressing my original ones. Regardless this doesn't sound like a productive conversation so no need to continue it.


The word _arbitrary_ means _any_ in the sentence I referenced. Meaning there's no need to duplicate other database vendors documentation since Peewee can call literally any function. The `fn` object creates a `Function` instance that the query compiler turns into a function call in SQL. Peewee doesn't have to know any more than that.


@KrisSiegel 

> I think it would be nice to list all functions available across all databases versus what's available for individual ones.

What about [personal functions](http://www.postgresql.org/docs/9.5/static/sql-createfunction.html)? ;-)


FWIW I got here by googling "peewee coalesce".  I started developing using Peewee without reading the docs end-to-end, so this issue helped me.

@skatenerd, interestingly, we're just exactly a month apart in using this issue to figure out how to use a `COALESCE` in Peewee.
Issue: Is it possible to load multiple JOINed models using a RawQuery?
Author: maaaks
State: closed
Created: 2016-03-25 09:20:06

If yes, how should I write the query, which aliases to use for rows?

For example, I want to load a `Site` object with joined `Page` objects which should be loaded using a custom recursive query.


As far as I understand, [`generate_column_map()`](https://github.com/coleifer/peewee/blob/65dbfc5b3b406278df15d55bd48dbfc4ab6f7d6f/peewee.py#L2205) generates a list containing every field in every model mentioned in `select()`, in the same order as they are defined in classes. Can I consider this a defined behavior? If yes, does it mean that I can just inherit from `RawQuery` and somehow force it to use the [`AggregateQueryResultWrapper`](https://github.com/coleifer/peewee/blob/65dbfc5b3b406278df15d55bd48dbfc4ab6f7d6f/peewee.py#L2316) in order to use results of my own query, and that I _do not need_ to use some specific table aliases?


No, it doesn't work because `AggregateQueryResultWrapper` also needs `_selects` and `_joins` fields which exist only in `SelectQuery` (and `RawQuery` is not a `SelectQuery`). :-(


By the way, is there any way to add a custom field to a model in `RawQuery`? E.g. field named `level`, for walking through a recursive result.


`RawQuery` does not support any fancy related-instance juju. It will simply patch all attributes returned by the cursor onto the model instance (or the dictionary or tuple if you're using .dicts() or .tuples()). There is not enough information available to peewee to generate the graph of joined models reliably.

You can, of course, write a wrapper that does the transformations you want.

Alternatively you can use the regular SelectQuery type of query, which supports traversing the model graph to reconstruct joined instances.

Yes, you can add whatever attributes you want with both RawQuery and SelectQuery.

For example:

``` python
class User(Model):
    username = CharField()

users = User.select(User.username, fn.LOWER(User.username).alias('all_lower_username'))
for user in users:
    print user.all_lower_username

users = User.raw('SELECT username, LOWER(username) AS all_lower_username FROM user')
# same as above.
```


Please respond if the above did not address your question and I'll re-open to discuss further.


> ``` sql
> python = User.raw('SELECT username, LOWER(username) AS all_lower_username FROM user')
> ```

I don't understand where will the `all_lower_username` go here? Will it become available as `user.all_lower_username` or something?


Yes, user.all_lower_username
Issue: Commit after select
Author: asztalosdani
State: closed
Created: 2016-03-23 14:07:45

Hi!

I am using peewee with PostgreSQL. I have tons of connections, which are 'idle in transaction', even if they use only select statements. If I call commit(), it becomes 'idle'. Why is there a transaction for the selects? Do I need to explicitly call commit?

Example:

```
plist = Person.select()
for person in plist:
    print(person.name, person.birthday)
# db.commit()
raw_input("Press Enter to continue...")
```

This leaves the connection 'idle in transaction', when using commit(), it's ok.
What is the correct way to handle this?
Thanks.


Actually, `PostgresqlDatabase` does call commit after every select query. How are you declaring / instantiating your database? What type of app is this?


It's a QT app, working in Maya, connecting to DB to select the project, then the asset you will work on.
PostgresqlDatabase was instanciated with autocommit=False. I changed it to True, now it seems to work.
But why is the select run in transaction?


> was instanciated with autocommit=False

This.

Unless you really understand Peewee's transaction management ID suggest using autocommit=True and using the provided `atomic` decorator/context manager when you want explicit transactions.


But why is the select run in transaction?

@coleifer can you answer this question?

The `SELECT` is "run in a transaction" because queries are always run in transactions, it's just that in autocommit mode those transactions are only 1 query long. In "autocommit" there's a commit() after each query to avoid leaving open, idle transactions. See #806 and #727 for a little more background.

I understand your question, but also bear in mind that when you're not within the scope of an explicit `atomic()` or `transaction()` block, then essentially each individual query you execute is going to be in its own transaction.
Issue: How do I update a foreign key directly?
Author: maaaks
State: closed
Created: 2016-03-23 13:52:48

This doesn't seem to work:

``` python
model.relatedmodel_id = 1
```


Fixed, thanks for reporting.


For what it's worth you can always set the actual foreign key field to an integer ID:

``` python
model.relatedmodel = 1
```


This is great. It always seemed odd to me to assign an integer to an attribute that really should be an object.

Does this change also allow creating and querying via the same identifiers? e.g.

`Foo(relatedmodel_id=1)`

or

`Foo.select().where(Foo.relatedmodel_id == 1)`


At the moment creation is supported, but for querying you will still need to use the foreign key field itself. And honestly, I don't think I'll change it.


Ok thanks.

Somewhat related: Is there any reason accessing a non-existent field doesn't issue an error/warning. I've spent a decent amount of time tracking down issues that end up being due to using an incorrect field name, e.g.:

``` python
# Incorrect
User.select().where(User.city_id == 1)  # This looks valid to me, but will result in the wrong query (with no error/warning)
User.create(city_id=1)  # This one works as of 2.8.1, but previously I'd end up scratching my head over why the city_id field wasn't getting set
```


That should raise an exception, I will reopen.


Cool. Thanks! And just to clarify, the second case can still occur with other values, e.g.

``` python
# These "work", but I would expect an exception
user = User.create(non_existent_field=44)
user = User(non_existent_field=235)
```


I actually decided to go ahead and support querying on the `xxx_id` field.
Issue: Blob fields of variable (possibly huge) size?
Author: gitttt
State: closed
Created: 2016-03-22 19:07:50

Is it possible to create fields of variable (possibly huge) size?
I am currently using `memory_content = BlobField(null=True)`. This seems not to be the right choice as I get

`/opt/Python2.7-32bit/lib/python2.7/site-packages/pymysql/cursors.py:158: Warning: Data truncated for column 'memory_content' at row 1`

 I am using mysql, i.e. the `BlobField()`is translated to a mysql blob.
The mysql docu says:

> A BLOB is a binary large object that can hold a variable amount of data. The four BLOB types are TINYBLOB, BLOB, MEDIUMBLOB, and LONGBLOB. These differ only in the maximum length of the values they can hold.

Do I have to create a custom field as discussed [here](http://docs.peewee-orm.com/en/latest/peewee/models.html#custom-fields)?

Or can I use another already existing field type (maybe CharField?)

Would it be any different with postgresql ?
What is the peewee best practice here?


Wow, I'm not sure how to advise you, I'd suggest consulting the MySQL documentation and if that doesn't help, seeing about reaching out on whatever support channels they have...or StackOverflow?

If you want to do a LONGBLOB field in peewee, you'll need to add a custom field type for it as blob fields correspond to plain old "BLOB" columns in MySQL. Your code would look like:

``` python
class LongBlobField(BlobField):
    db_field = 'longblob'

db = MySQLDatabase('my_db', fields={'longblob': 'LONGBLOB'})
```

You might also consider storing the blobs externally on the filesystem -- some databases aren't optimized for storing massive values in every row.

Regarding postgres, again you should consult their docs.


Thanks for the code snipped. Seems to work nicely for me.
Issue: Fix logic error
Author: lneoe
State: closed
Created: 2016-03-22 10:04:06

## Fixed
- fix logic error in `playhouse.shortcuts.model_to_dict`
  if `backrefs = True`,  only `descriptor` and `foreign_key` fields both not in the `only` will excute `continue`

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

class Tweet(Model):
    use = ForeignKeyField(User, related_name='tweets')

user = User(name='tim')
tweet = Tweet(user=user, content='my first tweets')
```

I  define two model  `User` and `Tweet`, and when I use `model_to_dict`, the output is

```
d = model_to_dict(user, only=[User.name, User.tweets, Tweet.content], backrefs=True)
print(d)
>>> {'name': 'tim'}
```

but the expect results should be

```
>>> {'name': 'tim', 'tweets': {'content': 'my first tweets'}}
```

This PR fixed this issue


Thanks, I've committed equivalent patch with a testcase. I appreciate this fix!!