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?
To insert null values to the database you have two options:
NoneAlso: 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))
DROP TABLE command.
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)
None) is the correct one.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)
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()
