akumoth/peewee-issues huggingface dataset:
Issue: Snippet function on FTS tables.
Author: leiserfg
State: closed
Created: 2015-04-01 22:28:14

When i write 

``` python
 FTSBook.select(fn.snippet(FTSBook)).where(FTSBook.match('python'))
```

 it returns:

``` sql
SELECT snippet("ftsbook" AS t1) FROM "ftsbook" AS t1 WHERE ("ftsbook" MATCH 'python')
```

But if i run this query it fails:

```
near "AS": syntax error
```

is it  a bug in peewee or there is a way to call a function that recive a table as argument?


The problem is that peewee assumes when it encounters a Model as part of a query, that it should alias it to the alias used for that table. In this case, however, we don't want that -- we simply want the table name to be passed in.

To remedy, you can rewrite this as:

``` python
from peewee import Entity
(FTSBook
 .select(fn.snippet(Entity(FTSBook._meta.db_table)))
 .where(FTSBook.match('python')))
```

If you are using peewee 2.5.0 or older, you can also use:

``` python
(FTSBook
 .select(fn.snippet(FTSBook._as_entity()))
 .where(FTSBook.match('python')))
```

Finally, if you are using master, I changed `_as_entity()` to a public method, so it is now `as_entity()`:

``` python
(FTSBook
 .select(fn.snippet(FTSBook.as_entity()))
 .where(FTSBook.match('python')))
```


Hi,
It seems the as_entity() method does not exist any more.
How should I use fn.snippet now?

You can use `._meta.entity` property instead:

```python
FTSBook.select(fn.snippet(FTSBook._meta.entity))
```
Issue: Unable to create json or jsonb columns in Postgres 9.4
Author: wishabhilash
State: closed
Created: 2015-04-01 13:59:49

Am trying to create a table with a jsonb column but its keeps on giving me error.

```
from peewee import *
from playhouse.postgres_ext import BinaryJSONField

db = PostgresqlDatabase(database="test")

class JSONTest3(Model):
    username = BinaryJSONField()

    class Meta:
        database = db

if __name__ == '__main__':
    db.connect()
    db.create_tables([JSONTest3])
```

My lib versions are:
    Postgres  == 9.4
    psycopg2 == 2.6

Error is:
    Traceback (most recent call last):
      File "peeweepostgres.py", line 16, in <module>
        db.create_tables([JSONTest3])
      File "/home/wish/virtualenv/local/lib/python2.7/site-packages/peewee.py", line 3042, in create_tables
        create_model_tables(models, fail_silently=safe)
      File "/home/wish/virtualenv/local/lib/python2.7/site-packages/peewee.py", line 4163, in create_model_tables
        m.create_table(*_create_table_kwargs)
      File "/home/wish/virtualenv/local/lib/python2.7/site-packages/peewee.py", line 3911, in create_table
        db.create_table(cls)
      File "/home/wish/virtualenv/local/lib/python2.7/site-packages/peewee.py", line 3039, in create_table
        return self.execute_sql(_qc.create_table(model_class, safe))
      File "/home/wish/virtualenv/local/lib/python2.7/site-packages/peewee.py", line 1718, in inner
        return self.parse_node(fn(_args, *_kwargs))
      File "/home/wish/virtualenv/local/lib/python2.7/site-packages/peewee.py", line 1746, in _create_table
        columns.append(self.field_definition(field))
      File "/home/wish/virtualenv/local/lib/python2.7/site-packages/peewee.py", line 1696, in field_definition
        column_type = self.get_column_type(field.get_db_field())
      File "/home/wish/virtualenv/local/lib/python2.7/site-packages/peewee.py", line 1325, in get_column_type
        return self._field_map[f]
     KeyError: 'json'

What can be the error am doing here???


You need to use playhouse.postgres_ext.PostgresqlExtDatabase.


Ohh thanks... :+1:
Issue: UNION query
Author: ybahador
State: closed
Created: 2015-03-31 22:41:26

I'm making a compound query by using the | operator but the generated query does not have correct parenthesis and results in a SQL error.

I'm using peewee version 2.5.0

I've also found a similar issue (#454) and based on the discussion it seems that the issue has been fixed in previous versions, It's strange that I'm still experiencing this problem.

Could you please look into it?

Thanks.


Can you share your code and the SQL? Its hard to help without any clues you know...


Also what database are you using? If you can't share your exact query can you give me an example that fails in a similar way?


Please comment and I will re-open.


I'm using postgres as my database.

Here is my code:

``` python
q1 = ASRank.select().where(ASRank.snapshot == '2014-02-04').order_by(ASRank.byte_rank).limit(10)
q2 = ASRank.select().where(ASRank.snapshot == '2014-02-04').order_by(ASRank.flow_rank).limit(10)
q = q1 | q2
```

which results in:

``` sql
SELECT "t2"."id", "t2"."snapshot_id", "t2"."asn", "t2"."byte_rank", "t2"."flow_rank", "t2"."bytes", "t2"."flows" 
FROM "asrank" AS t2 
WHERE ("t2"."snapshot_id" = 2014-02-04) ORDER BY "t2"."byte_rank" LIMIT 10 
UNION 
SELECT "t2"."id", "t2"."snapshot_id", "t2"."asn", "t2"."byte_rank", "t2"."flow_rank", "t2"."bytes", "t2"."flows" 
FROM "asrank" AS t2 
WHERE ("t2"."snapshot_id" = 2014-02-04) ORDER BY "t2"."flow_rank" LIMIT 10 ORDER BY "t1"."byte_rank"
```


Ouch, yes I've verified the bug on both Postgresql and SQLite.


I believe these problems should now be fixed in `master`. SQLite does not support limit/order by in the components of a compound query, but since you're using Postgres this ended up being a problem of fixing the parentheses. Your query should now be working if you pull the latest code. I'll make a new release in the next week or two.


Oh, and thank you very much for reporting this!!


Thanks for fixing the issue quickly.
Issue: Update pk seq
Author: elgow
State: closed
Created: 2015-03-27 22:54:06

New method to force the DB to ensure that the next auto-generated key will be greater than any existing key, including explicitly inserted keys. On sqlite and MySQL this happens automatically, so the method is a no-op. For Postgresql it provides a canned version of a nice query to update the sequence, saving peewee users from having to write it themselves. 

This might possibly be simplified and better integrated by having a method to turn auto_increment on/off rather than just setting the attribute on the model. Then this method could be run whenever auto_increment is explicitly activated. 


Thank you for the effort you put into this, but I don't have any plans to merge this functionality, as it seems to me something that would be a better fit for your own library code.


The use case for this is actually very common. Many, if not most, DBs have some reference tables that are populated with an initial set of values and later added to, and those tables are usually the target of foreign key relations. The peewee docs even contain a section on how to do it, "Manually specifying primary keys".  If a peewee user follows the docs, then after re-activating auto_increment in a Postgresql database, as instructed by the docs, they are going to encounter a duplicate key constraint violation, unless they do something like what I've done. If the  sequence update is not done by peewee then the user must code it as a special case for Postgresql if they wish to target different DBs. 

For a feature as frequently used as auto-increment, and with such a troublesome incompatibility between Postgresql and the other back-end DBs, isn't it worth one method on the model to keep peewee client code DB neutral? 


> The use case for this is actually very common

I'd beg to differ, as in the 4+ years peewee's been around, you're the first to ask for a sequence updating helper.

My stance is that relying on particular values for auto-incremented, sequence-backed columns is an anti-pattern. I understand that maybe you're restoring data, but in the very rare cases I've done that all I had to do was restore the data then set the currval of the sequence -- it was a one-time operation, just another part of restoring from backup.

The docs you've referenced even emphasize that this is a one-time type operation:

> To handle this on a **one-off** basis, you can simply tell peewee to turn off auto_increment during the import


I agree that it's a one-off operation, though possibly one time for each new deployment. But when you are finished with it the DB might be broken (if it's postgresql), or it might not. The docs don't mention that your DB might be broken, and there's no database neutral code that you can write to make sure that it's fixed. That's all I'm trying to create, the simplest possible uniform way to fix any DB after inserting explicit keys into an auto-increment table.
Issue: Does .dicts() or .tuples() force an eval?
Author: syegulalp
State: closed
Created: 2015-03-26 04:30:02

I have the following code:

```
media_association = MediaAssociation.select(MediaAssociation.id).where(
            MediaAssociation.blog == self.id).dicts()

media = Media.select().where(Media.id << media_association)
```

Would this cause an `n+1` query, where `media_association` generates one query and then `media` another? My gut tells me that would be the case, but I wanted to be sure.


Well, first of all, two queries is not N+1 queries. N+1 queries happens when you have a loop, for instance, and for every result in the outer query you execute an additional query.

So no, it's not N+1.

In fact, it is only 1 query, which you can verify by [enabling query logging](http://docs.peewee-orm.com/en/latest/peewee/database.html#logging-queries).

Calling `dicts()` or `tuples()` will not evaluate the query. Select queries are evaluated when you call `execute()` on them, iterate over them, call `len()` on them, or index into them.


Excellent, just what I needed to know. Thanks.
Issue: insert_many() + insert_returning support
Author: ianawilson
State: closed
Created: 2015-03-25 06:16:49

I saw that there was support for RETURNING but only for single-row INSERTs, and I needed support for multi-row INSERTs with RETURNING as well.

Based on your tests, it looks like you may not have wanted to include this functionality. If that's the case, I'd be happy to make this a non-default option for `insert_many()` so that the default behavior is unchanged, but getting the primary keys of all new rows is available as an option for those who want it.

Otherwise, if this does seem like a good default behavior, then I've updated the tests and this should be good to merge.


I didn't think it made a lot of sense to return the IDs for bulk inserts. If inserting a lot of rows, it could mean a lot of data being transferred back over the wire for all those inserts. Furthermore, insert_many() doesn't return instances, so what would be the point of having the IDs?

Also, the behavior prior to 2.5 was to only return the latest insert ID of a batch. 


In my particular use case, I'm using peewee in a service that ingests large amounts of data and writes it to the db (postgres). We have another service that is responsible for updating the search index (elasticsearch), and it drastically simplifies things if I can pass the list of new IDs to the search index service after the bulk insert.

I'm not convinced this behavior makes sense as a default, but I do think it's nice to have as an option, especially because the behavior in 2.5 is nearly there. Like I mentioned, I'd be happy to rewrite it to be a non-default option for `insert_many()` / `InsertQuery` -- shouldn't be hard.

EDIT: The tests ran fine in my environment, so I'll have to look at why they're failing on Travis a little later.


> I do think it's nice to have as an option

Sounds good to me.


You can now call:

``` python
data = [{'username': username} for username in list_of_usernames]
user_ids = User.insert_many(data).return_id_list().execute()
print user_ids
```


http://docs.peewee-orm.com/en/latest/peewee/api.html#InsertQuery.return_id_list
Issue: ArrayField: to lower 
Author: havannavar
State: closed
Created: 2015-03-24 20:04:16

I am trying to convert ArrayField values to lower case, but unsuccessful

here is my query with exception

```
  Tweets.select().where((fn.Lower(Tweets.preferred_city) == fn.Lower(city)) & (fn.array_lower(Tweets.tagging).contains_any(['foo','bar'])))

      ProgrammingError: function contains_any(unknown) does not exist
    LINE 1: ..."."PREFERRED_CITY") = Lower('NewYork')) AND contains_a...
```

I am using Postgresql 9.4 and in the document i can see this
http://www.postgresql.org/docs/9.4/static/functions-array.html


I don't think `array_lower` does what you are expecting there. It returns the lower bound of the requested array dimension.


So, can you let me know, how can i resolve this issue ?


I am not sure. Can you express this query using plain SQL? If you can give me the correct SQL, then I will help you translate it to utilize peewee.
Issue: Add pwiz option to preserve original DB column order.
Author: elgow
State: closed
Created: 2015-03-22 20:06:37

Add pwiz option to preserve original DB column order in generated model definitions.  Very useful when a DB created by Peewee will be loaded from a dump file created with native DB tools (e.g. Postgresql INSERT type dump).


Hello Charles,

This is a minor modification to enable pwiz to create models that preserve the column ordering from the original source DB. I needed that in order to use dump files created with native Postgresql tools from the original DB. The new behavior an option so that default pwiz behavior is unchanged.  I hope that you will find it to be a useful addition. 

   Ed


Nice work. Could you add a unit test?


Hi Charles,

All fixes in. Please let me know if you think the way I've divided up the EXPECTED text is "too clever by half" and if you'd prefer just plain text for the new test. 

  Ed


> Please let me know if you think the way I've divided up the EXPECTED text is "too clever by half" and if you'd prefer just plain text for the new test.

errr yeah


I switched to full explicit text for each expected value per your preference. Also prevented test failure of unsupported feature under Python 2.6


Thank you so much! I've mergd a very slightly modified version of your changes. I also caught an unreported bug while messing with the tests, so even better!
Issue: Get "raw" ForeignKeyField value without firing a query
Author: rudyryk
State: closed
Created: 2015-03-20 19:06:40

I haven't found a way to simply get related object id for ForeignKeyField without performing a query.

Is that currently possible? That would be convenient in cases when we need to know only related object id, i.e. when trying to read from cache etc.

Here's what I mean:

``` python
import peewee

db = peewee.SqliteDatabase('test.db')

class User(peewee.Model):
    username = peewee.CharField(max_length=100)
    class Meta:
        database = db
User.create_table(true)

class Post(peewee.Model):
    user = peewee.ForeignKeyField(User)
    class Meta:
        database = db
Post.create_table(true)

user = User.create(username="John")
Post.create(user=user)

post = Post.get(Post.id == 1)

# This will fire an extra query to get whole User object!
print(post.user.id)
```


`post._data['user']`


Thank you Charles! :)
Issue: playhouse.db_url shouldn’t remove the leading slash
Author: bfontaine
State: closed
Created: 2015-03-20 15:29:20

[`playhouse.db_url`removes the first character of the path](https://github.com/coleifer/peewee/blob/master/playhouse/db_url.py#L45) , which means that when you think you’re connecting to `/foo/bar/mydb.db` it’ll in fact try to connect to `foo/bar/mydb.db` and fail.

This means I’ve to use `sqlite3:////foo/bar/mydb.db` to use an absolute path. Why does it behave like this?


This follows the pattern used by SQLAlchemy:

http://docs.sqlalchemy.org/en/rel_0_9/core/engines.html#sqlite

This translates to `sqlite://<no host, so empty>/<path to database>`.


See also: https://pythonhosted.org/Flask-SQLAlchemy/config.html


Thanks!
Issue: negative operator for descending order
Author: langit
State: closed
Created: 2015-03-18 23:41:41

I'd like to propose a syntax sugar for ordering. Instead of 

```
.order_by(User.name.desc())
```

one can also do

```
.order_by( - User.name)
```

which simply requires a hook method in class Node:

```
  def __neg__(self):
         self._ordering = "DESC"
```


- 08b3c0ee03576e16965b9003b38b4bad4b126fb3
- 3ced2b6d3955243fb815250158c44af94b212781
- 2f114136cb823f5a5b0c936dcc93d313c9e4f368
Issue: Delete with joins
Author: jakedt
State: closed
Created: 2015-03-18 20:23:53

Currently peewee doesn't support joins in delete queries. We've worked around this by using where clauses and nested subqueries (to force mysql to create a temp table). This is causing our DB to have a lot of deadlocks. One way around this would be to allow deletes to use joins.

An example query:

``` python
    subq = A.select(A.id).join(B).where(B.thing != example).alias('ps')
    inner = A.select(subq.c.id).from_(subq)
    A.delete().where(A.id << inner).execute()
```

could become:

``` python
    A.delete(A).join(B).where(B.thing != example).execute()
```

Thoughts? Pointers on the implementation? Examples of where this fails horribly?


This appears to not work at all with sqlite, and so is probably a non-starter. Feel free to close if you agree.


I don't believe it is supported by Postgres either, so yes I think I will close it out. If your deletes happen infrequently you could always recourse to having a "raw" SQL query to accomplish them, if that makes things easier.

Thanks for the good bug report and for being understanding about the wontfix.


This is supported in Postgresql with the [`USING` clause](https://www.postgresql.org/docs/current/static/sql-delete.html)

> PostgreSQL lets you reference columns of other tables in the WHERE condition by specifying the other tables in the USING clause. For example, to delete all films produced by a given producer, one can do:
> 
> `DELETE FROM films USING producers
>   WHERE producer_id = producers.id AND producers.name = 'foo';`

But since at the Peewee level you can just use s [raw query](http://stackoverflow.com/a/38068039/1161906), so I'm not sure this changes anything.
Issue: SQLite journal mode hangs
Author: wkschwartz
State: closed
Created: 2015-03-18 20:19:08

## Summary

The following code hangs indefinitely.

``` python
>>> import peewee
>>> db = peewee.SqliteDatabase(':memory:', journal_mode='WAL')
>>> db.connect()
```
## Diagnosis

When you hit Ctrl+C to trigger `KeyboardInterrupt` exception, you get the following traceback, indicating that `_add_conn_hooks`, which is called from `connect` (first line of the traceback), recursively calls back into `connect` (second to last line of the traceback), causing a deadlock with the database object's lock.

``` python
^CTraceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File ".../venv/lib/python3.4/site-packages/peewee.py", line 2810, in connect
    **self.connect_kwargs)
  File ".../venv/lib/python3.4/site-packages/peewee.py", line 3018, in _connect
    self._add_conn_hooks(conn)
  File ".../venv/lib/python3.4/site-packages/peewee.py", line 3026, in _add_conn_hooks
    self.execute_sql('PRAGMA journal_mode=%s;' % self._journal_mode)
  File ".../venv/lib/python3.4/site-packages/peewee.py", line 2867, in execute_sql
    cursor = self.get_cursor()
  File ".../venv/lib/python3.4/site-packages/peewee.py", line 2833, in get_cursor
    return self.get_conn().cursor()
  File ".../venv/lib/python3.4/site-packages/peewee.py", line 2826, in get_conn
    self.connect()
  File ".../venv/lib/python3.4/site-packages/peewee.py", line 2803, in connect
    with self._conn_lock:
KeyboardInterrupt
```
## Solution
- [ ] There appear to be no tests in the repo for `SqliteDatabase`'s `journal_mode` argument. One should be added.
- [ ] `_add_conn_hooks` needs to execute SQL directly on the `conn` connection argument it is passed rather than using the public `execute_sql` method. This will avoid the recursion.
Issue: implicit join via select function
Author: langit
State: closed
Created: 2015-03-18 16:27:14

I use django a lot and have just come to know about peewee, and I already like it. When I read of the N+1 behavior in the quickstart, an idea suddenly hit me: would it be nice if peewee can automatically figure out how to join tables behind the scenes so that users are relieved from such SQL concepts? Here is what I mean in more details:

```
for pet in Pet .select(Pet.name,  Pet.owner.name
                 ).where(Pet.animal_type == 'cat'):
    print pet.name, pet.owner.name
```

From the fields provided in the select(...) function, it should be possible to infer what kind of join is needed to provide those selected fields. Just a very rough idea, I hope you find it interesting and worth refining. Looking forward to hearing from you on what you think about this idea.


This is definitely a nice API and something I will consider for peewee 3.0 (whenever that might be, no timetable yet but I'm starting to think about it). I've added this to the TODOs for peewee 3. Closing for now.
Issue: Custom bindings for selection expressions
Author: jhorman
State: closed
Created: 2015-03-13 17:48:00

Currently there is no way to have the results of a selection sub expression land on a joined instance, instead of on the top level instance. Many times a model is expected to always have a field defined, even if it was inflated via a join.

Example:

``` python
BlogEntry.select(
    BlogEntry, 
    User, 
    fn.Exists(Role.select(Role.id).where(
        Role.user == User.id,
        Role.role == 'admin'
    )).alias('is_admin').bind_to(User)
).join(User)
```

Here, we expect the `User` objects to always have `is_admin` defined, even if inflated via a join.


Related to topic here https://groups.google.com/forum/#!topic/peewee-orm/Ny-2UE6yPvs


Neat! Do you mind renaming `target` to `bind_to` and adding documentation on the `Node` class' api docs?


Sounds good. Made those changes @coleifer
Issue: Error with long names in tables 
Author: sidan93
State: closed
Created: 2015-03-13 14:31:57

DATABASE: postgressql
only works postgressql!

I have a table called "TableTableTable" with primary key "@TableTableTable"
CURRVAL complains of a very long name "TableTableTable_@TableTableTable_seq" and cuts it


I think the better fix is to just manually specify the sequence name to be whatever postgresql truncated it to. e.g.:

``` python
tabletabletable = PrimaryKeyField(sequence='tabletabletable_id_s')
```
Issue: pwiz takes a very long time
Author: digi604
State: closed
Created: 2015-03-11 16:28:18

i have a db with many tables... pwiz seams to take a looong time... up to 30min to generate 1 table model


Uhhh this isn't a bug really. How many tables you got? What kind of database?


Also typically you'll only need to run pwiz once so even if it takes a while hopefully it's a one-time thing. pwiz takes a long time because it needs to traverse the graph of tables and introspect each one, finding col type info, constraints, indexes and foreign keys. There's no, afaik, way to speed that up. If you want to profile the code and try to find hotspots, by all means, but I think it's pretty much going to take time for large databases.
Issue: extending database.connect_kwargs in / with ExecutionContext
Author: richey-v
State: closed
Created: 2015-03-09 16:41:38

I'd like to be able to have same application (in Flask) be able to connect to database (MySQL) with different arguments for some transactions.  Specifically, looking for a way to have certain transactions coming from certain URLs be read-only and some be read/write.

I thought db.execution_context decorator would be a very cool way to annotate the handlers for the smaller set of the read/write URLs (letting default be read-only).

I started digging into extending peewee classes to do this, but wanted to check with you before spending much time.  Maybe a similar feature is planned, or maybe there is a better way?


Unfortunately I don't know how well this will work given the API of `ExecutionContext`. You might look into the new `Using` helper, as I think this is more what you want:

http://docs.peewee-orm.com/en/latest/peewee/api.html#Using