Why i have such performance issues using QSqlQuery in Python

Question

I have to add multiple entries into a sqlite database, one by one, because of the keys i have to use. I am doing it with QSqlQuery and facing perfomance issues. The following code takes over 15 Minutes to run through.

def import_vfc(self):
    fname = QtWidgets.QFileDialog.getOpenFileName(QtWidgets.QWidget(), 'Open a file', '',
                                                  'VCF Files (*.vcf)')
    try:
        contacts = import_contacts(fname[0])
        query = QSqlQuery()
        temp_phone_list = []
        temp_email_list = []

        for contact in contacts:
            query.exec("INSERT INTO person (forename, lastname, mergedAddress)" 
                       f"VALUES ('{contact.FirstName}', '{contact.LastName}', '{contact.Address}' )")
            
            query.exec("SELECT max(PersonId) FROM person")
            query.first()
            contact_id = query.value(0)
            
            for number in contact.Phonenumbers:
                temp_phone_list.append(number)
        
            phone_list = list(set(temp_phone_list))#remove duplicates
            for number in phone_list:
                query.exec("INSERT INTO phonenumbers (phonenumber)" f"VALUES ('{number}')")
                query.exec("SELECT max(PhoneNumberId) FROM Phonenumbers")
                query.first()
                phone_id = query.value(0)
                query.exec("INSERT INTO person_phone_junction (personid, phonenumberid) " f"VALUES ('{contact_id}', '{phone_id}')")

            for email in contact.Email:
                temp_email_list.append(email)

            email_list = list(set(temp_email_list))#remove duplicates
            for email in email_list:
                query.exec("INSERT INTO Emails (email)" f"VALUES ('{email}')")
                query.exec("SELECT max(EmailId) FROM Emails")
                query.first()
                email_id = query.value(0)
                query.exec("INSERT INTO person_email_junction (personid, emailid) " f"VALUES ('{contact_id}', '{email_id}')")
            
            if contact.Birthday:
                birthday = contact.Birthday
                query.exec(f"Update Person set Birthday= '{birthday}' where PersonId= {contact_id} ")
        
        self.setup_model()
        self.setup_phone_model()
        self.setup_email_model()
        self.tableView.selectRow(0)
    except Exception as e:
        ErrorLogger.WriteError(traceback.format_exc())
        QtWidgets.QMessageBox.critical(None, 'Exception raised', format(e))
        return        

The first part, getting the contacts for import_contacts(fname[0]) is pretty fast. Only the following qsqlqueries are very slow. All in all there are 168 contacts to insert, which is not too much and should be done mauch faster. How can i improve the performance? Is QSql the main reason?

Answer 1

I have found the solution. The list in the loop were getting longer and longer:

                for number in contact.Phonenumbers:
                temp_phone_list.append(number)
        
            phone_list = list(set(temp_phone_list))#remove duplicates
            for number in phone_list:
                query.exec("INSERT INTO phonenumbers (phonenumber)" f"VALUES ('{number}')")
                query.exec("SELECT max(PhoneNumberId) FROM Phonenumbers")
                query.first()
                phone_id = query.value(0)
                query.exec("INSERT INTO person_phone_junction (personid, phonenumberid) " f"VALUES ('{contact_id}', '{phone_id}')")

so simply clearing them did the job: temp_phone_list.clear() phone_list.clear()