Menu
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?
381
Example - With INSERT Statement INSERT INTO contacts (first_name, last_name) SELECT first_name, last_name FROM employees WHERE employee_number IS NULL; This PostgreSQL IS NULL example will insert records into the contacts table where the employee_number contains a NULL value.
Unlike other programming languages such as PHP or Java or C, Python does not have a null value. Instead, there is the 'None' keyword that you can use to define a null value.
NULL is a special character when inserting into sql (without quotes). If you use the second insert, it would insert the string "NULL", bu the first will have a statement like: INSERT INTO table (var) VALUES (NULL) which will put a special NULL in for the value.
To insert null values to the database you have two options:
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)) If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
