Re: Inserting default values into execute_values

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.

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)

>
> -Steve
>
> On Wed, Apr 1, 2020 at 1:03 PM Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com
> <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 3/31/20 7:16 PM, Daniele Varrazzo wrote:
> >>> On 3/31/20 3:27 PM, Stephen Lagree wrote:
> >>>> Hello,
> >>>>
> >>>> I am trying to insert into a table to generate sequential ids.  Is
> >>>> there a way to do this repeatedly using execute_values if there is
> >>>> only one column and it is auto incremented?
> >
> > The point of execute_values is to convert a sequence of records
> into a
> > VALUES thing (that's what the placeholder is for) and shoot it to the
> > db in one go. I think your task is much simpler than that.
> >
> > In order to do what you want to do you use execute_batch and use a
> > list of empty tuples for instance;
> >
> >      psycopg2.extras.execute_batch(cur, "insert into testins (id)
> > values (default)", [() for i in range(10)])
> >
> > but I think this is still silly: you are still sending a lot of
> > strings from client to serve which do very little.
> >
> > You can easily do the same loop entirely in the database, executing a
> > statement such as:
> >
> >      do $$
> >      declare i int;
> >      begin
> >          for i in select * from generate_series(1, 10)
> >          loop
> >              insert into testins (id) values (default);
> >          end loop;
> >      end
> >      $$ language plpgsql;
> >
> > but this is still means doing n separate inserts. Even faster
> would be
> > just not rely on the DEFAULT literal, if you know the table you are
> > inserting into or you don't mind introspecting the schema:
> >
> >      insert into testins (id) select nextval('testins_id_seq') from
> > generate_series(1, 10);
> >
> > On Wed, 1 Apr 2020 at 12:08, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
> >
> >>> A solution from Daniele Varrazzo.  I can't find the mailing
> list post
> >>> where it appeared, just where I use it in code:
> >
> >
> > Thank you for fishing that out! But I think since the introduction of
> > the 'psycopg2.sql' module the correct way to do that is to use
> > something like 'sql.SQL("DEFAULT")' to compose into a query.
>
> Thanks, still wrapping my head around psycopg2.sql.
>
> A simple example:
>
> test=# \d t2
>                                   Table "public.t2"
>   Column |       Type        | Collation | Nullable |
> Default
>
> --------+-------------------+-----------+----------+--------------------------------
>   id     | integer           |           | not null |
> nextval('t2_id_seq'::regclass)
>   name   | character varying |           |          |
> Indexes:
>      "t2_pkey" PRIMARY KEY, btree (id)
>
>
> import psycopg2
> from psycopg2 import sql
>
> con = psycopg2.connect("dbname=test host=localhost user=aklaver")
>
> q1 = sql.SQL("insert into t2  values
> ({})").format(sql.SQL(",").join([sql.SQL("DEFAULT"),
> sql.Literal('test2')]))
>
> print(q1.as_string(con))
>
>
> insert into t2  values (DEFAULT,E'test2')
>
> cur.execute(q1)
>
> test=# select * from t2;
>   id | name
> ----+-------
>    1 | test
>    2 | test2
>
>
> >
> > Cheers,
> >
> > -- Daniele
> >
>
>
> --
> Adrian Klaver
> adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com