python - 在使用python将数据从csv复制到postgres之前如何检查一行是否已经存在

Question

我尝试使用 python 将 csv 数据从多个文件转储到 postgres。我成功了。但是我想在复制到数据库 postgres 之前检查特定行是否已经存在。请检查我的代码

 SQL_STATEMENT = """
    COPY %s FROM STDIN WITH
        CSV
        HEADER
        DELIMITER AS ','
    """

def process_file(conn, table_name, file_object):
    cursor = conn.cursor()
    cursor.
    cursor.copy_expert(sql=SQL_STATEMENT % table_name, file=file_object)
    conn.commit()
    cursor.close()


connection = psycopg2.connect("dbname=dataflow user=postgres host=localhost password=root")
try:
    process_file(connection, 'mytable', f)
finally:
    connection.close()

请建议我怎么做。

Answer 1

COPY 只是将格式正确的数据加载到表中 - 无需预处理。因此，您可以将 csv 复制到临时表，然后将行插入到您的表中，跳过现有的:

   CREATE TABLE temp_t AS SELECT * FROM table_name WHERE false
   ;
   COPY temp_t FROM STDIN WITH
        CSV
        HEADER
        DELIMITER AS ','
    ;
    INSERT INTO table_name 
      SELECT * 
      FROM temp_t
      EXCEPT
      SELECT * 
      FROM table_name
     ;

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

COPY FROM copies data from a file to a table (appending the data to whatever is in the table already)