Re: Inserting default values into execute_values - Mailing list psycopg
From | Stephen Lagree |
---|---|
Subject | Re: Inserting default values into execute_values |
Date | |
Msg-id | CADkZaxUhmBhF05ywiLF7yJwzXe102rPFOqkLm4TquM=PJtKB+Q@mail.gmail.com Whole thread Raw |
In response to | Re: Inserting default values into execute_values (Adrian Klaver <adrian.klaver@aklaver.com>) |
Responses |
Re: Inserting default values into execute_values
|
List | psycopg |
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)
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
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.
-Steve
On Wed, Apr 1, 2020 at 1:03 PM Adrian Klaver <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> 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