akumoth/peewee-issues huggingface dataset:
Issue: Alias fields gets lost when doing union
Author: kwellman
State: closed
Created: 2018-05-09 22:15:28

I wrote a little test to show my problem:

```python
from peewee import Model, SqliteDatabase, CharField, ForeignKeyField

database = SqliteDatabase(':memory:')

class BaseModel(Model):
    class Meta:
        database = database

class Tournament(BaseModel):
    name = CharField()

class Result(BaseModel):
    tournament = ForeignKeyField(Tournament)

database.create_tables([Tournament, Result])
t = Tournament.create(name='Tournament1')
Result.create(tournament=t)

q1 = Tournament.select(Tournament, Result).join(Result, on=(Result.tournament==Tournament.id).alias('result'))
q2 = Tournament.select(Tournament, Result).join(Result, on=(Result.tournament==Tournament.id).alias('result'))

# this works fine
for tournament in q1:
    print tournament.result

# this works fine
for tournament in q2:
    print tournament.result

# raises "AttributeError: 'Tournament' object has no attribute 'result'"
for tournament in (q1+q2):
    print tournament.result
```

I want to run a union of two queries that each have a join that results in a new field that holds the joined row. Unfortunately the queries are not returning the new field when I do a union but it works fine running the queries separately. Is this a bug?

Thanks

I added a test-case that I think is equivalent to your example. It is passing for me: 909b371123a73ea00b7db18bbdb870083286d603

Here's the test-case code:

```python
    def test_union_with_join(self):
        u1, u2 = [User.create(username='u%s' % i) for i in (1, 2)]
        for u, ts in ((u1, ('t1', 't2')), (u2, ('t1',))):
            for t in ts:
                Tweet.create(user=u, content='%s-%s' % (u.username, t))

        q1 = (User
              .select(User, Tweet)
              .join(Tweet, on=(Tweet.user == User.id).alias('tweet')))
        q2 = (User
              .select(User, Tweet)
              .join(Tweet, on=(Tweet.user == User.id).alias('tweet')))

        with self.assertQueryCount(1):
            self.assertEqual(
                sorted([(user.username, user.tweet.content) for user in q1]),
                [('u1', 'u1-t1'), ('u1', 'u1-t2'), ('u2', 'u2-t1')])

        uq = q1.union_all(q2)
        with self.assertQueryCount(1):
            result = [(user.username, user.tweet.content) for user in uq]
            self.assertEqual(sorted(result), [
                ('u1', 'u1-t1'),
                ('u1', 'u1-t1'),
                ('u1', 'u1-t2'),
                ('u1', 'u1-t2'),
                ('u2', 'u2-t1'),
                ('u2', 'u2-t1'),
            ])
```

Am I missing something?

Travis-CI build including the above patch: https://travis-ci.org/coleifer/peewee/builds/377071024 (passed)

Sorry. I feel really stupid for not checking which peewee version I was using. 

I just upgraded to the latest from peewee version 3.2.3 and it works. 

Yep...I'm an idiot.

No need to apologize, this was fixed only recently.
Issue: flask_utils fails to import if libsqlite3 or sqlite-devel is not installed
Author: bmamouri
State: closed
Created: 2018-05-08 07:31:01

Similar issue in the past: https://github.com/coleifer/peewee/issues/1218

I use PostgreSQL and my server does not have SQLite installed. When I want to import FlaskDB from flask_utils, I encounter the following issue:

```
  File "/home/ubuntu/presentation/api/server.py", line 4, in <module>
    from playhouse.flask_utils import FlaskDB
  File "/home/ubuntu/.local/share/virtualenvs/api-eYDWRPCf/lib/python3.6/site-packages/playhouse/flask_utils.py", line 12, in <module>
    from playhouse.db_url import connect as db_url_connect
  File "/home/ubuntu/.local/share/virtualenvs/api-eYDWRPCf/lib/python3.6/site-packages/playhouse/db_url.py", line 9, in <module>
    from playhouse.sqlite_ext import SqliteExtDatabase
  File "/home/ubuntu/.local/share/virtualenvs/api-eYDWRPCf/lib/python3.6/site-packages/playhouse/sqlite_ext.py", line 74, in <module>
    FTS_VER = sqlite3.sqlite_version_info[:3] >= (3, 7, 4) and 'FTS4' or 'FTS3'
```

The output of importing sqlite3:
```
ubuntu@REDACTED:~/wattcost/presentation/api$ python -m sqlite3
Traceback (most recent call last):
  File "/home/ubuntu/.pyenv/versions/3.6.5/lib/python3.6/runpy.py", line 183, in _run_module_as_main
    mod_name, mod_spec, code = _get_module_details(mod_name, _Error)
  File "/home/ubuntu/.pyenv/versions/3.6.5/lib/python3.6/runpy.py", line 142, in _get_module_details
    return _get_module_details(pkg_main_name, error)
  File "/home/ubuntu/.pyenv/versions/3.6.5/lib/python3.6/runpy.py", line 109, in _get_module_details
    __import__(pkg_name)
  File "/home/ubuntu/.pyenv/versions/3.6.5/lib/python3.6/sqlite3/__init__.py", line 23, in <module>
    from sqlite3.dbapi2 import *
  File "/home/ubuntu/.pyenv/versions/3.6.5/lib/python3.6/sqlite3/dbapi2.py", line 27, in <module>
    from _sqlite3 import *
ModuleNotFoundError: No module named '_sqlite3'
```

It seems the root cause of the issue is [here](https://github.com/coleifer/peewee/blob/master/peewee.py#L30):

```
try:
    from pysqlite3 import dbapi2 as pysq3
except ImportError:
    try:
        from pysqlite2 import dbapi2 as pysq3
    except ImportError:
        pysq3 = None  # <= What is causing the problem
try:
    import sqlite3
except ImportError:
    sqlite3 = pysq3
else:
    if pysq3 and pysq3.sqlite_version_info >= sqlite3.sqlite_version_info:
        sqlite3 = pysq3
```

One way to solve this problem would be to convert FTS_VER into a lambda function so it can be evaluated lazily.

Oops, just realized the problem is already fixed in the [latest version](https://github.com/coleifer/peewee/blob/master/playhouse/sqlite_ext.py#L47).

Please close the issue. Thanks
Issue: Request: forgetting to wrap comparisons in parentheses, or using the wrong operators should cause an error
Author: robhawkins
State: closed
Created: 2018-05-08 04:23:44

Is it possible to throw an exception when someone writes a condition the wrong way in peewee?

I'm just getting to know this very useful tool.  I used it for the first time a week ago, and then again today, and had forgotten to use `&` instead of `and` and also forgot to wrap the conditions in parentheses.  This caused some unexpected results during testing and did not throw an error indicating the root cause of the issue.

Is it possible to identify such issues for the user?  This cost me an extra 45 mins of development time and I wonder if that could be avoided in the future in the off chance I forget again.  Thanks!  This is a great tool.

Sorry, there's not really any way to identify these kinds of mistakes. The documentation makes it abundantly clear that you're to use `&` and `|`, typically if you do make the mistake of using `and` and `or` you won't repeat it.
Issue: different select behavior between 3.3.4 and 3.2.2
Author: trim21
State: closed
Created: 2018-05-06 23:57:28

I try some code like this
```python
# coding=utf-8

import peewee
from peewee import IntegerField, FixedCharField, TextField

db = peewee.SqliteDatabase('example.db')


class DB(peewee.Model):
    class Meta:
        database = db


class Table1(DB):
    name = TextField(unique=True, null=False)
    status = TextField()


class Table2(DB):
    user_name = TextField(unique=True)
    status = TextField()


if __name__ == '__main__':
    Table1.create_table(fail_silently=True)
    Table2.create_table(fail_silently=True)
    Table1.create(name='name', status='status_table_1')
    Table2.create(user_name='name', status='status_table_2')

    data = Table1.select(Table1, Table2.status) \
        .join(Table2, peewee.JOIN['LEFT_OUTER'], on=(Table1.name == Table2.user_name)) \
        .where(Table1.name == 'name').objects()
    print(data[0].status)
```
in peewee 3.2.2 it's `status_table_2`
but in peewee 3.3.4 it's `status_table_1`


I wonder is this a break change without change major version, or it's just a bug.



I try many peewee versions, 3.2.4 is first version that return the values of table1 and 3.2.3 is the latest version that return the value of table2

The change that was introduced was to avoid overwriting values. You're selecting all columns on `Table1` (including it's status field), so it's expected that the status field would contain the value on Table1.

To capture the status field on table 2 there are a number of things you can do:

* specify an alias for the Table2.status value (e.g. Table2.status.alias('status2'))
* if a foreign-key exists between the joined tables, look-up the attribute on the joined instance
* otherwise specify a join attribute by specifying an alias on the join predicate
Issue: auto primary key field
Author: ra-esmith
State: closed
Created: 2018-05-06 15:37:16

Using peewee 3.3.1 (just upgraded to 3.3.4).

Python
On ubuntu 16

"sometimes" I find a rather odd new error.

I have various classes which have foriegn keys etc,  some have no primary key defined.  Peewee defiens an auto increments the key on creation, or it has in the "past versions".  However, I now find that "sometimes" the auto increment of the primary key does not occur and it comes out as null in the insert and blows an error in sql.  Saying null value in colum n";id"; violates not-null constraint (postgres).

Then "failing row contains (null, ...)
statement is
insert into table ("foo_id", ..

and foo is the foreign table.

NOW, if i put int an autofield and explcitly deal with that then the problem goes away.

But would rather not rewrite my code, and would like to know what is up. 

Ideas?

Evan

looks like the model.insert call is not making the primary key.  i have not intentionally passed in a primary key, also possible the record coming in has null on the primary key already and you are not overwriting that?  I am going to try .create instead of .insert

Peewee has a convention of using `id` for an auto-incrementing primary key field. Perhaps you're subclassing models which override the primary key?

Can you provide example code I can use to reliably replicate this issue? Without that I'm a little at a loss how to replicate this or even verify there is a bug.

Please comment with additional details or steps I can follow to replicate, and I will be happy to reopen and investigate.

Hello,

Thanks for the help.

I have resolved this on my end.

Issue comes up if one has a postgres 9.6 version.   I moved to 10 postgres and it went away.
However, I also was more brutal in my code as related to ID generation and storage, and perhaps that also fixed the problem.

Eitherway I am in peewee 3x and looks to be working now.

Thanks!!!!
Evan

Sent with [ProtonMail](https://protonmail.com) Secure Email.

‐‐‐‐‐‐‐ Original Message ‐‐‐‐‐‐‐
On May 8, 2018 9:45 AM, Charles Leifer <notifications@github.com> wrote:

> Please comment with additional details or steps I can follow to replicate, and I will be happy to reopen and investigate.
>
> —
> You are receiving this because you authored the thread.
> Reply to this email directly, [view it on GitHub](https://github.com/coleifer/peewee/issues/1598#issuecomment-387407156), or [mute the thread](https://github.com/notifications/unsubscribe-auth/AXFzJlcgsNjM032U-b8jr-Y-ANUeknJIks5twaFjgaJpZM4T0FOF).
Issue: Peewee returns Decimal(), unsupported type.
Author: mtairu
State: closed
Created: 2018-05-05 07:18:33

Querying data from RDS, and peewee get the record succesfully however, when I loop through to get the value, it returns Decimal(), or corresponding type. I used peewee in my last project, and it did not return data like this.

```
records = market.select().limit(1)
return [r.volume for r in records]

results:
[Decimal(89033)]
```

solved.
Issue: Model level constraint
Author: bySabi
State: closed
Created: 2018-04-30 18:14:22

A incomer here ...
I know the field constraints but I need `null=False` for all fields.

Can I set `null=False` by default for a Model, Is this possible?



 

Fields have `null=False` by default, or in other words, by default fields do not allow null values.

@coleifer thanks for take the time to answer Peewee beginners questions. You are doing a very good job here 👏

one more ... 

I create a SQLite db and a model with a DateField, say:
```
class MyModel(BaseModel):
  n1 = IntegerField(unique=True)
  date1 = DateField(unique=True)
```
Then the model is populate from a json file:
```
   with open('json.jl') as f:
      for line in f.readlines()
         data = MyModel(**json.loads(line))
         data.save()
```
The above code is running fine, UNIQUE constraints raise exceptions has expected. 
But I put, intentionally, wrong values on `date1` field like '2017-1-59' or 'any string'  and the values get store without `Exception`. I thought that `DateField` enforce some constraint or validation. 

What I miss here??
  

You should be passing them in as Python `datetime.date` objects rather than as strings.

`DateField` only set the field type on table creation, but don´t do validation beyond null check or unique constraints? 


It's more complicated than that, unfortunately.

SQLite doesn't have a true date/time data-type. Databases like MySQL or Postgres would raise an error if you tried to insert garbage data, but with SQLite you need to either represent the date-time as a string (in which case you should use YYYY-mm-dd HH:MM:SS to ensure lexicographic sorting) or an integer timestamp.

If you want to enforce validation, you can subclass DateTimeField (or DateField or whatever) and modify the `db_value()` method to ensure strings are valid dates (or datetimes or whatever).

@coleifer now is clear to me. Thanks a lot for the details in the explanation.

I take your advice and convert date str to `datetime.date` before save it.
Issue: Missing things from Changes in 3.0 doc
Author: tomako
State: closed
Created: 2018-04-29 07:08:20

I've just moved a project to the latest version and found few shortcomings that I think are missing from the list of changes. I've checked also the release notes but neither found there. Anyway they might be useful for others.

- Database class init parameter `fields` (for adding custom fields) has been renamed to `field_types`
- SQL class init parameter `params` now is expected to be iterable
- SelectQuery's `union_all` method is gone but there is an operator (`+`) instead of it. As I remember I used the method call because there was no operator. And in fact I see the `union_all` method in the CTE class. TBH it was simpler to use the operator than understand what CTE is ;)

Ps. Special thanks for the `BigAutoField`. I was about to add it.

Thanks for reporting, should be fixed.

I added the following methods to select queries:

* `union_all()`
* `union()`
* `intersect()`
* `except_()`
Issue: Feature: get_unique_row
Author: yanghg-basefx
State: closed
Created: 2018-04-28 02:21:55

Consider: If one database can't have an unique index, but some queries must get a unique record, what should we do?

So I implement this method in SelectQuery (peewee 2.8.1):

```python
def get_unique_row(self):
    clone = self.paginate(1, 2)
    qrw = clone.execute()
    try:
        row = next(qrw)
    except StopIteration:
        raise self.model_class.DoesNotExist('...')
    else:
        try:
            next(qrw)
        except StopIteration:
            return row
        else:
            raise self.model_class.ExistsTooMany('...')
```

It's really useful for us, and avoided a lot of unexpected exceptions. So if you can add a method like it to peewee 3.X, that's best! We can easier to upgrade our own peewee branch.

Thanks!

Given how easy it is to add this to your own base model class, I'm going to pass on adding similar functionality to peewee. As you noted, the proper way to guarantee uniqueness is to have a unique constraint.

```python
@classmethod
def get_unique_row(cls, *expr):
    query = cls.select().where(*expr).limit(2)
    results = list(query)
    if len(results) == 1:
        return results[0]
    elif len(results) == 0:
        raise cls.DoesNotExist
    else:
        raise NonUniqueException
```

That's what I need! Thanks!
Issue: Fix issue with sort_models and DeferredForeignKey
Author: sanjredfield
State: closed
Created: 2018-04-27 08:08:01

I found an issue when using DeferredForeignKey and create_tables. The easiest way to duplicate the issue is to make a small modification to [this](http://docs.peewee-orm.com/en/latest/peewee/models.html#circular-foreign-key-dependencies) example from the peewee docs. 

Here is the original example, which works fine. 

```python
class User(Model):
    username = CharField()
    favorite_tweet = DeferredForeignKey('Tweet', null=True)
class Tweet(Model):
    message = TextField()
    user = ForeignKeyField(User, backref='tweets')

db.create_tables([User, Tweet])
```

However, one small change, and the example no longer works. The change is simply to rename `User` to `AUser`.

```python
class AUser(Model):
    username = CharField()
    favorite_tweet = DeferredForeignKey('Tweet', null=True)

class Tweet(Model):
    message = TextField()
    user = ForeignKeyField(AUser, backref='tweets')

db.create_tables([AUser, Tweet])
```

The original example from the docs works because `Tweet` is alphabetically before `User`, and `sort_models.dfs` gets called in the order: `Tweet` -> `User`. However, if we rename `User` to `AUser`, the call order of `dfs` is now `AUser` -> `Tweet`, therefore causing `Tweet` to be created first. This throws an error:

```
Traceback (most recent call last):
  File "<stdin>", line 1, in <module>
  File "goals/model/__init__.py", line 54, in create_tables
    db.create_tables([AUser, Tweet])
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2724, in create_tables
    model.create_table(**options)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 5456, in create_table
    cls._schema.create_all(safe, **options)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 4722, in create_all
    self.create_table(safe, **table_options)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 4618, in create_table
    self.database.execute(self._create_table(safe=safe, **options))
  File "/usr/lib/python2.7/site-packages/playhouse/postgres_ext.py", line 446, in execute
    cursor = self.execute_sql(sql, params, commit=commit)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2583, in execute_sql
    self.commit()
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2376, in __exit__
    reraise(new_type, new_type(*exc_args), traceback)
  File "/usr/lib/python2.7/site-packages/peewee.py", line 2576, in execute_sql
    cursor.execute(sql, params or ())
peewee.ProgrammingError: relation "auser" does not exist
```

The attached pull request fixes the issue by making a small modification to `dfs` in `sort_models`. I've added a check to ensure that deferred foreign keys are not traversed by the dfs. 

Thank you, I've submitted an equivalent cleaned-up patch with a test-case: d92036e7602f29c5ce4dae4ff2ffd09c28fab218

Thanks very much, I'll be sure to include a test case if I submit another PR in the future.
Issue: Database.cursor parameter documentation and consistency.
Author: diekhans
State: closed
Created: 2018-04-27 03:12:50

peewee-3.3.1

http://docs.peewee-orm.com/en/latest/peewee/api.html#Database.cursor

Indicates that Database.currsor(commit=None )  has cursor as an optional an parameter.  However, It does not define what this parameter does.

playhouse.postgres_ext.PostgresqlExtDatabase.cursor(commit) has the commit parameter as required. which conflictes with the definition in the base class.

thanks for the increidble package.


Thanks, I've updated the method signature for `PostgresqlExtDatabase` and mentioned in the docs that the parameter is internal-use-only. fbb4ded7000d5163344c67236fee7a1fae38991c
Issue: [attention] DATABASE_URL issue
Author: ak4nv
State: closed
Created: 2018-04-24 09:27:49

If you like using DATABASE_URL, just keep in mind that your password should not contain a "?" character.
```shell
>>> from urllib.parse import urlparse
>>> urlparse('postgres://login:p?assword@localhost:5432/db')
ParseResult(scheme='postgres', netloc='login:p', path='', params='', query='assword@localhost:5432/db', fragment='')
```
Happy conding! :smile: 

Ew interesting I wonder if that's been reported to python, since it's in the urllib.parse module.

https://bugs.python.org/issue18140
Issue: Each object be an singleton though the entire application
Author: GabrielSalla
State: closed
Created: 2018-04-20 14:27:24

SQLAlchemy has a neat feature where everytime an object of a model is instantiated, if this instance already existed, it would return the same object it already had (exactly). As an example, if you select objects from the database twice, both answers would be the same, including the `id()` of the objects.

It's a really interesting approach as we can select objects from anywhere in the code, change it's values and it will change anywhere this object exists too (as they are the same).

This would avoid problems where you already have the instance somewhere, select for the object in another function, change it's values but the first object will remain intact.

Is it an option for Peewee? I don't even know how hard it would be to implement it.

Here's an example code of what I mean.

```python
from peewee import *


class BaseModel(Model):
    class Meta:
        database = db


class User(BaseModel):
    username = CharField(unique=True)


db = SqliteDatabase('my_database.db')
db.connect()
db.create_tables([User])
charlie = User.create(username='charlie')
c1 = User.get(User.username == 'charlie')
c2 = User.get(User.username == 'charlie')

# Assert all of them are the same
assert id(charlie) == id(c1) == id(c2)
```

So, SQLAlchemy uses an identity-map to do this kind of thing. Which brings a lot of other logic into the equation, such as the concept of session/object lifetimes, aggregating changes during commit, etc.

Peewee, on the other hand, is an active-record ORM. Objects are containers for row data rather than canonical representations of the rows themselves. I find this mental model easier to work with. I know which queries are going to be executed and when they'll run.

It's a matter of preference, but peewee is fundamentally different from SQLAlchemy in this regard.
Issue: Window function for MySQL
Author: victators
State: closed
Created: 2018-04-19 21:44:30

Are the window function queries supported for MySQL databases? I tried running the following and I get a ProgrammingError: (1064, "You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '(PARTITION BY `t1`.`date`) AS `avg` FROM `PROJECT` AS `aap` WHERE (' at line 1")

Thank you!

```python
try: 
    db.connect()
    aap = PROJECT.alias('aap')
    query = (aap.select(aap.date, 
                        aap.animal,
                        aap.value, 
                        fn.AVG(aap.value).over(partition_by = [PROJECT.date]).alias('avg'))
                .where((aap.date == '2018-02-08') & (aap.animal == 'DOG')))
finally:
    db.close()
print([c.avg for c in query])
```

> Are the window function queries supported for MySQL databases?

I'm not sure, although they are supported with integration tests for Postgresql.

You might try this for now:

```python
w = Window(partition_by=[PROJECT.date])
query = (aap
         .select(aap.date, aap.animal, aap.value, fn.AVG(aap.value).over(w).alias('avg'))
         .where((aap.date == '2018-02-08') & (aap.animal == 'DOG'))
         .window(w))  # Try adding this.
```

I tried that too from your example in the docs but I just realized my MariaDB is running on MySQL 5.6 and window functions don't seem to come out until MySQL 8.0 so it was doomed from the start. Thank you for your help though

Yeah, recent MySQL and MariaDB have support but I haven't tested it personally.