How to export data from CSV file to PostgreSQL table using python?

Question

Python

How to export data from a CSV file to a PostgreSQL table so that it is replaced rather than added to the existing ones?
Now you have to first clear the table, and then only add data using the following code:

import psycopg2

    
conn = psycopg2.connect("host=localhost dbname=postgres user=postgres password=1111111")
    cur = conn.cursor()
    with open('file.csv', 'r', encoding="utf-8") as f:
        next(f)
        cur.copy_from(f, 'table_name', sep=',')
    conn.commit()

But the data is added, but it needs to be replaced.

Answer 1

Without knowledge of the data structure and table structure, there is little to understand here.
But in general, instead of stupid copying from csv via copy_from*(), you need to:
1. Read the file yourself. Use the csv module , it can conveniently parse this format, including skipping the header line.
2. Select a primary key in your data to understand when such a row is already in the database, and when it is not yet. When creating a table, specify the primary key acc. column.
3. interpose the data through request of type INSERT... ON CONFLICT UPDATE. See the postgres documentation for the exact syntax, usually called UPSERT.
The problem with copy_from() is that your database table must have the same set of columns and in the same order as the CSV, although this restriction is completely optional.