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

Question

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?

• Ignacio Vazquez-Abrams: Which DB-API adapter are you using?
• xpanta: by DB-API adapter I suppose you mean the db interface psycopg2. If not, can you be more specific?

Answer 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))

• xpanta: Thank you. Can you point me somewhere so I can read why string interpolation in python may lead to sql-injection. This is very interesting and I haven't thought of that.
• mechanical_meat: You're welcome. This describes well what SQL injection attacks are: en.wikipedia.org/wiki/SQL_injection How does this relate to string interpolation?: Say you receive untrusted input to your program. That input could contain, for example, a DROP TABLE command.
• Varun Verma: I'm using None for the column value but get an error: ProgrammingError: column "none" does not exist LINE 1: ...01.00-ng20', report_date='2016年03月30日', ec_enabled=None, ec_s... Here's the original query that's failing: UPDATE kw_daily_data_mart SET hostname='ctrlkey.com', users_licensed=7, licensed_users=10, sw_version='kw2016.01.00-ng20', report_date='2016年03月30日', ec_enabled=None, ec_server_count=1, config_max_file_version=10, av_enabled=True, location_count=1, sso_enabled=False WHERE customer_id=127892 AND installation_id=3585 AND hostname='ctrlkey.com' RETURNING id
• Romulus: I am using None, too and I get the same error as @Varun. Same python package psycopg2, v2.6.2. Posgresql v9.6
• Varun Verma: Solution also works with an update query. Thanks for sharing!

Answer 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)

• bucky: this works for psycopg2 2.8.4, solution with inserting None ends up with the value 'None' in the db, as least for postgres
• Paul Hoffman: If the field being inserted is a text field, using 'NULL', simply inserts those four characters. The answer above (use None) is the correct one.

Answer 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)

Answer 4

A simpler approach which also is practical with high number of columns:

Let row be a list of values to be inserted that may contain None. To insert it into PostgreSQL we do as follows

values = ','.join(["'" + str(i) + "'" if i else 'NULL' for i in row])
cursor.execute('insert into myTable VALUES ({});'.format(values))
conn.commit()

• quents: Maybe off topic but, I could suggest not to insert to a table without specifying the column names.