Expose an API for running `QueryString`

[dantownsend]

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 https://github.com/piccolo-orm/piccolo/discussions/403 and https://github.com/piccolo-orm/piccolo/pull/405.

[theelderbeever]

I think it would be really helpful to be able to nest QueryString objects as well. This would make passing the parameters easier to construct as well and I think would help with making helper functions down the road. Right now if you do something like the following...

querystring = QueryString("INSERT INTO managers (name, salary, json_data)  VALUES ({},{},{})", "Bob", 100_000, '{"hello": "world"}')

querystring_w_conflict = QueryString("{} ON CONFLICT (name) DO NOTHING", querystring)

The second QueryString interpolation will turn the json data into '' a blank string.