Python Forum
Error when inserting CSV file
Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Error when inserting CSV file
#1
I have two SQL files that work correctly and create two tables with one foreign key.

CREATE TABLE IF NOT EXISTS department (
    department_id SERIAL NOT NULL PRIMARY KEY,
    department_name VARCHAR(255) NOT NULL,
);
CREATE TABLE IF NOT EXISTS employee (
    employee_id SERIAL NOT NULL PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    employee_department INTEGER REFERENCES department(department_id) NOT NULL,
);
I wrote a script, with which I could load a csv file into my created table. This works as expected.

import psycopg2

conn = psycopg2.connect("host=172.17.0.2 dbname=accounts user=user password=password")
cur = conn.cursor()
with open('DEPTS.csv', 'r') as f:
    next(f)
    cur.copy_from(f, 'department', columns=('department_name'), sep=',')
conn.commit()
DEPARTMENT TABLE
Quote:department_id | department_name
[PK] integer | character(255)
1 | IT
2 | ACCOUNT
3 | BUSINESS
4 | SERVICE

I also successfully created the second table, but I can not fill it. Because in my csv file the data represents a string, and the field employee_department expects an integer. I tried to make the employee_department to be a string, but then an error occurs on the type mismatch. I'm new to this and do not understand the problem in my sql script or code? Help my please.

Quote:EMPLOYEE TABLE
employee_id | first_name | employee_department
[PK] integer | character(255) | integer

Quote:ROW EXAMPLE in csv:
1 Richelle BUSINESS
2 Angla BUSINESS
3 JOEL SERVICE
4 Mariann BUSINESS

My attempt to create a script, but produces an error type.

import psycopg2

conn = psycopg2.connect("host=172.17.0.2 dbname=accounts user=user password=password")
cur = conn.cursor()
with open('EMPLOYEE.csv', 'r') as f:
    next(f)
    cur.copy_from(f, 'employee', columns=('employee_id', 'first_name', 'employee_department'), sep=',')
    conn.commit()
conn.commit()
Reply
#2
if it is indeed a csv file, you should open and read using pandas package. Code would look like (replaced f with csvf, single letter names shouldn't be used, they mean nothing to whoever (other than the author) reads them, especially this one because starting with python 3.6, f stands for f-string and may be misinterpreted).

You should make sure values for delimiter and quotechar are correct for your file
only use next(fp) if you want to eliminate first row, which is the header and is useful for SQL insert

import pandas

with open 'EMPLOYEE.csv', 'r') as csvf:
    df = pandas.read_csv(csvf)
    df.to_sql('employee', conn, if_exists='append', index=False)
Reply


Forum Jump:

User Panel Messages

Announcements
Announcement #1 8/1/2020
Announcement #2 8/2/2020
Announcement #3 8/6/2020