Psycopg2 copy_from引发DataError：整数的无效输入语法 [英] Psycopg2 copy_from throws DataError: invalid input syntax for integer

问题描述

我有一个带有一些整数列的表。我正在使用psycopg2的copy_from

I have a table with some integer columns. I am using psycopg2's copy_from

conn = psycopg2.connect(database=the_database,
                            user="postgres",
                            password=PASSWORD,
                            host="",
                            port="")

print "Putting data in the table: Opened database successfully"
cur = conn.cursor()
with open(the_file, 'r') as f:
    cur.copy_from(file=f, table = the_table, sep=the_delimiter)
    conn.commit()
print "Successfully copied all data to the database!"
conn.close()

错误表明它希望第8列是整数而不是字符串。但是，Python的write方法只能将字符串读取到文件中。因此，当您的文件只能具有整数的字符表示形式（例如str（your_number））时，如何将充满数字的字符串表示形式的文件导入具有期望整数的列的postgres表中。

The error says that it expects the 8th column to be an integer and not a string. But, Python's write method can only read strings to the file. So, how would you import a file full of string representation of number to postgres table with columns that expect integer when your file can only have character representation of the integer (e.g. str(your_number)).

您要么必须以整数格式将数字写入文件（Python的write方法不允许），要么psycopg2应该足够聪明，作为copy_from过程的一部分进行转换，显然不是。知道了任何想法。

You either have to write numbers in integer format to the file (which Python's write method disallows) or psycopg2 should be smart enough to the conversion as part of copy_from procedure, which it apparently is not. Any idea is appreciated.

推荐答案

我最终使用了copy_expert命令。请注意，在Windows上，您必须设置文件的权限。这篇文章非常有用设置权限。

I ended up using copy_expert command. Note that on Windows, you have to set the permission of the file. This post is very useful setting permission.

with open(the_file, 'r') as f:            
        sql_copy_statement = "copy {table} FROM '"'{from_file}'"' DELIMITER '"'{deli}'"' {file_type} HEADER;".format(table = the_table,
                                                                                                                     from_file = the_file,
                                                                                                                     deli = the_delimiter,
                                                                                                                     file_type = the_file_type                                                                                                                                         
                                                                                                                    )
        print sql_copy_statement
        cur.copy_expert(sql_copy_statement, f)
        conn.commit()

这篇关于Psycopg2 copy_from引发DataError：整数的无效输入语法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！