Importing data from csv to database using Python

Question

I need to create a database using info from a csv file, format:
name,house,birth example:

Adelaide Murton,Slytherin,1982  
Adrian Pucey,Slytherin,1977

Then I need to export to the database after splitting the name in the following format: first | middle | last | house | birth

Below is my code; it exports like 1st row only first name, 2nd row only middle name, 3d row only last name, 4th row only house , etc. and everything else appears NULL. I end up having 792 rows instead of 40 in my database. Output image

import csv
import sys
import cs50

db = cs50.SQL("sqlite:///students.db")

if len(sys.argv) != 2:
        sys.exit("Usage: import.py file.csv")
    
with open(sys.argv[1],'r') as f:
    reader = csv.DictReader(f)
    for row in reader:
        x = row["name"].split()
        db.execute("INSERT INTO students (first, middle, last, house, birth) VALUES (?,?,?,?,?)", x[0], x[1] if len(x)==3 else None, x[2] if len(x)==3 else x[1], row["house"], row["birth"])

Can you edit your question to show what your output looks like? — irowe
– irowe, Commented Jul 14, 2020 at 21:09
Did you try to do some debugging? Are the values of row and x in the loop what you expect them to be? — mkrieger1
– mkrieger1, Commented Jul 14, 2020 at 21:10
I added the output image; yes, I did the debugging and the values of row and x are what they should be. — annawho
– annawho, Commented Jul 14, 2020 at 21:16

DinoCoderSaurus · Accepted Answer · 2020-07-15 10:49:07Z

1

Make sure the students table is empty before running import, otherwise all the rows from all the attempts, buggy or not, are in the database. A bug that creates the described output would only insert approx 280 rows. (7 db rows per 1 csv row)

answered Jul 15, 2020 at 10:49

DinoCoderSaurus

6,5502 gold badges13 silver badges17 bronze badges

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

1 Comment

annawho Over a year ago

Yes, that was it, after I emptied it it worked. Thank you!

Collectives™ on Stack Overflow

Importing data from csv to database using Python

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related