如何使用 Python 将“NULL”值插入到 PostgreSQL 数据库中？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4231491/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert 'NULL' values into PostgreSQL database using Python?
提问by xpanta
Is there a good practice for entering NULLkey values to a PostgreSQL database when a variable is Nonein Python?
NULL当变量None在 Python 中时，是否有向 PostgreSQL 数据库输入键值的好习惯？
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 TypeErrorexception when user_idis None.
TypeError当user_idis时导致异常None。
How can a NULLbe inserted into the database when a value is None, using the psycopg2driver?
NULL当值为 时None，如何使用psycopg2驱动程序将 a插入到数据库中？
采纳答案by mechanical_meat
To insert null values to the database you have two options:
要将空值插入数据库，您有两种选择：
- omit that field from your INSERT statement, or
- use
None
- 从您的 INSERT 语句中省略该字段，或
- 用
None
Also: To guard against SQL-injection you should not use normal string interpolation for your queries.
另外：为了防止 SQL 注入，您不应该对查询使用普通的字符串插值。
You should pass two (2) arguments to execute(), e.g.:
您应该将两 (2) 个参数传递给execute()，例如：
mycursor.execute("""INSERT INTO products
(city_id, product_id, quantity, price)
VALUES (%s, %s, %s, %s)""",
(city_id, product_id, quantity, price))
Alternative #2:
备选方案#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))
回答by wolf2600
With the current psycopg, instead of None, use a variable set to 'NULL'.
使用当前的 psycopg，而不是 None，使用设置为“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)
回答by LoMaPh
A simpler approach which also is practical with high number of columns:
一种更简单的方法，也适用于大量列：
Let rowbe a list of values to be inserted that may contain None. To insert it into PostgreSQL we do as follows
让row是一个要插入的值列表，其中可能包含None. 要将其插入 PostgreSQL，我们执行以下操作
values = ','.join(["'" + str(i) + "'" if i else 'NULL' for i in row])
cursor.execute('insert into myTable VALUES ({});'.format(values))
conn.commit()
