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(dot)klaver(at)aklaver(dot)com