I have developed this script to read
employee_language table records of a PostgreSQL database using Python 2 (due to some OS limitation). I want to find duplicate language records for each employee, check if there are any duplicate language records for each employee, get the most completed language records among duplicated language records and finally return the ids of language records which are not duplicated with ids of most completed language records of duplicated ones (one records among many duplicates for every language record). This method works well for me but when I run this script for a great number of employee records (500K to 1000K) and it take a long time. Therefore, I'm wondering how I can make this method faster and restructure it better.
def read_employees_with_language_record(self): self.apps_cursor.execute(""" SELECT id FROM hr_employee limit 1000 """) employee_ids = self.apps_cursor.fetchall() counter = 0 for employee_id in employee_ids: # Call method to get duplicate language records for employee language_records_not_be_deleted = self.get_employee_language_data( employee_id['id']) counter += 1 # Call method to delete duplicate language records for employee self.delete_employee_duplicate_language_record( employee_id['id'], language_records_not_be_deleted) print 'Employee count ##############', counter def get_employee_language_data(self, employee_id): self.apps_cursor.execute("SELECT id, language_id, hr_employee_id, lang_write, lang_speak, lang_read FROM employee_language WHERE hr_employee_id=%s" % employee_id) employee_languages = self.apps_cursor.fetchall() language_id_list = Counter(language.get('language_id') for language in employee_languages) repeated_language_records =  non_repeated_language_records =  duplicate_language_ids_list = set() repeated_language_records_not_to_be_deleted =  for language in employee_languages: if language_id_list[language['language_id']] > 1: duplicate_language_ids_list.add(language['language_id']) repeated_language_records.append(language) else: non_repeated_language_records.append(language) for unique_record in duplicate_language_ids_list: temp_list =  flag_3 = False flag_2 = False flag_1 = False for repeated in repeated_language_records: if repeated['language_id'] == unique_record: if None not in (repeated['lang_speak'], repeated['lang_read'], repeated['lang_write']): temp_list = repeated flag_3 = True elif (repeated['lang_speak'], repeated['lang_read'], repeated['lang_write']).count(None) == 1 and not flag_3: temp_list = repeated flag_2 = True elif (repeated['lang_speak'], repeated['lang_read'], repeated['lang_write']).count(None) == 2 and not flag_3 and not flag_2: temp_list = repeated flag_1 = True elif not flag_3 and not flag_2 and not flag_1: temp_list = repeated repeated_language_records_not_to_be_deleted.append(temp_list) language_record_ids_not_to_be_deleted =  for record in non_repeated_language_records + repeated_language_records_not_to_be_deleted: language_record_ids_not_to_be_deleted.append(int(record['id'])) return language_record_ids_not_to_be_deleted deleted_language_record_ids_list =  def delete_employee_duplicate_language_record(self, hr_employee_id, record_ids): if record_ids: self.apps_cursor.execute(""" SELECT id FROM employee_language WHERE hr_employee_id=%s AND id NOT IN %s """, (hr_employee_id, tuple(record_ids),)) deleted_language_records = self.apps_cursor.fetchall() for language_record in deleted_language_records: self.deleted_language_record_ids_list.append( language_record['id']) self.apps_cursor.execute(""" DELETE FROM employee_language WHERE hr_employee_id=%s AND id NOT IN %s """, (hr_employee_id, tuple(record_ids),)) def print_total_deleted_language_record(self): print 'Total deleted language records are: ', len( self.deleted_language_record_ids_list)