Read from CSV file and insert into database Python

Question

I am trying to read a CSV file and insert into a PostgreSQL database. But I want 1st and 5th column item as integer. So, I am converting 1st and 5th columns into an integer. But it is showing error < date_2 = int(row[0])

IndexError: list index out of range>

    with open('parts_time.txt') as csvfile:
        readCSV = csv.reader(csvfile, delimiter=',')
        for row in readCSV:

            date_2 = int(row[0])
            date_4 = int(row[4])

            cursor.execute("INSERT INTO parts_time 
            (time_id,time_day,time_month,time_year,vendor_idk)"\
            "VALUES (%s,%s,%s,%s,%s)",
           [date_2,row[1],row[2],row[3],date_4])

You probably have an empty line at the end of the file

DeepSpace
– DeepSpace

2018-12-17 11:23:47 +00:00
Commented Dec 17, 2018 at 11:23 — DeepSpace
– DeepSpace, Commented Dec 17, 2018 at 11:23
is the header in csv same to the headers in table ?

Shrey
– Shrey

2018-12-17 12:04:30 +00:00
Commented Dec 17, 2018 at 12:04 — Shrey
– Shrey, Commented Dec 17, 2018 at 12:04

Oyono · Accepted Answer · 2018-12-17 11:29:47Z

1

Try processing the row only if there is something inside:

with open('parts_time.txt') as csvfile:
    readCSV = csv.reader(csvfile, delimiter=',')
    for row in readCSV:
        if row:
            date_2 = int(row[0])
            date_4 = int(row[4])

            cursor.execute("INSERT INTO parts_time 
            (time_id,time_day,time_month,time_year,vendor_idk)"\
            "VALUES (%s,%s,%s,%s,%s)",
            [date_2,row[1],row[2],row[3],date_4])

answered Dec 17, 2018 at 11:29

Oyono

3671 silver badge8 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Jishnunand P K · Accepted Answer · 2018-12-17 11:46:57Z

0

Seems your file contains empty lines due to that your getting IndexError Try this.

with open('t.txt') as csvfile:
    readCSV = csv.reader(csvfile, delimiter=',')
    for row in readCSV:
        if len(row) != 5:
            continue
        date_2 = int(row[0])
        date_4 = int(row[4])
        cursor.execute("INSERT INTO parts_time (time_id,time_day,time_month,time_year,vendor_idk) VALUES (%s,%s,%s,%s,%s)" % tuple([date_2,row[1],row[2],row[3],date_4]))

answered Dec 17, 2018 at 11:46

Jishnunand P K

2902 silver badges9 bronze badges

Comments

Shrey · Accepted Answer · 2018-12-17 12:50:04Z

The error you see is because of your improper data in csv. While dealing with csv files i always use pandas. By this you don't have to think about the issues like you are facing, it will automatically resolve it for you. Clubbing that with the executemany, will make your code run faster.

import pandas
df = pd.read_csv('parts_time.txt')
df.columns = ['time_id', 'time_day', 'time_month', 'time_year', 'vendor_idk'] # You can skip this line if the column names in csv file matches that in Database
df['time_id'] = df['time_id'].astype(int)
df['vendor_idk'] = df['vendor_idk'].astype(int)
cursor.prepare("insert into parts_time(time_id,time_day,time_month,time_year,vendor_idk) values(:1, :2, :3 ,:4, :5)")
cursor.executemany(None, df.values.tolist())

Collectives™ on Stack Overflow

Read from CSV file and insert into database Python

3 Answers 3

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related