akumoth/peewee-issues huggingface dataset:
Issue: Data Class query result
Author: hugo-pires
State: closed
Created: 2018-04-19 17:17:46

Hello

Is it possible the get the query results as a Data Class instance?

Thank you

You mean the 3.7 data-classes? Sure...just provide your own constructor function and call .objects(), e.g.:

```python
def make_data_class(data_dict):
    # do whatever.
    return DataClass(**data_dict)

for data_class_obj in some_query.objects(make_data_class):
    ...
```

Thank you. I am sorry but I am a newbie. After adapting your code I am getting the following error:

```AttributeError: 'SelectQuery' object has no attribute 'objects'```

What version of peewee are you using?

peewee                    2.10.1


Peewee 3.2.5 is the most recent version.

Thank you.
Issue: timestamp auto update
Author: 1xinghuan
State: closed
Created: 2018-04-19 14:19:56

Can peewee support auto update time when modify data? Like in MySql, it can use "ON UPDATE CURRENT_TIMESTAMP":
```
CREATE TABLE `timestampTest` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`last_modify_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)
```

Sure, with the caveat that the new value will not be available to your code unless you explicitly re-fetch the new value.

```python
class TimestampTest(Model):
    name = CharField(max_length=20)
    create_time = DateTimeField(constraints=[SQL('DEFAULT CURRENT_TIMESTAMP')])
    last_modify_time = DateTimeField(constraints=[SQL('DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP')])
```

Thanks, but I find it can only be useful when using Model.update(). If I use Model.save(), it doesn't auto update.

```
    query = TimestampTest.update(name="37a_000").where(TimestampTest.id == 1)
    query.execute()
```
This will update the last_modify_time.

```
    test = TimestampTest.get(id=1)
    test.name = "37a_0a"
    test.save()
```
This doesn't update.

Good point. You'll need to specify that peewee should only save the name field:

```python
test.save(only=(TimestampTest.name,))
```

That ought to do the trick.
Issue: Date storage and converstion
Author: biskit
State: closed
Created: 2018-04-19 13:47:11

I have a date coming in like this '04/20/2018 7:52 PM'. It's stored in a DateTimeField column as is. So any comparison against datetime.now() fails (typically > some datetime).

That format is no good because it doesn't sort lexicographically. Sqlite doesn't have a dedicate date/time data-type. You'll want to convert those to YYYY-mm-ddTHH:MM:SS so they sort correctly.

Peewee *can* read values like that if you specify the strptime format, however comparisons like the one in your example will not work correctly:

```python
lousy_datetime = DateTimeField(formats=['%m/%d/%Y %I:%M %p'])
```

It should be possible to also craft a one-time migration using sqlite's date/time functions: https://www.sqlite.org/lang_datefunc.html
Issue: Problem importing ManyToManyField from playhouse.fields in pipenv
Author: philippnkling
State: closed
Created: 2018-04-19 09:17:55

Hey,

recently I am having the problem of being unable to import the ManyToManyField from playhouse.fields on an Ubuntu server where I am using pipenv to manage packages and run python. Namely the command

`from playhouse.fields import ManyToManyField`

will lead to the error:

`ImportError: cannot import name ManyToManyField`

When I am using the same import command on my computer, it works, so this problem might be related to the virtualenvironment of pipenv. However, maybe this is a problem of different versions and you could help me. 
Additionally, the import command `from playhouse.shortcuts import ManyToManyField` works. Could I use this instead of the playhouse.fields entry? Are they the same? Can you tell me, why it might not find the ManyToManyField in the playhouse.fields? I would be grateful for any reference.
Thank you.

Sounds like `pipenv` has an old version of peewee on your computer.

In peewee 3.x `ManyToManyField` has moved into `peewee`, so:

```python
# 3.x
from peewee import ManyToManyField
```

I'm not familiar with `pipenv` but it sounds like it's using an older version of peewee on your computer and the newer 3.x on your server.

Thank you. This seemed to be the case for me.
Issue: Issue with union queries for models with similar columns
Author: bison
State: closed
Created: 2018-04-18 17:54:29

When I union two queries that are selecting multiple models, the IDs seem to be confused (actually any column with the same name):

```python
query = Tweet.select(Tweet, User).join(User).where(User.id == 1)
query2 = Tweet.select(Tweet, User).join(User)

union = query | query2

for t in union:
    print(t.id)
```

Outputs `1` twice, where I would expect `1` followed by `2`. I've only tested on sqlite with latest 3.x release of peewee. Am I doing something wrong? This worked in the last 2.x release I tested.

I wouldn't say you're doing anything wrong. What's happening is the result-rows from the cursor look like:

```sql
select t1.id, t1.user_id, t1.content, t2.id, t2.username 
from tweet as t1 
inner join users as t2 on (t1.user_id=t2.id) 
where t2.id = 1 
union 
select t3.id, t3.user_id, t3.content, t4.id, t4.username 
from tweet as t3 
inner join users as t4 on (t3.user_id=t4.id);
```

Produces:

```
id|user_id|content|id|username
 1|      1|   u1-1| 1|      u1
 2|      1|   u1-2| 1|      u1
 3|      2|   u2-1| 2|      u2
```

When peewee iterates over the result rows, it sees columns named (id, user_id, content, id, username). The 2nd id corresponds to the user's id rather than the tweet id. You can fix this in a number of ways without needing to update peewee:

1. Don't select the user's id.
2. Alias the user's id to a different attribute.

With the patch I've just pushed (80a2048837100c102d5387366cb1a5531739cb91) it should no longer be a problem, because:

1. Instead of putting all attributes onto the selected model when a UNION is present, attempt to resolve the model graph. So the user's "id" and "username" would be put on Tweet.user (e.g. tweet.user.id, tweet.user.username).
2. When returning a flat result, do not overwrite attrs. So "id" in your example would be the tweet id since that's the first "id" column we see. The 2nd user "id" would not be stored.

Got it. Looks like 3.2.4 works for me. Thanks!
Issue: Move playhouse module as a submodule on peewee?
Author: vadimkantorov
State: closed
Created: 2018-04-18 13:12:33

Finding in source code something like `import playhouse.shortcuts` is not very specific or self-explaining IMO.

A big leap would be moving at least some functions like `model_to_dict` / `dict_to_model` to stock peewee, but even making them `peewee.playhouse.model_to_dict` would be more discoverable.

Thanks for a great package!



This is simply how peewee is organized. Peewee is a standalone python module and all extras/extensions are included in a separate namespace.

I see your point. My suggestion was to move naming in the direction of making `playhouse` module name more related to peewee. A middle ground may be `peweeplayhouse`?

It's been this way since ... 2010? I'm not too interested in changing it.
Issue: Column name conflict
Author: biskit
State: closed
Created: 2018-04-18 02:04:50

This one left me scratching my head till trial and error found it.

1. Create a table with a column name 'update' - OK
2. Create a record - OK
2. Query a record - OK
3. Update a record - Not OK. Messages are extremely confusing from 'TypeError: 'str' object is not callable' to 'TypeError: 'unicode' object is not callable' to what not.

If this is documented somewhere, please disregard this.

Right, so you named a column the same as a classmethod.

The workaround here is to:

```python
class Foo(Model):
    update = TimestampField()  # uh-oh.

# do this instead:
class Foo(Model):
    update_ = TimestampField(column_name='update')  # added "_" to avoid name conflict.
```

I suppose I can add this to the docs as well.

A document update would be extremely helpful. Not sure how one can find it there, but atleast one may get lucky.

For sure, there's starting to be a bit of sprawl in the docs. If you had suggestions for re-organizing I'd be open to any ideas.

Added a note in the bottom of the "Fields" sub-section (after creating custom field).

I was getting that feeling too. One has to balance verbosity with tersity and finding that spot is hard. I'm bad at documentation, but there's nothing like more documentation (just more maintenance :-). 
Wish I had some out-of-the-box ideas...
Issue: Joins are shared after clone
Author: bison
State: closed
Created: 2018-04-17 15:21:16

On latest 3.x release (haven't tested others), the list of joins appears to be shared between queries after calling clone:

```python
query = Tweet.select()
new_query = query.clone()
new_query.join(User)

print(query.sql())
print(new_query.sql())
```
Outputs:
```
('SELECT "t1"."id", "t1"."user_id", "t1"."text" FROM "tweet" AS "t1" INNER JOIN "user" AS "t2" ON ("t1"."user_id" = "t2"."id")', [])
('SELECT "t1"."id", "t1"."user_id", "t1"."text" FROM "tweet" AS "t1" INNER JOIN "user" AS "t2" ON ("t1"."user_id" = "t2"."id")', [])
```

Some quick testing revealed a bug in the cloning of the from-list and the join dictionaries. Pushed a fix.

For what it's worthy, queries should be immutable -- so no need to call clone():

```python
query = Tweet.select()
new_query = query.join(User)
# query -> select * from tweet
# new_query -> select * from tweet join user
```
Issue: Can ManyToManyField support "self"
Author: 1xinghuan
State: closed
Created: 2018-04-17 13:32:47

Is that possible to use ManyToManyField in such way:

```
class User(ModelBase):
    name = CharField(column_name='NAME', null=False)
    followers = ManyToManyField("self", backref="following")
    class Meta:
        db_table = 'users'
```

Then it can create a table like "user_user_through"

You're _much_ better off just doing this:

```python

class User(ModelBase):
    name = CharField()
    class Meta:
        table_name = 'users'

class Relationship(ModelBase):
    from_user = ForeignKeyField(User, backref='following')
    to_user = ForeignKeyField(User, backref='followers')
```
Issue: ArrayField equals queries
Author: tuukkamustonen
State: closed
Created: 2018-04-16 11:11:47

How do you query [ArrayField](http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#ArrayField) for equal list comparison like:

```sql
... WHERE field = ARRAY['element', 'another'];
```

`.where(MyModel.field == ['element', 'another'])` produces:

```sql
WHERE (\"field\" = (ARRAY['e', 'l', 'e', 'm', 'e', 'n', 't']::TEXT[], ARRAY['a', 'n', 'o', 't', 'h', 'e', 'r']::TEXT[]))
```

(Still using the oldish peewee `2.10.2`, sorry!)

You might try using `Param` or `Passthrough` around your list, e.g.

```python
.where(MyModel.field == Passthrough(['element', 'another']))
```

In peewee 3.x, for what it's worth, it's not necessary to use a wrapper around the value, you can express the equality as you'd expect:

```python
query = SomeModel.get(SomeModel.array_field == ['foo', 'bar', 'baz'])
```

> ```python
> .where(MyModel.field == Passthrough(['element', 'another']))
> ```

This indeed does the trick, interesting. I was using `Clause` as a workaround, but `Passthrough`/`Param` (both work) makes it a bit cleaner and safer.

Will definitively upgrade to 3 soon. Thanks.

Actually, maybe I could ask in which (other) cases do we need `Param`/`Passthrough`? Docs don't mention them.

Or are these completely removed in 3.x? **Edit:** seems so as they're not found in ``peewee.py`` anymore.

Removed in 3.x. For 2.x you'll want to use it in places where you're passing a non-scalar type (like a list or something) to a database driver that understands those types...i.e., psycopg2 understands lists and dicts.
Issue: UnboundLocalError: local variable 'constructor' referenced before assignment
Author: raphael0202
State: closed
Created: 2018-04-16 08:27:56

This error occurs on Peewee 3.1.1, after an upgrade from Peewee 2.9.3.

Here is quick script that reproduces the error:
```import peewee

class Bot(BaseModel):
    name = peewee.CharField(max_length=30, index=True)

class User(BaseModel):
    original_id = peewee.CharField(max_length=50, null=True)

class PlatformBridge(BaseModel):
    bot = peewee.ForeignKeyField(Bot, backref='platform_bridges')
    user = peewee.ForeignKeyField(User, backref='platform_bridges')

PlatformBridge.select(PlatformBridge, User, Bot).join(User).switch().join(Bot)
```

with `BaseModel` being a base class where the db connection is configured.

The stacktrace:
```
Traceback (most recent call last):
  File "test.py", line 14, in <module>
    PlatformBridge.select(PlatformBridge, User, Bot).join(User).switch().join(Bot)
  File "/opt/likeabot/virtualenv/lib/python3.5/site-packages/peewee.py", line 569, in inner
    method(clone, *args, **kwargs)
  File "/opt/likeabot/virtualenv/lib/python3.5/site-packages/peewee.py", line 5634, in join
    on, attr, constructor = self._normalize_join(src, dest, on, attr)
  File "/opt/likeabot/virtualenv/lib/python3.5/site-packages/peewee.py", line 5628, in _normalize_join
    return (on, attr, constructor)
UnboundLocalError: local variable 'constructor' referenced before assignment
```

on Python 3.5.2.

When calling `switch()` you need to pass the model you are switching the context to. The error message is not very helpful, but you should change your code in this manner:

```python
.join(User).switch(PlatformBridge).join(Bot)
```

I've also amended the implementation of `switch()` so that when called with no parameters it will default to switching to the query's model.

Ok, thank you for the quick response!
Issue: Duplicate AS clause in SQL when aliasing a function call
Author: Terrance
State: closed
Created: 2018-04-15 15:41:50

I have two tables -- `User`, and `Data` which references users.  When querying, I add a property on the user result for whether any data rows reference that user (in reality there's other metadata attached here too):

```python
class User(Model):
    ...
    @classmethod
    def select_context(cls):
        return (cls.select(cls, fn.Exists(Data.select()
                                              .where(Data.user == User.id))
                                              .alias("exists")))
```

Running that query using APSW on Peewee 3.x gives me `apsw.SQLError: SQLError: near "AS": syntax error`, but this seemed to work fine on 2.x.

SQL example in Peewee 2.10.2:

```python
>>> User.select(User.id, fn.Exists(Data.select()).alias("exists")).sql()
('SELECT "t1"."id", Exists(SELECT "t2"."id" FROM "data" AS t2) AS exists FROM "user" AS t1', [])
```

And in 3.x (tested with 3.0.0 and 3.2.2), note two `AS` clauses for `exists`:

```python
>>> User.select(User.id, fn.Exists(Data.select()).alias("exists")).sql()
('SELECT "t1"."id", Exists(SELECT "t2"."id" FROM "data" AS "t2") AS "t3" AS "exists" FROM "user" AS "t1"', [])
```

I had a look through the 3.0 changes but couldn't see anything obvious that applies here?

Thanks for reporting, I'll take a look.

Fixed, thanks.

Works great, thanks for the quick response!  🙂
Issue: Compare dates
Author: loukash
State: closed
Created: 2018-04-13 13:01:49

My model:

```
class Cert(db.Model):
    not_before = DateTimeField();
    not_after = DateTimeField();

    @hybrid_property
    def is_valid(self):
        return True if (self.not_after > datetime.now()) and (self.not_before <= datetime.now()) else False

    @hybrid_property
    def valid_for(self):
        return (self.not_after - datetime.now()).days

    @valid_for.expression
    def valid_for(cls):
        return fn.AGE(cls.not_after)

    @is_valid.expression
    def is_valid(cls):
        return True if (cls.not_after - fn.SELECT(SQL('localtimestamp')) > 0) and (fn.SELECT(SQL('localtimestamp') - cls.not_before) <= 0) else False
```

I use expression based on "virtual" fields.
1st problem:

```
query = Cert.select().where(Cert.is_valid == True)
('SELECT "t1"."id", "t1"."not_before", "t1"."not_after"  FROM "cert" AS "t1" WHERE %s', [True])
```

2nd problem:
```
query = Cert.select().where(Cert.valid_for > 99)
('SELECT "t1"."id", "t1"."not_before", "t1"."not_after" FROM "cert" AS "t1" WHERE (AGE("t1"."not_after") > %s)', [99])
No operator matches the given name and argument type(s). You might need to add explicit type casts.
```
99 is int. How I can set it as postgresql interval? (interval '99 day')

Where is my mistakes?

Fix problem_1
```
	@hybrid_method
	def is_valid(self):
		return (self.not_after > datetime.now()) & (self.not_before < datetime.now())
```
`Cert.select().where(Cert.is_valid() == True)`

Here is perhaps a cleaner way of implementing `is_valid()`:

```python

    @hybrid_property
    def is_valid(cls):
        n = datetime.datetime.now()
        return self.not_before <= n <= self.not_after

    @is_valid.expression
    def is_valid(self):
        now = SQL('current_timestamp')
        return (self.not_before <= now) & (self.not_after >= now)
```

As far as "valid_for", you can pass a `datetime.timedelta` to Peewee and it should do the right thing:

```python

    @hybrid_property
    def valid_for(self):
        return (self.not_after - datetime.datetime.now()).days

    @valid_for.expression
    def valid_for(self):
        return fn.AGE(self.end)

query = Cert.select().where(Cert.valid_for > datetime.timedelta(days=99))
```

Does that address your problem? Also note you do not need to write "== True" in your expressions.

@coleifer Thank you :)
Issue: "return_id_list"  should be mentioned in 3.0 backwards-incompatible changes.
Author: aniketpanjwani
State: closed
Created: 2018-04-13 03:15:07

In 2.x, there was a "return_id_list" method for Postgresql by which you could get back the new ids created through "insert_many". It appears that this has been replaced with a more general "returning" method.

However, neither change is documented in the "Changes in 3.0" page. Happy to make a PR myself, but I just wanted to confirm first that you agreed with my assessment.

Cheers!

Ahh, you're right, sorry I missed that. I can add it.
Issue: PooledDatabase's reuse_if_open setting is not working
Author: axiaoxin
State: closed
Created: 2018-04-12 03:17:48

https://github.com/coleifer/peewee/blob/master/playhouse/pool.py#L122

the reuse_if_open params is no used

And the usage of 
    
    with db:
        do_some_query()

still raise the MySQL gone away error

f0a6f11aea19089af34befed2dab886706e1593a


Why the super class of PooledDatabase is object? there is no connect function. dose the super class is Database? 

`PooledDatabase` is used in multi-class inheritance later on in the module.
Issue: Migration leaves out foreign key constraint
Author: csytan
State: closed
Created: 2018-04-11 22:58:16

When adding a foreign key column using `playhouse.migrate`, the foreign key constraint is missing from the updated table schema. Here's some test code:

## 1. Create a test table with a working foreign key
```
# Using peewee==3.2.2

import peewee
from playhouse import migrate

database = peewee.SqliteDatabase('testdb')
database.connect()

class Table(peewee.Model):
    old_foreign_key = peewee.ForeignKeyField('self', null=True, on_delete='SET NULL')
    class Meta:
        database = database

database.create_tables([
    Table
], safe=True)

database.close()
```

## 2. Add a foreign key to the test table

```
my_db = migrate.SqliteDatabase('testdb')
migrator = migrate.SqliteMigrator(my_db)

with my_db.transaction():
    field = migrate.ForeignKeyField(
        Table, field=Table.id, null=True, on_delete='SET NULL')
    migrate.migrate(
        migrator.add_column('table', 'new_foreign_key', field)
    )

migrator.database.close()
```

## Results

Foreign key constraint is missing for the `new_foreign_key`. Expecting a `FOREIGN KEY ("new_foreign_key_id") REFERENCES "table" ("id") ON DELETE SET NULL)` clause.

<img width="682" alt="screen shot 2018-04-11 at 3 52 43 pm" src="https://user-images.githubusercontent.com/16357/38647310-740cc2ae-3da0-11e8-8c03-30fee5c0f1be.png">


As far as I know sqlite doesn't support adding constraints. Can you try executing the equivalent alter table query and see what happens?

Sure thing. It looks like `ALTER TABLE` works with the `ON DELETE` clause:

```
~/dev$ sqlite3 testdb
SQLite version 3.19.3 2017-06-27 16:48:08
Enter ".help" for usage hints.
sqlite> PRAGMA foreign_keys = ON;
sqlite>
sqlite> CREATE TABLE IF NOT EXISTS "table" (
   ...>    id INTEGER PRIMARY KEY AUTOINCREMENT
   ...> );
sqlite>
sqlite> ALTER TABLE "table" ADD COLUMN "fk" INTEGER REFERENCES "table"(id) ON DELETE SET NULL;
sqlite>
sqlite> .schema table
CREATE TABLE IF NOT EXISTS "table" (
   id INTEGER PRIMARY KEY AUTOINCREMENT
, "fk" INTEGER REFERENCES "table"(id) ON DELETE SET NULL);
sqlite>
```



I believe this should be fixed in 449450d738a8c8f2893e5309d03d03bc249e9d7e

Added test-case which shows the "ON DELETE" bits are being carried-over into the migration. Thanks for reporting.

Thanks @coleifer. I've tested on master and it's working correctly now 👍
Issue: Model.create() option to skip saving immediately
Author: vadimkantorov
State: closed
Created: 2018-04-09 15:23:20

From what I can see in the source code, there is no option to postpone saving:
https://github.com/coleifer/peewee/blob/master/peewee.py#L5160

How do I postpone saving? I wish to do some object modification and having a single db request would be more economical.

Should I wrap the `create` call in `db.atomic()`? Like in https://github.com/coleifer/peewee/blob/master/peewee.py#L5214

Currently I wrap it with the following falcon-middleware:
```python
class TransactionPerRequest(object):
    def process_request(self, req, resp):
        self.db.connect()
        self.db.begin()

    def process_response(self, req, resp, resource):
        if not self.db.is_closed():
            self.db.commit()
            self.db.close()

    def __init__(self, db):
        self.db = db
```

but the model is not updated, unless `save()` is called explicitly again after `create()`.

Thanks!

Well... you can wrap your operations in a transaction, using `atomic()`. `atomic()` is a context-manager or decorator, but if you want to use it in the above middleware-type API, you should be able to:

```python

class TransactionPerRequest(object):
    def process_request(self, req, resp):
        req._txn = self.db.atomic()
        req._txn.__enter__()

    def process_response(self, req, resp, resource):
        if not self.db.is_closed():
            req._txn.__exit__(None, None, None)
            self.db.close()

    def __init__(self, db):
        self.db = db
```

Will this also prevent (non-null, etc) constraints from being validated on creation time? A way to enable a delay to actual saving time would be great.

> Will this also prevent (non-null, etc) constraints from being validated on creation time?

It should, as the INSERTs are executed -- they are simply executed in a transaction which is not committed until the `atomic` block exits (which is in the process_response example).

If you're trying to postpone actually inserting the records (and dealing with any integrity-errors, etc) -- I'd suggest using a Queue and pushing the models into it, then at the end of the request consume the queue and save all the models.