我正在使用Python的csv模块将数据从sql server写入一个csv文件,然后使用copy命令将csv文件上传到posterre数据库。问题是Python的csv编写器会自动将null转换为空字符串“”,当列是int或float数据类型时,它会失败,并且它会尝试在应该是None或null值时插入此“”。
为了尽可能方便地与实现DB API的模块进行交互,将值None作为空字符串写入。
https://docs.python.org/3.4/library/csv.html?highlight=csv#csv.writer
保持空值的最好方法是什么?用Python编写csv有没有更好的方法?我愿意接受所有的建议。
范例:
我有远期和远期价值:
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
"",""
型
NULL
指定表示空值的字符串。文本格式的默认值为\N(反斜杠-N),CSV格式的默认值为不带引号的空字符串。在不想区分空值和空字符串的情况下,您可能更喜欢使用空字符串(即使是文本格式)。使用二进制格式时不允许使用此选项。
https://www.postgresql.org/docs/9.2/sql-copy.html
答案:
对我来说,解决这个问题的是将引用更改为csv.QUOTE_MINIMAL。
命令writer对象只引用那些包含特殊字符(如分隔符、引号或行终止符中的任何字符)的字段。
相关问题:-Postgresql COPY empty string as NULL not work
6条答案
按热度按时间1cklez4t1#
你有两个选择:在Python中更改
csv.writing
引号选项,或告诉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
对象只引用那些包含特殊字符的字段,如 *,quotechar 或 * linetimator * 中的任何字符。csv.QUOTE_NONE
指示
writer
对象从不引用字段。当当前**出现在输出数据中时,它前面是当前 escapechar 字符。因为你所写的只是经度和纬度值,所以这里不需要任何引号,数据中没有分隔符或引号字符。
无论使用哪种选项,
None
值的CSV输出都是一个简单的空字符串:字符串
PostgreSQL 9.4
COPY FROM
、NULL
值和FORCE_NULL
从PostgreSQL 9.4开始,当你使用
FORCE_NULL
选项时,你也可以强制PostgreSQL接受带引号的空字符串作为NULL
s。从COPY FROM
documentation:FORCE_NULL
将指定列的值与空字符串进行匹配,即使它已被引用,如果找到匹配项,则将值设置为
NULL
。在默认情况下,空字符串为空,这会将引用的空字符串转换为NULL
。此选项仅在COPY FROM
中允许,并且仅在使用CSV格式时允许。在
FORCE_NULL
选项中删除列可以让PostgreSQL接受空列和""
作为这些列的NULL
值,例如:型
在这一点上,你在Python端使用了什么引用选项就不再重要了。
其他需要考虑的选项
其他数据库的简单数据转换任务,不要使用Python
如果你已经查询数据库来整理数据进入PostgreSQL,考虑 * 直接插入到PostgreSQL *。如果数据来自其他来源,使用foreign data wrapper (fdw) module可以让你省去中间人,直接从其他来源将数据拉入PostgreSQL。
Numpy数据?考虑使用COPY FROM作为二进制文件,直接从Python
Numpy数据可以通过二进制
COPY FROM
更有效地插入;链接的答案用所需的额外元数据和字节排序来增加numpy结构化数组,然后有效地创建数据的二进制副本,并使用COPY FROM STDIN WITH BINARY
和psycopg2.copy_expert()
方法将其插入PostgreSQL。这巧妙地避免了数字->文本->数字转换。持久化数据以处理管道中的大型数据集?
不要重新发明数据管道轮子。考虑使用现有的项目,如Apache Spark,它们已经解决了效率问题。Spark允许您将数据视为结构化流,并包括run data analysis steps in parallel的基础设施,您可以处理distributed, structured data as Pandas dataframes。
另一种选择是查看Dask,以帮助在分布式任务之间共享数据集,从而处理大量数据。
即使将一个已经运行的项目转换到Spark可能是一个太远的步骤,至少考虑使用Apache Arrow,Spark构建在上面的数据交换平台。
pyarrow
project可以让你通过Parquet文件或exchange data over IPC交换数据。Pandas和Numpy团队在支持Arrow和Dask的需求方面投入了大量资金(这些项目之间的核心成员有相当大的重叠),并正在积极努力使Python数据交换尽可能高效,包括extending Python's
pickle
module to allow for out-of-band data streams,以避免共享数据时不必要的内存复制。fiei3ece2#
您的代码
字符串
按原样使用writer,但您不必这样做。您可以使用生成器解析和三进制表达式筛选值以更改某些特定值
型
xxe27gdn3#
您正在请求
csv.QUOTE_NONNUMERIC
。这将把所有不是数字的内容转换为字符串。您应该考虑使用csv.QUOTE_MINIMAL
,因为它可能更符合您的要求:测试码:
字符串
结果:
test1.csv:
型
test2.csv:
型
hjqgdpho4#
我使用Python的csv模块将数据从sql server写入csv文件,然后使用copy命令将csv文件上传到postgres数据库。
我相信您真正的需求是需要在文件系统中跳跃数据行,正如上面的句子和问题标题所表明的那样,你现在是**问题是,csv格式对RDBMS的NULL概念提供了很差的支持。让我通过稍微改变一下问题来解决你的问题。我想向你介绍一下parquet格式。给定一组表,行在内存中,它允许您 * 非常快速 * 将它们持久化到压缩的二进制文件中,并恢复它们,元数据和NULL完好无损,没有文本引用的麻烦。下面是一个例子,使用pyarrow 0.12.1Parquet引擎:
字符串
输出量:
型
一旦恢复了数据库框架中的行,就可以自由地调用
df2.to_sql()
或使用其他一些常用技术将数字和NULL放入DB表中。编辑:
如果你能在PG服务器上运行
.to_sql()
,或者在同一个局域网上,那么就这样做。否则你最喜欢的技术可能会涉及.copy_expert()
。为什么?总的来说,使用psycopg 2,“批量查询很慢”。中间层,如sqlalchemy和pandas,以及编写良好的应用程序,关心插入性能,将使用.executemany()
。其想法是一次发送大量行,所以TCP会得到一个巨大的SQL文本缓冲区,并一次性发送,这会使端到端通道的带宽饱和,就像copy_expert向TCP发送一个大缓冲区以获得高带宽一样。相比之下,psycopg 2驱动程序缺乏对高性能executemmany的支持。从2.7.4开始,它只是一次执行一个项目,通过WAN发送SQL命令,并在发送下一个命令之前等待一个往返时间的结果。Ping您的服务器;如果ping时间表明每秒可以进行十几次往返,然后计划每秒只插入十几行。大部分时间都花在等待应答包上,而不是花在处理数据库行上。如果将来psycopg 2能提供更好的支持,那就太好了。
yizd12fk5#
从Python 3.12开始,你可以使用引号
csv.QUOTE_NOTNULL
,它的作用和它看起来的一样:它总是引用值(即使是数字值),除了None。它看起来是这样的:
字符串
正如你所指出的,PostgreSQL的文档说
NULL
默认为“CSV格式的无引号空字符串”,所以最后一行将被视为两个NULL。gv8xihay6#
我会使用pandas、psycopg2和sqlalchemy。确保安装了它们。来自您当前的工作流程,避免写入csv
字符串