database_search.py

# Search data in a SQLite database, save it to the Excel file

import os
import sqlite3
from tqdm import tqdm
from datetime import datetime
from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill, Color, Border, Side, Alignment

database_file_name = 'TZ092005.db'
excel_file_name = 'data.xlsx'


excel_workbook = load_workbook(excel_file_name)
excel_sheet = excel_workbook.active

excel_sheet['G1'].value = 'ID'
excel_sheet['H1'].value = 'DOB'

font = Font(bold=True)
fill = PatternFill(patternType='solid', fill_type='solid', fgColor=Color('F2F2F2'))
border = Border(left=Side(style='thin'), right=Side(style='thin'), top=Side(style='thin'), bottom=Side(style='thin'))
alignment = Alignment(horizontal='center', vertical='center')

for i in range(1, 8 + 1):
    cell = excel_sheet.cell(row=1, column=i)
    cell.font = font
    cell.fill = fill
    cell.border = border
    cell.alignment = alignment


database_connection = sqlite3.connect(database_file_name)
database_cursor = database_connection.cursor()

sql = 'select id, b_year from m where l_name like ? and f_name like ? and b_year like ?'


def proper_value(value):
    return str(value).strip() if value is not None else ''


progress_bar = tqdm(total=excel_sheet.max_row - 1)

for row_number, row in enumerate(excel_sheet.values, start=1):
    if row_number == 1:
        continue

    last_name = proper_value(row[1])
    first_name = proper_value(row[2])
    year = proper_value(row[3])
    month = proper_value(row[4])
    day = proper_value(row[5])

    database_cursor.execute(sql, (f'%{last_name}%', f'%{first_name}%', f'%{year}%{month}%{day}%'))
    results = database_cursor.fetchone()
    if results[0] is not None:
        excel_sheet[f'G{row_number}'].value = results[0]
    if results[1] is not None:
        excel_sheet[f'H{row_number}'].value = datetime.strptime(results[1], "%Y%m%d")
        excel_sheet[f'H{row_number}'].number_format = 'yyyy-mm-dd'

    progress_bar.update()

progress_bar.close()
excel_workbook.save(excel_file_name)
excel_workbook.close()
database_connection.close()


# Adjust columns width (works under Windows only)
if os.name == 'nt':
    from win32com.client import Dispatch

    excel = Dispatch('Excel.Application')
    wb = excel.Workbooks.Open(os.path.abspath(excel_file_name))
    excel.ActiveSheet.Columns.AutoFit()
    wb.Save()
    wb.Close()