python怎么往数据库插null_如何使用Python将NULL值插入PostgreSQL数据库？

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:

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