Subject: Re: Inserting default values into execute_values
To: Daniele Varrazzo <daniele.varrazzo@gmail.com>
Cc: Stephen Lagree <stephen.lagree@gmail.com>, psycopg@postgresql.org
References: 
 <CADkZaxXqU+c6_GnW_L0SOgc63iYHomm0RVSpb_+Kww5scVyA4w@mail.gmail.com>
 <6db34cfa-c531-18ff-4368-bea8c171f6db@aklaver.com>
 <3cdc3522-f09a-529a-e329-b50418fb4172@aklaver.com>
 <CA+mi_8YoJ_sSx5Uvo672PSn0sWowpARAOHGfcoa7rsCwmXyJnA@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <e2a450e2-4667-d159-1171-65041c0a5e3b@aklaver.com>
Date: Wed, 1 Apr 2020 13:03:38 -0700
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:68.0) Gecko/20100101
 Thunderbird/68.6.0
MIME-Version: 1.0
In-Reply-To: 
 <CA+mi_8YoJ_sSx5Uvo672PSn0sWowpARAOHGfcoa7rsCwmXyJnA@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 7bit
Precedence: bulk

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 <adrian.klaver@aklaver.com> 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