import sqlite3
import time
import json
import sys
import os
from xlsxwriter.workbook import Workbook


def query_by_sql(cur, select_sql):
    cur.execute(select_sql)
    return cur.fetchall()

# --------------------------------------------------------------
dbpath = ""
with open("C:/Users/Public/Documents/dxls.txt", "r") as yaml_f:
    dbpath = yaml_f.read()
list_dbpath = json.loads(dbpath)

if not list_dbpath:
    exit(0)

list_alldbname= []
list_tblnames= []
list_sql= []
list = list_dbpath['outputxlsx']
for db in list:
    list_alldbname.append(db['dbname'])
    list_tblnames.append(db['sheetname'])
    list_sql.append(db['sql'])

# list_dbpath = []
# dbpath1 = "./DectDB-20210912-150511.db"
# dbpaht2 = "./DectDB1.db"
# dbpaht3 = "./DectDB2.db"
# list_dbpath.append(dbpath1)
# list_dbpath.append(dbpaht2)
# list_dbpath.append(dbpaht3)

endxlsx_path = "C:/Users/Public/Documents/output.xlsx"
workbook = Workbook(endxlsx_path)
gxlsx_sheet_currow = {}
gxlsx_sheet_tblobj = {}

waitreporttime = time.time()
itemtime = waitreporttime

list_dbname1=list_alldbname[0]
list_dbname=list_dbname1.split(";")

for dbname in list_dbname:

    conn = sqlite3.connect(dbname)
    cursor = conn.cursor()

    for tbl_name in list_tblnames:
        itemtime = time.time() - itemtime
        print("processing data %s, use time %.3f s" % (tbl_name, itemtime))
        itemtime = time.time()

        select_sql = list_sql[0] % tbl_name
        tbl_valueform = cursor.execute(select_sql)
        tbl_title = [tuple[0] for tuple in tbl_valueform.description]  # 表头

        if tbl_name in gxlsx_sheet_tblobj.keys():
            worksheet = gxlsx_sheet_tblobj[tbl_name]
        else:
            worksheet = workbook.add_worksheet(tbl_name)
            gxlsx_sheet_tblobj[tbl_name] = worksheet

        currow = 0
        if tbl_name in gxlsx_sheet_currow.keys():
            currow = gxlsx_sheet_currow[tbl_name]
        else:
            currow = 0
            gxlsx_sheet_currow[tbl_name] = currow
        if currow == 0:
            for col, name in enumerate(tbl_title):
                worksheet.write(0, col, name)
            currow = 1

        # 传入数据库路径，db.s3db或者test.sqlite
        for i, row in enumerate(tbl_valueform):
            for j, value in enumerate(row):
                gxlsx_sheet_currow[tbl_name] = currow + i
                worksheet.write(currow + i, j, value)
    cursor.close()
    conn.close()

# print(workbook.sheetnames.keys())
# sheet = workbook.sheetnames["C1_T61"]
# sheet.write(0, 0, "value")

workbook.close()
waitreporttime = time.time() - waitreporttime
print("wait report time:%.3f s" % waitreporttime)


