项目中的一些配置文件使用了数据库存放,有时需要比较两个db文件的差异,由于常见的比较工具并不能比较db文件,因此自己写了一个小工具将db文件中的内容导出为xml(一个table导出为一个xml文件),比较合并xml后再将xml文件内容更新到db文件。
工具的目录结构:
使用方法:
将db文件拖到dbToXml.bat 即可将db中的文件导出为xml;
将导出xml的文件夹拖到xmlToDb.bat 并输入目标db文件的名称, 即可将文件夹下xml的内容中更新到db;
代码实现如下:
dbToXml.bat:
@echo off
set floder=%1%
set p_path=%cd%
%p_path%\Python27\python.exe %p_path%\dbToXml.py -p %floder%
pause
xmlToDb.bat:
@echo off
set /p dbName=please enter db name:
set floder=%1%
set p_path=%cd%
%p_path%\Python27\python.exe %p_path%\xmlToDb.py -p %floder% -n %dbName%
pause
dbToXml.py(db导出xml):
#!/usr/bin/python
# -*- coding:UTF-8 -*-
import os,os.path
import sys,getopt
import shutil
import sqlite3
import xml.etree.ElementTree as ET
curDir=os.path.dirname(os.path.realpath(__file__))
outXmlPath=curDir+'\\outXml'
def getFilePath():
opts,args=getopt.getopt(sys.argv[1:],"p:s:")
file_path=""
for op,value in opts:
if op == "-p":
file_path=value
return file_path
def createFloder(goal_dir):
if os.path.exists(goal_dir):
shutil.rmtree(goal_dir)
if not os.path.exists(goal_dir):
os.mkdir(goal_dir)
def loadTable(cursorRead,name):
cursorRead.execute("PRAGMA table_info([%s])" %(name))
data = cursorRead.fetchall()
keys=[]
for x in data:
keys.append(x[1])
cursorRead.execute("select * from %s" %(name))
contents = cursorRead.fetchall()
a = ET.Element("root")
for tu in contents:
newNode = ET.SubElement(a,"info")
for idx in xrange (0,len(keys)):
if tu[idx] is None:
newNode.attrib[keys[idx]] = ''
else:
newNode.attrib[keys[idx]] = str(tu[idx])
tree = ET.ElementTree(a)
tree.write(outXmlPath+"\\%s.xml" %(name),encoding = 'utf-8')
def loadDb():
reload(sys)
sys.setdefaultencoding('utf-8')
createFloder(outXmlPath)
db = sqlite3.connect(getFilePath())
cursorRead = db.cursor()
cursorRead.execute("select name from sqlite_master where type='table'")
tableNames = cursorRead.fetchall()
for name in tableNames:
loadTable(cursorRead,name)
db.close()
for x in xrange(0,3):
print('db to xml suc')
def main():
loadDb()
if __name__ == '__main__':
main()
xmlToDb.py(xml内容更新到db文件):
#!/usr/bin/python
# -*- coding:UTF-8 -*-
import os,os.path
import sys,getopt
import shutil
import sqlite3
import xml.etree.ElementTree as ET
curDir=os.path.dirname(os.path.realpath(__file__))
def getFilePath():
opts,args=getopt.getopt(sys.argv[1:],"p:n:s:")
file_path=''
dbName=''
for op,value in opts:
if op == '-p':
file_path=value
elif op=='-n':
dbName=value
return file_path,dbName
def createFloder(goal_dir):
if os.path.exists(goal_dir):
shutil.rmtree(goal_dir)
if not os.path.exists(goal_dir):
os.mkdir(goal_dir)
def resetTable(cursorRead,filePath,tableName):
typeStr='('
closeAppendType=False
xmlFp = ET.parse(filePath)
root=xmlFp.getroot()
for child in root:
valueStr=' values('
sz=len(child.attrib)
index=0
for eva in child.attrib:
if not closeAppendType:
typeStr+=eva
valueStr+='"'+child.attrib[eva]+'"'
index+=1
if index!=sz:
valueStr+=','
if not closeAppendType:
typeStr+=','
valueStr+=')'
closeAppendType=True
cmdStr='insert into %s'%(tableName)+typeStr+')'+valueStr
print(cmdStr)
cursorRead.execute(cmdStr)
def loadDb():
reload(sys)
sys.setdefaultencoding('utf-8')
parms=getFilePath()
floderPath=parms[0]
dirs = os.listdir(floderPath)
dbName=curDir+'\\'+parms[1]+'.db'
print(dbName)
if not os.path.exists(dbName):
print('not find file:'+dbName)
exit(0)
db = sqlite3.connect(dbName)
cursorRead = db.cursor()
for file in dirs:
tu=file.split('.');
print(tu[0])
cursorRead.execute("delete from %s" %(tu[0]))
db.commit()
tmp_path = os.path.join(floderPath, file)
resetTable(cursorRead,tmp_path,tu[0])
db.commit()
db.close()
for x in xrange(0,3):
print('xml to db suc')
def main():
loadDb()
if __name__ == '__main__':
main()
这样就实现db文件的比较合并了...