Python3 读sqlite数据库
sqlite3_reader.py
这个类主要是读取图层字段信息
import sqlite3
import re
class Sqlite3FieldInfo:
def __init__(self):
self.__fieldName = ''
self.__fieldType = ''
self.__fieldIndex = 0
self.__fieldLength = 0
@property
def fieldName(self):
return self.__fieldName
@fieldName.setter
def fieldName(self, fieldName):
self.__fieldName = fieldName
@property
def fieldType(self):
return self.__fieldType
@fieldType.setter
def fieldType(self, fieldType):
self.__fieldType = fieldType
@property
def fieldIndex(self):
return self.__fieldIndex
@fieldIndex.setter
def fieldIndex(self, index):
self.__fieldIndex = index
@property
def fieldLength(self):
return self.__fieldLength
@fieldLength.setter
def fieldLength(self, length):
self.__fieldLength = length
def toList(self):
return [self.fieldName, self.fieldType, self.fieldIndex, self.fieldLength]
class Sqlite3Reader:
def __init__(self, *args, **kwargs):
self.__databaseConnector = None
super(Sqlite3Reader, self).__init__(*args, **kwargs)
@property
def databaseConnector(self):
return self.__databaseConnector
def connectDatabase(self, path):
self.__databaseConnector = sqlite3.connect(path)
def getAllTableNames(self):
tableNameList = []
if not self.databaseConnector:
return tableNameList
getAllTablesSQLString = "select name from sqlite_master where type='table' order by name"
databaseConnectorCursor = self.databaseConnector.cursor()
databaseConnectorCursor.execute(getAllTablesSQLString)
for items in databaseConnectorCursor.fetchall():
tableNameList.append(items[0])
return tableNameList
def __fieldInfoGenerator(self, fieldInfoRawData):
def getSqlite3FieldInfoByList():
for fieldInfoItem in fieldInfoRawData:
fieldInfo = Sqlite3FieldInfo()
fieldInfo.fieldIndex = fieldInfoItem[0]
fieldInfo.fieldName = fieldInfoItem[1]
fieldInfo.fieldType = fieldInfoItem[2]
yield fieldInfo
return getSqlite3FieldInfoByList
def __getTableFieldsInfoByTableNameHelper(self,fieldsInfoRawData,fieldInfoList):
for fieldInfo in self.__fieldInfoGenerator(fieldsInfoRawData)():
fieldInfoList.append(fieldInfo)
def getTableFieldsInfoByTableName(self, tableName):
fieldsInfoList = []
getTableDescribeStr = "PRAGMA table_info('" + tableName + "')"
databaseConnectorCursor = self.databaseConnector.cursor()
databaseConnectorCursor.execute(getTableDescribeStr)
fieldInfosRawData = databaseConnectorCursor.fetchall()
self.__getTableFieldsInfoByTableNameHelper(fieldInfosRawData,fieldsInfoList)
return fieldsInfoList
def getTableContentByTableName(self, tableName):
getTableContentStr = "select * from " + tableName
databaseConnectorCursor = self.databaseConnector.cursor()
databaseConnectorCursor.execute(getTableContentStr)
return databaseConnectorCursor.fetchall()
'''
if __name__ == '__main__':
databaseReader = Sqlite3Reader()
databaseReader.connectDatabase('config.cfg')
tableNameList = databaseReader.getAllTableNames()
#print(tableNameList)
for item in tableNameList:
print(item)
fieldInfoList = databaseReader.getTableDescribeByTableName(item)
for fieldInfo in fieldInfoList:
print(fieldInfo.fieldName, fieldInfo.fieldType,
fieldInfo.fieldIndex, fieldInfo.fieldLength)
print('*****************************************************************************')
'''
confige_info.py
这个主要是读取数据库内容的类,以后会再次修改
import sqlite3_reader
class ConfigInfo:
def __init__(self,path):
self.__configReader = None
self.__resultFile = open(path,'w',encoding='utf-8')
@property
def resultFile(self):
return self.__resultFile
@property
def configReader(self):
return self.__configReader
def __initConfigReader(self,path):
self.__configReader = sqlite3_reader.Sqlite3Reader()
self.__configReader.connectDatabase(path)
def closeResultFiles(self):
if self.__resultFile:
self.__resultFile.close()
def __nextTableName(self):
for tableName in self.configReader.getAllTableNames():
yield tableName
def tableInfo(self,tableName):
return self.configReader.getTableFieldsInfoByTableName(tableName)
def tableContent(self,tableName):
return self.configReader.getTableContentByTableName(tableName)
def readConfig(self,path):
self.__initConfigReader(path)
for tableName in self.__nextTableName():
print(self.tableInfo(tableName))
print(self.tableContent(tableName))
print('************************************************************************************************')
def readConfigAndOutputResults(self):
pass
if __name__ == '__main__':
configInfo = ConfigInfo('result.csv')
configInfo.readConfig('config.cfg')