Is there a way to convert these identifiers to a PostgreSQL query using psycopg2:
total_query_fields = (
'p.id',
'p.name',
'p.type',
'p.price',
'o.date', # please, notice the 'o' alias here
'o.transaction', # please, notice the 'o' alias here
'p.warehouse',
'p.location',
)
# they may get split into their own tables if necessary:
product_query_fields = ('id', 'name', 'type', 'price', 'warehouse', 'location',)
order_query_fields = ('date', 'transaction',)
into something like this:
import psycopg2
from psycopg2 import sql
myid = 100
sql_query = sql.SQL("""
SELECT {fields} FROM product p
INNER JOIN owner o ON p.id = o.product_id
WHERE p.id = {jidx} AND (o.dateof_purchase IS NOT NULL
OR o.state = 'checked_out' );"""
).format(
fields = # there should be the readable ***magic***
jidx = sql.Literal(myid)
)
?
Even after understanding my problem by reading this thread, I'm not able to figure out a nice way for getting the list of my fields using a dotted notation. There must probably be two map() used, I guess, and both an sql.SQL('.').join(...) and sql.SQL(', ').join(...).
Or maybe something more elegant, e.g. using SELECT {}.{}...?
Because for the moment I'm having trouble with that:
fields = sql.SQL(', ').join(map(sql.Identifier, total_query_fields)),
because it will escape all the sequences "table.fields" with double quotes, which is definitely not valid in SQL:
# /!\ INVALID SQL QUERY /!\:
print(sql_query.as_string(conn))
# will print:
# SELECT "p.id", "p.name", "p.type", "p.price", "o.date", "o.transaction", "p.warehouse", "p.location" FROM product p
# INNER JOIN owner o ON p.id = o.product_id
# WHERE p.id = 100 AND (o.dateof_purchase IS NOT NULL
# OR o.state = 'checked_out' );
And indeed, if I copy/paste the last query directly in my favorite PostgreSQL query tool:
ERROR: column "p.id" does not exist
LINE 1: SELECT "p.id", "p.name", "p.type", "p.price", "o.date",...
^
HINT: Perhaps you meant to reference the column "p.id" or the column "o.id".
SQL state: 42703
Character: 8
The same error is obviously raised by psycopg2:
UndefinedColumn: column "p.id" does not exist
LINE 1: SELECT "p.id", "p.name", "p.type", "p.price", "o.date...
^
HINT: Perhaps you meant to reference the column "p.id" or the column "o.id".
The documentation is also clear on that:
Versioning:
psycopg2.__version__
'2.9.1 (dt dec pq3 ext lo64)'
