public inbox for [email protected]  

help / color / mirror / Atom feed

From: Adrian Klaver <[email protected]>
To: Stephen Lagree <[email protected]>
To: [email protected]
Subject: Re: Inserting default values into execute_values
Date: Tue, 31 Mar 2020 15:57:31 -0700
Message-ID: <[email protected]> (raw)
In-Reply-To: <CADkZaxXqU+c6_GnW_L0SOgc63iYHomm0RVSpb_+Kww5scVyA4w@mail.gmail.com>
References: <CADkZaxXqU+c6_GnW_L0SOgc63iYHomm0RVSpb_+Kww5scVyA4w@mail.gmail.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?
> 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
[email protected]

view thread (8+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected]
  Subject: Re: Inserting default values into execute_values
  In-Reply-To: <[email protected]>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox