akumoth/peewee-issues huggingface dataset:
Issue: Iterating over big dataset
Author: demongoo
State: closed
Created: 2014-09-16 14:16:31

I try to iterate result set:

```
for field1, field2 in query.tuples().iterator():
    print .... 
```

But it failes with 'out of memory' (script just gets killed by kernel), so, I see no difference in my case - either with iterator or without.

I have there python 2.6 and peewee 2.3.2.

I'm not familiar much with lazy loading in python, except generators, but how this supposed to work: https://github.com/coleifer/peewee/blob/master/peewee.py#L2439  ? In `execute` `self._qr` gets constructed anyway - so how can this save memory to allow coping with big dataset? Is it me missing something, or maybe obsolete python version or ...?


Normally peewee will cache result rows so that if you iterate, slice or index the result set multiple times you will not incur multiple queries. When you use `iterator()`, rows will not be cached.

It's still possible to run out of memory, depending on your database, because the rows are not streamed to the cursor but loaded all at once. What database are you using?


I use mysql.
But why are rows loaded at once?


> But why are rows loaded at once?

You'll have to look into the implementation of the python driver. My understanding is that the database loads the rows into memory and passes them to the cursor, rather than streaming them to the cursor.

Peewee's `iterator()` behavior is covered by unit tests and I believe it to be working, so I am going to close this.


> You'll have to look into the implementation of the python driver 

By looking up the pymysql driver, is it possible to use the `fetchall_unbuffered()` when iterating large results?


https://pymysql.readthedocs.io/en/latest/modules/cursors.html#pymysql.cursors.SSCursor.fetchall_unbuffered
Issue: UUID field is not instantiated multiple times for insert_many fields
Author: alexlatchford
State: closed
Created: 2014-09-16 00:19:06

Hi Charles,

``` python
class MyModel(Model):
    uuid = UUIDField(primary_key=True, default=uuid.uuid4)
    name = CharField()

my_models = [{"name": "foo"}, {"name": "bar"}]
MyModel.insert_many(my_models).execute()
```

You'll get the same uuid in `InsertQuery._defaults` applied to both rows and it'll result in an error similar to the one found below. 

```
Traceback (most recent call last):
  File "/Library/Python/2.7/site-packages/cherrypy/_cprequest.py", line 656, in respond
    response.body = self.handler()
  File "/Library/Python/2.7/site-packages/cherrypy/lib/encoding.py", line 188, in __call__
    self.body = self.oldhandler(*args, **kwargs)
  File "/Library/Python/2.7/site-packages/cherrypy/_cpdispatch.py", line 34, in __call__
    return self.callable(*self.args, **self.kwargs)
  File "/Users/alexlatchford/Code/abbreviate/a8-product/a8_product/../a8_product/lib/auth_tools.py", line 11, in __ca
    return f(*args, **kwargs)
  File "/Users/alexlatchford/Code/abbreviate/a8-product/a8_product/../a8_product/routes/auth/signup.py", line 80, in POST
    NewsFilter.insert_many(filters).execute()
  File "/Users/alexlatchford/Code/peewee/peewee.py", line 2555, in execute
    return self.database.last_insert_id(self._execute(), self.model_class)
  File "/Users/alexlatchford/Code/peewee/peewee.py", line 2120, in _execute
    return self.database.execute_sql(sql, params, self.require_commit)
  File "/Users/alexlatchford/Code/peewee/playhouse/postgres_ext.py", line 250, in execute_sql
    logger.exception('%s %s', sql, params)
  File "/Users/alexlatchford/Code/peewee/peewee.py", line 2591, in __exit__
    reraise(new_type, new_type(*exc_value.args), traceback)
  File "/Users/alexlatchford/Code/peewee/playhouse/postgres_ext.py", line 250, in execute_sql
    logger.exception('%s %s', sql, params)
IntegrityError: duplicate key value violates unique constraint "news_filter_pkey"
DETAIL:  Key (uuid)=(1116e6d9-dfaa-4709-be2d-95ef5cfc4983) already exists.
```

I've fixed it for myself at the moment by simply generating the UUID's outside of peewee :)

Many thanks,
Alex


Well, I would think that in those examples you would get an error because the first row's UUID would be `None`. But if you specified `default=uuid.uuid4` or something, I think your error would be replicated.


Ah yes, sorry I knew I'd forgotten something in the example! I've updated my original post


Should be fixed in 5c59f44915f7418b031e27ec8bc4806e562dd5d4 thanks for reporting!
Issue: Docs build error for peewee-2.3.2
Author: pitchforks
State: closed
Created: 2014-09-15 21:24:28

Getting this while running Sphinx v1.2.1 to build the docs

```
peewee-2.3.2/docs/peewee/playhouse.rst:2346: ERROR: Malformed table.
Text in column margin in table line 6.

================   =============  =======================================
Option             Default        Description
================   =============  =======================================
``-l,--logging``   False          Log all queries to stdout.
``-e,--engine``    sqlite         Database driver to use.
``-d,--database``  ``:memory:``   Database to connect to.
================   =============  =======================================
```
Issue: caniusepython3 thinks peewee does not support python3
Author: Amleto
State: closed
Created: 2014-09-14 22:42:52

Is this down to the tags used on pypi?


I'm not positive, I tried to find the reason or code in caniusepython3.com's codebase but didn't seem to see it.


Appears to be fixed with the latest release. Thanks for mentioning this.
Issue: ExtQueryWrapper raises exception when selecting fn.NOW()
Author: demongoo
State: closed
Created: 2014-09-12 14:40:34

When using something like this: `Record.select(Record.datetime, fn.NOW()).tuples()`, on iteration exception gets thrown:

```
File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 1686, in next
    obj = self.iterate()
File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 1672, in iterate
    self.initialize(self.cursor.description)
File "/usr/local/lib/python2.7/dist-packages/peewee.py", line 1724, in initialize
    isinstance(select_column.arguments[0], Field)):
IndexError: tuple index out of range
```

The place in current master is: https://github.com/coleifer/peewee/blob/master/peewee.py#L1729

Should be checked that `len(select_column.arguments) > 0`


Nice catch, you're right.
Issue: Sub-queries using union
Author: awahlig
State: closed
Created: 2014-08-23 03:22:45

First of all, thanks for your continued great work! I've been using peewee successfully for quite some time now.

Today I hit a small issue though. If you build a union query and try to use it in WHERE of another query using << (IN), the union in the resulting SQL will not have parentheses around it which causes a syntax error.

``` python
from peewee import *

db = SqliteDatabase('test.db')

class A(Model):
    foo = IntegerField()
    class Meta:
        database = db

class B(Model):
    bar = IntegerField()
    class Meta:
        database = db

class C(Model):
    baz = IntegerField()
    class Meta:
        database = db

print A.select().where(A.foo << (B.select(B.bar) | C.select(C.baz)))
```

```
<class '__main__.A'> SELECT t1."id", t1."foo" FROM "a" AS t1 WHERE (t1."foo" IN SELECT t2."bar" FROM "b" AS t2 UNION SELECT t3."baz" FROM "c" AS t3) []
```

Using version 2.2.5.


Ahh, good catch. I had to be explicit about removing parentheses from union queries because one of the dbs (I think SQLite?) didn't like them, but I did not think about subqueries. I'll look into a fix, thanks for the great bug report.


Fixed in b13ff6c131d0722f6a3cfb54c8280bc99ea0
Issue: Python 3 problem with division
Author: NJDFan
State: closed
Created: 2014-08-23 00:42:18

All the other mathematical operators work fine in queries, but division doesn't.  This is most likely because Python3 killed `__div__` in favor of `__truediv__` and `__floordiv__`.

After the line that reads::

```
__div__ = _e(OP_DIV)
```

I put::

```
__truediv__ = _e(OP_DIV)
```

And that seemed to fix it, but I'm not set up to run full regression tests on things.


Thanks, good catch.
Issue: Table name not quoted as of 2.3.0
Author: jhorman
State: closed
Created: 2014-08-19 18:32:33

I accidentally upgraded to 2.3.0 on a staging environment, and /admin started throwing errors. I have a table called `user` (I regret this, it is a keyword), and in 2.2.5 everything is ok. In 2.3.0, `user` is no longer quoted properly when attempting to update a record in /admin, possibly everywhere. I end up with

```
WHERE (user."id" = 79)
```

Actually, the difference might be that in 2.2.5, `user` isn't even in the WHERE.


See #414 -- I'll push a new release soon.


Oh, sorry, didn't see it was already filed.
Issue: “exc_value.args” at line 2591 may caused the error " AttributeError: 'str' object has no attribute 'args' " sometime
Author: JoneXiong
State: closed
Created: 2014-08-16 04:12:51

eg:
if  you haven't seted "check_same_thread=False" may lead to:
  exc_value = “SQLite objects created in a thread can only be used in that same thread.The object was created in thread id.....”
and then: 
    reraise(new_type, new_type(*exc_value.args), traceback)
AttributeError: 'str' object has no attribute 'args'


On python 2, I get:

```
ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140311384954624 and this is thread id 140311230535424
```

Which I believe is the correct traceback. What python version are you using?


Actually, on python 3, I get:

```
peewee.ProgrammingError: SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140570851063552 and this is thread id 140570760951552
```

So I believe this is working as I expect.


Thanks for you reply! I  used Python 2.6.6 on CentOs 6.5 x86_64 and got the error.


Cool, I don't currently have access to a Python 2.6 interpreter -- I'll see what I can do about compiling one. I wonder if the difference in exceptions is documented anywhere..I looked at the Python 2.7 changelog but nothing jumped out at me.


I'm confused why `exc_value` is a string, according to the python 2.6 docs, `exc_value` should be an instance of the `exc_type` being raised:

https://docs.python.org/2.6/library/sys.html#sys.exc_info

> value gets the exception parameter (its associated value or the second argument to raise, which is always a class instance if the exception type is a class object)


I am unable to replicate the error. Here is a script I am using:

``` python
import sqlite3
import sys
import threading

conn = sqlite3.connect('/tmp/my.db')

def foo():
    try:
        curs = conn.cursor()
        curs.execute('select 1')
    except Exception as exc:
        print exc
        et, ev, tb = sys.exc_info()
        print et
        print ev
        print type(ev)
        print ev.args

t = threading.Thread(target=foo)
t.start()
t.join()
```

Output:

```
python2.6 x.py
SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140622844954368 and this is thread id 140622810523392
<class 'sqlite3.ProgrammingError'>
SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140622844954368 and this is thread id 140622810523392
<class 'sqlite3.ProgrammingError'>
('SQLite objects created in a thread can only be used in that same thread.The object was created in thread id 140622844954368 and this is thread id 140622810523392',)
```

What do you think? What output do you get with that script?


I'm going to close this out, please reopen if you would like to add anything.


It seems like MySQLdb python module affects on `exc_value` behavior. Workaround: use PyMySQL module instead.
Issue: Database.drop_tables hide warning when a table doesn't exist
Author: rvindas-gap
State: closed
Created: 2014-08-14 16:51:21

Just ugraded to version 2.3.0 and I was trying the `drop_tables` function. If a table doesn't exist then I get a warning like the following (even specifying the `safe=True` parameter)

```
/mypathto/env/site-packages/peewee.py:2702: Warning: Unknown table 'variables' cursor.execute(sql, params or ())
/mypathto/env/site-packages/peewee.py:2702: Warning: Unknown table 'companies' cursor.execute(sql, params or ())
/mypathto/env/site-packages/peewee.py:2702: Warning: Unknown table 'users' cursor.execute(sql, params or ())
```

Is there a way to turn off the warnings when trying to drop a table that doesn't exist and when specifying the `safe=True` parameter?


When I test this locally it appears to be working correctly:

``` python
In [4]: db.drop_tables([User])

.... traceback ....

/home/charles/pypath/peewee.pyc in execute_sql(self, sql, params, require_commit)
   2711             cursor = self.get_cursor()
   2712             try:
-> 2713                 cursor.execute(sql, params or ())
   2714             except Exception as exc:
   2715                 if self.get_autocommit() and self.autorollback:

OperationalError: no such table: user

In [5]: db.drop_tables([User], safe=True)

In [6]: db.drop_tables([User], safe=True)
```

I believe you can just ignore the warnings.
Issue: Regexp only works 1st time
Author: coleifer
State: closed
Created: 2014-08-14 13:48:38

I'm running into an issue with the peewee regexp function. Queries like this work the first time I run it:

```
query = Table.select().where(Table.column.regexp('string'))
```

However, if I try to run it with a different string, I get this error: 'unicode' object has no attribute 'regexp':

```
query = Table.select().where(Table.column.regexp('string2'))
```

I usually run it twice because I need to see how specific to make the regexp string.

I've tried disconnecting/reconnecting to the db, but that doesn't help. The only thing that works is if I close ipython and restart it. Is this a memory/buffer issue? Is there a good way to get this to work? Thanks in advance.
Issue: No quotes are put around "user" table name
Author: extesy
State: closed
Created: 2014-08-14 04:12:04

Upgrading peewee from 2.2.5 to 2.3.0 broke something since now I get syntax error exception for this postgres query:

```
UPDATE "user" SET "last_active_at" = %s WHERE (user."id" = %s)
```

The problem is that "user" table name is a reserved keyword so it must be put in quotes.


What SQL was generated previously? Was an alias used? Sorry I don't recall.


I don't know, probably just quotes around the table name, just like right after UPDATE word.


This should be fixed by bb48ca37fcdd2728c469c50aee8cb824a8aff4f1
Issue: Add in support for passing in JSON dumps functions
Author: alexlatchford
State: closed
Created: 2014-08-10 23:45:38

Hi Charles,

I've added in support for passing through a `json.dumps` function to psycopg2. I need it to serialise datetime formats going into the db, but obviously this is nice and generic.

Let me know what you think.

Cheers,
Alex


Nice, thanks for this!
Issue: Getting size of a ResultSet without using count()
Author: arski
State: closed
Created: 2014-08-07 07:11:39

Basically the reason is that I don't want to run another query when I know that I will be iterating over the ResultSet anyway, so I'd be happy to load it into memory and use `len()` or something like that.

I'm used to Django doing this automatically when you call `len()` on a query/resultset, but here even if I do `.execute()` first, `len()` still doesn't work..

What would be the best approach to do this?


You were very close -- the best way to get the result size if you plan to iterate anyways, is to call `list()` on the query and then take the length of that.

``` python
query = User.select()
num_results = len(list(query))
for item in query:
    print item
```

This will only result in a single select query being issued.


Aha, thank you!
Issue: include sql command when sql/programmer error raised
Author: Amleto
State: closed
Created: 2014-08-04 20:04:44

Hi,
I have tens or hundreds of thousands of rows to commit (MySQL) and I had db connection issues when trying to commit them all in one go.  The data is provided by a generator and I take slices (itertools.islice) that are passed in to an insert_many(...).execute() call.

When calling insert_many(...).execute() for the second time an sql/programmer exception is raised.  I initially thought this was just because the generator was exhausted, but after writing unit tests (well, I wrote units for in memory sqlite, but the real code is connecting to mysql) I see that peewee handles this ok.

It's strange to me that insert_many(...).execute() works the first time but not the second.  If I could see the sql that fails it could be useful.

Any help/advice appreciated.


You can try logging: http://docs.peewee-orm.com/en/latest/peewee/cookbook.html#logging-queries

Or you can implement `Database.sql_error_handler` http://docs.peewee-orm.com/en/latest/peewee/api.html?highlight=sql_error_handler#Database.sql_error_handler

Please comment if you find that this is an error in peewee.
Issue: Ability to change query_context when joining
Author: alexlatchford
State: closed
Created: 2014-07-31 23:38:04

Hi Charles,

Just been writing a query that I think should be functionally possible in SQL but isn't currently supported in peewee.

The query is looking to select any articles that are in an enabled news feed, with at least one entry with a specific state in the article_state table.

``` python
class NewsFeed(Model):
    uuid = UUIDField(primary_key=True)
    enabled = BooleanField()

class Article(Model):
    uuid = UUIDField(primary_key=True)
    title = CharField()
    news_feed = ForeignKeyField(NewsFeed)

class ArticleState(Model):
    state = CharField(choices=('a', 'b', 'c'))
    article = ForeignKeyField(Article)

# Create and populate..

recent_articles = Article.select(
        Article.title
    ).join(NewsFeed).join(ArticleState).where(
        (NewsFeed.enabled == True) & (ArticleState.state == 'a')
    ).group_by(
        Article.title
    ).having(fn.Count(ArticleState.article) > 0)
```

That query throws the following error:

```
Traceback (most recent call last):
  File "/System/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/threading.py", line 808, in __bootstrap_inner
    self.run()
  File "/Library/Python/2.7/site-packages/cherrypy/process/plugins.py", line 485, in run
    self.function(*self.args, **self.kwargs)
  File "/Users/alexlatchford/Code/abbreviate/a8-aggregate/a8_aggregate/../a8_aggregate/tasks/sync_feeds.py", line 40, in sync_feeds
    load_cache(cached_articles)
  File "/Users/alexlatchford/Code/abbreviate/a8-aggregate/a8_aggregate/../a8_aggregate/tasks/sync_feeds.py", line 27, in load_cache
    ).join(NewsFeed).join(ArticleState).where(
  File "/Library/Python/2.7/site-packages/peewee.py", line 241, in inner
    func(clone, *args, **kwargs)
  File "/Library/Python/2.7/site-packages/peewee.py", line 1752, in join
    self._query_ctx, model_class))
ValueError: No foreign key between <class 'a8_aggregate.database.NewsFeed'> and <class 'a8_aggregate.database.ArticleState'>
```

I understand the problem is the ordering of the `join()` calls and I'm wondering if there is a different way to write this query to achieve the results I'm after. For the meantime I'm going to write it as two queries but it's obviously not ideal :(

Cheers,
Alex


I'm presuming the raw SQL produced should be something like this, which shows it's possible:

``` sql
SELECT
    a.title
FROM
    article a,
    INNER JOIN news_feed nf ON a.news_feed_id = a.uuid
    INNER JOIN article_state ars ON ars.article_id = a.uuid
WHERE
    nf.enabled = 1
    AND ars.state = 'a'
GROUP BY
    a.title
HAVING
    COUNT(ars.article_id) > 0
```


You can use [.switch()](http://docs.peewee-orm.com/en/latest/peewee/api.html#Query.switch) to switch the query context. I believe that's what you're looking for.


Awesome, was looking for change instead of switch. Hate it when that happens! And with the docs literally just below where I was looking!
Issue: Auto-incrementing BigIntegerField
Author: tfeldmann
State: closed
Created: 2014-07-25 11:40:46

Hello,
I'm working on an application with a mysql database. Several hundred loggers will each send a measurement value every second for three years - so I have to use a BigIntegerField.
`id = BigIntegerField(primary_key=True)` works, but doesn't auto-increment. Is there any way to do this in peewee?
Thanks!


As a workaround I overrode the `create` method. Please let me know if there is a better way.

``` python
class Dataset(BaseModel):
    id = BigIntegerField(primary_key=True)

    @classmethod
    def create(cls, *args, **kwargs):
        next_id = cls.select(fn.Max(cls.id)).scalar() + 1
        return super().create(id=next_id, *args, **kwargs)
```


Yes but it's not built-in at the moment. You can create a new field type and register it with the db, or create a subclass of the bigint field and override the `__ddl__()` method. [Here are docs on creating a custom field](http://docs.peewee-orm.com/en/latest/peewee/models.html#creating-a-custom-field).

Going the former route, you might:

``` python
class AutoBigIntegerField(BigIntegerField):
    db_field = 'auto_bigint'

db = MySQLDatabase('mydb', ...)
db.register_fields('auto_bigint', 'BIGINT AUTO_INCREMENT')
```

I _believe_ that should do what you want.


Thank you, cool stuff!
`fields` must be a dict and `register_fields()` did not work (KeyError on `create_table()`, so I did this:

``` python
database = MySQLDatabase(
    user=...
    ...
    fields={'auto_bigint': 'BIGINT AUTO_INCREMENT'},
)
```


Oh right, yep just woke up... Cool well if that's working then I'm going to close. Please let me know if you are still encountering issues.
Issue: Not unique table/alias in a single INNER JOIN query
Author: lalstef
State: closed
Created: 2014-07-25 10:06:38

Hi,

I have a model DataRow which has a foreign key to itself (you can see it below). This query

```
DataRow.filter(parent=1864)
```

caused the following error:

```
InternalError: (1066, u"Not unique table/alias: 't1'")
```

Here is the SQL that was executed:

```
Error executing query 
SELECT t1.`id`, t1.`data`, t1.`parent_id`, t1.`board_id`, t1.`order`, t1.`colour` 
FROM `datarow` AS t1 INNER JOIN `datarow` AS t1 ON (t1.`parent_id` = t1.`id`) WHERE 
(t1.`parent_id` = %s) ([1864])
```

Here is the data model:

```
import peewee
from flask_peewee.db import Database

app = Flask(__name__, static_url_path='/assets')
db = Database(app)

class DataRow(db.Model):
    DEFAULT_ROW_COLOUR = '#ffffff'

    data = peewee.TextField()
    parent = peewee.ForeignKeyField('self', null=True, blank=True)
    board = peewee.ForeignKeyField(Board)
    order = peewee.FloatField(null=True)
    colour = peewee.CharField(default=DEFAULT_ROW_COLOUR)
```

Even though I may have made  some mistake in data modeling or in my peewee usage, I think I shouldn't be getting this error, right?

Thanks


Just an FYI that the `filter()` API is actually deprecated.

That aside, it looks like it's interpreting the foreign key as a column requiring a join. This query should accomplish what you want:

``` python
DataRow.select().where(DataRow.parent == 1234)
```

Also, if you find yourself wanting to perform a join on the parent row, you need to use [Model.alias()](http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.alias).