如何使用 Python 将“NULL"值插入到 PostgreSQL 数据库中? [英] How to insert 'NULL' values into PostgreSQL database using Python?

问题描述

当 Python 中的变量为 None 时，是否有将 NULL 键值输入到 PostgreSQL 数据库的好习惯?

Is there a good practice for entering NULL key values to a PostgreSQL database when a variable is None in Python?

运行此查询:

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

当 user_id 为 None 时，会导致 TypeError 异常.

results in a a TypeError exception when user_id is None.

当值为 None 时，如何使用 psycopg2 驱动程序将 NULL 插入数据库?

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:

1. 从您的 INSERT 语句中省略该字段，或
2. 使用无

另外:为了防止 SQL 注入，您不应该对查询使用普通的字符串插值.

Also: To guard against SQL-injection you should not use normal string interpolation for your queries.

您应该将两 (2) 个参数传递给 execute()，例如:

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

替代方案#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))

这篇关于如何使用 Python 将“NULL"值插入到 PostgreSQL 数据库中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！