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.kla...@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.kla...@aklaver.com
