Escaping identifiers for safe dynamic SQL in Python for PostgreSQL

[michalc]

Is there a way to safely construct dynamic SQL for PostgreSQL in Python using ADBC?

I think I’m essentially looking for the equivalent of psycopg’s sql module for ADBC. Or maybe a way of using psycopg’s sql module with ADBC?

[zeroshade]

ADBC doesn't provide a module for dynamic SQL construction, but anything which can generate the query string could be used with ADBC (just as it could with standard DBAPI functions). In the case of the psycopg sql module you linked to, it appears to have an as_string() method that could generate the full query string which could then be passed to cur.execute(...) with ADBC.

The same would be true for things like sqlglot and so on which can generate the SQL query string for you.

[lidavidm]

It's also possible to use ADBC with things like SQLAlchemy. If I can find the time I'd like to put together a PostgreSQL dialect for use with ADBC.

[lidavidm]

I also wouldn't necessarily be opposed to adding quoting utilities, but nothing as sophisticated as what psycopg has. #1398

[michalc]

In the case of the psycopg sql module you linked to, it appears to have an as_string() method that could generate the full query string which could then be passed to cur.execute(...) with ADBC.

Ah! I was about to say "but as_string requires a very psycopg-y 'context' object" but I see it's now optional (and since it's a protocol could also construct something more adbc-y if needed...)

Thank you!