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}分钟!')
python3连接sqlite统计目标信息模板
最新推荐文章于 2024-07-19 10:10:58 发布