python3连接sqlite统计目标信息模板

import sqlite3
import copy
import lxml
from openpyxl import Workbook
from openpyxl import load_workbook
import collections
import time

def Sqlite2List(dbPath: str) -> list:

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

    ret = [] #返回值
    
    map_chaid_laneids = collections.defaultdict(list)   #获取道路下的车道列表
    map_laneid_currdir = collections.defaultdict(int)   #获取车道的当前转向
    sqlstr = "select id, chaid, Current_Dir from BD_LAN;"
    cursor.execute(sqlstr)
    for row in cursor:
        laneid = int(row[0])
        chaid = int(row[1])
        currentdir = int(row[2])
        map_chaid_laneids[chaid].append(laneid)
        map_laneid_currdir[laneid] = currentdir

    map_chaid_successors = collections.defaultdict(list)  #获取道路的后继道路列表
    sqlstr = "SELECT chaidfrom, chaidto FROM BD_LAC;"
    cursor.execute(sqlstr)
    for row in cursor:
        chaidfrom = int(row[0])
        chaidto = int(row[1])
        map_chaid_successors[chaidfrom].append(chaidto)    

    #找到符合条件的道路id，即"拥有直行左转右转的后继车道的道路id"
    sqlstr = 'select id from BD_CHA;'
    cursor.execute(sqlstr)
    for row in cursor:
        chaid = int(row[0])
        # 获得chaid的后继道路列表
        if chaid in map_chaid_successors:
            # print("有后继道路!")
            set_cdirs = set() #获得后继道路的车道的当前转向集合
            for successorchaid in map_chaid_successors[chaid]:
                #遍历该道路下的所有车道
                for laneid in map_chaid_laneids[successorchaid]:
                    set_cdirs.add(map_laneid_currdir[laneid])
            if 1 in set_cdirs and 2 in set_cdirs and 4 in set_cdirs:
                # print("找到了一条目标车道!")
                ret.append(chaid)
        else:
            # print("无后继道路!")
            continue

    conn.commit() #提交连接
    cursor.close() #关闭游标
    conn.close() #关闭连接
    return ret

def WriteXlsx(path: str, data: list[list[str]], head: str)->None:
    wb = Workbook()
    ws = wb.active
    
    ws.append(head)
    for x in data:
        # print(f'x = {x}')
        x = [x]
        ws.append(x)
    wb.save(path)
    return

start_t = time.time()


dbPath = 'D:\\0important file\\3 城市数据发布\\【ANP量产】上海-全域cube-S29-2_编译前置_SD_BPF_97tile_1440_dev主线流程_20230402\\BIDB_2_3_310000_23023110002000_CRB_230402___1680395058_XhEksorc.db'
rows = Sqlite2List(dbPath)
print(f'目标link数目为：{len(rows)}')

head = ['chaid']
WriteXlsx('linkid.xlsx', rows, head)
print(f'程序成功执行！总共耗时{(time.time() - start_t) / 60.0}分钟！')