Sep-16-2018, 10:09 AM
I have two SQL files that work correctly and create two tables with one foreign key.
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.
My attempt to create a script, but produces an error type.
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()