Subject: Re: Inserting default values into execute_values
To: Stephen Lagree <stephen.lagree@gmail.com>
Cc: Daniele Varrazzo <daniele.varrazzo@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>
 <e2a450e2-4667-d159-1171-65041c0a5e3b@aklaver.com>
 <CADkZaxUhmBhF05ywiLF7yJwzXe102rPFOqkLm4TquM=PJtKB+Q@mail.gmail.com>
From: Adrian Klaver <adrian.klaver@aklaver.com>
Message-ID: <0f255c7c-0eb0-fbfa-8c47-029f98b9a4f2@aklaver.com>
Date: Wed, 1 Apr 2020 14:04:37 -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: 
 <CADkZaxUhmBhF05ywiLF7yJwzXe102rPFOqkLm4TquM=PJtKB+Q@mail.gmail.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 4/1/20 1:31 PM, Stephen Lagree wrote:
> 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.

The DEFAULT and sql.SQL("DEFAULT") both return objects that do not play 
well with the template as you found out.

The simplest way I found is to do:

query2 = "INSERT INTO t2 (id, name) VALUES %s RETURNING id;"

execute_values(cur, query2, args_list, template="(DEFAULT, DEFAULT)", 
page_size=100, fetch=True) 


[(3,), (4,)]

test=# alter table t2 alter COLUMN name set default 'name';
ALTER TABLE
test=# select * from t2;
  id | name
----+-------
   1 | test
   2 | test2
(2 rows)

test=# select * from t2;
  id | name
----+-------
   1 | test
   2 | test2
   3 | name
   4 | name
(4 rows)


> 
> -Steve
> 
> On Wed, Apr 1, 2020 at 1:03 PM Adrian Klaver <adrian.klaver@aklaver.com 
> <mailto:adrian.klaver@aklaver.com>> 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
>     <adrian.klaver@aklaver.com <mailto: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 <mailto:adrian.klaver@aklaver.com>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com