# 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)
#==========================================        
if __name__ == "__main__":
    main()

fb_conn = fdb.connect(dsn='firebird:d:\\pogran.FDB',user='sysdba',password='masterkey',sql_dialect=3,charset='WIN1251')

sql_prep=fb_cur.prep(r"INSERT INTO gpk_input (lat_name) VALUES (?)")

fb_cur.execute(sql_prep,[Sheet.Cells(y,1)])

fb_cur.execute(sql_prep,["AAA BBB"])

fb_conn = fdb.connect(dsn='firebird:d:\\pogran.FDB',user='sysdba',password='masterkey',sql_dialect=3,charset='UTF8')

# coding: cp1251
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:\\pgr.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,sex,sex_name,c_pasp_type,pasp_type_name,pasp_num,identif,reg_date,c_direction,direction_name,c_country,country_name) 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,65002):
                if str(Sheet.Cells(y,1)) != 'None':
                    fb_cur.execute(sql_prep,[str(Sheet.Cells(y,1)),str(Sheet.Cells(y,2)),str(Sheet.Cells(y,3))[0:19],int(Sheet.Cells(y,4)),str(Sheet.Cells(y,5)),int(Sheet.Cells(y,6)),str(Sheet.Cells(y,7)),str(Sheet.Cells(y,8)),str(Sheet.Cells(y,9)),str(Sheet.Cells(y,10))[0:19],int(Sheet.Cells(y,11)),str(Sheet.Cells(y,12)),int(Sheet.Cells(y,13)),str(Sheet.Cells(y,14))])
        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)

#==========================================        
if __name__ == "__main__":
    main()

   try:
        for Sheet in xlWb.WorkSheets:
            print("Sheet=",Sheet.Name," ",datetime.datetime.now())
            for y in range(2,65002):
                if str(Sheet.Cells(y,1)) != 'None':                
                    arr= [str(c) for c in Sheet.Range(Sheet.Cells(y, 1), Sheet.Cells(y,14))]
                    fb_cur.execute(sql_prep,[arr[0],arr[1],arr[2][0:19],int(float(arr[3])),arr[4],int(float(arr[5])),arr[6],arr[7],arr[8],arr[9][0:19],int(float(arr[10])),arr[11],int(float(arr[12])),arr[13]])
        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()

try:
        for Sheet in xlWb.WorkSheets:
            print("Sheet=",Sheet.Name," ",datetime.datetime.now())
            arr= [str(c) for c in Sheet.Range(Sheet.Cells(2, 1), Sheet.Cells(65000,14))]
            print(1,datetime.datetime.now())
            fb_cur.execute(sql_prep,[arr[0],arr[1],arr[2][0:19],int(float(arr[3])),arr[4],int(float(arr[5])),arr[6],arr[7],arr[8],arr[9][0:19],int(float(arr[10])),arr[11],int(float(arr[12])),arr[13]])
        trans.commit()        
        fb_cur.close()        
        trans.close()