Subject: Re: Inserting default values into execute_values
From: Adrian Klaver <adrian.klaver@aklaver.com>
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>
 <0f255c7c-0eb0-fbfa-8c47-029f98b9a4f2@aklaver.com>
Message-ID: <fa748f68-6c8f-382d-8e17-68e0c3f5e537@aklaver.com>
Date: Wed, 1 Apr 2020 14:15:49 -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: <0f255c7c-0eb0-fbfa-8c47-029f98b9a4f2@aklaver.com>
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Language: en-US
Content-Transfer-Encoding: 8bit
Precedence: bulk

On 4/1/20 2:04 PM, Adrian Klaver wrote:
> 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.

Not concentrating, the above is not correct for the DEFAULT case:

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. 
The below allows
     for doing that.
     """
     def __conform__(self, proto):
         if proto is psycopg2.extensions.ISQLQuote:
             return self

     def getquoted(self):
         return 'DEFAULT'


DEFAULT = Default()

args_list = [(DEFAULT, DEFAULT)]

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

[(6,)]

select * from t2;
  id | name
----+-------
   1 | test
   2 | test2
   3 | name
   4 | name
   5 | name
   6 | name


What we both forgot is that args_list needs to a sequence of sequences.


> 
> 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)
> 
> 
> 
> 
> 
>>


-- 
Adrian Klaver
adrian.klaver@aklaver.com