public inbox for [email protected]  

help / color / mirror / Atom feed

From: Daniele Varrazzo <[email protected]>
To: Adrian Klaver <[email protected]>
Cc: Stephen Lagree <[email protected]>
Cc: [email protected]
Subject: Re: Inserting default values into execute_values
Date: Wed, 1 Apr 2020 15:16:58 +1300
Message-ID: <CA+mi_8YoJ_sSx5Uvo672PSn0sWowpARAOHGfcoa7rsCwmXyJnA@mail.gmail.com> (raw)
In-Reply-To: <[email protected]>
References: <CADkZaxXqU+c6_GnW_L0SOgc63iYHomm0RVSpb_+Kww5scVyA4w@mail.gmail.com>
	<[email protected]>
	<[email protected]>

> > 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 <[email protected]> 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.

Cheers,

-- Daniele

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Inserting default values into execute_values
  In-Reply-To: <CA+mi_8YoJ_sSx5Uvo672PSn0sWowpARAOHGfcoa7rsCwmXyJnA@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox