public inbox for [email protected]
help / color / mirror / Atom feedinsert DEFAULT value
4+ messages / 3 participants
[nested] [flat]
* insert DEFAULT value
@ 2021-03-03 21:51 Hans Ginzel <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Hans Ginzel @ 2021-03-03 21:51 UTC (permalink / raw)
To: psycopg
How to insert DEFAULT value, please?
import psycopg2
from psycopg2.sql import DEFAULT # https://www.postgresql-archive.org/Inserting-default-values-into-execute-values-td6130148.html
db = psycopg2.connect(host='host', dbname='db')
cursor = db.cursor()
cursor.execute("DROP TABLE IF EXISTS test_default")
cursor.execute("CREATE TABLE test_default(i int NOT NULL DEFAULT 1)")
cursor.execute("INSERT INTO test_default VALUES (%s)", (DEFAULT,))
cursor.execute("DROP TABLE IF EXISTS test_default")
cursor.execute("CREATE TABLE test_default(j jsonb NOT NULL DEFAULT '{}'::jsonb)")
cursor.execute("INSERT INTO test_default VALUES (%s)", (DEFAULT,))
For both cases I get
psycopg2.ProgrammingError: can't adapt type 'SQL'
Thank you in advance,
HG
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: insert DEFAULT value
@ 2021-03-03 23:23 Adrian Klaver <[email protected]>
parent: Hans Ginzel <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Adrian Klaver @ 2021-03-03 23:23 UTC (permalink / raw)
To: Hans Ginzel <[email protected]>; psycopg
On 3/3/21 1:51 PM, Hans Ginzel wrote:
> How to insert DEFAULT value, please?
>
> import psycopg2
> from psycopg2.sql import DEFAULT #
> https://www.postgresql-archive.org/Inserting-default-values-into-execute-values-td6130148.html
>
>
> db = psycopg2.connect(host='host', dbname='db')
> cursor = db.cursor()
> cursor.execute("DROP TABLE IF EXISTS test_default")
> cursor.execute("CREATE TABLE test_default(i int NOT NULL DEFAULT 1)")
> cursor.execute("INSERT INTO test_default VALUES (%s)", (DEFAULT,))
>
> cursor.execute("DROP TABLE IF EXISTS test_default")
> cursor.execute("CREATE TABLE test_default(j jsonb NOT NULL DEFAULT
> '{}'::jsonb)")
> cursor.execute("INSERT INTO test_default VALUES (%s)", (DEFAULT,))
>
> For both cases I get
> psycopg2.ProgrammingError: can't adapt type 'SQL'
You need to do something like:
cur.execute(sql.SQL("INSERT INTO test_default VALUES ({})").format(DEFAULT))
>
> Thank you in advance,
> HG
>
>
--
Adrian Klaver
[email protected]
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: insert DEFAULT value
@ 2021-03-04 05:43 Hans Ginzel <[email protected]>
parent: Adrian Klaver <[email protected]>
0 siblings, 1 reply; 4+ messages in thread
From: Hans Ginzel @ 2021-03-04 05:43 UTC (permalink / raw)
To: Adrian Klaver <[email protected]>; +Cc: psycopg
On Wed, Mar 03, 2021 at 03:23:28PM -0800, Adrian Klaver wrote:
>On 3/3/21 1:51 PM, Hans Ginzel wrote:
>You need to do something like:
>cur.execute(sql.SQL("INSERT INTO test_default VALUES ({})").format(DEFAULT))
Formating values into SQL statement is done by the execute(), isn't it?
I like the solution from
https://www.postgresql-archive.org/Inserting-default-values-into-execute-values-td6130148.html
https://www.postgresql-archive.org/Inserting-default-values-into-execute-values-td6130148.html
class Default(object):
"""Set up DEFAULT value for a field.
When doing INSERT or UPDATE in Postgres one can use DEFAULT/default
as the value to have the server use the default set on the field.
"""
def __conform__(self, proto):
if proto is psycopg2.extensions.ISQLQuote:
return self
def getquoted(self):
return 'DEFAULT'
DEFAULT = Default()
cursor.execute("INSERT INTO test_default VALUES (%s)", (DEFAULT,))
Could this be added to the psycopg2 code, please?
H.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: insert DEFAULT value
@ 2021-03-04 10:33 Daniele Varrazzo <[email protected]>
parent: Hans Ginzel <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Daniele Varrazzo @ 2021-03-04 10:33 UTC (permalink / raw)
To: Hans Ginzel <[email protected]>; +Cc: Adrian Klaver <[email protected]>; psycopg
On Thu, 4 Mar 2021, 06:43 Hans Ginzel, <[email protected]> wrote:
> On Wed, Mar 03, 2021 at 03:23:28PM -0800, Adrian Klaver wrote:
> >On 3/3/21 1:51 PM, Hans Ginzel wrote:
> >You need to do something like:
> >cur.execute(sql.SQL("INSERT INTO test_default VALUES
> ({})").format(DEFAULT))
>
> Formating values into SQL statement is done by the execute(), isn't it?
>
It's done on the client. It's done by execute(), yes, by calling
".as_string(conn)" on the resulting object, which is something exposed by
the API and which you can do too in your program.
cursor.execute("INSERT INTO test_default VALUES (%s)", (DEFAULT,))
>
> Could this be added to the psycopg2 code, please?
>
Nope, not a chance. I thought it was a good idea when I proposed it, which
was not in April 2020, when my message was fished from the interwebz
memories, but I think I wrote it around 2006? Anyway I would answer my then
self that it is not a good idea. Parameter passing is for parameters,
DEFAULT is a SQL construct: it wouldn't work when the adapter evolves into
sending query and params separately (which is happening in psycopg3).
So it won't land in the adapter, no, and the correct way of doing that job
is to use explicit client-side binding through the psycopg2.sql facilities,
passing a 'sql.Literal(value)', or a 'sql.SQL("DEFAULT")' to override it,
to a '{}' placeholder, or to pass a 'sql.Placeholder()' instead of
DEFAULT... many ways to skin a client-side cat.
You are free to use my old recipe but it won't be added in psycopg2, which
has since grown a better way to do the same task. :)
Cheers
-- Daniele
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2021-03-04 10:33 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2021-03-03 21:51 insert DEFAULT value Hans Ginzel <[email protected]>
2021-03-03 23:23 ` Adrian Klaver <[email protected]>
2021-03-04 05:43 ` Hans Ginzel <[email protected]>
2021-03-04 10:33 ` Daniele Varrazzo <[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