Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jJk1X-0004r9-6Y for psycopg@arkaria.postgresql.org; Wed, 01 Apr 2020 20:31:43 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1jJk1W-00053y-2c for psycopg@arkaria.postgresql.org; Wed, 01 Apr 2020 20:31:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1jJk1V-00053r-Hd for psycopg@lists.postgresql.org; Wed, 01 Apr 2020 20:31:41 +0000 Received: from mail-lj1-x244.google.com ([2a00:1450:4864:20::244]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1jJk1T-0008KJ-6T for psycopg@postgresql.org; Wed, 01 Apr 2020 20:31:40 +0000 Received: by mail-lj1-x244.google.com with SMTP id n17so878436lji.8 for ; Wed, 01 Apr 2020 13:31:38 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20161025; h=mime-version:references:in-reply-to:from:date:message-id:subject:to :cc; bh=Xuc1VRHCnLzJlxmSHp6tIXuJTRj2hPzP6ObUuYHfIO8=; b=uOeCCTX8cIST/13AK+aRIGvJKihhT02eiozzuX+UTmVshk77PEK9t3DQneBIW9DMry DbIl4MBuxpZGtG7vv3qfBh2tmujLwdLJKi/gysrPzQnC12mdNdodM+BPgrjLYW9KSkBS dauzXbjFBNDFbx3YPD8cvMSXOhI4Rib4d3sEYFuiaCA78QIqT+M8gzRAIR4l+ExbNyZK TeXfz56Wrk8rntrbj1sVvyjNOns8SZCgrDkQdYG8q5BO8mC/1n17L2J0Bp7bCu2vSbLm Ytta/9Zvfq6o5/jc0Te1sRoUc3DXCbz4QA1kE9xx3azC6a4TUyzVHleuhL2O7zpXplAe escw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20161025; h=x-gm-message-state:mime-version:references:in-reply-to:from:date :message-id:subject:to:cc; bh=Xuc1VRHCnLzJlxmSHp6tIXuJTRj2hPzP6ObUuYHfIO8=; b=XkQGT2sZ+d57G+RXzY/JqtVnBdVxzsfsQiSfLFkf0WrHiH8HXY05q2iSc+cPJlH6I+ IQvy2WEkB6RcWb8yStT9W9fnnqPRM0c/+2ZPZxUIHAyxxAKYB4h/I7lbWlwkROyvFQJw F+5hycpzpJ7hZtT6iqcYCYQHLbzMWpRhohDaLcMFKEdYMgMgAnqA/6y96sS5FCxeQGeD SY1fz3s/Bb/FD+hDK3BGpkD/RCaGuU3yk5zOKZxQcXgHu/JaRAm93VlGVzEYN/p1nPUF AHJt4lp6KQoOSmzQSpm+0o5O6J+udoI3G/6uQqevsmj2NJmwA2NZD88i8Xp5ZsP4pgzM nAWg== X-Gm-Message-State: AGi0PuahS2ULi/sJjU8YWZSfKXDmxtZkIBDtAaEUHbWf/DwV679lF6ct BgDuTovrVS4RLMzHXJnsHeeGcWgsfu7WjLHnqwu79e9lLmk= X-Google-Smtp-Source: APiQypLmjf89CiP9g2f8V1yimW65Bju5yZSuMDdS1gMgz77E5TMjpRhxp8C9BHqjcHst7KH63VGN6GeChUopioPOWZI= X-Received: by 2002:a2e:9851:: with SMTP id e17mr36162ljj.208.1585773097185; Wed, 01 Apr 2020 13:31:37 -0700 (PDT) MIME-Version: 1.0 References: <6db34cfa-c531-18ff-4368-bea8c171f6db@aklaver.com> <3cdc3522-f09a-529a-e329-b50418fb4172@aklaver.com> In-Reply-To: From: Stephen Lagree Date: Wed, 1 Apr 2020 13:31:25 -0700 Message-ID: Subject: Re: Inserting default values into execute_values To: Adrian Klaver Cc: Daniele Varrazzo , psycopg@postgresql.org Content-Type: multipart/alternative; boundary="00000000000026f56905a2409211" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Precedence: bulk --00000000000026f56905a2409211 Content-Type: text/plain; charset="UTF-8" Thanks Daniele and Adrian, your answers were really helpful! Daniele, you are right, it is a waste sending long strings when I am just trying to generate entries in the sequence. I do want to do it in one shot so your generate_series suggestion should be great insert into testins (id) select nextval('testins_id_seq') from generate_series(1, 10); However, I was playing around with the sql.Default and Adrian's Default class and couldn't get them to work with execute_values. I know in my case it might not make sense to use a Default literal if that is all that is being added, but it might make sense for a query that sometimes is used for inserting DEFAULT and sometimes to insert a value. query2 = "INSERT INTO MYTABLE (id) VALUES %s RETURNING id;" args_list = [sql.DEFAULT, sql.DEFAULT] execute_values(cursor, query2, args_list, template=None, page_size=100, fetch=True) There is a TypeError in execute_values for both Adrian's Default and sql.Default: for page in _paginate(argslist, page_size=page_size): if template is None: > template = b'(' + b','.join([b'%s'] * len(page[0])) + b')' E TypeError: object of type 'SQL' has no len() ../../.con da/envs/stbase/lib/python3.7/site-packages/psycopg2/extras.py:1275: TypeError I added a len and slicing function to Adrian's default class and tried it, but it then had an error with the mogrify line in execute values. I tried a few variations of templates with and without parentheses and that didn't work either. -Steve On Wed, Apr 1, 2020 at 1:03 PM Adrian Klaver wrote: > On 3/31/20 7:16 PM, Daniele Varrazzo wrote: > >>> On 3/31/20 3:27 PM, Stephen Lagree wrote: > >>>> Hello, > >>>> > >>>> I am trying to insert into a table to generate sequential ids. Is > >>>> there a way to do this repeatedly using execute_values if there is > >>>> only one column and it is auto incremented? > > > > The point of execute_values is to convert a sequence of records into a > > VALUES thing (that's what the placeholder is for) and shoot it to the > > db in one go. I think your task is much simpler than that. > > > > In order to do what you want to do you use execute_batch and use a > > list of empty tuples for instance; > > > > psycopg2.extras.execute_batch(cur, "insert into testins (id) > > values (default)", [() for i in range(10)]) > > > > but I think this is still silly: you are still sending a lot of > > strings from client to serve which do very little. > > > > You can easily do the same loop entirely in the database, executing a > > statement such as: > > > > do $$ > > declare i int; > > begin > > for i in select * from generate_series(1, 10) > > loop > > insert into testins (id) values (default); > > end loop; > > end > > $$ language plpgsql; > > > > but this is still means doing n separate inserts. Even faster would be > > just not rely on the DEFAULT literal, if you know the table you are > > inserting into or you don't mind introspecting the schema: > > > > insert into testins (id) select nextval('testins_id_seq') from > > generate_series(1, 10); > > > > On Wed, 1 Apr 2020 at 12:08, Adrian Klaver > wrote: > > > >>> A solution from Daniele Varrazzo. I can't find the mailing list post > >>> where it appeared, just where I use it in code: > > > > > > Thank you for fishing that out! But I think since the introduction of > > the 'psycopg2.sql' module the correct way to do that is to use > > something like 'sql.SQL("DEFAULT")' to compose into a query. > > Thanks, still wrapping my head around psycopg2.sql. > > A simple example: > > test=# \d t2 > Table "public.t2" > Column | Type | Collation | Nullable | Default > > > --------+-------------------+-----------+----------+-------------------------------- > id | integer | | not null | > nextval('t2_id_seq'::regclass) > name | character varying | | | > Indexes: > "t2_pkey" PRIMARY KEY, btree (id) > > > import psycopg2 > from psycopg2 import sql > > con = psycopg2.connect("dbname=test host=localhost user=aklaver") > > q1 = sql.SQL("insert into t2 values > ({})").format(sql.SQL(",").join([sql.SQL("DEFAULT"), > sql.Literal('test2')])) > > print(q1.as_string(con)) > > > insert into t2 values (DEFAULT,E'test2') > > cur.execute(q1) > > test=# select * from t2; > id | name > ----+------- > 1 | test > 2 | test2 > > > > > > Cheers, > > > > -- Daniele > > > > > -- > Adrian Klaver > adrian.klaver@aklaver.com > --00000000000026f56905a2409211 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
Thanks Daniele and Adrian, your answers were really h= elpful!

Daniele, you are right, it is a waste send= ing long strings when I am just trying to generate entries in the sequence.=
I do want to do it in one shot so your generate_series suggestio= n should be great
=C2=A0 =C2=A0 insert into testins (id) select n= extval('testins_id_seq') from generate_series(1, 10);

However, I was playing around with the sql.Default and Adri= an's Default class and couldn't get them to work with execute_value= s.=C2=A0 I know in my case it might not make sense to use a Default literal= if that is all that is being added, but it might make sense for a query th= at sometimes is used for inserting DEFAULT and sometimes to insert a value.=
=C2=A0
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 q= uery2 =3D "INSERT INTO MYTABLE (id) VALUES %s RETURNING id;"
= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 args_list =3D [sql.DEFAULT, sql.D= EFAULT]
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 execute_values(cursor,= query2, args_list,
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0template=3DNone, page_size=3D1= 00, fetch=3DTrue)

There is a TypeError in exec= ute_values for both Adrian's Default and sql.Default:
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 for page in _paginate= (argslist, page_size=3Dpage_size):
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 if template is None:
=C2=A0 =C2=A0 > =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 template =3D b'(' + b',&= #39;.join([b'%s'] * len(page[0])) + b')'
=C2=A0 =C2=A0 E= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 TypeError: object of type= 'SQL' has no len()

=C2=A0 =C2=A0 ../../.con=C2=A0 da/envs/s= tbase/lib/python3.7/site-packages/psycopg2/extras.py:1275: TypeError

I added a len and slicing function to Adrian's d= efault class and tried it, but it then had an error with the mogrify line i= n execute=C2=A0values.=C2=A0 I tried a few variations of templates with and= without parentheses and that didn't work either.

<= div>-Steve

On Wed, Apr 1, 2020 at 1:03 PM Adrian Klaver <adrian.klaver@aklaver.com> wr= ote:
On 3/31/20 = 7:16 PM, Daniele Varrazzo wrote:
>>> On 3/31/20 3:27 PM, Stephen Lagree wrote:
>>>> Hello,
>>>>
>>>> I am trying to insert into a table to generate sequential = ids.=C2=A0 Is
>>>> there a way to do this repeatedly using execute_values if = there is
>>>> only one column and it is auto incremented?
>
> The point of execute_values is to convert a sequence of records into a=
> VALUES thing (that's what the placeholder is for) and shoot it to = the
> db in one go. I think your task is much simpler than that.
>
> In order to do what you want to do you use execute_batch and use a
> list of empty tuples for instance;
>
>=C2=A0 =C2=A0 =C2=A0 psycopg2.extras.execute_batch(cur, "insert in= to testins (id)
> values (default)", [() for i in range(10)])
>
> but I think this is still silly: you are still sending a lot of
> strings from client to serve which do very little.
>
> You can easily do the same loop entirely in the database, executing a<= br> > statement such as:
>
>=C2=A0 =C2=A0 =C2=A0 do $$
>=C2=A0 =C2=A0 =C2=A0 declare i int;
>=C2=A0 =C2=A0 =C2=A0 begin
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 for i in select * from generate_seri= es(1, 10)
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 loop
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 insert into testins (i= d) values (default);
>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 end loop;
>=C2=A0 =C2=A0 =C2=A0 end
>=C2=A0 =C2=A0 =C2=A0 $$ language plpgsql;
>
> but this is still means doing n separate inserts. Even faster would be=
> just not rely on the DEFAULT literal, if you know the table you are > inserting into or you don't mind introspecting the schema:
>
>=C2=A0 =C2=A0 =C2=A0 insert into testins (id) select nextval('testi= ns_id_seq') from
> generate_series(1, 10);
>
> On Wed, 1 Apr 2020 at 12:08, Adrian Klaver <adrian.klaver@aklaver.com> w= rote:
>
>>> A solution from Daniele Varrazzo.=C2=A0 I can't find the m= ailing list post
>>> where it appeared, just where I use it in code:
>
>
> Thank you for fishing that out! But I think since the introduction of<= br> > the 'psycopg2.sql' module the correct way to do that is to use=
> something like 'sql.SQL("DEFAULT")' to compose into = a query.

Thanks, still wrapping my head around psycopg2.sql.

A simple example:

test=3D# \d t2
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Table "public.t2" =C2=A0 Column |=C2=A0 =C2=A0 =C2=A0 =C2=A0Type=C2=A0 =C2=A0 =C2=A0 =C2=A0 |= Collation | Nullable |=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 Default
--------+-------------------+-----------+----------+-----------------------= ---------
=C2=A0 id=C2=A0 =C2=A0 =C2=A0| integer=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| not null |
nextval('t2_id_seq'::regclass)
=C2=A0 name=C2=A0 =C2=A0| character varying |=C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0|=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 |
Indexes:
=C2=A0 =C2=A0 =C2=A0"t2_pkey" PRIMARY KEY, btree (id)


import psycopg2
from psycopg2 import sql

con =3D psycopg2.connect("dbname=3Dtest host=3Dlocalhost user=3Daklave= r")

q1 =3D sql.SQL("insert into t2=C2=A0 values
({})").format(sql.SQL(",").join([sql.SQL("DEFAULT"= ),
sql.Literal('test2')]))

print(q1.as_string(con))


insert into t2=C2=A0 values (DEFAULT,E'test2')

cur.execute(q1)

test=3D# select * from t2;
=C2=A0 id | name
----+-------
=C2=A0 =C2=A01 | test
=C2=A0 =C2=A02 | test2


>
> Cheers,
>
> -- Daniele
>


--
Adrian Klaver
adrian.klave= r@aklaver.com
--00000000000026f56905a2409211--