CODEWITHSUNDEEP
pythonpostgresqlcsvimport
Jeff
Jeff

Reputation: 103

Import CSV without primary key to existing table

I have an existing table in Postgresql that has an id column (serial) for row identification and is the primary key. I have a script to import the CSV's, which do not contain the id column. Here is the code I'm using:

file_list = glob.glob(path)

for f in file_list:
    if os.stat(f).st_size != 0:
        filename = os.path.basename(f)
        arc_csv = arc_path + filename

        data = pandas.read_csv(f, index_col = 0)
        ind = data.apply(lambda x: not pandas.isnull(x.values).any(),axis=1)
        data[ind].to_csv(arc_csv)
        cursor.execute("COPY table FROM %s WITH CSV HEADER DELIMITER ','",(arc_csv,))
        conn.commit()
        os.remove(f)
    else:
        os.remove(f)

The script cannot import the CSV with the id (p_key) column present in the table due to it not existing the CSV, so I have 2 options I can think of: 1- Issue a command to drop the id column before the import and add it back after the import, or 2- Find a way to increase the id column via my cursor.execute command.

My question is which approach is better and a good way of going about it (or of course someone has a better idea!)? Thanks.

Upvotes: 3

Views: 4632

Answers (1)

Victorqedu
Victorqedu

Reputation: 494

COPY command contains the columns you want to insert. You must skip PK in the columns list: COPY table(col1, col2,...)

COPY documentation

Upvotes: 4

Related Questions