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.kla...@aklaver.com
<mailto:adrian.kla...@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.kla...@aklaver.com <mailto: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 <mailto:adrian.kla...@aklaver.com>
--
Adrian Klaver
adrian.kla...@aklaver.com
