0

I was testing the code out and ended up running "python import.py characters.csv" multiple times. Now when I go into sqlite3 and print out the data it's repeated itself 3 times over, once for every time I ran the import command.

How do I delete the repeated data from the database?

from sys import argv
import cs50
import csv

if len(argv) != 2:
    print('Usage: python import.py [NAME OF CSV FILE]')
    exit(1)

db = cs50.SQL("sqlite:///students.db")
with open(argv[-1], "r") as characters:
    
    reader = csv.DictReader(characters)
    
    for row in reader:

        currentname = row['name'].split()
        first, middle, last = currentname[0], currentname[1] if len(currentname) == 3 else None, currentname[-1]
        
        house = row['house']
        birth = row['birth']
        
        # if len(currentname) == 3:
        #     first, middle, last = currentname
        # else:
        #     first, last = currentname
        #     middle = None
        

        
        db.execute("INSERT INTO students (first, middle, last, house, birth) VALUES(?, ?, ?, ?, ?)", first, middle, last, house, birth)

print("Rows Inserted") 
0

There are two possible methods. The first method is to delete the repeated data only. For this, open the database in PHPLiteAdmin. After that select the table. Then select individual rows until all the repetitions are covered. Then delete the selected values. The other method is to delete all the data from the table and then run import.py once. To delete the contents of the table only, not the table itself, run

DELETE FROM students

command.

Hope this helps. If it does then please check the tickmark.

0

There is a third method. Delete the database and start over. This has the advantage of resetting all automatic counters, particularly any sequential automatic keys that would be entered when rows are added.

If you only delete all the data from a table, the auto incremented counters will not be reset. You can manually reset them though, if you wish.

Or, you could delete the table and rebuild it to do the same.

The point is, there are almost always lots of alternatives, but be sure to consider what may or may not happen along the way. ;-)

0

I just finished submitting pset7, and I encountered the same problem. Turns out if you're using the cs50 IDE you can just use the following commands in the terminal--

sqlite3 students.db
DELETE FROM students;

This will delete all the records in your students table, but it will maintain the structure of the table. So it will not delete the column headers like house, birth, etc. In other words it will be just like how it was from the start. Now you can just run import.py again and reimport all your records. Hope this helps!

EDIT Reading user "Cliff B"'s answer, I went and checked, and it turns out that if you just delete all the records it does not reset the id counter in the students table which is auto incremented with every record added starting from 1. So it's not exactly the same as when you first received the database in your IDE. However, it seems that doesn't matter in the context of this problem as we don't have anything to do with the id field. But it's a curious point to keep in mind.

I submitted the problem and it gave me a full score, so it's probably fine.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .