Re: How to insert either a value or the column default?

On Sat, Aug 23, 2014 at 7:10 PM, W. Matthew Wilson <matt(at)tplus1(dot)com> wrote:
> I have a table that looks sort of like this:
>
> create table tasks
> (
> task_id serial primary key,
> title text,
> status text not null default 'planned'
> );
>
> In python, I have a function like this:
>
> def insert_task(title, status=None):
> ....
>
> and when status is passed in, I want to run a SQL insert statement like this:
>
> insert into tasks
> (title, status)
> values
> (%s, %s)
>
> but when status is not passed in, I want to run this SQL insert instead:
>
> insert into tasks
> (title, status)
> values
> (%s, default)

You can "easily" do that in psycopg with:

class Default(object):
def __conform__(self, proto):
if proto is psycopg2.extensions.ISQLQuote:
return self
def getquoted(self):
return 'DEFAULT'

DEFAULT = Default()

>>> print cur.mogrify('insert into place values (%s, %s)',
['adsf', DEFAULT])
insert into place values ('adsf', DEFAULT)

You can find more details at
http://initd.org/psycopg/docs/advanced.html#adapting-new-python-types-to-sql-syntax

It should be added to the library (it was first discussed in 2003...),
but it's one of these things that will stop working when psycopg will
start using the "extended query protocol" (together with other nifty
features such as string literals for table/columns names) so in my
mind it can only be included when psycopg will be able to do both
client-side parameter interpolation and server-side arguments passing,
and when the distinction between the two strategies will be clear
(this is planned for a future psycopg3 but there is no timeline for it
yet).

-- Daniele