How to insert 'NULL' values into PostgreSQL database using Python?

Is there a good practice for entering NULL key values to a PostgreSQL database when a variable is None in Python?

Running this query:

mycursor.execute('INSERT INTO products (user_id, city_id, product_id, quantity, price) VALUES (%i, %i, %i, %i, %f)' %(user_id, city_id, product_id, quantity, price))

results in a a TypeError exception when user_id is None.

How can a NULL be inserted into the database when a value is None, using the psycopg2 driver?

Solution 1:

To insert null values to the database you have two options:

1. omit that field from your INSERT statement, or
2. use None

Also: To guard against SQL-injection you should not use normal string interpolation for your queries.

You should pass two (2) arguments to execute(), e.g.:

mycursor.execute("""INSERT INTO products 
                    (city_id, product_id, quantity, price) 
                    VALUES (%s, %s, %s, %s)""", 
                 (city_id, product_id, quantity, price))

Alternative #2:

user_id = None
mycursor.execute("""INSERT INTO products 
                    (user_id, city_id, product_id, quantity, price) 
                    VALUES (%s, %s, %s, %s, %s)""", 
                 (user_id, city_id, product_id, quantity, price))

Solution 2:

With the current psycopg, instead of None, use a variable set to 'NULL'.

variable = 'NULL'
insert_query = """insert into my_table values(date'{}',{},{})"""
format_query = insert_query.format('9999-12-31', variable, variable)
curr.execute(format_query)
conn.commit()

>> insert into my_table values(date'9999-12-31',NULL,NULL)

Solution 3:

Here is my solution:

text = 'INSERT INTO products (user_id, city_id, product_id, quantity, price) VALUES (%i, %i, %i, %i, %f)' %(user_id, city_id, product_id, quantity, price))

text = text.replace("nan", "null")

mycursor.execute(text)