利用Python3 读sqlite数据库

Python3 读sqlite数据库

sqlite3_reader.py

这个类主要是读取图层字段信息

import sqlite3
import re


class Sqlite3FieldInfo:
    #目前无法确定sqlite3数据库字段长度的获取有没有问题
    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装饰器只是getter的标签，只是说明self.config_connector就是从这个函数获取而已,只是效果和我们的getter一样
    ##从@property的装饰器衍生出另外一个@Property.setter,同理，只是效果和我们的setter保持一致
    ##这里还需要注意的一点就是,@property修饰的函数,无论是getter,setter,都必须保证函数名称一致,但是参数可以不一致

    @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

	#利用生成器不断迭代取出下一个fieldInfo,以简化代码,毕竟**一个函数只能做一件事**
	#但是这里我觉得很奇怪.又一个生成器我是这样写的,但是每次准报错,我不知道为何只能把list放到闭包内才能够顺利执行,期待下次我找到原因:
	# def __getSqlite3FieldInfoByList(self, fieldInfoRawData):
#         for fieldInfoItem in fieldInfoRawData:
#             fieldInfo = Sqlite3FieldInfo()
#             fieldInfo.fieldIndex = fieldInfoItem[0]
#             fieldInfo.fieldName = fieldInfoItem[1]
#             fieldInfo.fieldType = fieldInfoItem[2]
#             #fieldInfo.fieldLength = fieldInfoItem[3]
#             yield fieldInfo
    def __fieldInfoGenerator(self, fieldInfoRawData):
        def getSqlite3FieldInfoByList():
            for fieldInfoItem in fieldInfoRawData:
                fieldInfo = Sqlite3FieldInfo()
                fieldInfo.fieldIndex = fieldInfoItem[0]
                fieldInfo.fieldName = fieldInfoItem[1]
                fieldInfo.fieldType = fieldInfoItem[2]
                #fieldInfo.fieldLength = fieldInfoItem[3]
                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()

   
   #如果要使用生成器的话，记得加上括号：self.__nextTableName()才是生成器，才能被迭代，也就是for tablename in self.__nextTableName()
    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')