akumoth/peewee-issues huggingface dataset:
Issue: Is there a possibility of model.save() returning 0
Author: Siva7891
State: closed
Created: 2017-02-24 06:56:27

1) When we try to insert a new row (i.e., model class's object) using model.save() (or model.save(forceinsert=True), will it ever return 0.? If we had primary key conflict, then we receive an exception. So can we safely assume that model.save() will always insert that object and if any problem, it would raise an exception

1) When we try to update a row (i.e., model class's object) using model.save() will it ever return 0?  Can we safely assume that model.save() will always update that object and if any problem, it would raise an exception?

I started to use peewee very recently on a small sized project. Currently, I am writing a wrapper code for all the model.save and handling the situation if the response is not '1'?  It is a right practice?

Ummm...that's an interesting question, I guess... I think that you shouldn't need to even check the return value. An exception will be raised if a constraint is violated, for instance. So calls to `save()` should really be assumed to work unless an exception is raised.

Thanks for replying much faster.
Issue: MemoryError when querying database from Process
Author: HaraldNordgren
State: closed
Created: 2017-02-21 13:38:51

I am trying to create a program with 3 processes that read from the same database. The code was working before I started introducing processes.

I am getting MemoryError when performing a `select()`. I suspect there is something wrong with sharing of resources:

**models.py**

```python
from playhouse.pool import PooledSqliteExtDatabase

file_scanner_database = PooledSqliteExtDatabase(
    None,
    max_connections=32,
)

class FileModel(Model):
    class Meta:
        database = file_scanner_database
```


**main.py**

```python
from file_scanner import FileScanner
from models import file_scanner_database
from models import FileModel
from multiprocessing import Process

def create_scanner_agent(data):
    scanner = FileScanner(data)
    scanner.start_scanner()

shared_info = {'db_location': '/absolute/path/to/database'}

file_scanner_database.init(shared_info['db_location'])
file_scanner_database.connect()
file_scanner_database.create_tables([FileModel], safe=True)

new_process = Process(
    target=create_scanner_agent,
    args=(shared_info,)
)
new_process.daemon = True
new_process.start()

try:
    new_process.join()
except KeyboardInterrupt:
    pass

new_process.terminate()
```

**file_scanner.py**

```python
from models import file_scanner_database
from models import FileModel

class FileScanner:
    def __init__(self, data):
        for k, v in data.items():
            setattr(self, k, v)

        file_scanner_database.init(self.db_location)
        file_scanner_database.connect()

    def start_scanner(self):
        while True:
            # THIS IS WHERE THE PROGRAM CRASHES
            for row in FileModel.select():
                ...
```

If I use `Thread` instead it seems to work.

Possibly related to https://github.com/ghaering/pysqlite/issues/110

If you're iterating over a large result set, peewee caches rows in-memory (to make subsequent iterations/indexing operations fast). In the case of a single-shot iteration over a lot of rows, though, this can OOM you.

To fix, just make this change to the problematic line of code:

```python
while True:
    # Note we add ".iterator()"
    for row in FileModel.select().iterator():
        ...
```

Please read:

http://docs.peewee-orm.com/en/latest/peewee/querying.html#iterating-over-lots-of-rows

This happens for an empty database too.

then I highly doubt the issue is in Peewee.
Issue: Give TypeError if calling 'update', etc. from Model instance instead of from class
Author: HaraldNordgren
State: closed
Created: 2017-02-19 19:18:30

Same as #1195 but based on the latest master commit with passing tests

It seems that Travis still builds from HEAD of origin/master with the PR commit placed on top, so this also fails.

Reverting "Move cdefs back into extern from block" and "Bring back tests" makes the tests work again.

Again, this branch probably should not be merged. But at least my commit is not breaking anything.
Issue: Give TypeError if calling 'update', etc. from Model instance instead of from class
Author: HaraldNordgren
State: closed
Created: 2017-02-19 18:41:22

I hit a snag with this that took me hours to debug. Since these methods really only make sense as class methods, I don't think you should to be able to call them from an instance.

Related to https://github.com/coleifer/peewee/issues/1192

This grosses up the code quite a bit. They already have the classmethod decorator.

I agree that it grosses up the code a bit. But it's very useful.

Oh well, it's your repo.
  

The grossness isn't a reflection of your coding ability, I should make that clear -- it's just one of those problems that requires a kinda gnarly fix. Thanks for the patch, I'm sorry if it was wasted time on your part.
Issue: Many to many creation results in AttributeError
Author: divmgl
State: closed
Created: 2017-02-19 01:43:23

Getting an attribute error when saving a model with two foreign key relationships.

```python
class Account(BaseModel):
    created_on = DateTimeField(null=False)
    username = CharField(null=False, unique=True)
    password = CharField(null=False)
    salt = CharField(null=False)
    name = CharField(null=False)
    last_session_on = DateTimeField()

class Board(BaseModel):
    name = CharField()
    created_on = DateTimeField(null=False)
    slug = CharField(null=False)

class AccountBoard(BaseModel):
    account = ForeignKeyField(Account, null=False, unique=True)
    board = ForeignKeyField(Board, null=False, unique=True)

    primary_key = CompositeKey('account', 'board')

    class Meta:
        db_table = "account_board"

kwargs = {"name": model.get("name")}

# App crashes here. board.id and session["id"] are returning integers
with db.atomic():
    board = Board.create(**kwargs)
    AccountBoard.create(board=board, account=session["id"])
```

```
builtins.AttributeError
AttributeError: 'bool' object has no attribute 'as_entity'
```

Can anyone point me in the right direction? I really don't understand what's wrong about this code.

This is definitely not the ideal scenario, but it fixed my errors so I can continue development... 

cc @coleifer 

Actually this breaks other functionality... so disregard. 

Had to do this instead:

```python
db.execute_sql("""
    INSERT INTO account_board (account_id, board_id)
    VALUES (%s, %s)
""", (session["id"], b.id))
```

You're calling Board.create().execute() which is weird...

Sorry, I deleted my previous comment, because it's actually not technically necessary to use the "_id" accessor when passing in the plain old integer foreign key ID (instead of a related model instance).

I'm having trouble with your example -- it doesn't make sense, is that from your actual code or is it something you made up for the ticket?

`Board.create(**kwargs).execute()` doesn't make any sense.

I see that part of the issue is related to having PRIMARY KEY=False. Why not make a composite key on both fields instead?

```
primary_key = CompositeKey('account', 'board')
```

Or does that just cause more issues for you?

Let me try making a composite key. I'm using Postgres and it doesn't
support composite keys but it does support adding a unique index with two
or more columns.

I didn't mean to put `Board.create`, I meant to put `Board.insert`. Also
the code is copy/pasted. I'll try your suggestions and get back to you.
Thanks for your help.

@coleifer i modified my code in my OP to reflect your changes. I'm now getting this error:

```
peewee.ProgrammingError: column "id" does not exist
LINE 1: ...rd" ("account_id", "board_id") VALUES (2, 79) RETURNING "id"
```



Disregard, I got it to work :) thanks @coleifer, I just needed to know about the `CompositeKey` class
Issue: Update() on row affects every row and should be disallowed
Author: HaraldNordgren
State: closed
Created: 2017-02-13 14:47:34

I used this code to update rows, and it seems that the update() function used in this way updates more than the current row. Possible every row in the model.

Maybe it should be altogether disallowed to avoid mistakes?

```
rows = Model.select().where(
    Model.status == SOME_STATUS
)

for row in rows:
    <do things, then depending on results do>
    row.update(
        job_status=SOME_NEW_STATUS
    ).execute()
```

And the correct syntax in this case seems to be

    for row in rows:
        <do things>
        row.job_status = NEW_STATUS
        row.save()

`update` is a classmethod, and is document as such. An update without a where clause is going to cause problems, obviously, as you found.
Issue: SqliteQueueDatabase exception fix for #1189
Author: krikunts
State: closed
Created: 2017-02-10 22:08:08

Fixing reference before assignment error #1189 

No, `exc` is set in the `else` clause, which runs if no Exception is raised...

I was mistaken! In Python 3, the expression: `except <Exception> as <var>` does not keep `<var>` in scope beyond the exception block. So you were correct in raising this issue. It is fixed in `master` now.

Great! My bad, I didn't specify python version (it was 3.5, for the record)
Issue: SqliteQueueDatabase Writer.execute() error
Author: krikunts
State: closed
Created: 2017-02-10 21:55:45

Hello! I read your blogpost about SqliteQueueDatabase and want to try it out, but I have an error using SqliteQueueDatabase 
UnboundLocalError: local variable 'exc' referenced before assignment
https://github.com/coleifer/peewee/blob/d99f4a2649ce02da2f67769e381e51264f3c3eac/playhouse/sqliteq.py#L176
If there is exception during sql execution, the exc var is not visible outside of except block

What exception is underneath the `UnboundLocalError` I wonder? I would've thought that catching Exception would suffice but it looks like whatever error is being raised extends `BaseException` instead?

I couldn't replicate. I added a test-case in which invalid SQL is executed via the AsyncCursor and it all works as I'd expect. I've pushed a new release (2.8.6) in case the bug existed in the last release, however.
Issue: SQLite :memory: is not working in threads
Author: charleschen
State: closed
Created: 2017-02-09 06:48:34

The following doesn't work:

```
import datetime
import threading
import time

import peewee

from playhouse import test_utils
from playhouse import sqlite_ext

db = sqlite_ext.SqliteExtDatabase('werd.sql')
test_db = sqlite_ext.SqliteExtDatabase(':memory:')


class SomeTable(peewee.Model):

    created_at = peewee.DateTimeField(default=datetime.datetime.now)

    class Meta:
        database = test_db


threads = []


def worker():
    print('db_instance', id(SomeTable._meta.database))  # same id here
    print('SomeTable count', SomeTable.select().count())  # will fail here
    return


peewee.create_model_tables([SomeTable], fail_silently=True)

SomeTable.create()
print('db_instance', id(SomeTable._meta.database), SomeTable.select().count())

for i in range(2):
    t = threading.Thread(target=worker)
    threads.append(t)
    t.start()

    time.sleep(2)  # just so we don't exit before the worker is done
```

is SQLite in memory not meant for threading?


In-memory databases only exist for the connection that opened them. Peewee, by default, uses a connection-per-thread, so each thread has it's own connection (and hence its own in-memory database). You can disable this by passing `threadlocals=False` to the db when initializing it. You might also need to pass `check_same_thread=False`, which is required to use pysqlite when you're sharing a connection across threads.

thanks for the clarification!

If i run this:
```
db = SqliteDatabase(':memory:', threadlocals=False, check_same_thread=False)
```

I get:
```
TypeError: 'threadlocals' is an invalid keyword argument for this function
```

Can someone help me how to get it work?

If you're using peewee 3.x, you do not need to pass the threadlocals keyword argument anymore. The error indicates this.

But i have the same problem without passing it.

You can use `thread_safe=False`. Sorry, I should have read the original issue description.

In 3.x, `thread_safe=True` means all threads have their own connection. You can pass `thread_safe=False` to make all your threads share the same connection to the underlying memory database.
Issue: autocommit should be True by default
Author: benjsto
State: closed
Created: 2017-02-07 22:27:04

As documented, and by my reading of the peewee source code, `autocommit` is intended to be True by default. However, that does not line up with what I'm seeing:

Initializing a peewee MySQLDatabase instance:
```
db = MySQLDatabase(settings.get('db_name'),
                   host=settings.get('db_host'),
                   port=settings.get('db_port'),
                   user=settings.get('db_user'),
                   passwd=settings.get('db_pw'),
                   charset='utf8mb4',)
```
Results in connections in the general log that look like:
```
170207 22:01:58  1591 Connect   root@172.17.0.4 on test_db
                 1591 Query     SET NAMES utf8mb4
                 1591 Query     set autocommit=0
                 1591 Query     SELECT `t1`.`id` FROM `table` AS t1 ORDER BY `t1`.`published_at` DESC LIMIT 1 OFFSET 0
                 1591 Query     commit
```
I've found a workaround by subclassing MySQLDatabase and overriding the _connect method and explicitly passing in `autocommit=True` there.

`autocommit` refers to the behavior of Peewee, not what the underlying driver may or may not be doing.
Issue: Model to dict for array of models
Author: theobouwman
State: closed
Created: 2017-01-30 20:39:35

`model_to_dict` can only convert a single model to a dictionary, right?
But what about an array of models, like this: `User.select().where(User.age > 19)` this will return all `users` above 19 years old. But what when I want to return this array as json? Then I need to loop through every object an make it a dictionary with `model_to_dict`.

I find this pritty inconvenient to do when making an API. I know I can solve this by making a helper method, but I thought this should be in the core package.

I can make a PR to add it to the playhouse module.

Plz correct me if i'm wrong...

Make a for loop. Jesus.

Wauw
Issue: "Using" is not thread safe.
Author: fangchih
State: closed
Created: 2017-01-20 02:42:40

Hi There,

I have a requirement that needs to use one Model to deal with multiple Sqlite db files, and this process may execute concurrently, I did try the "Using" which get from [document]( http://docs.peewee-orm.com/en/latest/peewee/database.html#using-multiple-databases) but found the "Using" is not a thread-safe operation that I cannot confirm which db context will be during the runtime.

May I know whether alternative solution existed?

Fangchih.



sample test code as below:
```

class RawData(BaseModel):
    dir_name = CharField()
    file_name = CharField()
    singer_name = CharField(null=True)
    song_name = CharField()

    class Meta:
        indexes = (
            (('dir_name', 'file_name'), True),
        )


def _insert(db, data):
    print '_insert', db
    with Using(db, [RawData]):
        RawData.insert_many_test(data).execute()


db1 = sqlitedatabase('tmp1.db')
db2 = sqlitedatabase('tmp2.db')

t1 = Thread(target=_insert, args=(db1, song_data[:9]))
t2 = Thread(target=_insert, args=(db2, song_data[:7]))

t1.start()
t2.start()
t1.join()
t2.join()

with Using(db1, [RawData]):
       cnt1 = RawData.select().count()
       self.assertEqual(cnt1, 9)

with Using(self.db2, [RawData]):
       cnt2 = RawData.select().count()
       self.assertEqual(cnt2, 7)

```






Would this also happen if i running peewee in a multi-threaded web-server? I'm not familiar with concurrent programming.

@chenbo515  I found this case only exists in below scenarios
1) concurrent access to peewee model class.  AND
2) wanna use one peewee model class to access multiple databases which have same schema.

You'll need to implement your own locking, unfortunately. Otherwise the `Using` blocks would need to add a lock. Something like this might work:

```python
from contextlib import contextmanager

db_lock = threading.Lock()

@contextmanager
def ConcurrentUsing(*args):
    with db_lock:
        with Using(*args):
            yield
```

@coleifer thank you.
Issue: fn.GROUP_CONCAT should support ORDER BY
Author: mithmatt
State: closed
Created: 2017-01-20 01:09:01

From [MySQL Docs](http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat), ```GROUP_CONCAT``` supports ```ORDER BY```

```
mysql> SELECT student_name,
    ->     GROUP_CONCAT(DISTINCT test_score
    ->               ORDER BY test_score DESC SEPARATOR ' ')
    ->     FROM student
    ->     GROUP BY student_name;
```



Try this:

```python
fn.GROUP_CONCAT(Clause(
    SQL('Distinct'),
    MyModel.test_score,
    SQL('ORDER BY'),
    MyModel.test_score,
    SQL('DESC SEPARATOR \' \'')))
```

Not very clean but that should hopefully help you get the SQL you need.
Issue: Postgresql FOR SHARE support
Author: kapucko
State: closed
Created: 2017-01-18 15:12:18

Hi,

I'm looking for support of FOR SHARE locking level in postgres (https://www.postgresql.org/docs/9.4/static/explicit-locking.html). I have found just this: http://docs.peewee-orm.com/en/latest/peewee/api.html#SelectQuery.for_update which generates FOR UPDATE.
Is there any way I can implement this as an extension?

Please help me.
Thank you.



Added in 7f6f578f6f06e865ea5f3823c163d39aa6ff2a96

```python
SomeModel.select().with_lock('SHARE')
```
Issue: how can use sub-table search?
Author: noogel
State: closed
Created: 2017-01-18 11:25:31

I have many of the same tables, just different table names.
like:
book_001
book_002
...
...
book_100

How to write a Model class according to different conditions of the table data.

E.g:
Book.get_model('001').select().execute()  => select * from book_001
Book.get_model('002').select().execute() => select * from book_002

How to implement the get_model() method.

Thanks!




This is not a feature of peewee.
Issue: Subquery problems with peewee Cython
Author: danygielow
State: closed
Created: 2017-01-18 10:12:50

I have encountered an error with subqueries and the usage of `SQL('*')` when peewee is built with Cython extensions. Without Cython, the behavior is the same as before.

TestCase:

```python
    def test_subselect(self):
        with test_database(self.mysql_test_db, [A]):
            A.create(a=1, b=3)
            A.create(a=2, b=4)

            sub = A.select(A.a, A.b)
            query = (A.select(peewee.SQL('*')).from_(sub.alias('sub')))

            for r in query.dicts():
                self.assertIn(r['a'], [1,  2])
                self.assertIn(r['b'], [3, 4])
```

Regardless, if the test is a representative example, it worked until version 2.8.4.
This time I won't be able to supply a PR, because of my lack of C skills.

Are you dead set on using `SQL('*')`? The "peewee" way would be to write:

```python
sub = sub.alias('sub')
query = A.select(sub.c.a, sub.c.b).from_(sub)
```

OK, you might be right. But there is still a discrepency in functionality between the Cython speedups and non-speedup code.

I close this issue, because we will use the columns from now on.
Issue: Slow on iterating over simple query
Author: GMaxera
State: closed
Created: 2017-01-17 22:30:11

I have this code:

```python
tweetInsights = Gnip_Tweet_Insights \
           .select() \
           .where(
               (Gnip_Tweet_Insights.client_account_id==clientAccountId) &
               (Gnip_Tweet_Insights.occurred_at==insight.occurred_at) &
               (Gnip_Tweet_Insights.by_echomany==insight.by_echomany))
print( "After Query ", time.perf_counter() - startTime )
for singleInsight in tweetInsights:
    pass
print( "After Dict ", time.perf_counter() - startTime )
```

The query itself is very simple but contains 5000 rows.
It takes less than 200 milliseconds if I do it with "Sequel Pro".

But iterating over these 5000 rows using the "for singleInsight in tweetInsights:" takes around 1 seconds. And for my user case is too much.
I don't understand why so long time for iterating over a 5000 rows. It's definitely not a large set of data.

I tried using .naive(), .dicts() and .iterator() but none of these speed up the cycle.

Is there something else I can use to speed up iterating ?


                

It's Python...

@coleifer, Please elaborate. 

I'm using peewee in cases where I am required to query 1 million or more rows.
```python
query = table.select()
data = list(query.dicts())
```

This process takes over 30s and when there are where clauses, it may take up to 1~2min.

Are you saying there are no way to increase the performance of peewee or do you believe other libraries say sqlalchemy will also take the same amount of time?

That's a shitcomment I made, must have been in a hurry or something, so apologies.

There's a short section in the docs about iterating over lots of rows: http://docs.peewee-orm.com/en/latest/peewee/querying.html#iterating-over-lots-of-rows

The key points are:

* If you are doing a single-shot iteration and do not need to cache rows in memory, tack on a `.iterator()` to your query. This helps keep memory usage down for large result sets.
* The fastest way to get lots of rows is to avoid creating expensive Python objects. Requesting that rows be returned as dicts, tuples or namedtuples is going to be fastest. If you have joins and are selecting from multiple models, you can also try using ".objects()", which skips traversing the model graph. You can find examples here: http://docs.peewee-orm.com/en/latest/peewee/relationships.html#selecting-from-multiple-sources

You can compare the runtime of the query by itself versus the overhead of Peewee by using "database.execute_sql(...)", passing it a SQL string and a list of params. Alternatively, if you have a query object, you can use "database.execute(query)". These methods return a DB-API cursor which you can iterate over. This should give you a good baseline for how the query performs in Python.

@coleifer thank you that you finally admitted that was shit comment. I was quite frustrated that you closed and dismissed my question as like I was stupid. Unfortunately, it's too late for me to use peewee again ... and at that time we decided to use PHP and now I'm working for another company :-)
Issue: Add full support for Postgres window functions
Author: alin23
State: closed
Created: 2017-01-17 17:38:57

* Add support for frame_clause

```
    frame_clause can be one of

    { RANGE | ROWS } frame_start
    { RANGE | ROWS } BETWEEN frame_start AND frame_end

    where frame_start and frame_end can be one of

    UNBOUNDED PRECEDING
    value PRECEDING
    CURRENT ROW
    value FOLLOWING
    UNBOUNDED FOLLOWING
```
* Fix `WINDOW` clause positioning. Should be after `HAVING`
* Allow non-list values for `partition_by` and `order_by`
Issue: Database default
Author: theobouwman
State: closed
Created: 2017-01-17 13:56:05

Is it possible to add a default which is done by the database server.
Like `DEFAULT CURRENT_TIMESTAMP` for a MySQL datetime field?

I would like to make a PR if it's not available.

Yes, you can specify arbitrary stuff in the `constraints` field parameter:

```python
timestamp = DateTimeField(constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')])
```

http://docs.peewee-orm.com/en/latest/peewee/models.html?#default-field-values