Psycopg3:复制语句中的NaN到null转换
Psycopg3:复制语句中的NaN到null转换
提问于 2022-10-23 16:36:08
在psycopg2中，可以使用以下适配器将NaN值隐藏为空：
__REGISTERED = False
def _nan_to_null(f,
_NULL=psycopg2.extensions.AsIs('NULL'),
_Float=psycopg2.extensions.Float):
if not np.isnan(f):
return _Float(f)
return _NULL
def register_nan_adapter():
global __REGISTERED
if not __REGISTERED:
print('Register nan to null adapter for psycopg2...')
psycopg2.extensions.register_adapter(float, _nan_to_null)
__REGISTERED = True
else:
print('nan to null adapter for psycopg2 is already registered!')如何在psycopg3中实现同样的目标？
文档说您可以为每个数据类型创建一个Dumper，所以我首先这样做了：
class NullNan(FloatDumper):
def dump(self, elem):
if np.isnan(elem):
return b"NULL"
else:
return super().dump(elem)
connection.adapters.register_dumper(float, NullNan)但这行不通。只返回一点也没有帮助。
在禤浩焯·克莱维尔回答后更新：
对于插入和更新来说，让dumper返回None似乎是可以的，但是对副本不起作用：
class NullNan(FloatDumper):
def dump(self, elem):
if np.isnan(elem):
return None
else:
return super().dump(elem)
with cursor.copy(f"COPY _r ({col_names_str}) FROM STDIN") as copy:
for index, row in df.iterrows():
rec = row.values.tolist()
copy.write_row(rec)导致例外：
psycopg.errors.QueryCanceled: COPY from stdin failed: error from Python: TypeError - expected string or bytes-like object回答 1
Stack Overflow用户
发布于 2022-10-23 17:21:09
嗯，那比我想的容易多了。事实上，正如两个更新所证明的那样。
import numpy as np
import psycopg
from psycopg.types.numeric import FloatDumper
class NanDumper(FloatDumper):
def dump(self, obj):
if np.isnan(obj):
return None
else:
return super().dump(obj)
con = psycopg.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()
cur.adapters.register_dumper(float, NanDumper)
cur.execute("select %s::float", [np.NaN]).fetchone()
(None,)
cur.execute("select %s::float", [3.68]).fetchone()
(3.68,)
\d float_test
Table "public.float_test"
Column | Type | Collation | Nullable | Default
-----------+------------------+-----------+----------+---------
id | integer | | |
float_fld | double precision | | |
cur.execute("insert into float_test(id, float_fld) values(%s, %s)", [1, np.NaN])
cur.execute("insert into float_test(id, float_fld) values(%s, %s)", [2, 4.56])
con.commit()
select * from float_test;
id | float_fld
----+-----------
1 | NULL
2 | 4.56使用COPY更新。部分解
data = [(5, 8.92), (6, np.NaN)]
with cur.copy("COPY float_test (id, float_fld) FROM STDIN WITH (FORMAT BINARY)") as copy:
copy.set_types(["int4", "float8"])
for row in data:
copy.write_row(row)
con.commit()
select * from float_test;
id | float_fld
----+-----------
5 | 8.92
6 | NaNNaN是每个数值类型的有效‘数字’。不确定为什么NanDumper转换没有被选中。
更新#2。找到了魔法酱汁：
from psycopg.types.numeric import FloatBinaryDumper
class NanBinaryDumper(FloatBinaryDumper):
def dump(self, obj):
if np.isnan(obj):
return None
else:
return super().dump(obj)
con = psycopg.connect(dbname="test", host='localhost', user='postgres', port=5432)
cur = con.cursor()
cur.adapters.register_dumper(float, NanBinaryDumper)
with cur.copy("COPY float_test (id, float_fld) FROM STDIN WITH (FORMAT BINARY)") as copy:
copy.set_types(["int4", "float8"])
for row in data:
copy.write_row(row)
con.commit()
select * from float_test;
id | float_fld
----+-----------
5 | 8.92
6 | NULL页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/74172915
复制相关文章
相似问题
腾讯云开发者
