Missing intermediate records from the CSV file while importing it in to My SQL by using python program

Question

I am using below python program to read the CSV file continuously and import its latest new records in to My SQL Database. The problem is missing of intermediate random records. For Example if CSV contain 100 records then MySQL DB contains less number of records

This works fine for a static CSV file, e.g. one where all the data to be processed and is already contained within the CSV file...

CSV file is updated by continuously by another application. I want to read that CSV file whenever there is change in it (new records appended in it), and then copy its latest records in to MySQL DB table.

import csv
import time
import mysql.connector


def csv_cycle(filex):
    with open(filex) as fd:
        x = csv.reader(fd)
        while True:
            for r in x:
                yield tuple(r)
            fd.seek(fd.tell())
            fd.readline()  # skips the header


file = 'A.csv'

mydb = mysql.connector.connect(host='localhost', user='root', passwd='admin$23', port=3306, database='sbo_alarms',
                               auth_plugin='mysql_native_password')
mycursor = mydb.cursor()
sql_insert = '''INSERT INTO alarms (Previous_State,Current_State,AlarmText,Priority,Category,TimeStamp)
VALUES (%s,%s,%s,%s,%s,%s)'''

for row in csv_cycle(file):
    try:
        mycursor.execute(sql_insert, row)
        mydb.commit()
    except Exception as e:
        print(e)

Answer 1

I have done some testing.

Start creating a A.csv file with 2 lines.

When starting your script the 2 records are read, and the script waits for next input.

Doing TYPE A.csv >>A.csv, which effectively doubles the number of lines in the CSV file, result in these 2 extra lines being read.

Repeating the TYPE A.csv >>A.csv, results in 8 lines.

Again repeating the TYPE A.csv >>A.csv, results in 16,32,64 lines.

But when doin it again i repeatedly get a response that 167 lines are processed. (and not the expected 128).

End of the output looks like:

156 ('1', '2', '3')
157 ('a', 'b', 'c')
158 ('1', '2', '3')
159 ('a', 'b', 'c')
160 ('1', '2', '3')
161 ('a', 'b', 'c')
162 ('1', '2', '3')
163 ('a', 'b', 'c')
164 ('1', '2', '3')
165 ('a', 'b', 'c')
166 ('1', '2', '3')
167 ('a', '')

I started with A.csv containing:

"a","b","c"
1,2,3

And the code I used (removed the database, and add echo to screen of processed records:

import csv
import time
import mysql.connector


def csv_cycle(filex):
    with open(filex) as fd:
        x = csv.reader(fd)
        while True:
            for r in x:
                yield tuple(r)
            #fd.seek(fd.tell())
            #fd.readline()  # skips the header

file = 'A.csv'

i=1
for row in csv_cycle(file):
    try:
        print(i, row)
        i=i+1
    except Exception as e:
        print('.')
        print(e)