akumoth/peewee-issues huggingface dataset:
Issue: Feature request: provide feedback if Model.save() succeeded
Author: CodeZombieCH
State: closed
Created: 2014-04-19 02:18:21

The current implementation of [Model.save()](https://github.com/coleifer/peewee/blob/master/peewee.py#L3071) does not provide any feedback if the save operation failed or succeeded. In the case a retrieved record has been deleted by a concurrent process in the meantime, the executed UPDATE statement finds no matching record and "fails" silently.

**Proposal 1**
The Model.save() implementation should be changed so it returns the number of affected rows by the executed SQL statement. This allows developers to ensure a successful save operation.

**Proposal 2**
The Model.save() implementation should be changed so it raises an exception in the case the number of rows affected by the UPDATE statement is not 1.

Example describing the issue:

```
from peewee import *

db = SqliteDatabase('example.sqlite')


class BaseModel(Model):

    class Meta:
        database = db


class Foo(BaseModel):
    bar = CharField()


def init():
    db.connect()

    Foo.create_table(True)


def close():
    db.close()

init()

# Prepare sample for test
f = Foo.create(bar='Lorem ipsum')
f.save()

f1 = Foo.get(Foo.bar == 'Lorem ipsum')
f2 = Foo.get(Foo.bar == 'Lorem ipsum')

f1.delete_instance()
f2.bar = 'dolor sit'

# Saving a record deleted in the meantime does "fail" silently
f2.save()

close()
```


Peewee doesn't try to be too clever here, but you're right, the update statement will fail silently, which I agree is bad.


Fixed in e73d3db12fb370c48cca8f45514a7fed9637949c -- returns number of rows affected (1 always in case of inserts, but could be 0/1 for updates).


Cool, thanks a lot.
Issue: playhouse.sqlcipher_ext.SqlCipherDatabase
Author: thedod
State: closed
Created: 2014-04-18 23:06:38

Tests pass with `-e pysqcipher` and with `--pysqlcipher`


Ouch :cold_sweat: forgot to test _without_ `-e pysqlcipher`. Sorting it out now...


If you take a look at peewee's source code you will see that I have tried to stick to pep8 in most places. I've also tried to have uniform formatting of comments.


I believe your tests are failing because you have not registered some default type converters. Take a look at the top of the peewee module and you'll see what I'm referring to (line ~130, `sqlite3.register_adapter()`).

Also there should be some basic unit tests for this module. You can see the other modules in the `playhouse` have some unit tests.


> I believe your tests are failing because you have not registered some default type converters. Take a look at the top of the peewee module and you'll see what I'm referring to (line ~130, sqlite3.register_adapter()).

Thanks. I'll look into this.

> Also there should be some basic unit tests for this module. You can see the other modules in the playhouse have some unit tests.

The only `--pyciphersql` test I can think of (that is not covered by running the regular tests with `-e pysqlcipher`) is: Create a db, store something, try to open it with wrong passphrase and fail, try with the right one and succeed. Would that do?


This is close but not quite ready. I don't know how many times I need to mention this but pay attention to spacing in your operations. Read pep8.


Lookin good mane. Last thing (I promise) is some docs in the `playhouse` documentation file.


This is now merged, thanks for the contribution!
Issue: Insert_many - ids of created rows
Author: piacias
State: closed
Created: 2014-04-14 08:58:11

Is it possible to get newly created ids, when creating rows via insert_many ?


I suppose, the reason I had not added that was out of memory considerations but on the whole I think that even if you were inserting a very large number of rows, it would be ok to keep the list of IDs around.


Actually, because the insert will in most cases be only a single statement, there is not a way to efficiently get the PK of all the inserted rows.
Issue: Array and JSON fields do not output SQL with table aliases
Author: mtwesley
State: closed
Created: 2014-04-13 16:05:55

I was having a problem with joining two tables with the same field name when one of them was of an array type, using the extra Postgres functionality from the Playhouse package. I narrowed it down to it being related to the table alias not being included in the where clause of the query. 

Took a while, and I'm not sure whether this is the correct "fix", but I think the problem was related to the alias_map not being passed into the parse_node() function, leading to an output of:

``` sql
where ("int_values"[1] = 123);
```

Instead of:

``` sql
where (t1."int_values"[1] = 123);
```

Here's a diff of the change I made that appears to work:

``` diff
diff --git a/playhouse/postgres_ext.py b/playhouse/postgres_ext.py
index a9c864f..98fc3e7 100644
--- a/playhouse/postgres_ext.py
+++ b/playhouse/postgres_ext.py
@@ -203,13 +203,13 @@ class PostgresqlExtCompiler(QueryCompiler):
         if unknown:
             if isinstance(node, ObjectSlice):
                 unknown = False
-                sql, params = self.parse_node(node.node)
+                sql, params = self.parse_node(node.node, alias_map, conv)
                 # Postgresql uses 1-based indexes.
                 parts = [str(part + 1) for part in node.parts]
                 sql = '%s[%s]' % (sql, ':'.join(parts))
             if isinstance(node, JsonLookup):
                 unknown = False
-                sql, params = self.parse_node(node.node)
+                sql, params = self.parse_node(node.node, alias_map, conv)
                 lookups = [sql]
                 for part in node.parts:
                     part_sql, part_params = self.parse_node(part)
```


Ah, nice catch!


Thanks for your help reporting and debugging this issue.
Issue: Possible to aggregate by month, day, etc?
Author: tonycpsu
State: closed
Created: 2014-04-10 14:58:11

What's the recommended way to do a group by month or day with a DateTimeField?  I know some of the underlying databases have date functions, and I know I could do it by formatting it as a string and truncating the hours, minutes, etc. but I thought there might be a more portable way to do it using Peewee.


It depends on your database.

For sqlite you might use the `strftime` function to truncate a timestamp. This would calculate a sum of some value for each month:

``` python
query = (Values
         .select(fn.Sum(Values.value).alias('average_value'))
         .group_by(fn.strftime('%Y-%m', Values.timestamp))
         .tuples())
```

For postgresql you would use `date_trunc`.

``` python
query = (Values
         .select(fn.Sum(Values.value).alias('average_value'))
         .group_by(fn.date_trunc('month', Values.timestamp))
         .tuples())
```


OK, was looking for something that wasn't as dependent on the underlying DB, but I can use that for now.  Thanks.


Yeah, it might be a good idea to create a custom `date_trunc` function for sqlite. As far as MySQL, I'm not really sure since I don't use MySQL myself.


Fixed in b6b6c2e9ed7215e25494c8bf4b8a73118eedc724
Issue: MySQL - using 'from_' function - 'Every derived table must have its own alias'
Author: Seidr
State: closed
Created: 2014-04-07 07:30:41

While playing around with the 'from_' function that was introduced recently (I believe it was pretty recent (see https://groups.google.com/forum/#!topic/peewee-orm/FSHhd9lZvUE), and ran into a bit of an issue when attempting to use the same query that worked under SQLITE, on a MySQL database.

The issue is - as the error message describes - that the table that is derived after using the 'from_' function is not provided with an alias.

Please see the below link to a gist that demonstrates this issue. Using SQLITE, upon visiting the root of the app (http://localhost:5000) you will get a list of IDs resulting from the Group_Concat (1,2,3,4,5). When running the same script, while using the MySQL config, you will get the following error upon visiting the root of the app.

"OperationalError: (1248, 'Every derived table must have its own alias')"

A solution would be to have an alias pool for queries that make use of the 'from_' function, for nested queries.

A test to demonstrate an error when using nested queries in peewee (MySQL): https://gist.github.com/Seidr/10016077

While I can work around this by performing the Group_Concat outside of peewee (i.e. just creating the list by looping over the result set), it'd be nice to be able to alias nested queries within peewee.

If you need any more information, do let me know, and I'll be happy to help.


Sorry, I should of mentioned..while this is using the flask-peewee wrapper, I've updated peewee to the most recent version (git clone) and am still experiencing the issue described above, and believe that if I were to re-write this test to use purely peewee, this issue would still be experienced.


Have you tried applying an alias to the inner query?

```
user_id = SQL("inner_query.user_id")
result_list = SQL("inner_query.result_list")
results = ResultSet.select(
    fn.Group_Concat(result_list).alias("result_list_n")
).from_(
    ResultSet.select(
        ResultSet.user.alias("user_id"),
        ResultSet.result.alias("result_list")
    ).
    order_by(
        ResultSet.result.asc()
    ).
    limit(
        5
    ).
            alias('inner_query')
).group_by(user_id).first().result_list_n
```

On Mon, Apr 7, 2014 at 3:24 AM, Seidr notifications@github.com wrote:

> Sorry, I should of mentioned..while this is using the flask-peewee
> wrapper, I've updated peewee to the most recent version (git clone) and am
> still experiencing the issue described above, and believe that if I were to
> re-write this test to use purely peewee, this issue would still be
> experienced.
> 
> ## 
> 
> Reply to this email directly or view it on GitHubhttps://github.com/coleifer/peewee/issues/336#issuecomment-39706110
> .


Wow - I'm sure I had tried that..

Thanks for getting back to me, and indeed aliasing the inner query solves the problem.
Issue: PostgreSQL  cast array to list
Author: mmqqg
State: closed
Created: 2014-04-03 14:15:43

```
post = BlogPost(content='awesome', tags=['foo', 'bar', 'baz'])
```

Is there a way to use  

``` python
for tag in post.tags:
    print(tag)
```

?

http://initd.org/psycopg/docs/extensions.html#psycopg2.extensions.new_array_type


I'm confused...isn't it already a list?

``` python
In [1]: from playhouse.tests_postgres import *

In [2]: am = Array
ArrayField  ArrayModel  

In [2]: am = ArrayModel(tags=['foo', 'bar', 'baz'])

In [3]: for tag in am.tags:
   ...:     print tag
   ...:     
foo
bar
baz
```


@mmqqg -- can you please respond with more details?


It's my fault, sorry. I check the db and find 

```
tags         | character varying           | not null
```

It should be

```
tags         | character varying(255)[]    | not null
```

Maybe I modify some peewee code that caused "Model.create_table()" didn't work well.

Thanks a lot!
Issue: feature request: add support for printing SQL statments
Author: intrixs
State: closed
Created: 2014-03-31 15:55:23

So in Django, you have the 'sqlall' command that allows you to do the following:
##### Prints the CREATE TABLE, custom SQL and CREATE INDEX SQL statments for the given model module name(s).

It would be nice if peewee adds support for displaying database-specific SQL statements of a model or query.

Thanks.


Did some research, and found out that maybe I can use 'logging' module to achieve part of 'sqlall' functionalities. 

http://stackoverflow.com/questions/6350411/how-to-retrieve-executed-sql-code-from-sqlalchemy
Issue: Check constraint not working with .create_table()
Author: bartkl
State: closed
Created: 2014-03-28 21:38:22

Hi,

I'm trying to define a simple `CHECK` constraint on a table, and to create it with `.create_all()`. It can't get it to work. I get no errors, but when I look at the table definition of the created table, it has no constraints defined.

My models file:

``` python
from peewee import *

db = MySQLDatabase('my_db_name, host='127.0.0.1', port=3306, user='my_user', password='my_secret') 

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

class Film(BaseModel):
    title = CharField()
    year = IntegerField(constraints=[Check('year > 0')])
```

The table definition according to `SHOW CREATE TABLE film`:

``` mysql
CREATE TABLE `film` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `year` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
```

I have browsed through peewee's source code, and I get the feeling any constraints other than the `FOREIGN KEY` aren't even touched by the `.create_table()` method. Relevant part quoted from its definition:

``` python
for field in meta.get_fields():
            columns.append(self.field_definition(field))
            if isinstance(field, ForeignKeyField) and not field.deferred:
                constraints.append(self.foreign_key_constraint(field))
```

Be warned, I'm a relative newbie, so I might be foolishly mistaken.

Thank you,
Bart


I looked into this matter some more, and apparently it is a MySQL issue:

> The CHECK clause is parsed but ignored by all storage engines.

[http://dev.mysql.com/doc/refman/5.5/en/create-table.html](MySQL docs)
(Version 5.5 corresponds with the one I use, but note that the latest documentation (5.7) also documents this.)

So, I'm sorry for wasting your time, this issue can be closed.
Issue: two first result in double
Author: dkmatt0
State: closed
Created: 2014-03-23 01:11:28

Hi, (I'm french, sorry for poor english...)
I make this code for testing :

```
from peewee import *
import datetime

class Test(Model):
    name = TextField(null=True)

Test.create_table()
Test.create(name='a')
Test.create(name='b')
Test.create(name='c')
Test.create(name='d')
Test.create(name='e')
Test.create(name='f')

select = Test.select()
for s in select:
    print(s.id)
    Test.update(name=0).where(Test.id==s.id).execute()
```

and I get this :

```
1
2
1
2
3
4
5
6
```

You can see "id" 1 and 2 appear two times !

If i change last line by :

```
    print(Test.update(name=0).where(Test.id==s.id))
```

I haven't this problem (but my db is not updated)

It's normal or it's a bug ?
Thank


If I add 

```
[s for s in select]
```

between select and for, like this :

```
select = Test.select()
[s for s in select]
for s in select:
    print(s.id)
    Test.update(name=0).where(Test.id==s.id).execute()
```

I haven't this problem...

I don't understand why ?!


@dkmatt0 

PEP-XXX (I don't remember which one) or just sane idea:
You should not modify object while you iterate over it. And you should be even more careful with lazy-objects.

Is this bug, or not, the better way to perform this logic (as I think):

_(pseudocode)_

```
with transaction
    id_list = list(select(ids))
    for every id in id_list
        perform operation and save
```

Something similar you do in your example with `[s for s in select]`: you consume laziness and perform operations related to static dataset.

But, again, its my IMHO.


See: 
- https://github.com/coleifer/peewee/issues/12#issuecomment-5614404
- https://github.com/coleifer/peewee/issues/81


OK perfect ! Thank you :+1:
Issue: table_alias
Author: stanegit
State: closed
Created: 2014-03-21 20:24:50

Hi , I know I asked for this and it is working but trying to get sql server working with peewee (pyodbc) in this part of code in peewee

```
def _max_alias(self, alias_map):
    max_alias = 0
    if alias_map:
        for alias in alias_map.values():
            alias_number = int(alias.lstrip('t'))
            if alias_number > max_alias:
                max_alias = alias_number
    return max_alias + 1
```

alias_number = int(alias.lstrip('t')) this part of code "assume" that alias for table is
like 't1'... etc, but if I defined table_alias this is were you get exception

so I kind of fix it (for what I understand :) ) with this

   def _max_alias(self, alias_map):
        max_alias = 0

```
    if alias_map :
        # for alias in alias_map.values():
        # fix
        for key,alias in alias_map.iteritems():
            if len(key._meta.table_alias) < 1:
                alias_number = int(alias.lstrip('t'))
                if alias_number > max_alias:
                    max_alias = alias_number
    return max_alias + 1
```

I hope this helps


Can you give me a set of models and a query that reproduces this error?
Issue: Joining 2 tables with naive is messing up pks
Author: masell
State: closed
Created: 2014-03-16 14:29:39

Should i not have only 1 instance of Model1 when calling with naive?
The ids are from Model2, but on Model1's instances.
I guess it's because the select query does not use any prefixing when selecting from multiple tables.

from peewee import SqliteDatabase, Model, PrimaryKeyField, CharField, \
  IntegerField, ForeignKeyField
from playhouse.test_utils import test_database

test_db = SqliteDatabase(':memory:')

class Model1(Model):
  a = CharField()                                                                                                                                                                 

class Model2(Model):
  b = CharField()
  m1 = ForeignKeyField(Model1)

if **name** == '**main**':

  with test_database(test_db, (Model1, Model2)):
    Model1.\
     create(id=1, a="A1").\
     create(id=2, a="A2")

```
Model2.\
 create(id=3, b="example2", m1=1).\
 create(id=4, b="example3", m1=1).\
 create(id=5, b="example4", m1=1)

res = (Model1
       .select(Model1, Model2)
       .join(Model2)
       .naive())
print res.sql

for a in res:
  print a, a.id, a.a
```

output>
<bound method SelectQuery.sql of <class '__main__.Model1'> SELECT t1."id", t1."a", t2."id", t2."b", t2."m1_id" FROM "model1" AS t1 INNER JOIN "model2" AS t2 ON (t1."id" = t2."m1_id") []>
<**main**.Model1 object at 0x1f61810> 3 A1
<**main**.Model1 object at 0x1f61d90> 4 A1
<**main**.Model1 object at 0x1f61a50> 5 A1


What do you want to happen, I guess? When you use `.naive()` peewee simply `setattr`s named values from the result column onto the model instance. If multiple columns are named `id` then the last one wins.

To circumvent that you could:

``` python
res = (Model1
          .select(Model1.id, Model1.a, Model2.id.alias('model2_id'), Model2.b)
          .join(Model2)
          .naive())
```

The other issue is that model1 may have many model2's. A better plan would be to use prefetch:

``` python

res = prefetch(Model1, Model2)
for a in res:
    for b in a.model2_set_prefetch:
        ....
```


I dont know, i think i may have missunderstood the docs.
Issue: Prefetch issue
Author: coleifer
State: closed
Created: 2014-03-13 13:55:08

Via SO: http://stackoverflow.com/questions/22368917/prefetch-db-rows-using-the-python-peewee-orm

---

I want to use the python peewee prefetch functionality.

I have three tables: A, B, and C.

B has a foreign key in its table to A, and table C has a foreign key to B.

I want to select all the rows in B, while also prefetching the rows in C that belong to those in B. However, I also want to grab the rows in A that B belongs to so I don't launch new queries every time I try accessing that property from the results.

This is the code I have currently:

``` python
b_query = B.select(B, A).join(A).where(B.user_id == user_id)
c_query = C.select()

prefetch(b_query, c_query)
```

However, this code fails with the error: OperationalError: (1241, 'Operand should contain 1 column(s)')

When I change the code to this it works:

``` python
b_query = B.select().join(A).where(B.user_id == user_id)
c_query = C.select()
```

But, since I am no longer selecting A in b_query, I believe every time I access b.a it will launch a new query, which I am trying to avoid. Any suggestions?

EDIT:

I did some more digging into this, and it seems that trying to do it the way I initially proposed might be impossible. The successful mechanism results in this query as one of the two queries resulting from the prefetch:

```
SELECT C.* from C WHERE C.B_id IN (SELECT B.id FROM B WHERE B.user_id == <user_id>)
```

Trying to add A into the select for b_query results in the following query:

```
SELECT C.* from C WHERE C.B_id IN (SELECT B.*, A.* WHERE B.user_id == <user_id>)
```

Obviously this won't work.


Fixed in e9a38d24ba0ef75b26728fc29cd8909fbda4e3f7 by manually selecting only the relevant PK when generating the subquery.
Issue: Travis CI
Author: bndr
State: closed
Created: 2014-03-07 07:39:39

Hi!

I would like to suggest implementing Travis CI for automation of tests on pull requests. http://docs.travis-ci.com/


+1


I absolutely hate their site and had removed travis-ci a while ago.  Be happy to add it back, though.


It's back.  It's like damn whack-a-mole trying to get these tests passing (all of which pass locally, of course). If you all can help debug these failures I'd always appreciate it! https://travis-ci.org/coleifer/peewee


Well that was fast. I was planning on debugging those tests this weekend, but it seems almost all of them are passing now. Good job! :)


All but this one test, getting very close. I had no idea there were so many py3k issues, so this was definitely a good suggestion.


[![Build Status](https://travis-ci.org/coleifer/peewee.png?branch=master)](https://travis-ci.org/coleifer/peewee)
Issue: How to use order_by with alias field?
Author: thibaultmeyer
State: closed
Created: 2014-03-06 09:56:46

Hello,

we have 2 issues with the order_by function when we use it with aliased field.
1. How to use  ASC and DESC with aliased field ?
2. The SQL generated request :

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

Output request:

``` sql
SELECT DISTINCT t1.`id`, t1.`vendor`, t1.`amount`, t1.`client`, 
MIN(t1.`date`) AS date FROM `transaction` AS t1 
GROUP BY t1.`matricule` ORDER BY 'date'
```

Rather than 

``` sql
SELECT DISTINCT t1.`id`, t1.`vendor`, t1.`amount`, t1.`client`, 
MIN(t1.`date`) AS date FROM `transaction` AS t1 
GROUP BY t1.`matricule` ORDER BY `date`
```

In MySQL  5.5.35, the request results is bad ordered with "date" but is well ordered with `date`.


You have a couple options:

``` python
.order_by(SQL('date'))

.order_by(fn.MIN(Transaction.date))
```


oh thanks. I can use .desc() & .asc() too ?
Issue: auto_round gone from DoubleField
Author: arnuschky
State: closed
Created: 2014-03-05 13:23:40

During upgrade from 2.17 to 2.2.0, auto_round vanished from DoubleField

Is that a bug, or is there a migration necessary? If the latter, there's a notice missing in the docs.


Ah. Apparently auto_round was only intended (and meaningful) for DecimalFields. 

Starting from 2.2.0 peewee now complains about the superfluous attribute; something that it didn't before, so our upgrade failed.

Can someone confirm this?


Yes that is correct.


Sorry for the inconvenience, I refactored a lot of Field code, including cleaning up the attributes.


Np, just caused us to clean up our code.