psycopg2 copy_expert()-如何在压缩的csv文件中复制? [英] psycopg2 copy_expert() - how to copy in a gzipped csv file?
问题描述
如果我的表是schema_one.table_five,文件名是file_to_import.csv.gz,为了给文件内容复制到表中,我应该给copy_expert()cmd什么参数?
If my table is schema_one.table_five and my file name is file_to_import.csv.gz, what args do I give the copy_expert() cmd in order to copy the file contents into the table?
这就是我要尝试的:
this_copy = '''COPY schema_one.table_five FROM STDIN with CSV'''
this_file = "file_to_import.csv.gz"
con = psycopg2.connect(dbname=dbname, host=host, port=port, user=user, password=password)
cur = con.cursor()
cur.copy_expert(this_copy, this_file)
这会产生错误:
cur.copy_expert(this_copy, this_file)
TypeError: file must be a readable file-like object for COPY FROM; a writable file-like object for COPY TO.
所以我如何告诉命令首先解压缩文件,然后指定定界符(在这种情况下为'|'),以便可以对其进行处理.
So how do I tell the command to first uncompress the file and then specify a delimiter (in this case '|') so that it can be processed.
第二个问题.如果我的文件位于名为"files_to_import"的目录中,即/home/dir1/dir2/files_to_import/file_to_import.csv.gz,有没有办法我可以仅指定目录并在该目录中的所有文件中使用pgm副本(到同一张桌子)?它们都是.csv.gz文件.
Secondary question. If my file is in a directory called "files_to_import" i.e. /home/dir1/dir2/files_to_import/file_to_import.csv.gz, is there a way that I can specify just the directory and have the pgm copy in all the files in that dir (to the same table)? They would all be .csv.gz files.
添加了12-30-16 0940 MST -针对评论: 试图正确获取COPY语句,但所有这些错误---
Added 12-30-16 0940 MST -- In response to comment: Trying to get COPY statement right, but all these error ---
this_file = "staging.tbl_testcopy.csv.gz"
this_copy_01 = '''COPY staging.tbl_testcopy_tmp FROM STDIN'''
this_copy_02 = '''COPY staging.tbl_testcopy_tmp FROM %s'''
this_copy_03 = '''COPY staging.tbl_testcopy_tmp FROM (%s)'''
this_copy_04 = '''COPY staging.tbl_testcopy_tmp FROM f'''
with gzip.open(this_file, 'rb') as f:
try:
cur.copy_expert(this_copy_01, f)
except Exception, e:
print e
try:
cur.copy_expert(this_copy_02, f)
except Exception, e:
print e
try:
cur.copy_expert(this_copy_03, f)
except Exception, e:
print e
try:
cur.copy_expert(this_copy_04, f)
except Exception, e:
print e
所有这些错误都在同一位置.那么,
All of these error, at the same place. So what should come after 'FROM' ?
syntax error at or near "STDIN"
LINE 1: COPY staging.tbl_testcopy_tmp FROM STDIN
^
syntax error at or near "%"
LINE 1: COPY staging.tbl_testcopy_tmp FROM %s
^
syntax error at or near "("
LINE 1: COPY staging.tbl_testcopy_tmp FROM (%s)
^
syntax error at or near "f"
LINE 1: COPY staging.tbl_testcopy_tmp FROM f
^
推荐答案
copy_expert
的file
参数应该是类似于object的文件,而不是文件名.对于常规的csv文件,您可以使用:
The file
argument to copy_expert
should be a file like object, not the file name. For a regular csv file you could use:
with open("file_to_import.csv", 'rb') as this_file:
cur.copy_expert(this_copy, this_file)
对于压缩文件,您可以使用gzip
模块打开文件:
For a gzipped file you could use the gzip
module to open the file:
import gzip
with gzip.open("file_to_import.csv.gz", 'rb') as this_file:
cur.copy_expert(this_copy, this_file)
要更改分隔符,您必须更改COPY语句.有关更多信息,请参见 COPY 文档.使用 copy_from
(具有可选的sep
参数),而不是copy_expert
.
To change the separator, you'll have to change the COPY statement. See the COPY docs for more information. It might be easier to use copy_from
(which has a optional sep
argument) instead of copy_expert
.
with gzip.open("file_to_import.csv.gz", 'rb') as this_file:
cur.copy_from(this_file, 'staging.tbl_testcopy_tmp', sep='|')
没有命令自动导入目录中的所有文件,您必须获得目录内容的清单并遍历目录.
There isn't a command to automatically import all the files in the directory, you'll have to get a listing of the directory's contents and loop through it.
这篇关于psycopg2 copy_expert()-如何在压缩的csv文件中复制?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!