execute_sql escaping

[danem]

I couldn't find a lot of documentation regarding this subject, but how can I bind lists to a custom sql query using execute_sql?

 models._database.execute_sql(

               """

               INSERT INTO artifactsubjecttag (artifact_id, subject_id)

               SELECT artifact.id, artifactsubject.id

                FROM artifact JOIN artifactsubject

               ON artifact.art_id == (?) AND artifactsubject.name IN (?)

               """, (art_id, ["foo","bar"])

           )

This doesn't work for me.

Thanks!

[danem]

And to be more clear, I get the following error:

InterfaceError: Error binding parameter 1 - probably unsupported type.

During handling of the above exception, another exception occurred:

I suspect it's due to the inclusion of a list.

Sorry I wasn't more clear earlier.

Thanks

[Radek Skiba]

My first question is, why You even need to execute the "custom query". There is already method implemented for what you want to do
And it's called "insert_from".

[Charles Leifer]

Peewee does something nice where if you pass a list it will expand the parameter interpolation characters and then flatten the actual values into a list.

So your example the problem is the IN clause, which you're representing as "?" and ['foo', 'bar']

Should be: IN (?, ?) and your param list should be (art_id, 'foo', 'bar').

Best plan is to try to use Peewee's query-builder, though, as it takes care of everything. Also supports INSERT INTO ... SELECT FROM.

http://docs.peewee-orm.com/en/latest/peewee/api.html#Model.insert_from

http://docs.peewee-orm.com/en/latest/peewee/querying.html#bulk-loading-from-another-table

On Wed, Mar 18, 2020 at 7:59 PM Radek Skiba <mak...@gmail.com> wrote:

My first question is, why You even need to execute the "custom query". There is already method implemented for what you want to do
And it's called "insert_from".

--
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/e7f5fbfe-09f7-418f-aa05-953ef1b9323c%40googlegroups.com.