如何将 numpy NaN 对象转换为 SQL 空值? [英] How do I convert numpy NaN objects to SQL nulls?

问题描述

我有一个要插入 SQL 数据库的 Pandas 数据框.我直接使用 Psycopg2 与数据库对话，而不是 SQLAlchemy，所以我不能使用内置 to_sql 函数的 Pandas.除了 numpy np.NaN 值被转换为文本作为 NaN 并插入到数据库中之外，几乎一切都按预期工作.它们确实应该被视为 SQL 空值.

I have a Pandas dataframe that I'm inserting into an SQL database. I'm using Psycopg2 directly to talk to the database, not SQLAlchemy, so I can't use Pandas built in to_sql functions. Almost everything works as expected except for the fact that numpy np.NaN values get converted to text as NaN and inserted into the database. They really should be treated as SQL null values.

因此，我正在尝试制作一个自定义适配器来将 np.NaN 转换为 SQL null，但我尝试过的所有操作都会导致在数据库中插入相同的 NaN 字符串.

So, I'm trying to make a custom adapter to convert np.NaN to SQL null but everything I've tried results in the same NaN strings being inserted in the database.

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

The code I'm currently trying is:

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

register_adapter(np.NaN, adapt_nans)

我已经尝试了许多围绕这个主题的变体，但都没有成功.

I've tried a number of variations along this theme but haven't had any luck.

推荐答案

我之前尝试的代码失败了，因为它假定 np.Nan 是它自己的类型，而实际上它是一个浮点数.以下代码，由 Daniele Varrazzo 提供psycopg2 邮件列表，正确完成工作.

The code I was trying previously fails because it assumes that np.Nan is its own type when it is actually a float. The following code, courtesy of Daniele Varrazzo on the psycopg2 mailing list, does the job correctly.

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)

这篇关于如何将 numpy NaN 对象转换为 SQL 空值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！