写入 csv 时如何保留空值

Question

我正在使用 Python 的 csv 模块将数据从 sql server 写入 csv 文件，然后使用复制命令将 csv 文件上传到 postgres 数据库。问题是 Python 的 csv 编写器会自动将 Nulls 转换为空字符串“”，当列是 int 或 float 数据类型时，它会失败我的工作，它会尝试插入这个“”，而它应该是一个 None 或 null 值。

为了尽可能容易地与实现 DB API 的模块进行交互，值 None 被写为空字符串。

https://docs.python.org/3.4/library/csv.html?highlight=csv#csv.writer

保留空值的最佳方法是什么？有没有更好的方法用 Python 编写 csvs？我愿意接受所有建议。

例子：

我有经纬度值：

 42.313270000    -71.116240000
42.377010000    -71.064770000
NULL    NULL

写入 csv 时，它将空值转换为“”：

 with file_path.open(mode='w', newline='') as outfile:
    csv_writer = csv.writer(outfile, delimiter=',', quoting=csv.QUOTE_NONNUMERIC)
    if include_headers:
        csv_writer.writerow(col[0] for col in self.cursor.description)
    for row in self.cursor:
        csv_writer.writerow(row)

.

 42.313270000,-71.116240000
42.377010000,-71.064770000
"",""

无效的

指定表示空值的字符串。默认值为文本格式的 \N（反斜杠-N）和 CSV 格式的未加引号的空字符串。对于不想区分空值和空字符串的情况，即使是文本格式，您也可能更喜欢空字符串。使用二进制格式时不允许使用此选项。

https://www.postgresql.org/docs/9.2/sql-copy.html

回答：

为我解决问题的是将引号更改为 csv.QUOTE_MINIMAL。

csv.QUOTE_MINIMAL 指示 writer 对象仅引用那些包含特殊字符（例如定界符、quotechar 或 lineterminator 中的任何字符）的字段。

相关问题：- Postgresql COPY empty string as NULL 不起作用

原文由 Jonathan Porter 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 1

您在这里有两个选择：更改 csv.writing Python 中的引号选项，或者告诉 PostgreSQL 接受带引号的字符串作为可能的 NULL（需要 PostgreSQL 9.4 或更新版本）

Python csv.writer() 和引用

在 Python 方面，您告诉 csv.writer() 对象添加引号，因为您将其配置为使用 csv.QUOTE_NONNUMERIC ：

指示 writer 对象引用所有非数字字段。

None 值是非数字的，因此导致写入 "" 。

切换到使用 csv.QUOTE_MINIMAL 或 csv.QUOTE_NONE ：

csv.QUOTE_MINIMAL

指示 writer 对象仅引用那些包含特殊字符的字段，例如 delimiter 、 quotechar 或 lineterminator 中的任何字符。

csv.QUOTE_NONE

指示 writer 对象从不引用字段。当前 定界符 出现在输出数据中时，它前面是当前 转义 字符。

由于您所写的只是经度和纬度值，因此此处不需要任何引号，数据中不存在定界符或引号字符。

使用任一选项， None 值的 CSV 输出只是一个空字符串：

 >>> import csv
>>> from io import StringIO
>>> def test_csv_writing(rows, quoting):
...     outfile = StringIO()
...     csv_writer = csv.writer(outfile, delimiter=',', quoting=quoting)
...     csv_writer.writerows(rows)
...     return outfile.getvalue()
...
>>> rows = [
...     [42.313270000, -71.116240000],
...     [42.377010000, -71.064770000],
...     [None, None],
... ]
>>> print(test_csv_writing(rows, csv.QUOTE_NONNUMERIC))
42.31327,-71.11624
42.37701,-71.06477
"",""

>>> print(test_csv_writing(rows, csv.QUOTE_MINIMAL))
42.31327,-71.11624
42.37701,-71.06477
,

>>> print(test_csv_writing(rows, csv.QUOTE_NONE))
42.31327,-71.11624
42.37701,-71.06477
,

PostgreSQL 9.4 COPY FROM , NULL 值和 FORCE_NULL

从 PostgreSQL 9.4 开始，当您使用 FORCE_NULL 选项时，您还可以强制 PostgreSQL 接受带引号的空字符串 NULL s。来自 COPY FROM 文档：

FORCE_NULL

将指定列的值与空字符串匹配，即使它已被引用，如果找到匹配项，则将值设置为 NULL 。在空字符串为空的默认情况下，这会将带引号的空字符串转换为 NULL 。此选项仅在 COPY FROM 中允许，并且仅在使用 CSV 格式时允许。

将列命名为 FORCE_NULL 选项让 PostgreSQL 接受空列和 "" 作为 NULL 列的值，-- 例如：

 COPY position (
    lon,
    lat
)
FROM "filename"
WITH (
    FORMAT csv,
    NULL '',
    DELIMITER ',',
    FORCE_NULL(lon, lat)
);

在这一点上，您在 Python 端使用什么引用选项不再重要。

其他需要考虑的选项

对于来自其他数据库的简单数据转换任务，不要使用 Python

如果您已经查询数据库以整理数据以进入 PostgreSQL，请考虑 直接插入到 Postgres 中。如果数据来自其他来源，使用 外部数据包装器 ( fdw ) 模块 可以让您省去中间人，直接从其他来源将数据拉入 PostgreSQL。

麻木的数据？考虑直接从 Python 使用 COPY FROM 作为二进制文件

通过 二进制 COPY FROM 可以更有效地插入 Numpy 数据；链接的答案用所需的额外元数据和字节顺序扩充了一个 numpy 结构化数组，然后有效地创建了数据的二进制副本，并使用 COPY FROM STDIN WITH BINARY 和 psycopg2.copy_expert() 方法 将其插入到 PostgreSQL 中。这巧妙地避免了数字 -> 文本 -> 数字转换。

持久化数据以处理管道中的大型数据集？

不要重新发明数据管道轮子。考虑使用已经解决效率问题的现有项目，例如 Apache Spark 。 Spark 允许您 将数据视为结构化流，并包含 并行运行数据分析步骤 的基础设施，并且您可以将 分布式结构化数据视为 Pandas 数据帧。

另一种选择可能是查看 Dask 以帮助在分布式任务之间共享数据集以处理大量数据。

即使将一个已经在运行的项目转换为 Spark 可能有点过分，但至少要考虑使用 Apache Arrow ，数据交换平台 Spark 构建于其之上。 pyarrow 项目 可以让您通过 Parquet 文件交换数据，或 通过 IPC 交换数据。

Pandas 和 Numpy 团队在支持 Arrow 和 Dask 的需求方面投入了大量资金（这些项目之间的核心成员有相当大的重叠），并积极致力于使 Python 数据交换尽可能高效，包括 扩展 Python 的 pickle 允许带外数据流的模块， 以避免共享数据时不必要的内存复制。

原文由 Martijn Pieters 发布，翻译遵循 CC BY-SA 4.0 许可协议

Answer 2

你的代码

for row in self.cursor:
    csv_writer.writerow(row)

按原样使用 writer，但您不必那样做。您可以过滤值以使用生成器理解和三元表达式更改某些特定值

for row in self.cursor:
    csv_writer.writerow("null" if x is None else x for x in row)

原文由 Jean-François Fabre 发布，翻译遵循 CC BY-SA 4.0 许可协议