public inbox for [email protected]
help / color / mirror / Atom feedQuery with boolean parameter
7+ messages / 4 participants
[nested] [flat]
* Query with boolean parameter
@ 2022-03-18 23:56 Paolo De Stefani <[email protected]>
2022-03-19 00:00 ` Re: Query with boolean parameter Christophe Pettus <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Paolo De Stefani @ 2022-03-18 23:56 UTC (permalink / raw)
To: psycopg
Hi all
Regarding query and parameter binding in psycopg3:
cur.execute('SELECT * FROM system.app_user WHERE can_edit_views = %s',
(True,))
<psycopg.Cursor [TUPLES_OK] [INTRANS] (host=localhost port=5433
user=postgres database=test4) at 0x127b7f0>
cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s',
(True,))
Traceback (most recent call last):
Python Shell, prompt 10, line 1
# Used internally for debug sandbox under external interpreter
File "C:\Python310\Lib\site-packages\psycopg\cursor.py", line 555, in
execute
raise ex.with_traceback(None)
psycopg.errors.SyntaxError: syntax error at or near "$1"
LINE 1: SELECT * FROM system.app_user WHERE can_edit_views IS $1
^
Is there any reason why the second query results in a syntax error?
I can use the first form but usually to check a boolean or null value in
SQL the IS [true|false|null]/ IS NOT [true|false|null] operator is used.
--
Paolo De Stefani
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with boolean parameter
2022-03-18 23:56 Query with boolean parameter Paolo De Stefani <[email protected]>
@ 2022-03-19 00:00 ` Christophe Pettus <[email protected]>
2022-03-19 12:10 ` Re: Query with boolean parameter Paolo De Stefani <[email protected]>
0 siblings, 1 reply; 7+ messages in thread
From: Christophe Pettus @ 2022-03-19 00:00 UTC (permalink / raw)
To: Paolo De Stefani <[email protected]>; +Cc: psycopg
> On Mar 18, 2022, at 16:56, Paolo De Stefani <[email protected]> wrote:
> Is there any reason why the second query results in a syntax error?
There's not IS operator in PostgreSQL (or in SQL). IS NULL, IS NOT NULL, IS TRUE, and IS FALSE are in effect unary postfix operators, so you can't construct them that way via parameter substitution.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with boolean parameter
2022-03-18 23:56 Query with boolean parameter Paolo De Stefani <[email protected]>
2022-03-19 00:00 ` Re: Query with boolean parameter Christophe Pettus <[email protected]>
@ 2022-03-19 12:10 ` Paolo De Stefani <[email protected]>
2022-03-19 13:43 ` Re: Query with boolean parameter Daniele Varrazzo <[email protected]>
2022-03-19 16:35 ` Re: Query with boolean parameter Christophe Pettus <[email protected]>
2022-03-19 17:34 ` Re: Query with boolean parameter Adrian Klaver <[email protected]>
0 siblings, 3 replies; 7+ messages in thread
From: Paolo De Stefani @ 2022-03-19 12:10 UTC (permalink / raw)
To: Christophe Pettus <[email protected]>; +Cc: psycopg
Il 19/03/2022 01:00 Christophe Pettus ha scritto:
>> On Mar 18, 2022, at 16:56, Paolo De Stefani <[email protected]>
>> wrote:
>> Is there any reason why the second query results in a syntax error?
>
> There's not IS operator in PostgreSQL (or in SQL). IS NULL, IS NOT
> NULL, IS TRUE, and IS FALSE are in effect unary postfix operators, so
> you can't construct them that way via parameter substitution.
Thanks, i see
The problem is (for me) that with psycopg2 this works:
cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS
TRUE')
cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s',
(True,))
cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s',
(None,))
Switching to psycopg 3 i have to consider many more differences than i
expected
--
Paolo De Stefani
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with boolean parameter
2022-03-18 23:56 Query with boolean parameter Paolo De Stefani <[email protected]>
2022-03-19 00:00 ` Re: Query with boolean parameter Christophe Pettus <[email protected]>
2022-03-19 12:10 ` Re: Query with boolean parameter Paolo De Stefani <[email protected]>
@ 2022-03-19 13:43 ` Daniele Varrazzo <[email protected]>
2 siblings, 0 replies; 7+ messages in thread
From: Daniele Varrazzo @ 2022-03-19 13:43 UTC (permalink / raw)
To: Paolo De Stefani <[email protected]>; +Cc: Christophe Pettus <[email protected]>; psycopg
> Il 19/03/2022 01:00 Christophe Pettus ha scritto:> >> On Mar 18, 2022, at 16:56, Paolo De Stefani <[email protected]>
> >> wrote:
> >> Is there any reason why the second query results in a syntax error?
> >
> > There's not IS operator in PostgreSQL (or in SQL). IS NULL, IS NOT
> > NULL, IS TRUE, and IS FALSE are in effect unary postfix operators, so
> > you can't construct them that way via parameter substitution.
That's unexpected. Thank you for the insight, Christophe.
On Sat, 19 Mar 2022 at 13:11, Paolo De Stefani <[email protected]> wrote:
> The problem is (for me) that with psycopg2 this works:
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS
> TRUE')
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s',
> (True,))
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s',
> (None,))
>
> Switching to psycopg 3 i have to consider many more differences than i
> expected
Maybe you can play around with IS NOT DISTINCT FROM?
https://www.postgresql.org/docs/current/functions-comparison.html
In [1]: import psycopg
In [2]: cnn = psycopg.connect(autocommit=True)
In [5]: cnn.execute("create table dist (id int primary key, cond bool)")
In [7]: cnn.cursor().executemany("insert into dist values (%s,
%s)", [(1, True), (2, False), (3, None)])
In [9]: cnn.execute("select * from dist where cond is not distinct
from %s", [True]).fetchone()
Out[9]: (1, True)
In [10]: cnn.execute("select * from dist where cond is not
distinct from %s", [False]).fetchone()
Out[10]: (2, False)
In [11]: cnn.execute("select * from dist where cond is not
distinct from %s", [None]).fetchone()
Out[11]: (3, None)
-- Daniele
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with boolean parameter
2022-03-18 23:56 Query with boolean parameter Paolo De Stefani <[email protected]>
2022-03-19 00:00 ` Re: Query with boolean parameter Christophe Pettus <[email protected]>
2022-03-19 12:10 ` Re: Query with boolean parameter Paolo De Stefani <[email protected]>
@ 2022-03-19 16:35 ` Christophe Pettus <[email protected]>
2022-03-19 19:30 ` Re: Query with boolean parameter Paolo De Stefani <[email protected]>
2 siblings, 1 reply; 7+ messages in thread
From: Christophe Pettus @ 2022-03-19 16:35 UTC (permalink / raw)
To: Paolo De Stefani <[email protected]>; +Cc: psycopg
> On Mar 19, 2022, at 05:10, Paolo De Stefani <[email protected]> wrote:
> Switching to psycopg 3 i have to consider many more differences than i expected
There have been some changes in the way psycopg2 does parameter substitution, although that one is an interesting case! You might consider using IS DISTINCT FROM as Daniele suggested, or just =, depending on how you want nulls handled.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with boolean parameter
2022-03-18 23:56 Query with boolean parameter Paolo De Stefani <[email protected]>
2022-03-19 00:00 ` Re: Query with boolean parameter Christophe Pettus <[email protected]>
2022-03-19 12:10 ` Re: Query with boolean parameter Paolo De Stefani <[email protected]>
2022-03-19 16:35 ` Re: Query with boolean parameter Christophe Pettus <[email protected]>
@ 2022-03-19 19:30 ` Paolo De Stefani <[email protected]>
0 siblings, 0 replies; 7+ messages in thread
From: Paolo De Stefani @ 2022-03-19 19:30 UTC (permalink / raw)
To: Christophe Pettus <[email protected]>; +Cc: psycopg
Il 19/03/2022 17:35 Christophe Pettus ha scritto:
>> On Mar 19, 2022, at 05:10, Paolo De Stefani <[email protected]>
>> wrote:
>> Switching to psycopg 3 i have to consider many more differences than i
>> expected
>
> There have been some changes in the way psycopg2 does parameter
> substitution, although that one is an interesting case! You might
> consider using IS DISTINCT FROM as Daniele suggested, or just =,
> depending on how you want nulls handled.
thanks to all for the suggestions i thimk i will use the '=' operator
what do you mean with 'depending on how you want nulls handled' ???
test4=# create table test (a text, b boolean);
CREATE TABLE
test4=# insert into test values ('aaa', true), ('bbb', false), ('ccc',
null);
INSERT 0 3
test4=# select * from test;
a | b
-----+---
aaa | t
bbb | f
ccc |
(3 rows)
test4=# select * from test where b = true;
a | b
-----+---
aaa | t
(1 row)
test4=# select * from test where b is true;
a | b
-----+---
aaa | t
(1 row)
test4=# select * from test where b = null;
a | b
---+---
(0 rows)
test4=# select * from test where b is null;
a | b
-----+---
ccc |
(1 row)
I will use '= True' or '= False' in psycopg cur.execute with parameter
substitution and 'IS NULL' without parameter when i need to check the
null value
--
Paolo De Stefani
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Query with boolean parameter
2022-03-18 23:56 Query with boolean parameter Paolo De Stefani <[email protected]>
2022-03-19 00:00 ` Re: Query with boolean parameter Christophe Pettus <[email protected]>
2022-03-19 12:10 ` Re: Query with boolean parameter Paolo De Stefani <[email protected]>
@ 2022-03-19 17:34 ` Adrian Klaver <[email protected]>
2 siblings, 0 replies; 7+ messages in thread
From: Adrian Klaver @ 2022-03-19 17:34 UTC (permalink / raw)
To: Paolo De Stefani <[email protected]>; Christophe Pettus <[email protected]>; +Cc: psycopg
On 3/19/22 05:10, Paolo De Stefani wrote:
> Il 19/03/2022 01:00 Christophe Pettus ha scritto:
>>> On Mar 18, 2022, at 16:56, Paolo De Stefani <[email protected]>
>>> wrote:
>>> Is there any reason why the second query results in a syntax error?
>>
>> There's not IS operator in PostgreSQL (or in SQL). IS NULL, IS NOT
>> NULL, IS TRUE, and IS FALSE are in effect unary postfix operators, so
>> you can't construct them that way via parameter substitution.
>
> Thanks, i see
>
> The problem is (for me) that with psycopg2 this works:
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS TRUE')
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s',
> (True,))
> cur.execute('SELECT * FROM system.app_user WHERE can_edit_views IS %s',
> (None,))
The only way I could get it to work:
cur.execute(sql.SQL("select 'f' IS {}").format(sql.SQL('TRUE')))
or
cur.execute(sql.SQL("select 'f' IS {}").format(sql.SQL(str(True))))
cur.fetchone()
(False,)
cur.execute(sql.SQL("select 'f' IS {}").format(sql.SQL('NULL')))
cur.fetchone()
(False,)
>
> Switching to psycopg 3 i have to consider many more differences than i
> expected
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2022-03-19 19:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-03-18 23:56 Query with boolean parameter Paolo De Stefani <[email protected]>
2022-03-19 00:00 ` Christophe Pettus <[email protected]>
2022-03-19 12:10 ` Paolo De Stefani <[email protected]>
2022-03-19 13:43 ` Daniele Varrazzo <[email protected]>
2022-03-19 16:35 ` Christophe Pettus <[email protected]>
2022-03-19 19:30 ` Paolo De Stefani <[email protected]>
2022-03-19 17:34 ` Adrian Klaver <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox