Re: Inserting default values into execute_values - Mailing list psycopg
From | Adrian Klaver |
---|---|
Subject | Re: Inserting default values into execute_values |
Date | |
Msg-id | e2a450e2-4667-d159-1171-65041c0a5e3b@aklaver.com Whole thread Raw |
In response to | Re: Inserting default values into execute_values (Daniele Varrazzo <daniele.varrazzo@gmail.com>) |
Responses |
Re: Inserting default values into execute_values
|
List | psycopg |
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> 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