psycopg NULL

[Lee Harr]

When I am retrieving rows using psycopg, a NULL value
from the database will be translated to python None.

What I cannot figure out is how to insert a NULL.



import psycopg

DSN = 'dbname=test6 user=auth1'
conn = psycopg.connect(DSN)
curs = conn.cursor()

curs.execute('CREATE TABLE foo (i integer)')
conn.commit()

# These all work
curs.execute('INSERT INTO foo (i) VALUES (%s)' % 5)
curs.execute('INSERT INTO foo (i) VALUES (%i)' % 4)
curs.execute('INSERT INTO foo (i) VALUES (%d)' % 3)
conn.commit()

# 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()

# If I insert NULL some other way, this works
curs.execute('SELECT * FROM foo')
rows = curs.fetchall()
for row in rows:
if row[0] is None:
print 'None!'



Any suggestions?

 

[Diez B. Roggisch]

# 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)')

 

[Ross M Karchner]

What is the value of psycopg.paramstyle ?

You probably have to follow the paramstyle format to get the automatic
translation between Python None to SQL Null.

http://www.python.org/peps/pep-0249.html

If it is 'pyformat' (Like MySQLdb), then this might 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()

note: all I did was replace the %'s with commas

-Ross

 

[Lee Harr]

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...

 

[Lee Harr]

What is the value of psycopg.paramstyle ?

You probably have to follow the paramstyle format to get the automatic
translation between Python None to SQL Null.

http://www.python.org/peps/pep-0249.html

A ha! Thanks for that link...

If it is 'pyformat' (Like MySQLdb), then this might 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()

note: all I did was replace the %'s with commas

That gives a syntax error ... but it is close!


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



With the pyformat paramstyle we need the %(varname)s style.

Thanks for your help.

 

[Lee Harr]

If it is 'pyformat' (Like MySQLdb), then this might work:

That gives a syntax error ... but it is close!


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



With the pyformat paramstyle we need the %(varname)s style.

Actually, the other way works too, if the 2nd parameter is
a sequence.

curs.execute('INSERT INTO foo (i) VALUES (%s)', (None,))


Thanks again.

 

[Dave Brueck]

That gives a syntax error ... but it is close!


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


With the pyformat paramstyle we need the %(varname)s style.

Lee, I think the following will work as well:

curs.execute('INSERT INTO foo (i) VALUES (%s)', (None,))

(passing in a tuple of arg values)

-Dave