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]
