Обсуждение: Inserting default values into execute_values
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?
It seems the execute_values requires at least one non-default value.
I am trying to do this:
query = "INSERT INTO MYTABLE (id) VALUES (DEFAULT) RETURNING id;"
execute_values(cursor, query, args_list, template=None, page_size=100, fetch=True)
If I don't use a %s argument and just put dummy values in the arglist, I get error
E ValueError: the query doesn't contain any '%s' placeholder
I understand why this doesn't work because it can't extract the placeholder and replicate values there.
If I change DEFAULT to %s and try to use blank tuples I get this
E psycopg2.errors.SyntaxError: syntax error at or near ")"
E LINE 1: INSERT INTO MYTABLE (id) VALUES (),(),() RETURNING id;
E LINE 1: INSERT INTO MYTABLE (id) VALUES (),(),() RETURNING id;
If I use "DEFAULT" as a string it tries to insert a string into an int column, not use the DEFAULT value. Is there a way to insert the default value here? I don't see anything like this in the documentation.
My table looks like this:
"CREATE TABLE MYTABLE (id SERIAL PRIMARY KEY)"
Thanks,
Steve
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?
> It seems the execute_values requires at least one non-default value.
>
> I am trying to do this:
> query = "INSERT INTO MYTABLE (id) VALUES (DEFAULT) RETURNING id;"
> execute_values(cursor, query, args_list, template=None,
> page_size=100, fetch=True)
>
> If I don't use a %s argument and just put dummy values in the arglist, I
> get error
> E ValueError: the query doesn't contain any '%s' placeholder
> I understand why this doesn't work because it can't extract the
> placeholder and replicate values there.
>
> If I change DEFAULT to %s and try to use blank tuples I get this
> E psycopg2.errors.SyntaxError: syntax error at or near ")"
> E LINE 1: INSERT INTO MYTABLE (id) VALUES (),(),() RETURNING id;
>
> If I use "DEFAULT" as a string it tries to insert a string into an int
> column, not use the DEFAULT value. Is there a way to insert the default
> value here? I don't see anything like this in the documentation.
>
> My table looks like this:
> "CREATE TABLE MYTABLE (id SERIAL PRIMARY KEY)"
A solution from Daniele Varrazzo. I can't find the mailing list post
where it appeared, just where I use it in code:
Given a file:
utilities/psycopg_helpers.py
"""Psycopg2 helper code.
Code for extending psycopg2.
"""
import psycopg2
class Default(object):
"""Set up DEFAULT value for a field.
When doing INSERT or UPDATE in Postgres one can use DEFAULT/default
as the value to have the server use the default set on the field.
The below allows
for doing that.
"""
def __conform__(self, proto):
if proto is psycopg2.extensions.ISQLQuote:
return self
def getquoted(self):
return 'DEFAULT'
DEFAULT = Default()
Then import it:
from .utilities.psycopg_helpers import DEFAULT
and use DEFAULT where you want a SQL DEFAULT.
>
> Thanks,
> Steve
--
Adrian Klaver
adrian.klaver@aklaver.com
On 3/31/20 3:57 PM, Adrian Klaver 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? >> It seems the execute_values requires at least one non-default value. >> >> I am trying to do this: >> query = "INSERT INTO MYTABLE (id) VALUES (DEFAULT) RETURNING id;" >> execute_values(cursor, query, args_list, template=None, >> page_size=100, fetch=True) >> >> If I don't use a %s argument and just put dummy values in the arglist, >> I get error >> E ValueError: the query doesn't contain any '%s' placeholder >> I understand why this doesn't work because it can't extract the >> placeholder and replicate values there. >> >> If I change DEFAULT to %s and try to use blank tuples I get this >> E psycopg2.errors.SyntaxError: syntax error at or near ")" >> E LINE 1: INSERT INTO MYTABLE (id) VALUES (),(),() RETURNING >> id; >> >> If I use "DEFAULT" as a string it tries to insert a string into an int >> column, not use the DEFAULT value. Is there a way to insert the >> default value here? I don't see anything like this in the documentation. >> >> My table looks like this: >> "CREATE TABLE MYTABLE (id SERIAL PRIMARY KEY)" > > > A solution from Daniele Varrazzo. I can't find the mailing list post > where it appeared, just where I use it in code: > I was searching the wrong list it is in the pgsql-general list: https://www.postgresql.org/message-id/CA%2Bmi_8ZQx-vMm6PMAw72a0sRATEh3RBXu5rwHHhNNpQk0YHwQg%40mail.gmail.com -- Adrian Klaver adrian.klaver@aklaver.com
> > 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.
Cheers,
-- Daniele
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
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
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
On 4/1/20 2:04 PM, Adrian Klaver wrote:
> 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.
Not concentrating, the above is not correct for the DEFAULT case:
class Default(object):
"""Set up DEFAULT value for a field.
When doing INSERT or UPDATE in Postgres one can use DEFAULT/default
as the value to have the server use the default set on the field.
The below allows
for doing that.
"""
def __conform__(self, proto):
if proto is psycopg2.extensions.ISQLQuote:
return self
def getquoted(self):
return 'DEFAULT'
DEFAULT = Default()
args_list = [(DEFAULT, DEFAULT)]
execute_values(cur, query2, args_list, template=None, page_size=100,
fetch=True)
[(6,)]
select * from t2;
id | name
----+-------
1 | test
2 | test2
3 | name
4 | name
5 | name
6 | name
What we both forgot is that args_list needs to a sequence of sequences.
>
> 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)
>
>
>
>
>
>>
--
Adrian Klaver
adrian.klaver@aklaver.com