Unparsed backref accessor causing peewee.InterfaceError: Error binding parameter?

[Linus]

Hello,

I'm having a problem where I'm getting the error "peewee.InterfaceError: Error binding parameter 0 - probably unsupported type.".

This is because of a prefetch query where peewee is trying to run

('SELECT "t1"."id", "t1"."name", "t1"."vanity_text" FROM "item_set" AS "t1" WHERE ("t1"."id" IN (SELECT ? FROM "item_design" AS "t2"))', [<peewee.BackrefAccessor object at 0x7fede2b6d9a0>])

where the BackrefAccessor is an invalid value I guess? It should just

I'm unsure if it's a bug or a mistake on my part. I've tried some good debug-printing within the peewee source and the line

fk_field = getattr(last_obj, backref.name)

in prefetch_add_subquery command returns the BackrefAccessor instead of an actual value. Any ideas on how to proceed?

[Charles Leifer]

Can you share some models and code representative of what you're trying to do?

--
You received this message because you are subscribed to the Google Groups "peewee-orm" group.
To unsubscribe from this group and stop receiving emails from it, send an email to peewee-orm+...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/peewee-orm/edccae38-b250-4d84-a056-161074833eaan%40googlegroups.com.

[Linus]

Yes, absolutely!

Simplifying the use, I basically have 2 models: ItemDesign and ItemSet, where an ItemDesign can have multiple item sets. ItemDesign have a lot of other FK relationships that works without a hitch. I'm using sqlite 3.22.

The code I run is a simple select query, where I want to preload data for the ItemDesign classes. Removing all other prefetch querying, the minimal example bugging is this:

prefetch(ItemDesign.select(), ItemSet.select())

The classes looks like this:

from playhouse.flask_utils import FlaskDB

database = FlaskDB()

class BaseModel(database.Model):
    class Meta:
        legacy_table_names = False

class BaseUUIDModel(BaseModel):
    id = UUIDField(primary_key=True, default=uuid.uuid4)

----

from peewee import CharField, TextField, ForeignKeyField, IntegerField
from playhouse.sqlite_ext import JSONField

class ItemDesign(BaseUUIDModel):
    name = CharField()
    rarity = CharField(choices=Rarity.choices_list(), default=Rarity.Normal)
    stats = JSONField()
    vanity_text = TextField(null=True)
    icon = ForeignKeyField(Icon, backref='item_uses')
    item_set = ForeignKeyField(ItemSet, backref='items', null=True, on_delete='SET NULL')
    item_slot = IntegerField(choices=ItemSlot.choices_list())
    item_type = ForeignKeyField(ItemType, backref='items', on_delete='SET NULL')

----

from peewee import CharField, TextField

class ItemSet(BaseUUIDModel):
    name = CharField(unique=True)
    vanity_text = TextField(null=True)

[Charles Leifer]

Before I dive into this, since you mentioned Sqlite, it's worth pointing out that the "N+1" problem doesn't really exist in Sqlite - often negating any performance benefit you intend to realize by using prefetch:

https://sqlite.org/np1queryprob.html

To view this discussion on the web visit https://groups.google.com/d/msgid/peewee-orm/2a9a8295-3aeb-4998-98a5-5d7f0d4f218dn%40googlegroups.com.

[Linus]

Yes, I actually read up on that yesterday. I'm planning to switch to postgres but as it's just a hobby project I haven't gotten around to it yet.

Anyway I did some more digging since this just showed up recently. I pinpointed the problem to a new model Item (acting like an "instance" of the idem design) looking like this:

class Item(BaseUUIDModel):
    item_design = ForeignKeyField(ItemDesign)

If I comment out that item_design field, or add a backref argument,  the prefetch query works. But looking like that it doesn't. To me it's weird since the class is seemingly unrelated to the prefetch-query, maybe a naming conflcit or something? I guess you would know the internal working of it better hehe

[Charles Leifer]

Ah yes! Thanks, the default backref would be specified as ItemDesign.item_set which obviously conflicts with your foreign-key of the same name.

I'd suggest just explicitly specifying a backref, e.g. "items", and you should be good-to-go.

To view this discussion on the web visit https://groups.google.com/d/msgid/peewee-orm/a88add6e-b108-4b50-8cf4-7e576ef47d6cn%40googlegroups.com.

[Linus]

Right, makes sense! A confusing error to be sure, but maybe it's hard to give a better one?

Anyhow, thanks for your help and a really nice library!