How to insert 'NULL' values into PostgreSQL database using Python?
To insert null values to the database you have two options:
- omit that field from your INSERT statement, or
- 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 = Nonemycursor.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))
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)
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)