python实例022--更换db配置中的字典值

因为项目需要,需要将上图配置中的字典换成下图对应形式

# -*- coding: utf-8 -*-
"""
Created on Tue Jun 15 15:22:13 2021

@author: 19088
"""

import sqlite3
import easygui
import sys
import re
import os
class dealFieldDict:
    # 构造函数
    def __init__(self,path):
        connect=self.connDb(path)
        self.connectdb=connect[0]
        self.conn=connect[0].cursor()
        self.tableDict=connect[1]
        self.valueDict=self.dealTable(self.tableDict)


    def dealText(self,text):
        if  text.lower() == 'none' or len(text) == 0 :
            return ''
        #print(text)
        dicts=text.split(";")
        pattern=re.compile("\d*")
        strReturn=''
        for enum in dicts:
            #print(enum)
            patternText=pattern.search(enum)
            strFirst=enum[:patternText.span()[1]]
            strSecond=enum[patternText.span()[1]:]
            #print(strFirst)
            #print(strSecond)
            strReturn+=strFirst + " " + strSecond + ";"
        return strReturn
    def connDb(self, path):
        # 连接db 获取db中的所有表
        conn = sqlite3.connect(path)
        cur = conn.cursor()
        tableDict = {}
        tables = []
        # 获取表名，保存在tab_name列表
        cur.execute("select name from sqlite_master where type='table' order by name")
        tab_name = cur.fetchall()
        tables = [line[0] for line in tab_name]
        for iterTable in tables:
            #################################
            print('正在处理{0}\n'.format(iterTable))
            count = 0
            # 获取表的列名（字段名），保存在col_names列表,每个表的字段名集为一个元组
            col_names = []
            strFind = "pragma table_info(" + iterTable + ");"
            cur.execute(strFind)
            col_name = cur.fetchall()
            for f in col_name:
                fname = f[1]
                #print(fname)
                col_names.append(fname)
            tableDict[iterTable]=col_names
        return conn,tableDict
    def dealTable(self,tableDict):
        updateValueDict={}
        if "SYS_ATTRIBUTEDEF" not in tableDict:
            print("所选数据中不包含 SYS_ATTRIBUTEDEF ")
            sys.exit()
        else:
            fields = tableDict["SYS_ATTRIBUTEDEF"]
            if "Dictionary" not in fields:
                print("SYS_ATTRIBUTEDEF 中不包含 Dictionary")
                sys.exit()
            else:
                strSelect = 'select rowid,Dictionary from SYS_ATTRIBUTEDEF'
                self.conn.execute(strSelect)
                findAll = self.conn.fetchall()
                for findValue in findAll:
                    rowid = findValue[0]
                    findText = findValue[1]
                    if  not findText or len(findText) == 0 :
                        continue
                    newtext = self.dealText(findText)
                    updateValueDict[rowid] = newtext
                    # print(findText)
        return updateValueDict
    def updateTable(self):
        updateValue=self.valueDict
        for key in updateValue:
            newvalue=updateValue.get(key).strip(";")
            strupdate="update SYS_ATTRIBUTEDEF set Dictionary= '{value}' where rowid = {id} ".format(value=newvalue,id=key)
            #values=(newvalue,key)
            self.conn.execute(strupdate)
        self.connectdb.commit()

# Press the green button in the gutter to run the script.
if __name__ == '__main__':
    path=easygui.fileopenbox("选择db","select","*.db")
    oldBaseName=os.path.basename(path)
    name,suffix=oldBaseName.split(".")
    newname=name+"_back."+suffix
    str="copy {oldname} {newname}".format(oldname=oldBaseName,newname=newname)
    os.popen(str)
    a=dealFieldDict(path)
    a.updateTable()
    print("处理完成!")
# See PyCharm help at https://www.jetbrains.com/help/pycharm/