From: | Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com> |
---|---|
To: | Stephen Lagree <stephen(dot)lagree(at)gmail(dot)com>, psycopg(at)postgresql(dot)org |
Subject: | Re: Inserting default values into execute_values |
Date: | 2020-03-31 22:57:31 |
Message-ID: | 6db34cfa-c531-18ff-4368-bea8c171f6db@aklaver.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | psycopg |
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(dot)klaver(at)aklaver(dot)com
From | Date | Subject | |
---|---|---|---|
Next Message | Adrian Klaver | 2020-03-31 23:08:29 | Re: Inserting default values into execute_values |
Previous Message | Stephen Lagree | 2020-03-31 22:27:05 | Inserting default values into execute_values |