str representation of query not compatible with Table.raw when using uuid/date columns

[theelderbeever]

Version info:
piccolo = "0.64.0"

I am attempting to use some of the query building tools to generate custom complex queries that aren't directly possible through the ORM at the moment. During this I ran into a case where Attempting to run the str(MyTable.insert(MyTable())) doesn't seem to work with uuid or date columns as they aren't wrapped in ''.

I believe the following minimal example should work

from datetime import datetime

from piccolo.columns import UUID, Timestamptz
from piccolo.table import Table


class Table(Table):

    uuid = UUID(primary_key=True)
    created_at = Timestamptz(default=datetime.now, index=True)
    updated_at = Timestamptz(default=datetime.now, index=True)


# Instantiate an instance
table = Table()
Table.raw(str(Table.insert(table))).run_sync()

It is contrived but the query fails with a SQL error midway through the UUID string that gets output. The uuid value should be wrapped in '' I believe.

Example Error

PostgresSyntaxError: syntax error at or near "fa"

This seems to come down to how the Table.querystring is formed. The values don't get wrapped and the datetime doesn't replace the ' ' with a T. Relevant Code

>>> print(table.querystring)
(3a47b208-4834-4d0d-9213-db3511a52886,2022-01-25 16:58:44.238463+00:00,2022-01-25 16:58:44.238470+00:00)

Ultimate goal here is that I am writing some custom on conflict statements and was taking inspiration from the Insert.postgres_querystrings method to construct them when I ran into this.

[dantownsend]

@theelderbeever A solution which might work for you is something like:

from piccolo.querystring import QueryString

# Assuming `Manager` is your table class, and `some_unique_column` is a unique column name:
query = QueryString(
    "{} ON CONFLICT (some_unique_column) DO NOTHING",
    Manager.insert(Manager(name='Guido')).querystrings[0]
)
await Manager._meta.db.run_querystring(query)

Internally Piccolo uses this QueryString class to compose queries, and then compiles them into a parameterised SQL statement, and values.

We don't really expose the ability to run these QueryString objects in an elegant way, but they are the best solution for composing your own queries. We could probably modify raw to accept a Querystring, or add a new method called run_querystring. So the API would be:

await Manager.run_querystring(querystring)

[theelderbeever]

Unfortunately the insert appears to only generate one querystring so a .querystrings[0].split("RETURNING")[0] still needs to be performed.

[dantownsend]

Ah, you're right. I originally tested it on SQLite, and it didn't have this problem.

There's an open PR for adding support for a returning clause: #203

So in the future it will be possible to omit the returning clause, but for now it isn't.

[dantownsend]

Someone started added ON CONFLICT support, but the PR stalled. It is something we'll add at some point.

But there are always edge cases which the ORM can't account for, so we need a better solution for people being able to modify their queries.