Re: Inserting default values into execute_values - Mailing list psycopg
From | Adrian Klaver |
---|---|
Subject | Re: Inserting default values into execute_values |
Date | |
Msg-id | 0f255c7c-0eb0-fbfa-8c47-029f98b9a4f2@aklaver.com Whole thread Raw |
In response to | Re: Inserting default values into execute_values (Stephen Lagree <stephen.lagree@gmail.com>) |
Responses |
Re: Inserting default values into execute_values
|
List | psycopg |
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.klaver@aklaver.com > <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.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.klaver@aklaver.com <mailto:adrian.klaver@aklaver.com> > -- Adrian Klaver adrian.klaver@aklaver.com