akumoth/peewee-issues huggingface dataset:
Issue: ForeignKeyFields out of sync?
Author: oz123
State: closed
Created: 2020-02-11 14:41:42

At the risk of sounding stupid, I don't know how to give this issue a title.

I have the following database schema:

```python
class UserMessage(BaseModel):
    uuid = UUIDField(index=True)
    link = UUIDField(index=True)
    email = CharField()
    name = CharField()
    message = CharField()
    timestamp = DateTimeField(default=datetime.datetime.utcnow)
    # files attribute is a foriegnkey on UploadedEncryptedFile

    @property
    def full_message(self):

        message = "Hi you got the following message from "
        message += self.name + ":\n"
        message += self.message

        logg.info("found %d files", self.files.count())

        if self.files.count():
            message += "\n\nAttched files:\n"
            url = "{APP_URL}/files/{link}".format(
                    **{"APP_URL": APP_URL, 'link': self.link})

            message += url

        return message


class UploadedEncryptedFile(BaseModel):
    name = CharField()
    file_content = BlobField()
    user = ForeignKeyField(UserMessage, backref='files', on_delete='CASCADE')


class User(BaseModel):
    name = CharField(unique=True)  # adding unique true creates an index
    email = CharField(255)
    password = BlobField(255)
```

When a UserMessage is created it gets the wrong id:
```sql
sqlite> SELECT id from usermessage;
id
9
sqlite> SELECT user_id FROM uploadedencryptedfile;
user_id
1
sqlite>
```

Could peewee doing something wrong here because there is a table called user?


usermessage is not user? I think you have problems with your schema. Peewee is not fundamentally broken, if that's what you're suggesting.

Try stackoverflow for questions like these in the future.

I am not suggesting peewee is broken. I am just trying to figure what is going on here. 
I being bugged by these issue for quite a while and I have no clue how to figure this out. Maybe I need to fix my schema. But I don't where to start. Is this really because I have a table called user?

Your schema is messed up is what I'm trying to suggest. Why would the usermessage's id be the same as the user's id? There's not even a foreign-key to the user table anywhere in your schema.

>because I have a table called user

No.

I dumped the database and re-imported it with an ID 1 instead of 9 (after editing the sql file). I don't know if it is a good idea. Maybe it's a patchy solution. 
By saying the schema is messed up you mean that the data was corrupt? Could it be because I made some changes without peewee?
Alternatively, by saying `Your schema is messed up` do you mean I need to change my models in Python?
I am sorry if these are trivial questions for you. This project is the first project I did with peewee so it's not trivial for me.
Issue: Add `.union` to the CTE class
Author: aconz2
State: closed
Created: 2020-02-10 20:19:41

This complements the existing `union_all` method for CTE's.

`tests/sql.py` is purely syntactic b/c the following are identical
```
 echo 'WITH RECURSIVE "fibonacci" AS (SELECT 1 AS "n", 0 AS "fib_n", 1 AS "next_fib_n" UNION ALL SELECT ("fibonacci"."n" + 1) AS "n", "fibonacci"."next_fib_n", ("fibonacci"."fib_n" + "fibonacci"."next_fib_n") FROM "fibonacci" WHERE ("n" < 10)) SELECT "fibonacci"."n", "fibonacci"."fib_n" FROM "fibonacci";' | sqlite3
```
```
 echo 'WITH RECURSIVE "fibonacci" AS (SELECT 1 AS "n", 0 AS "fib_n", 1 AS "next_fib_n" UNION SELECT ("fibonacci"."n" + 1) AS "n", "fibonacci"."next_fib_n", ("fibonacci"."fib_n" + "fibonacci"."next_fib_n") FROM "fibonacci" WHERE ("n" < 10)) SELECT "fibonacci"."n", "fibonacci"."fib_n" FROM "fibonacci";' | sqlite3
```
`tests/models.py` adds a semantic test on the transitive closure of a directed graph. I tried to stay within the existing test models but needed a non-tree to be able to test properly.

Let me know if you'd like anything else from this




UNION ALL is provided because it is needed for writing recursive queries, so the omission was somewhat intentional.

And UNION is required for writing other recursive queries. I added another small example to further illustrate the difference [here](https://github.com/aconz2/peewee/blob/feature/cte-union/tests/models.py#L2829-L2839) 



Thanks, I've merged an equivalent patch.

Awesome, much appreciated
Issue: inserting silently fails
Author: DavHau
State: closed
Created: 2020-02-09 19:23:42

python: 3.7
peewee: 3.13.1

The following code fails to insert without any notice:
```python
from peewee import *

db = SqliteDatabase(f'test.db')

class BaseModel(Model):
    class Meta:
        database = db

class Script(BaseModel):
    name = CharField()
    version = CharField()
    script = TextField(null=True)
    class Meta:
        primary_key = CompositeKey('name', 'version')

db.connect()
db.create_tables([Script])

Script(name="test", version="1.1.1", script="test script").save()
print(Script.select().exists())
```

When you have a primary key that is not autogenerated, you need to specify `force_insert=True` when saving.

http://docs.peewee-orm.com/en/latest/peewee/models.html#id4

Here's my advice: don't use composite keys. Just use the standard autoinc primary key and use a multi-column unique constraint. You'll have a **much** better time.

Peewee doesn't support foreign keys to composite primary keys, either, which sometimes trips people up.

Lastly, Google before you post, because this is heavily traveled ground
Issue: LATERAL join without on=
Author: albireox
State: closed
Created: 2020-02-04 08:43:01

I'm trying to do the following query

```python
class Sky(peewee.Model):

    pk = peewee.PrimaryKeyField()
    ra = peewee.FloatField()
    dec = peewee.FloatField()

    class Meta:
        db_table = 'sky'
        database = database

# Select a sample here. For now use just a filter on pk for testing.
sample = (Sky
          .select()
          .where(Sky.pk > 200000)
          .cte('sample'))

subq = (Sky
        .select(sample.c.pk)
        .where(
            peewee.fn.q3c_join(sample.c.ra, sample.c.dec, Sky.ra, Sky.dec, 1. / 600))
        .order_by(
            peewee.fn.q3c_dist(sample.c.ra, sample.c.dec, Sky.ra, Sky.dec).asc())
        .limit(1)
        .offset(1))

ss = (sample
      .select_from(sample.c.pk, subq.c.pk)
      .join(subq, peewee.JOIN.LEFT_LATERAL)
      .where(subq.c.pk.is_null())
      .with_cte(sample))

ss.execute()
```

This query fails with 

```
peewee.ProgrammingError: syntax error at or near "WHERE"
LINE 1: ..."."ra", "t3"."dec") ASC LIMIT 1 OFFSET 1) AS "t2" WHERE ("t2...
```

If I add an `on=true` to the left lateral join, that translates into an `ON 1` in the SQL query which fails with 

```
ERROR:  argument of JOIN/ON must be type boolean, not type integer
LINE 1: ...ra", "t3"."dec") ASC LIMIT 1 OFFSET 1) AS "t2" ON 1 WHERE ("...
```

If I do something like `on='true'` that works, I assume because it's always evaluated as true. I wonder if there's a better way of doing this or if peewee should add the `ON true` automatically.

Also, I'm not sure if the example [here](http://docs.peewee-orm.com/en/latest/peewee/hacks.html#postgres-lateral-joins) is incomplete since it does not include an `on=` clause.



Can you share your desired SQL?

Possibly there's some quirk in Postgresql's query grammar that requires the "ON true" clause. That's rather annoying that it's so particular about it -- but for now I'll suggest using `SQL('true')` until I can look into this a bit more.

The desired SQL would be 

```
WITH "sample" AS (
    SELECT "t1"."pk", "t1"."ra", "t1"."dec" FROM "sky" AS "t1" WHERE ("t1"."pk" > 200000)) 
SELECT "sample"."pk", "t2"."pk" FROM "sample" LEFT JOIN LATERAL (
    SELECT "sample"."pk" FROM "sky" AS "t3" WHERE (("t3"."pk" IN (SELECT * FROM "sample")) AND q3c_join("sample"."ra", "sample"."dec", "t3"."ra", "t3"."dec", 0.0016666666666666668)) ORDER BY q3c_dist("sample"."ra", "sample"."dec", "t3"."ra", "t3"."dec") ASC LIMIT 1 OFFSET 1) 
AS "t2" ON true WHERE ("t2"."pk" IS NULL)
```

The only think that doesn't seem to be output is the `ON true`.

I agree that using `SQL('true')` is better than the `'true'` string and it fixes the problem for me. 

Ahhh, I think I know what the issue is!

This was only fixed recently, in 8829c948 -- which will automatically add the "on true" expression whenever you use a lateral join. So you might try upgrading to 3.13 or newer.
Issue: Recursive save
Author: ZhaofengWu
State: closed
Created: 2020-02-02 01:42:43

Is there a convenient function to recursively save a nested object? I'm using [`dict_to_model`](http://docs.peewee-orm.com/en/latest/peewee/playhouse.html#dict_to_model) that produces a nested object, and calling `save` on the returned object seems to only save the top-level one.

It's pretty simple - check out: https://github.com/coleifer/flask-peewee/blob/master/flask_peewee/rest.py#L419 for an example of how flask-peewee's REST module handles it.

You could also write something recursive. To get the relations, just look at the `model._meta.refs` dict, which maps field instances to related models.

Untested:

```python

accum = [obj]
while accum:
    curr = accum.pop()
    curr.save()
    for fk in curr._meta.refs:
        rel_obj = getattr(curr, fk.name, None)
        if rel_obj is not None:
            accum.append(rel_obj)
```

It looks like this does successfully save all the objects, but it doesn't seem to properly save the foreign key relations in the database. Do you know why that is?

You probably aren't setting things properly, I have no idea - check your code.
Issue: About optional field
Author: ZhaofengWu
State: closed
Created: 2020-01-31 06:17:53

If I want to designate some `Field` as being optional, in its `__init__` should I use `null=True`, or set `default` to some value (`"NULL"` or `None`)? Or should I be using both of these two options at the same time?

If a field is optional, use `null=True`. It will default to an empty (NULL) value.
Issue: Just the first row can be inserted by insert_many(). The database I uesd is postgresql
Author: TraderWithPython
State: closed
Created: 2020-01-27 13:04:23

The model is

        class DbTick(Model):
            instrument_id: str = CharField() 
            datetime: datetime = DateTimeField() 

            bid_price1: float = FloatField()
            bid_price2: float = FloatField(null=True)
            bid_price3: float = FloatField(null=True)
            bid_price4: float = FloatField(null=True)
            bid_price5: float = FloatField(null=True)

            ask_price1: float = FloatField()
            ask_price2: float = FloatField(null=True)
            ask_price3: float = FloatField(null=True)
            ask_price4: float = FloatField(null=True)
            ask_price5: float = FloatField(null=True)

            bid_volume1: float = FloatField()
            bid_volume2: float = FloatField(null=True)
            bid_volume3: float = FloatField(null=True)
            bid_volume4: float = FloatField(null=True)
            bid_volume5: float = FloatField(null=True)

            ask_volume1: float = FloatField()
            ask_volume2: float = FloatField(null=True)
            ask_volume3: float = FloatField(null=True)
            ask_volume4: float = FloatField(null=True)
            ask_volume5: float = FloatField(null=True)

            last_price: float = FloatField() 
            highest: float = FloatField() 
            lowest: float = FloatField() 
            open: float = FloatField()
            close: float = FloatField()
            average: float = FloatField()

            volume: float = FloatField()
            amount: float = FloatField()
            open_interest: float = FloatField() 

            upper_limit: float = FloatField()
            lower_limit: float = FloatField() 

            pre_open_interest: float = FloatField() 
            pre_settlement: float = FloatField()
            pre_close: float = FloatField() 

            price_tick: float = FloatField() 
            volume_multiple: int = IntegerField() 
            ins_class: str = CharField()
            expire_datetime: datetime = DateTimeField() 
            expired: int = IntegerField() 

            class Meta:
                database = db
                primary_key = CompositeKey('instrument_id', 'datetime')  

And I insert data by the method insert_many(),

        with self.db.atomic() as transaction: 
            for batch in chunked(ticks, 100): 
                 self.DbTick.insert_many(batch).on_conflict_ignore().execute()

The result is that only the first row in the ticks can be inserted. This code is wrapped in a coroutine.

Thanks for helping

I'm not sure what to tell you, but I've added a (passing) unit-test which shows that this is working correctly in my test setup. If you can provide me a minimal failing test-case I will re-examine, but I think the problem is not with Peewee.
Issue: Q: How to defer SqliteQueueDatabase?
Author: BoboTiG
State: closed
Created: 2020-01-24 16:52:31

Hello,

This is a very great module, thanks for that :muscle: 

I am trying to defer `SqliteQueueDatabase` initalization as the database filename is only known at run-time. This is the code:
```python
# models.py
PRAGMAS = (("foreign_keys", 1), ("journal_mode", "wal"), ("synchronous", 3))
DATABASE = SqliteQueueDatabase(None, autostart=False, pragmas=PRAGMAS)

class BaseModel(Model):
    class Meta:
        database = DATABASE
        only_save_dirty = True
```
And elsewhere I set the real database file:
```python
# file.py
db = "my-file.db"
DATABASE.init(db, pragmas=PRAGMAS)  # I am not sure if pragmas is useful?
DATABASE.start()
DATABASE.connect()
DATABASE.create_tables(MODELS)
```

Unfortunately, I cannot manage to make the code works: tables are not created.
Note that the same code (minus `DATABASE.start()`) works like a charm if I use `SqliteDatabase`.

Do you have a clue what I am doing wrong?

Thanks a lot :)

The tables *are* created, but since this multi-threaded, their creation may be deferred for a moment.

For example:

```python

db = SqliteQueueDatabase(None)

class Reg(Model):
    key = TextField()
    value = IntegerField()
    class Meta:
        database = db

db.init('foo.db')
db.start()
db.connect()
db.create_tables([Reg])

print(db.database)
print(db.get_tables())
```

The above code may print:

```
foo.db
[]
```

Which indicates that the tables aren't created immediately (though they *are* created as soon as the running thread switches). It seems you have forgotten, maybe, that python is single-threaded?

To force a context-switch, you can introduce a tiny sleep(), which will switch contexts and then show you the expected output:

```python

print(db.database)
import time; time.sleep(0.01)
print(db.get_tables())
```

This prints the expected:

```
foo.db
['reg']
```

I think it does not work because the parent's thread is a QThread.
I also tried those snippets (just before the `time.sleep()` trick) without luck:

```python
from PyQt5.QtCore import QCoreApplication
QCoreApplication.processEvents()
```
```python
from PyQt5.QtCore import QTimer
timer = QTimer()
timer.timeout.connect(lambda: None)
timer.start(100)
```

Do you know some workaround?

how other resolve this question?

import time; time.sleep(0.01)





this is my code:

it is ok, `sleep()`  is primary code.

```python
# -*- coding: utf-8 -*-
import time

from peewee import TextField, AutoField, Model
from playhouse.sqliteq import SqliteQueueDatabase

db = SqliteQueueDatabase(database='database.db')


class UserModel(Model):
    id = AutoField()
    name = TextField()

    class Meta:
        database = db

        table_name = 'tb_user'


# cerate
db.create_tables([UserModel])

# wait
time.sleep(0.01)

# query
row = UserModel.select('id').where(
    UserModel.id, '=', 1
).get_or_none()

print(row)
# None

```

You could try using a barrier or an event. See https://docs.python.org/3/library/threading.html
Issue: Array aggregate with ORDER BY clause
Author: iksteen
State: closed
Created: 2020-01-24 14:39:05

Postgresql supports ordered array aggregation in the form of:
`ARRAY_AGG(expression [ORDER BY [sort_expression {ASC | DESC}], [...])`

The way I currently implement it:
```
fn.array_agg(NodeList([
    subscriptions_q.c.topic_id,
    SQL("ORDER BY"),
    popularity_q.c.popularity.asc(),
]))
```

Do you happen to know a nicer way of implementing array_agg's order by clause using peewee? If not, would you be willing to accept a PR that adds a function that adds this functionality to f.e. playhouse.postgres_ext?

The method you're describing is an acceptable way of handling this. I'm going to pass on implementing a fix right now, but may reconsider it later.

Actual fix is now in: fe84246980d0b68290db9566b40dad446eefd600

That's awesome! Thanks for the quick response (and implementing the feature!).
Issue: join model with prefetched another model
Author: senya96
State: closed
Created: 2020-01-23 16:16:11

Hello.
I have 3 models.
```python
class Model1(peewee.Model):
	model2 = ForeignKeyField(Model2)

class Model2(peewee.Model):
	pass

class Model3(peewee.Model):
	model2 = ForeignKeyField(Model2)
```

I want to select all data from **Model1** joined with **Model2** and prefetch all **Model3-objects** to every **Model2-object**.
I don't understand how I can do it.

Thank you.
Issue: Select the nth element of an array created dynamically in a query
Author: bdoms
State: closed
Created: 2020-01-21 22:26:49

Love peewee, using the most recent version (3.13.1) and I've run into a situation causing an error. I may not have the right syntax, but after scouring the web and the docs and the source code I can't find it if it exists.

I'm trying to mimic this SQL, which works in Postgres:

```sql
select (string_to_array(page.url, '/'))[2] from page;
```

Getting array elements from fields using the `ArrayField` extension works, but when the arrays are created with `string_to_array` the way to access a specific element is not obvious.

Here's the setup:

```python
from peewee import Model, fn, CharField
from playhouse.postgres_ext import PostgresqlExtDatabase

peewee_db = PostgresqlExtDatabase('mydbname')

class Page(Model):
    url = CharField(null=True)

    class Meta:
        database = peewee_db

peewee_db.connect()

Page.select(fn.STRING_TO_ARRAY(Page.url, '/')[2]).execute()
```

And it produces this output:

```
peewee.ProgrammingError: operator does not exist: text[] = integer
LINE 1: SELECT (STRING_TO_ARRAY("t1"."url", '/') = 2) FROM "page" A...
                                                  ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
```

If there is a cast or function that can get the nth item out in a situation like this I would really appreciate the help in figuring it out. The types `text[]` and `integer` look right to me, which I think is where some of the confusion is coming from.

You need a couple things. It seems postgres *requires* the additional parentheses around the function invocation -- which is annoying but workable.

```python

from peewee import EnclosedNodeList
from playhouse.postgres_ext import ObjectSlice

# Function invocation - nothing weird here.
f = fn.string_to_array(Page.url, '/')

# Wrap function invocation in additional parentheses.
fp = EnclosedNodeList([f])

# Indexing operation. Postgres uses 1-based indexes, so 1->2.
# This addition happens in Peewee to allow Python code to emulate
# python's 0-based indexing.
item = ObjectSlice(fp, [1])

query = Page.select(item.alias('url_part'))
```
Issue: prefetch causes fields to be added in dirty_fields
Author: navolotsky
State: closed
Created: 2020-01-21 11:17:10

`prefetch` causes fields related to prefetched models to be added in `dirty_fields`.

Could you say why this is so? Is it not possible to make another implementation?

Thanks so much for the answer and excellent ORM.

@coleifer Please check it. I got this behavior again on Peewee 3.13.3, Python 3.8.2 (tags/v3.8.2:7b3ab59, Feb 25 2020, 23:03:10) [MSC v.1916 64 bit (AMD64)] on win32.

The regression test is passing fine for me. If you add a failing test, I will look into this again.

```python
from peewee import CharField, ForeignKeyField, Model, SqliteDatabase

database = SqliteDatabase(":memory:")


class A(Model):
    class Meta:
        database = database
        only_save_dirty = True
    some_str = CharField()


class B(Model):
    class Meta:
        database = database
        only_save_dirty = True
    another_str = CharField()
    a_instance = ForeignKeyField(model=A)


if __name__ == "__main__":
    database.create_tables(models=[A, B])
    a = A.create(some_str='s1')
    B.create(another_str='s2', a_instance=a)
    b1 = B.select().where(B.another_str == 's2').execute()[0]
    print(f"Without `prefetch`, dirty fields: {b1.dirty_fields}")
    b2 = B.select().where(B.another_str == 's2').prefetch(A)[0]
    print(f"With `prefetch`, dirty fields: {b2.dirty_fields}")

```
![изображение](https://user-images.githubusercontent.com/43314216/82337128-144ecd00-99f4-11ea-84d2-783ec280771d.png)


OP DELIVERS. Thank you!

Hey - I think I see the problem. There's absolutely no reason to use `prefetch` when going from a child-model to a parent-model, as SQL already handles that. Just do:

```python

query = B.select(B, A).join(A).where(B.another_str == 's2')
```
Issue: AttributeError: 'str' object has no attribute 'unwrap'
Author: Rizhiy
State: closed
Created: 2020-01-20 16:41:28

I'm trying to retrieve rows from the database and get this error.
My table looks as follows:
```python
class Post(Model):
    date = DateField()
    user = ForeignKeyField(User, backref='numbers')
	number1 = DecimalField(6,2)
    number2 = DecimalField(6,2)

    class Meta:
		database = SqliteDatabase('data.db')
        primary_key = CompositeKey('date', 'user')
```

What can be the problem?

Paste the full traceback.

Oh, I see - you're using `Decimal` instead of `DecimalField`. That's likely the issue.

That's mistake in the code here. In my file it is correct.

Then paste the full traceback??

```
Traceback (most recent call last):
  File "tmp.py", line 22, in <module>
    for stuff in Post.select('date', 'user', 'number1'):
  File "/home/rizhiy/anaconda3/envs/data/lib/python3.7/site-packages/peewee.py", line 4287, in next
    self.cursor_wrapper.iterate()
  File "/home/rizhiy/anaconda3/envs/data/lib/python3.7/site-packages/peewee.py", line 4203, in iterate
    self.initialize()  # Lazy initialization.
  File "/home/rizhiy/anaconda3/envs/data/lib/python3.7/site-packages/peewee.py", line 7251, in _initialize_columns
    node = raw_node.unwrap()
AttributeError: 'str' object has no attribute 'unwrap'
```

You need to read the docs. It's nowhere indicated that you pass string field-names into the select() method. You use the field instances themselves.

```python
Post.select(Post.date, Post.number1, ...)
```

Also selecting Post.user will just grab the user_id.