# coding: utf-8
from win32com.client import Dispatch
import pythoncom
import datetime
import sys
import fdb
#==================================================================      
def main(argv=None):
    start_time=datetime.datetime.now()
    print(start_time)
    fb_conn = fdb.connect(dsn='firebird:d:\\pgn.FDB',user='user',password='password')
    trans=fb_conn.trans()
    trans.begin
    fb_cur=trans.cursor()
    sql_prep=fb_cur.prep(r"INSERT INTO gpk_input (lat_name,name,to_date) VALUES (?,?,?)")
    
    xlApp = Dispatch("Excel.Application")
    xlApp.Visible=False
    xlWb = xlApp.Workbooks.Open(r"D:\Documents\children.xls")
    try:
        for Sheet in xlWb.WorkSheets:
            print("Sheet=",Sheet.Name," ",datetime.datetime.now())
            for y in range(2,10):
                if str(Sheet.Cells(y,1)) != 'None':
                    # Sheet.Cells(y,1) - ФИО в латинской транскрипции
                    # Sheet.Cells(y,2) - ФИО на русском
                    # Sheet.Cells(y,3) - дата чего-то . Пока не интересно
                    dt1=str(Sheet.Cells(y,3))
                    dt1=dt1[0:19]
                    dt1=str(dt1[8:10])+'.'+str(dt1[5:7])+'.'+str(dt1[0:4])+' '+str(dt1[11:])
                    print(type(Sheet.Cells(y,2)))
                    rus_name=str(Sheet.Cells(y,2))
                    print(type(rus_name))
                    print('-------\n','orig=',rus_name)
                    fb_cur.execute(sql_prep,[Sheet.Cells(y,1),rus_name.encode('utf-8'),dt1])
        trans.commit()
        fb_cur.close()        
        trans.close()
    except Exception as e3:
        print("main try:","Line "+format(sys.exc_info()[-1].tb_lineno),e3.__str__())
        print("Sheet='",Sheet.Name,"' y=",y)        
        trans.rollback()
        fb_cur.close()
        trans.close()
        
    xlApp.Quit()        
    fin_time=datetime.datetime.now()
    print(fin_time," | ",fin_time-start_time)

r"INSERT INTO gpk_input (lat_name,name,to_date) VALUES (?,CAST(? AS VARCHAR(50)),?)"