Error when inserting CSV file - Printable Version +- Python Forum (https://python-forum.io) +-- Forum: Python Coding (https://python-forum.io/forum-7.html) +--- Forum: Data Science (https://python-forum.io/forum-44.html) +--- Thread: Error when inserting CSV file (/thread-12856.html) |
Error when inserting CSV file - vndywarhol - Sep-16-2018 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 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 Quote:ROW EXAMPLE in csv: 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() RE: Error when inserting CSV file - Larz60+ - Sep-16-2018 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) |