Connection.commit() not uploading all rows to database

Question

I am trying to populate a MySQL database table from a CSV file using PyMySQL. The CSV file has approx 948,000 rows. The script works fine but only approximately 840,000 rows appear in the database, I don't know where the rest go.

I am guessing this has something to do with connection.commit() method so I have tried committing at the end of the script as well as after every 10,000 INSERT queries but nothing works so far. Any ideas what I might be doing wrong?

I have attached the relevant code snippet below:

    with gzip.open(temp_file_path, "rt", encoding="utf-8") as f:
        reader = csv.reader(f)
        for row in reader:
            # if num % 10000 == 0:
                # conn.commit()
            print("[+] Processing row: ", num)

            sql = "INSERT INTO `{0}`({1}) VALUES({2})".format(table_name, ", ".join(columns),
                                                              ", ".join(["%s"] * len(columns)))
            result = cursor.execute(sql, row)
            if result == 1:
                num += 1
            else:
                print("Not inserted!")
    conn.commit()
    cursor.close()
    conn.close()

I am reading the CSV using CSV's reader module hence my row is already an array. — Muhammad Mutahhar Bin Muzaffar, Mar 16 '22 at 19:51
yes, your commit has to be in the loop see https://stackoverflow.com/questions/46543436/speeding-bulk-insert-into-mysql-with-python — nbk, Mar 16 '22 at 20:00
I have tried doing that as well. See the commented code. Even if I move the commit inside the loop, the database still shows approx 840K records whereas the file has 940K records. — Muhammad Mutahhar Bin Muzaffar, Mar 16 '22 at 20:04
please read the link and the solutions and see the commit has to run for every row — nbk, Mar 16 '22 at 20:09
Read the link and implemented the batch insert solution with commit inside the loop. It's a much faster approach but still doesn't solve my problem. The database shows only 907K records where it should have been 948K records. — Muhammad Mutahhar Bin Muzaffar, Mar 16 '22 at 20:21
so you have more rows as before, i think a few have an error which you didn't check. so extend your code with soe error handling — nbk, Mar 16 '22 at 20:30

Connection.commit() not uploading all rows to database

0 Answers0