I am trying to write a dynamic SQL command using Python / Postgres. In my where clause I want to use a query parameter (which is user defined) that has to look for NULL values within a code column (varchar), but in other cases also for specific numbers.
If I have to check for a certain value I use this:
cursor.execute("""
select
z.code as code
from
s_order as o
LEFT JOIN
s_code as z ON o.id = z.id
where
z.code = %(own_id)s;
""", {
'own_id': entry
})
However if I have to check for NULL values the SQL and more specifically the WHERE clause would have to be
select
z.code as code
from
s_order as o
LEFT JOIN
s_code as z ON o.id = z.id
WHERE
z.code IS NULL;
The query parameter as used in the first statement does not work for the second since it can only replace the value on the right side of the equation, but not the operator. I have read here (https://realpython.com/prevent-python-sql-injection/#using-query-parameters-in-sql) that table names can also be substituted using SQL identifiers provided by psycopg2, but could not find out how to replace a whole WHERE clause or at least the operator.
Unfortunately I cannot change the NULL values in the code column (e.g. using a default value) since these NULL values are created through the JOIN operation.
My only option at the moment would be to have different SQL queries based on the input value, but since the SQL query is quite long (I shortened it for this question) and I have many similar queries it would result in a lot of similar code...So how can I make this WHERE clause dynamic?
EDIT: In addition to the answer marked as correct, I want to add my own solution, which is not so elegant, but might be helpful in more complicated scenarios, as the NULL fields are replaced with the 'default' value of COALESCE:
create view orsc as
select
coalesce(z.code), 'default') as code
from
s_order as o
LEFT JOIN
s_code as z ON o.id = z.id;
SELECT
orsc.code as code2
from
orsc
WHERE
code2 = 'default'; //or any other value
EDIT2: See comments of marked answer why a view is probably not necessary at all.
EDIT3: This question is not helpful since it asks only for checking for NULL values. Besides this an IF statement as shown in the answer would substantially increase my whole codebase (each query is quite long and is used often in slightly adapted ways).
-
No, unfortunately not. The answer suggests to make either one or the other SQL command using IF, which is something that would work, but is not desirable for me as described above.Greg Holst– Greg Holst2019年12月19日 17:57:37 +00:00Commented Dec 19, 2019 at 17:57
-
@richyen can you please confirm that the linked discussion does not answer my question? SO gives me a box that says that a similar question might answer my question and it does not go away. Or maybe an upvote of my question would help...Greg Holst– Greg Holst2019年12月21日 11:10:11 +00:00Commented Dec 21, 2019 at 11:10
5 Answers 5
Consider COALESCE to give NULL a default value. Below assumes z.code is a varchar or text. If a integer/numeric, change 'default' to a number value (e.g., 9999).
sql = """SELECT
z.code as code
FROM
s_order as o
LEFT JOIN
s_code as z ON o.id = z.id
WHERE
COALESCE(z.code, 'default') = %(own_id)s;
"""
cursor.execute(sql, {'own_id': entry})
cursor.execute(sql, {'own_id': 'default'}) # RETURNS NULLs IN z.code
3 Comments
I dont know the phyton syntax, but this is the idea:
condition String;
if own_id = "NULL"{
condition= " z.code IS NULL ";
}else{
condition= " z.code = " + own_id;
}
cursor.execute("""
select
z.code as code
from
s_order as o
LEFT JOIN
s_code as z ON o.id = z.id
where
%(condition);
""", {
'condition': entry
})
Comments
If I understand you correctly, you want to be able to send in null as well as other values and have the correct result returned. That is, the problem is with the comparsion not returning anything if the insent value is null. This would solve the problem - perhaps with a little performance decrease.
cursor.execute("""
select
z.code as code
from
s_order as o
LEFT JOIN
s_code as z ON o.id = z.id
where
z.code is not distinct from %(own_id)s;
""", {
'own_id': entry
})
Best regards,
Bjarni
Comments
As in the solution I had linked in the comments, there's really no way around using an if block. You can try this:
pre_query="""
select
z.code as code
from
s_order as o
LEFT JOIN
s_code as z ON o.id = z.id
where
"""
args={entry}
zcode="z.code = %s"
if entry == None:
zcode="z.code IS NULL"
args={}
end_query=";" // other conditions
full_query=pre_query+zcode+end_query
cursor.execute(full_query,args)
Comments
you can use z.code IS NOT DISTINCT FROM %(own_id)s which is like = but where NULLs are treated like normal values (i.e. NULL = NULL, NULL != any non-null value).