psycopg NULL

[Lee Harr]

On 2004-02-18, Diez B. Roggisch <deets_noospaam at web.de> wrote:
>> # None of these work ...
>> #curs.execute('INSERT INTO foo (i) VALUES (%s)' % None)
>> #curs.execute('INSERT INTO foo (i) VALUES (%d)' % None)
>> #curs.execute('INSERT INTO foo (i) VALUES (%i)' % None)
>> #conn.commit()
>
>> Any suggestions?
>
> Try literal NULL - as the statement is a string, the types are figured out
> by pg itself:
>
> curs.execute('INSERT INTO foo (i) VALUES (NULL)')
>


Yes, that works.

But, when I have a larger number of fields to fill with my
INSERT statement, I would rather not have to set up
separate code paths for each one that might be NULL.


curs.execute('CREATE TABLE foo (i integer, x integer, y integer, z integer)')
conn.commit()

vars = {'i': 3, 'x': 2, 'y': 1, 'z': None}
curs.execute('''INSERT INTO foo (i, x, y, z)
    VALUES (%(i)i, %(x)i, %(y)i, %(z)i )''' % vars)



However... that points me in a direction that works. Instead
of using python None, I can use the string 'NULL'


vars = {'i': 3, 'x': 2, 'y': 1, 'z': 'NULL'}
curs.execute('''INSERT INTO foo (i, x, y, z)
    VALUES (%(i)s, %(x)s, %(y)s, %(z)s )''' % vars)



Seems a bit awkward since the value will come back out as
None though...