public inbox for [email protected]
help / color / mirror / Atom feedFrom: Adrian Klaver <[email protected]>
To: Stephen Lagree <[email protected]>
Cc: Daniele Varrazzo <[email protected]>
Cc: [email protected]
Subject: Re: Inserting default values into execute_values
Date: Wed, 1 Apr 2020 14:15:49 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <[email protected]>
References: <CADkZaxXqU+c6_GnW_L0SOgc63iYHomm0RVSpb_+Kww5scVyA4w@mail.gmail.com>
<[email protected]>
<[email protected]>
<CA+mi_8YoJ_sSx5Uvo672PSn0sWowpARAOHGfcoa7rsCwmXyJnA@mail.gmail.com>
<[email protected]>
<CADkZaxUhmBhF05ywiLF7yJwzXe102rPFOqkLm4TquM=PJtKB+Q@mail.gmail.com>
<[email protected]>
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
[email protected]
view thread (8+ messages)
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: [email protected]
Cc: [email protected], [email protected], [email protected]
Subject: Re: Inserting default values into execute_values
In-Reply-To: <[email protected]>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox