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

On 08/24/2014 11:50 AM, Daniele Varrazzo wrote:
> 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)
>

Well that is cool. So you could do:

status = None

In [17]: cur.execute("insert into tasks(title, status) values(%s, %s)",
["first", status or DEFAULT])

In [18]: con.commit()

test=> select * from tasks;
task_id | title | status
---------+-------+---------
1 | first | planned

> yet).
>
> -- Daniele
>
>

--
Adrian Klaver
adrian(dot)klaver(at)aklaver(dot)com