如何将numpy的NaN对象转换为SQL中的null值？

Question

我有一个Pandas数据框，我要将其插入到SQL数据库中。我直接使用Psycopg2与数据库进行通信，而不是使用SQLAlchemy，因此无法使用Pandas内置的to_sql函数。几乎所有的东西都按照预期工作，只有一个问题：numpy np.NaN值被转换为文本NaN并插入到数据库中。它们应该被视为SQL空值。

所以，我试图制作一个自定义适配器来将np.NaN转换为SQL空值，但是我尝试过的所有方法都导致相同的NaN字符串被插入到数据库中。

我目前正在尝试的代码是：

def adapt_nans(null):
    a = adapt(None).getquoted()
    return AsIs(a)

register_adapter(np.NaN, adapt_nans)

我已经尝试过许多类似的变化，但都没有成功。

- Gregory Arenius

---

个人而言，我认为 NaN 不应该转换为 NULL，因为它们根本不是同一回事，但我可以想象出某些情况下这样做可能是有意义的。我会使用 BEFORE INSERT OR UPDATE ... FOR EACH ROW ... 触发器来进行转换。 - Craig Ringer

我确实理解NaN和NULL之间的一般区别，但在这种特殊情况下，它们实际上是相同的。数据从平面文件读入数据框中，在缺失数据的情况下，Pandas会插入NaN。 - Gregory Arenius

Answer 1

我之前尝试的代码失败了，因为它假设np.Nan是自己的类型，但实际上它是一个浮点数。以下代码来自于psycopg2邮件列表中Daniele Varrazzo的慷慨分享，可以正确地完成任务。

def nan_to_null(f,
        _NULL=psycopg2.extensions.AsIs('NULL'),
        _Float=psycopg2.extensions.Float):
    if not np.isnan(f):
        return _Float(f)
    return _NULL

 psycopg2.extensions.register_adapter(float, nan_to_null)

- Gregory Arenius

---

这个函数似乎无法使用np.float64。不知道为什么。将if f is not _NaN:更改为if not np.isnan(f):可以解决问题。除此以外完美无缺！ - Jens de Bruijn

@JensdeBruijn 我想知道float64类型的NaN是否与常规float类型的NaN不同。 - Gregory Arenius

我也感到惊讶。如果不是那个问题，可能是pandas引起的吗？使用上述语句，我的错误得到了修复。我只是想将其作为一个选项展示给其他遇到同样问题的人。 - Jens de Bruijn

我不得不使用@JensdeBruijn提出的小修复，现在运行得很好。 - cbcoutinho

我也不得不使用小修复，我提交了一份编辑建议。 - Fierr

Answer 2

如果你试图将Pandas数据框的数据插入到PostgreSQL中，并且出现了NaN错误，那么你需要做的是：

import psycopg2

output_df = output_df.fillna(psycopg2.extensions.AsIs('NULL'))

#Now insert output_df data in the table

- Arghya Nandy

---

psycopg 3的等效版本是什么？ - undefined

类型错误：字段'zzz'预期是一个数字，但得到了<psycopg2.extensions.AsIs object at 0x7f6bd5dbdb70>。 - undefined

Answer 3

这个答案是Gregory Arenius的答案的一个替代版本。我已经用检查值是否等于自身的方法替换了条件语句，以适用于任何NaN值。

def nan_to_null(f,
         _NULL=psycopg2.extensions.AsIs('NULL')
         _Float=psycopg2.extensions.Float)):
    if f != f:
        return _NULL
    else:
         return _Float(f)

 psycopg2.extensions.register_adapter(float, nan_to_null)

如果您检查一个NaN值是否等于自身，结果会得到False。这种行为的原理在Stephen Canon的回答中被详细解释。

- A Merii

Answer 4

我相信最简单的方法是：

。

df.where(pd.notnull(df), None)

当导入到Postgres时，None将被“翻译”为NULL。

- Yannis

---

只有在列不是数字/整数类型时才有效。 - Jean-Baptiste Graille