akumoth/peewee-issues huggingface dataset:
Issue: Convenient way to select random records from union
Author: vegeta1k95
State: closed
Created: 2018-04-06 21:21:26

Suppose there are two `SELECT` queries that can't be joined into one by joining `WHERE` clauses for some reason.
Is there any way to get their random ordered union?
Example:
Both this
```
s1 = SomeModel.select().where( some_condition1 )
s2 = SomeModel.select().where( some_condition2 )
s3 = (s1 & s2).order_by(fn.Random())
```
and this
```
s1 = SomeModel.select().where( some_condition1 ).ordered_by(fn.Random())
s2 = SomeModel.select().where( some_condition2 ).ordered_by(fn.Random())
s3 = (s1 & s2)
```
 raise error:
```
peewee.OperationalError: 1st ORDER BY term does not match any column in the result set
```
**My current workaround:**
```
import random
s1 = SomeModel.select().where( some_condition1 )
s2 = SomeModel.select().where( some_condition2 )
s3 = (s1 & s2)
s3_list = list(s3)
random.shuffle(s3_list)
```

```python
s1 = SomeModel.select().where(...)
s2 = SomeModel.select().where(...)
query = (s1 & s2).order_by(fn.RANDOM())
```

@coleifer 
Whenever I'm trying to iterate through this new query or make a list I get error:
```
peewee.OperationalError: 1st ORDER BY term does not match any column in the result set
```
Does `ModelCompoundSelectQuery` support iterations?

What database are you using?

And yes, it supports iteration. The error is in the SQL query.

I use mostly `SqliteQueueDatabase` from `playhouse.sqliteq`.
But I also tried this with standard `SqliteDatabase` and got the same error.
Peewee version: `3.2.2`, Python `3.6.3`

This works:

```python
class Register(BaseModel):
    value = IntegerField()

class Data(BaseModel):
    key = TextField()

db.create_tables([Register, Data])

for i in range(10):
    Register.create(value=i)

for k in range(10):
    Data.create(key='k%s' % k)

data = Data.select(fn.RANDOM(), Data)
reg = Register.select(fn.RANDOM(), Register)
union = (data | reg).order_by(SQL('1'))
for row in union.tuples():
    print(row)
```

Basically it selects a random value and uses it as the first column in each query. Then in the union query, we order all results by the first column (a random value).
Issue: Foreign keys must specify a `rel_field`
Author: csytan
State: closed
Created: 2018-04-06 19:25:10

Hi Charles,

Thank you for your excellent library 🙌

I'm trying to do some migrations by adding a ForeignKeyField, but I'm getting this error:
https://github.com/coleifer/peewee/blob/master/playhouse/migrate.py#L264

I couldn't find any documentation on what the `rel_field` is supposed to be.

## Code
```
with my_db.transaction():
    migrate.migrate(
        migrator.add_column('service', 'parent', peewee.ForeignKeyField(
            db.ServiceCategory, null=True, on_delete='SET NULL'))
    )
```

```
ValueError: Foreign keys must specify a `rel_field`.
```

## After adding a `rel_field`

```
with my_db.transaction():
    migrate.migrate(
        migrator.add_column('service', 'parent', peewee.ForeignKeyField(
            db.ServiceCategory, rel_field='servicecategory', null=True, on_delete='SET NULL'))
    )
```

```
Traceback (most recent call last):
  File "run.py", line 179, in <module>
    db.migrations.run()
  File "/Users/csytan/Documents/dev/cozycal/db/migrations/__init__.py", line 18, in run
    importlib.import_module('db.migrations.{}'.format(version))
  File "/usr/local/Cellar/python/3.6.5/Frameworks/Python.framework/Versions/3.6/lib/python3.6/importlib/__init__.py", line 126, in import_module
    return _bootstrap._gcd_import(name[level:], package, level)
  File "<frozen importlib._bootstrap>", line 994, in _gcd_import
  File "<frozen importlib._bootstrap>", line 971, in _find_and_load
  File "<frozen importlib._bootstrap>", line 955, in _find_and_load_unlocked
  File "<frozen importlib._bootstrap>", line 665, in _load_unlocked
  File "<frozen importlib._bootstrap_external>", line 678, in exec_module
  File "<frozen importlib._bootstrap>", line 219, in _call_with_frames_removed
  File "/Users/csytan/Documents/dev/cozycal/db/migrations/51.py", line 11, in <module>
    db.ServiceCategory, rel_field='servicecategory', null=True, on_delete='SET NULL'))
  File "/Users/csytan/Library/Python/3.6/lib/python/site-packages/peewee.py", line 4127, in __init__
    super(ForeignKeyField, self).__init__(*args, **kwargs)
TypeError: __init__() got an unexpected keyword argument 'rel_field'
```




The error message is out-of-date. The parameter name is "field" (no "rel_"). It refers to the field on the model your FK points to - typically the primary key.

```
# e.g., to reference the "id" field of ServiceCategory:
ForeignKeyField(db.ServiceCategory, db.ServiceCategory.id, null=True, on_delete='SET NULL')
```
Issue: Update api.rst for Sqlitedatabase inheritance
Author: Declan1984
State: closed
Created: 2018-04-06 17:17:58

Adding an explicit indication that Sqlitedatabase inherits from Database; if proposed change is accepted, I can go into the other classes and add similar explicit reference.

Thanks I'll pass on this for now. It'd be nice if the python domain of ReST supported this type of annotation but I couldn't find it.
Issue: FTS with snippet/extract
Author: rcarmo
State: closed
Created: 2018-04-04 21:10:44

Hi there,

I'm porting an older bit of code to 3.x, and am having trouble with the following:

```python
    query = (FTSPage.select(Page,
                            FTSPage,
                            fn.snippet(FTSPage.as_entity()).alias('extract'),
                            FTSPage.bm25().alias('score'))
                    .join(Page)
                    .where(FTSPage.match(qstring))
                    .order_by(SQL('score').desc())
                    .order_by(Page.mtime.desc())
                    .limit(limit))
```

This allows me to render an extract of the match document, but breaks in 3.x. I read through the `Entity` documentation, and tried using `Entity(FTSPage)`instead of `as_entity`, but clearly that is not the way to go here... Any pointers?

(Also, any hints regarding moving to FTS5 would be appreciated - the docs seem straightforward, but I suspect there will be some caveats)

Try:

```python
fn.snippet(FTSPage._meta.entity).alias('extract')
```

FTS5 is a completely different library, really. I'd suggest doing a very close reading of the fts5 sqlite documentation if you haven't already. Personally, I've gone back to FTS4 because FTS5 was just a bit problematic (especially dealing with special chars in search queries).

Thanks, `._meta.entity`did the trick (in the sense that now I have a logical error someplace else and an empty query result, but at least it runs). I was using 2.8.x, so I missed any doc updates in the meantime.

As to FTS5, I'll probably wait, then. I did check out the docs briefly, but since I need accented character support I'll to a test fork...

Thanks for the quick reply!

Well, feel free to share the model definitions and the query, be happy to try and troubleshoot.

Thanks, will check a few things first, since I need to validate what’s going in to the FTS table - will reply here if it’s not a content error...

This is the only page I can find discussing peewee and ```fts5``` ```snippet()``` on the web. Hope the following helps someone in the future, possibly me.

I was able to get the snippet out of an ```FTS5Model``` class using the following:
```fn.snippet(Record._meta.entity, 1, '<b>', '</b>', '...', 10).alias('snippet')```

I am, however, doing as @coleifer and dropping ```FTS5Model``` for ```FTSModel``` with ```fts4.``` My reason is that I could not find a way to store unique records using ```FTS5Model``` but I know how to do it with ```FTSModel``` + ```Model```. It seems ```fts5```is not mature enough yet.
Issue: insert_many fails on sqlite 3.6.20
Author: binrush
State: closed
Created: 2018-04-03 05:01:13

Hi! Thank you for your great project.

On peewee version 3.2.2, I'm trying to use insert_many with sqite 3.6.20:

```python
import peewee as pw
import logging

logging.basicConfig(level=logging.DEBUG)

db = pw.SqliteDatabase(':memory:')

class TestTable(pw.Model):
    id = pw.PrimaryKeyField()
    data = pw.TextField()

    class Meta:
        database = db

db.create_tables([TestTable])

TestTable.insert_many([{'id': 1, 'data': 'one'}, {'id': 2, 'data': 'two'}]).execute()
```

But I get error, because sqlite 3.6.20 does not support multiple inserts syntax:

```
---------------------------------------------------------------------------
OperationalError                          Traceback (most recent call last)
~/pe-ve/lib/python3.5/site-packages/peewee.py in execute_sql(self, sql, params, commit)
   2519             try:
-> 2520                 cursor.execute(sql, params or ())
   2521             except Exception:

OperationalError: near ",": syntax error
```

Dump of queries peewee executes:
```
DEBUG:peewee:('CREATE TABLE IF NOT EXISTS "testtable" ("id" INTEGER NOT NULL PRIMARY KEY, "data" TEXT NOT NULL)', [])
DEBUG:peewee:('INSERT INTO "testtable" ("id", "data") VALUES (?, ?), (?, ?)', [1, 'one', 2, 'two'])
```

In older peewee version (2.8.3), in such cases peewee dropped to multiple inserts instead (it had bug with on_conflict clause though). Was this compatibility dropped by some reason?

Correct, SQLite began supporting multi-value INSERT statements in 3.7.11, which was released over 6 years ago: http://sqlite.org/releaselog/3_7_11.html

For simplicity, Peewee 3.0 no longer includes this workaround. You can, of course, write your code to process the INSERTs in a loop (which is all 2.x did).
Issue: IPField Design
Author: ghost
State: closed
Created: 2018-04-02 00:13:23

Hello, about the Design of new Peewee `IPField`, I am the creator of a package `peewee-extra-fields`,
we have a similar Field for IP, I think we can tinker and collaborate for better on that field:

We have `IPAddressField` and  `IPNetworkField` its features are:

- They use Native Pythons `ipaddress.IPv4Address` and `ipaddress.IPv4Network`
- They use IPv4 and IPv6 transparently.
- The can do checking whether or not two hosts are on the same subnet, iterating over all hosts in a particular subnet, checking whether or not a string represents a valid IP address or network definition, etc etc.

**Compare:**

Peewee:

```python
class IPField(BigIntegerField):
    def db_value(self, val):
        if val is not None:
            return struct.unpack('!I', socket.inet_aton(val))[0]

    def python_value(self, val):
        if val is not None:
            return socket.inet_ntoa(struct.pack('!I', val))
```

peewee-extra-fields:

```python
class IPAddressField(BigIntegerField):
    def db_value(self, value: str) -> int:
        if value and isinstance(value, (str, int)):
            try:
                ip_address(value)
            except Exception as error:
                raise ValueError(f"""{self.__class__.__name__} IP Value is
                not a Valid IP v4 or v6 Address: {value} --> {error}.""")
            else:
                return int(ip_address(value))  # Valid IPv4Address/IPv6Address.
        return value                           # is None.

    def python_value(self, value: str) -> IPv4Address:
        return ip_address(value) if value else value
```

My implementation is transparently Compatible with IPv6:

```python
>>> struct.unpack('!I', socket.inet_aton("fe80::12c3:7bff:fe92:9d4c"))[0]
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
OSError: illegal IP address string passed to inet_aton
illegal IP address string passed to inet_aton

>>> int(ip_address("fe80::12c3:7bff:fe92:9d4c"))
338288524927261089655370957493906873676

>>> socket.inet_ntoa(struct.pack('!I', 338288524927261089655370957493906873676))
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
struct.error: argument out of range
argument out of range

>>> str(ip_address("fe80::12c3:7bff:fe92:9d4c"))
'fe80::12c3:7bff:fe92:9d4c'
```

If you like it feel free to steal the code for Peewee itself, no problem.
If you dont like it feel free to Close this Issue, no problem.
:smile_cat: 

Mine does not support Python 2 tho... :thinking: 

Because the `ipaddress` is a Python 3.x-only module, I'm going to pass on any related modifications to the peewee `IPField`. I think it's perfectly fine for this functionality to continue existing in a third-party library like yours (until such time as Peewee drops python 2 support, which I don't have plans for but will probably happen at some point).

Thanks for sharing this with me, I wasn't aware of the `ipaddress` module and it looks very nice.
Issue: playhouse.postgres_ext.BinaryJSONField returns string
Author: felipetrz
State: closed
Created: 2018-03-28 22:13:00

When I store data in a postgres JSONB field using the provided field type, it should be accessed as a dict/list object when I retrieve it. Instead it's being returned as a string.

The JSONB code is tested, so if something were that badly broken it would certainly show up as a lot of test failures.

Are you using `playhouse.postgres_ext.PostgresqlExtDatabase` instead of the generic `peewee.PostgresqlDatabase`?

Sorry, the bug was actually with peewee-moves, that wasn't creating the table with the correct field type.
Issue: [BUG] Float gets autoconverted into integer in query in Peewee 3.1
Author: Egor3f
State: closed
Created: 2018-03-26 05:57:23

I have exaclty the same issue, as there: https://github.com/coleifer/peewee/issues/1150
I need to multiply IntegerField by float value in select query (to apply commission or discount to price field).
But in actual SQL query float gets converted to integer, so 0.9 becomes 0.

In peewee v2 I could use `Item.select((Item.price * Passthrough(multiplier)).alias('final_price'))`

There is no Passthrough function in peewee v3.

My database is Mariadb through pymysql, if it matters.

It seeems strange that such simple action is impossible in peewee. And more strange, that in Peewee v2 I could do that, and in new version I can't.

P.S. I don't need any CASTing, just need to pass right value without converting.

Just a thought -- if you have a price and are multiplying it by a float, that could cause issues... you might have better luck using a `DecimalField` which will not introduce precision errors.

I'll look into the issue as reported, just thought I'd mention that as a suggestion.

I just wrote a small test-case and it is passing:

```python
class Item(TestModel):
    price = IntegerField()
    multiplier = FloatField(default=1.)


class TestFieldValueHandling(ModelTestCase):
    requires = [Item]

    def test_int_float_multi(self):
        i = Item.create(price=10, multiplier=0.75)
        i_db = (Item
                .select(Item, (Item.price * Item.multiplier).alias('total'))
                .where(Item.id == i.id)
                .get())
        self.assertEqual(i_db.price, 10)
```

Can you help me understand what I've got wrong in my setup so I can replicate this?

Ahh, got it to fail, nevermind:

```python
        i_db = (Item
                .select(Item, (Item.price * 0.75).alias('total'))
                .where(Item.id == i.id)
                .get())
        self.assertEqual(i_db.price, 10)
        self.assertEqual(i_db.multiplier, .75)
        self.assertEqual(i_db.total, 7.5)  # FAILS -- 0
```

Fixed in: 969c208f34f159b742e81be722e6a409400843ad

You should now be able to pass "False" as the 2nd parameter of `Value` (the 3.0 replacement for Passthrough):

```python
Item.select((Item.price * Value(multiplier, False)).alias('final_price'))
```
Issue: Why don't you support oracle database?
Author: inpool
State: closed
Created: 2018-03-26 03:03:44

I often need to write tools about existing apps, which are based on Oracle, and I love peewee, and when I'm ready to use her, I find that she doesn't support Oracle. Why does peewee not support Oracle?

It's pretty obvious that open source developers won't bother with Oracle and it's licences. 
What's the justification for writing and orm in your free time for a database you don't use?

If you are interested, it's easy to add a backed for Oracle, and if you have a license you can actually even test the code.

Maybe you can convince your employer to pay for the effort as a path to migrate from Oracle to PostgreSQL.

Oracle has a DB-API 2.0-compatible Python driver, I believe it's called `cx_Oracle`? If you want to add support for Oracle you should be able to subclass `Database` and implement the appropriate methods to get it working with Oracle.

As @oz123 alluded to, Oracle is a proprietary database. I have no interest in supporting or maintaining support for it.

Thank you! I'll try it.
Issue: pip install peewee fails with Anaconda on Win10
Author: happylrd
State: closed
Created: 2018-03-23 03:35:43

`pip install peewee` fails with Anaconda on Windows 10.
- OS: Windows 10
- Python: Python 3.6.4 :: Anaconda, Inc.
- Cython: 0.27.3

### error
```
playhouse\_sqlite_ext.c(531): fatal error C1083: Cannot open include file: 'sqlite3.h': No such file or directory
    error: command 'D:\\MyIDE\\VS2015\\VC\\BIN\\x86_amd64\\cl.exe' failed with exit status 2
```

**But when I used official Python without Anaconda, it installed successfully**.So what can I do for installing peewee successfully with Python in Anaconda?



Hmm... I don't know, with setup.py, how to check if a header file/shared lib is going to be available ahead-of-time.

@coleifer  `conda install -c conda-forge peewee` can solve this problem, the only regrettable thing is that the version of peewee is a little old, just 2.10.2.

**Supplement 2018.4.10**
The version of [peewee](https://anaconda.org/conda-forge/peewee) is v3.1.5, it's enough to use for me. 

Ew, yeah... Peewee has much more extensive integration with SQLite in 3.x, although it's restricted to a module in `playhouse`. I'm not sure how to conditionally compile that, if Cython and a C compiler is installed but sqlite is not?

Cython and a C compiler has installed.
conda list:
`sqlite                    3.22.0               h9d3ae62_0`
So I'm puzzled about installing peewee fail with pip.
Or can you update the version of [peewee ](https://anaconda.org/conda-forge/peewee) in conda-forge?

I have no idea what conda is?

Anaconda is a python and R distribution. It aims to provide everything you need (python wise) for data science "out of the box".
It includes:
- The core python language
- 100+ python "packages" (libraries)
- Spyder (IDE/editor - like pycharm) and Jupyter
- **conda, Anaconda's own package manager**, used for updating Anaconda and packages

See [here](https://stackoverflow.com/questions/49471840/anaconda-error-cannot-open-include-file-sqlite3-h-no-such-file-or-directory), it may help to update the version of peewee.

@happylrd probably better to ask [here](https://github.com/conda-forge/peewee-feedstock) since it seems the conda package wasn't made by coleifer.

It looks like @pmlandwehr and @dharhas have marked themselves as the maintainers of that.

Let me rephrase: I totally don't care what conda is.

I'd suggest you check it out with whoever the maintainers are.

For all you winblows lusers out there, I've added an option to the setup.py script to skip building SQLite-specific C extensions. Docs here:

http://docs.peewee-orm.com/en/latest/peewee/installation.html#skip-compilation-of-sqlite-extensions

This environment var is supported in the latest version on PyPI -- 3.1.7, which I just pushed. Hope this helps!
Issue: ArrayField should use the db_value and python_value methods of it's data-type
Author: coleifer
State: closed
Created: 2018-03-22 17:32:33

Currently `ArrayField` does no processing in either `db_value` or `python_value`, meaning arrays of fields that implement custom conversion will not work as expected.

eba0aefdc63b2d4106334cd5c11d1c9de659fb12
Issue: Add documentation for ~ opertor
Author: dyadav7
State: closed
Created: 2018-03-22 12:25:10

Documentation has example for contains - e.g. Facility.name.contains('tennis').
There should be example for reverse of this contain API / reverse of any than queries.
e.g. I want to search for a Facility for which the Facility.name does not contain "tennis".

See: https://stackoverflow.com/questions/25734566/operator-not-in-with-peewee 

Thanks
Deepak

There is an example.

![s1521726276 43](https://user-images.githubusercontent.com/119974/37774099-4aba906e-2dad-11e8-9683-3e9c572bbe14.png)


Thanks
Issue: a + b != sum([a, b])
Author: phryk
State: closed
Created: 2018-03-20 14:44:30

Expected behavior:
`a + b == sum([a, b])`

Actual behavior:

```
>>> a = User.select(User.id)
>>> b = News.select(News.id)
>>> a + b == sum([a, b])
False
>>> (a + b).sql()
('SELECT "t1"."id" FROM "user" AS "t1" UNION ALL SELECT "t2"."id" FROM "news" AS "t2"', [])
>>> sum([a, b]).sql()
('? UNION ALL SELECT "t1"."id" FROM "user" AS "t1" UNION ALL SELECT "t2"."id" FROM "news" AS "t2"', [0])
```

It would be A) logically consistent and B) nice for dynamic query construction if the behavior of `sum` on queries could be fixed.

Peewee uses operator overloads to compose expressions and queries themselves. For queries, the operators are:

* addition: UNION ALL
* bitwise or: UNION
* bitwise and: INTERSECT
* subtraction: EXCEPT

I'm not sure how the `sum` builtin is implemented but apparently it is doing something weird. You can use `reduce()` with `operator.add` instead of `sum`... Or actually maybe that's the problem, it's doing a left-fold with `0` as the initial value?

The only other alternative I can think that would be appropriate would be to throw an error if a user attempts to add a query and a scalar value. This would cause `sum` (or any fold/reduce with a zero initial value) to fail, presumably, so I'm not sure that solves your problem.

In short, I think you'll need to work around this using your own iterator.
Issue: drop_table does not pass **options properly
Author: ccosby
State: closed
Created: 2018-03-19 15:32:24

https://github.com/coleifer/peewee/blob/74f3b30e6c0a6f8461b8bd33a21fd5fe9ad5c8c3/peewee.py#L4542

It appears as if **options should be passed to _drop_table() instead of execute() here.

unittest doesn't catch this one because it tests the private method instead of the public one.

Thanks, fixed.
Issue: Add column if doesn't exist
Author: Shashwat986
State: closed
Created: 2018-03-18 14:11:33

I was wondering if it would be useful to have a method to add any new columns to a table, or to all tables in the database. It'll make db migrations much easier.

In my head, usage would look like:
```py
db.connect()
db.create_tables([User, Tweet])
db.add_columns([User, Tweet]) # or User.add_columns()
```

I feel this makes sense, especially after `create_tables` is safe by default now, so, in theory, it can be run every time. Similarly, `add_columns` can also run every time, making any and all migrations single-line.

I was looking at this as a use-case primarily for SQLite, because SQLite doesn't allow any other ALTER TABLE commands, so, this makes more sense than a migration; however, it may make sense for all database types.

Also, if this is something we're interested in, I'm more than happy with picking this up myself, and creating a PR.

Adding columns strikes me as being different-enough from just creating tables that I think the current division of APIs is appropriate.

The table is based on a model, which is declared in Python code -- from the perspective of a running app, it's structure is static. When adding (or otherwise altering) fields on a model, you do it offline, modifying the model definition. Since it's not a dynamic operation, it makes sense to code the schema migrations in a similar manner. Also, `create_tables` ensures that a table exists, but it does not otherwise alter a pre-existing table.

Changing a pre-existing database table is a very dangerous operation and I think that such a method would cause a lot of confusion.