Re: Inserting default values into execute_values

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