Try and Catch Exception not working as expected using pyodbc in python

Goal: Write a script which will read a file line by line and then insert it into the database. The line should be written to a log file if the insertion fails

Problem to solve for: It appears if the line cannot be inserted, my print statement works which is “Failed to insert into the product table” but the line not inserted should be written to a text file called log.txt. It appears only one of those lines are being inserted and none of the others are written to log file.

Can someone please suggest what is wrong with my try/catch exception? It should be writing all non inserted lines to the log file

import threading
import queue
import pyodbc
import re
import datetime

#Configure the number of threads
n_thread = 5
#Create the queue needed to manage threads
queue = queue.Queue()

db = pyodbc.connect('Driver={SQL Server};'
                      'Server=R-USLA-01006;'
                      'Database=C49_GAP_Reference;'
                      'Trusted_Connection=yes;')


cursor = db.cursor()


class ThreadClass(threading.Thread):
    def __init__(self, queue):
        threading.Thread.__init__(self)
    #Assign thread working with queue
        self.queue = queue

    def run(self):
        while True:
            host = self.queue.get()  #Get from queue job
            print (self.getName() + ":" + host)
            #signals to queue when the task is finished
            self.queue.task_done()

    #Create numbered process such that each thread can read a line
for i in range(n_thread):
    t = ThreadClass(queue)
    t.setDaemon(True)
    #Start thread
    t.start()

hostfile = open("C:\Users\rparpaniDesktop\Powershell\GAP\product_dump.txt", encoding='utf-8')
for line in hostfile:
    #Put line to queue
    # print(line)
    values = line.split("t")
    print(values)

    datetime_object1 = values[17]
    datetime_object2 = values[18]
    datetime_object3 = values[19]

    try:
        x = datetime.datetime.strptime(datetime_object1, '%Y-%m-%d %H:%M:%S.%f')

    except:
        x = datetime.datetime.strptime(datetime_object1, '%Y-%m-%d %H:%M:%S')
    
    print(x)

    try:
        y = datetime.datetime.strptime(datetime_object2, '%Y-%m-%d %H:%M:%S.%f')

    except:
        y = datetime.datetime.strptime(datetime_object2, '%Y-%m-%d %H:%M:%S')
    
    print(y)

    try:
        z = datetime.datetime.strptime(datetime_object3, '%Y-%m-%d %H:%M:%S.%f')

    except:
        z = datetime.datetime.strptime(datetime_object3, '%Y-%m-%d %H:%M:%S')
    
    print(z)
    
    

    QuantityPerOrderUnit = values[21]
    
    try:
        variable = int(QuantityPerOrderUnit)
    except:
        variable = -1

    print(variable) 

    #1.Add a try and catch exception while you insert. Anything not inserted will go into a log file 
    # query = ("INSERT INTO product.ARDMProductTestDump(ArticleID, ProductHierarchy1, ProductHierarchy1Description,ProductHierarchy2) VALUES(" + values[0] + ','  + values[1] + ',' + values[2] + ',' + values[3] + ")")
    # query = ("INSERT INTO product.ARDMProductTestDump(ArticleID, ProductHierarchy1, ProductHierarchy1Description,ProductHierarchy2)" "VALUES(?, ?, ?, ?)")


    #query = ("INSERT INTO product.ARDMProductTestDump(ArticleID, ProductHierarchy1, ProductHierarchy1Description,ProductHierarchy2, ProductHierarchy2Description,ProductHierarchy3, ProductHierarchy3Description,ProductHierarchy4, ProductHierarchy4Description,ProductHierarchy5, ProductHierarchy5Description,ProductHierarchy6, ProductHierarchy6Description, ProductName,ProductDescription,ReceiptDescription,AlternateSKU,ReleaseDate,InsertedDateTime,UpdatedDateTime,OrderUnitID,QuantityPerOrderUnit, VendorProductID, VendorID, VendorName, ImageURL)" "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
    try:
        query = ("INSERT INTO product.ARDMProductTestDump(ArticleID, ProductHierarchy1, ProductHierarchy1Description,ProductHierarchy2, ProductHierarchy2Description,ProductHierarchy3, ProductHierarchy3Description,ProductHierarchy4, ProductHierarchy4Description,ProductHierarchy5, ProductHierarchy5Description,ProductHierarchy6, ProductHierarchy6Description, ProductName,ProductDescription,ReceiptDescription,AlternateSKU,ReleaseDate,InsertedDateTime,UpdatedDateTime,OrderUnitID,QuantityPerOrderUnit, VendorProductID, VendorID, VendorName, ImageURL)" "VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)")
        cursor.execute(query, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], values[9], values[10], values[11], values[12], values[13], values[14], values[15], values[16], x, y, z, values[20], variable, values[22], values[23], values[24], values[25])
        print(query)
        db.commit()

    except pyodbc.Error as e:
        print("Failed to insert record into the product table")
        with open("Log.txt", "w") as text_file:
            text_file.write(line)
            
        
    # print(query)
    queue.put(line)

   
    
    # cursor.execute(query, values[0], values[1], values[2], values[3], values[4], values[5], values[6], values[7], values[8], values[9], values[10], values[11], values[12], values[13], values[14], values[15], values[16], x, y, z, values[20], variable, values[22], values[23], values[24], values[25])

    # db.commit()
    #wait on the queue until everything has been processed
    queue.join()

cursor.close()

Answer

You are opening the log file in write mode each time there is an exception. This means you begin writing at the beginning of the file, overwriting anything previously written to it.

You should open in append mode instead, so that you start writing at the end of the file. Change "w" to "a" in your open() call.