Piccolo uses a class called QueryString internally for composing queries. It's very powerful, and allows you to compose complex nested queries, and compiles to a parameterised SQL statement and values.
At the moment, if someone wants to compose their own query, they have to use raw. For example await Band.raw('some custom query').
As a middle ground, we should allow the user to compose their own queries using QueryString. This is currently possible, but it's undocumented, and the API isn't pretty.
For example, if we wanted to modify an INSERT query to add an ON CONFLICT clause:
from piccolo.querystring import QueryString
# Assuming `Manager` is your table class, and `some_unique_column` is a unique column name:
querystring = QueryString(
"{} ON CONFLICT (some_unique_column) DO NOTHING",
Manager.insert(Manager(name='Guido')).querystrings[0]
)
await Manager._meta.db.run_querystring(querystring)We could directly expose a way of running QueryString, either by modifying Table.raw so it can accept a string or QueryString. Alternatively, we could add a new method (something like run_querystring).
from piccolo.querystring import QueryString
querystring = QueryString(
"{} ON CONFLICT (some_unique_column) DO NOTHING",
Manager.insert(Manager(name='Guido')) # We should allow a query to be passed in, and auto extract the querystring from it.
)
await Manager.run_querystring(querystring)This will need documenting, possibly under the same section of the docs which covers raw queries.
Originally discussed in #403 and #405.
Piccolo uses a class called
QueryStringinternally for composing queries. It's very powerful, and allows you to compose complex nested queries, and compiles to a parameterised SQL statement and values.At the moment, if someone wants to compose their own query, they have to use
raw. For exampleawait Band.raw('some custom query').As a middle ground, we should allow the user to compose their own queries using
QueryString. This is currently possible, but it's undocumented, and the API isn't pretty.For example, if we wanted to modify an
INSERTquery to add anON CONFLICTclause:We could directly expose a way of running
QueryString, either by modifyingTable.rawso it can accept a string orQueryString. Alternatively, we could add a new method (something likerun_querystring).This will need documenting, possibly under the same section of the docs which covers raw queries.
Originally discussed in #403 and #405.