def click_pbAccept(self): # Обновить/добавить a = self.leFilter.text().strip() if lenl(a) < 10 or lenl(a) > 11: return None elif lenl(a) == 10: a = l('7' + self.leFilter.text()) else: if str(l(a))[0] == '8': a = '7' + str(l(a))[1:] elif str(l(a))[0] == '7': q = 0 else: return None self.dbconn.connect() dbcursor = self.dbconn.cursor() dbcursor.execute('SELECT count(*) FROM contacts WHERE phone = %s', (a,)) rows = dbcursor.fetchall() if rows[0][0] == 0: dbcursor.execute('INSERT contacts (phone, about, edit_date) VALUES(%s,%s,%s)',(a, self.leAbout.text().strip(), datetime.now())) else: dbcursor.execute('UPDATE contacts SET about = %s, edit_date = %s WHERE phone = %s',(self.leAbout.text().strip(), datetime.now(), a)) self.dbconn.commit() self.leFilter.setText('') self.setup_tableWidget() return
def first_setup_tableWidget(self): self.tableWidget.setColumnCount(0) self.tableWidget.setRowCount(0) # Кол-во строк из таблицы sql = 'SELECT phone, tip, about, DATE_FORMAT(edit_date,"%d.%m %H:%i") FROM contacts' if lenl(self.leFilter.text()) > 0: sql += ' WHERE phone LIKE "%' + str(l(self.leFilter.text())) + '%"' self.dbconn.connect() read_cursor = self.dbconn.cursor() read_cursor.execute(sql) rows = read_cursor.fetchall() self.tableWidget.setColumnCount(4) # Устанавливаем кол-во колонок self.tableWidget.setRowCount(len(rows)) # Кол-во строк из таблицы for i, row in enumerate(rows): for j, cell in enumerate(row): label = QLabel() if j == 0: label.setText(fine_phone(str(cell))) else: label.setText(str(cell)) label.setAlignment(Qt.AlignCenter) self.tableWidget.setCellWidget(i, j, label) # self.tableWidget.setItem(i, j, QTableWidgetItem(str(cell))) # Устанавливаем заголовки таблицы self.tableWidget.setHorizontalHeaderLabels(["телефон", "тип", "описание","дата"]) # Устанавливаем выравнивание на заголовки self.tableWidget.horizontalHeaderItem(0).setTextAlignment(Qt.AlignCenter) self.tableWidget.horizontalHeaderItem(1).setTextAlignment(Qt.AlignCenter) self.tableWidget.horizontalHeaderItem(2).setTextAlignment(Qt.AlignCenter) # делаем ресайз колонок по содержимому self.tableWidget.resizeColumnsToContents() return
elem = p(d=driver, f='c', **clicktity['cAddrFACTtoo'] ) # Адреса регистрации и проживания всегда отличаются wj(driver) elem.click() wj(driver) probel = '' if s(res_inp['РайонРЕГ']) != '' and s(res_inp['ГородРЕГ']) != '': probel = ' ' res_inp['РайонРЕГ'] = s(res_inp['РайонРЕГ']) + probel + s( res_inp['ГородРЕГ']) if s(res_inp['НасПунктРЕГ']) == '': res_inp['НасПунктРЕГ'] = s(res_inp['РайонРЕГ']) if s(res_inp['УлицаРЕГ']) == '': res_inp['УлицаРЕГ'] = s(res_inp['НасПунктРЕГ']) if lenl(res_inp['ИндексРЕГ']) != 0: my_input(driver, ['ИндексРЕГ'], res_inp, inputtity) if chk(d=driver, f='p', **clicktity['ПроверкаИндекса']): elem = p(d=driver, f='p', **inputtity['ИндексРЕГ']) wj(driver) elem.clear() wj(driver) elem.send_keys(' ') wj(driver) if chk(d=driver, f='p', **inputtity['РегионРЕГ']): if p(d=driver, f='p', **inputtity['РегионРЕГзнач']) == '': my_input(driver, ['РегионРЕГ'], res_inp, inputtity) if chk(d=driver, f='p', **inputtity['РайонРЕГ']): if p(d=driver, f='p', **inputtity['РайонРЕГзнач']) == '': my_input(driver, ['РайонРЕГ'], res_inp, inputtity) if chk(d=driver, f='p', **inputtity['НасПунктРЕГ']):
write_row = (xlsx_file_cut[0:xlsx_file_cut.rfind('.xlsx')], ) for k, cell in enumerate(row): if fields[k][2:] == 'date': try: write_row += (datetime.strptime(cell.value, "%d.%m.%Y").date(), ) except: write_row += (datetime.strptime('11.11.1111', "%d.%m.%Y").date(), ) print(datetime.now().strftime("%H:%M:%S") + ' В файле ' + xlsx_file_cut + ' в строке ' + str(j + 1) + ' в поле ' + fields[k] + ' значение ' + str(cell.value) + ' сброшено до 11.11.1111') elif fields[k] == 'snils': write_row += (l(cell.value), ) if not (lenl(cell.value) < 12 and l(cell.value) > 100): omit = True print('СНИЛС ' + str(cell.value) + ' пропущен') elif fields[k] == 'from_tbl': write_row += (xlsx_file_cut[0:xlsx_file_cut.rfind('.xlsx')], ) elif fields[k] == 'id': q = 0 else: write_row += (cell.value, ) if not omit: write_rows.append(write_row) if j % 10000 == 0: write_cursor = dbconn.cursor() write_cursor.executemany(sql, write_rows) dbconn.commit() write_rows = []
region = chuvak(row[headers['p_region']]) region_ch = 'p' if not region: region = row[headers['p_place']] kladr_ok = False region_ch = 'p' if not region: region = 'РЕГИОН НЕ УКАЗАН' region_id = -1 for j, alfa_region in enumerate(ALFA_REGIONS): if alfa_region.upper().find(region) > -1: region_id = ALFA_REGIONS[alfa_region] break b_country = '' if lenl(row[headers['b_country']]) > 0: b_country = row[headers['b_country']] b_region = '' if lenl(row[headers['b_region']]) > 0: b_region = row[headers['b_region']] b_district = '' if lenl(row[headers['b_district']]) > 0: b_district = row[headers['b_district']] b_place = '' if lenl(row[headers['b_place']]) > 0: b_place = row[headers['b_place']] if b_country.upper().strip() == 'РОССИЯ' or b_country.upper().strip( ) == 'РФ': b_country = '' if len(b_place) > 35:
path = get_path(sys.argv[1]) print('\n' + datetime.now().strftime("%H:%M:%S") + ' Начинаем преобразование и нарезку xlsx файлов \n') k = 1 # Счетчик строк в csv file_number = 1 cl_csvs = [] for i, sheet in enumerate( sheets): # Загружаем все xlsx файлы по мере сохранения в БД for j, row in enumerate(sheet.rows): # Теперь строки if j == 0: continue cl_csv = {} if lenl(row[keys[IN_SNILS[0]]].value ) < 12: # and l(row[keys[IN_SNILS[0]]].value) > 100: cl_csv[IN_SNILS[0]] = '{:=011d}'.format( l(row[keys[IN_SNILS[0]]].value)) cl_csvs.append(cl_csv) else: print('\nСНИЛС ' + str(row[keys[IN_SNILS[0]]].value) + ' пропущен\n') if k % 15000 == 0: with open(path + '{:=03d}'.format(file_number) + '.csv', 'w', encoding='cp1251') as output_file: dict_writer = csv.DictWriter( output_file, [IN_SNILS[0]], delimiter=';') #, quoting=csv.QUOTE_NONNUMERIC) dict_writer.writeheader() dict_writer.writerows(cl_csvs)