Python之sqlite操作

util_sqlite.py`

# -*- coding: utf-8 -*-

import os
import time
from PyQt5.QtSql import QSqlDatabase, QSqlQuery
from datetime import datetime


class SQLiteTools():
    def __init__(self):
        pass

    def createConnection(self, DB_name):
        db = QSqlDatabase.addDatabase("QSQLITE")
        db.setDatabaseName(DB_name)
        db.open()
        self.que = QSqlQuery()

    '''
    判断指定数据表是否存在
    :return: 在返回True,否则返回False
    '''
    def selectTableExist(self, tbname):
        sql = u"select * from sqlite_master where type='table' and name = '" + tbname + u"';"
        self.que.exec_(sql)
        return self.que.next()

    '''
    创建通用数据表，默认第一列为主键，名称:ID，类型:INTEGER, 自增
    :return:  null
    '''
    def createSQLtable(self, tbname):
        sql = u"CREATE TABLE if not exists " + tbname + \
            u" (ID INTEGER PRIMARY KEY AUTOINCREMENT);"
        self.que.exec_(sql)

    '''
    指定数据表添加列
    :param genre: 添加列类型
    :return: null
    '''
    def addSQLtableColumn(self, tbname, columnName, genre):
        sql = u"ALTER TABLE " + tbname + u" ADD " + columnName + " " + genre + ";"
        self.que.exec_(sql)

    '''
    指定数据表添加指定行
    :return: null
    '''
    def addSQLtableRow(self, tbname, rowNum):
        db = QSqlDatabase.database()
        db.transaction()
        sql = "INSERT INTO " + tbname + " (ID) VALUES (:row);"
        self.que.prepare(sql)
        for row in range(rowNum):
            self.que.bindValue(":row", row)
            self.que.exec_()
        db.commit()

    '''
    更新数据表指定位置的值
    :return: null
    '''
    def setSQLtableValue(self, tbname, column, row, value):
        sql = u"UPDATE " + tbname + u" SET " + column + "=:value WHERE ID=:row;"
        self.que.prepare(sql)
        self.que.bindValue(":value", value)
        self.que.bindValue(":row", row)
        self.que.exec_()

    '''
    获取指定表格总行数
    :return: 行数
    '''
    def getSQLtableRowNum(self, tbname):
        result = 0
        sql = "SELECT COUNT(*) FROM " + tbname + ";"
        self.que.exec_(sql)
        if self.que.next():
            result = self.que.value(0)
        return result

    '''
    获取指定表所有字段名称
    :return:  列名称列表
    '''
    def getSQLtableColumnName(self, tbname):
        sql = "PRAGMA table_info([" + tbname + "])"
        self.que.exec_(sql)
        NameList = []
        while self.que.next():
            result = self.que.value(1)
            if result and result != "ID":
                NameList.append(result)
        return NameList

    '''
    读取指定数据表的指定数据
    :return: 值
    '''
    def getSQLtableValue(self, tbname, column, row):
        value = 0
        sql = "SELECT " + column + " FROM " + \
            tbname + " WHERE ID=" + str(row) + ";"
        self.que.exec_(sql)
        if self.que.next():
            value = self.que.value(0)
        return value

    '''
    读取数据表指定列的所有数据
    :return: 值列表
    '''
    def getSQLtableColumn(self, tbname, column):
        sql = "SELECT " + column + " FROM " + tbname + ";"
        value_list = []
        if self.que.exec_(sql):
            column_index = self.que.record().indexOf(column)  # 获取列索引值
            while self.que.next():
                value = self.que.value(column_index)
                value_list.append(value)
        return value_list

    '''
    获取指定表格指定行数据
    :return: 值列表
    '''
    def getSQLtableRow(self, tbname, row):
        columnNum = len(self.getSQLtableColumnName(tbname))
        sql = "SELECT * FROM " + tbname + " WHERE ID=" + str(row) + ";"
        self.que.exec_(sql)
        valueList = []
        while self.que.next():
            for i in range(1, columnNum+1):
                result = self.que.value(i)
                valueList.append(result)
        return valueList

    '''
    指定数据表删除指定列
    :return: null
    '''
    def delSQLtableColumn(self, tbname, columnName):
        # ALTER TABLE 表名 drop 列名;   #SQLite不支持
        tbName = "tbName0"
        CloumnNameList = self.getSQLtableColumnName(tbname)
        if columnName in CloumnNameList:
            CloumnNameList.remove(columnName)
        CloumnNameList.insert(0, "ID")
        strName = ','.join(CloumnNameList)
        sql = u"create table "+tbName+" as select " + \
            strName+" from "+tbname+" where 1 = 1"
        self.que.exec_(sql)
        self.delSQLtable(tbname)
        self.removeSQLtableName(tbName, tbname)

    '''
    指定数据表删除指定行数
    :param rowNum: 要删除总行数
    :return: null
    '''
    def delSQLtableRow(self, tbname, startRow, rowNum=1):
         for row in range(startRow, startRow + rowNum):
            sql = "DELETE FROM " + tbname + " WHERE ID = " + str(row) + ";"
            self.que.exec_(sql)

    '''
    重命名表格
    :return:  null
    '''
    def removeSQLtableName(self, tbname, newTbname):
        sql = u"alter table "+tbname+" rename to "+newTbname+";"
        self.que.exec_(sql)

    '''
    清空指定数据表
    :return:  null
    '''
    def ClearSQLtableRowValue(self, tbname):
        sql = "DELETE FROM " + tbname + ";"
        self.que.exec_(sql)

    '''
    删除指定表
    :return: null
    '''
    def delSQLtable(self, tbname):
        # drop table record;
        sql = u"drop table "+tbname+";"
        self.que.exec_(sql)


if __name__ == '__main__':
    class sqliteOps():
        def __init__(self, PathFile=None):

            # 如果存在mysql.ini先删除，方便下列代码的测试
            if os.path.exists(PathFile):
                os.remove(PathFile)

            self.sqlite = SQLiteTools()
            self.sqlite.createConnection(PathFile)

        # 创建表
        def createTable(self, projectName, grpNum, chNum):
            tableNames = ["{}_Grp{}_Ch{}".format(projectName, grp, ch) for grp in range(1, grpNum+1) for ch in range(1, chNum+1)]
            db = QSqlDatabase.database()
            db.transaction()
            for tableName in tableNames:
                if not self.sqlite.selectTableExist(tableName):
                    self.sqlite.createSQLtable(tableName)
                    self.sqlite.addSQLtableColumn(tableName, 'date_time', 'text')
                    self.sqlite.addSQLtableColumn(tableName, 'voltage', 'text')
                    self.sqlite.addSQLtableColumn(tableName, 'current', 'text')
                    self.sqlite.addSQLtableColumn(tableName, 'io', 'text')
            db.commit()

        # 添加数据
        def addOneRowData(self, tableName, dateTime, voltage, current, io):
            row_num_index = self.sqlite.getSQLtableRowNum(tableName)
            db = QSqlDatabase.database()
            db.transaction()
            for row in range(row_num_index+1, row_num_index+2):
                self.sqlite.que.prepare("INSERT INTO " + tableName + " (ID, date_time, voltage, current, io) VALUES (?, ?, ?, ?, ?);")
                self.sqlite.que.addBindValue(row)
                self.sqlite.que.addBindValue(dateTime)
                self.sqlite.que.addBindValue(voltage)
                self.sqlite.que.addBindValue(current)
                self.sqlite.que.addBindValue(io)
                self.sqlite.que.exec_()
            db.commit()

        # 添加数据
        def addMultipleRowData(self, tableName, data):
            db = QSqlDatabase.database()
            db.transaction()
            for row in data:
                self.sqlite.que.prepare("INSERT INTO " + tableName + " (ID, date_time, voltage, current, io) VALUES (?, ?, ?, ?, ?);")
                for value in row:
                    self.sqlite.que.addBindValue(value)
                self.sqlite.que.exec_()
            db.commit()

        # 获取数据
        def getData(self, tableName):
            print(tableName)
            value = self.sqlite.getSQLtableRowNum(tableName)
            print("数据表总行数：", value)

            value = self.sqlite.getSQLtableColumnName(tableName)
            print("所有列的名称(不包括ID列)：", value)

            value = self.sqlite.getSQLtableColumn(tableName, "date_time")
            print("date_time列的所有值：", value)
            value = self.sqlite.getSQLtableColumn(tableName, "voltage")
            print("voltage列的所有值：", value)
            value = self.sqlite.getSQLtableColumn(tableName, "current")
            print("current列的所有值：", value)
            value = self.sqlite.getSQLtableColumn(tableName, "io")
            print("io列的所有值：", value)

        # 删除数据
        def delData(self, tableName):
            value = self.sqlite.getSQLtableRowNum(tableName)
            print("删除前数据表总行数：", value)
            self.sqlite.delSQLtableRow(tableName, 10, 5)
            value = self.sqlite.getSQLtableRowNum(tableName)
            print("删除后数据表总行数：", value)

            value = self.sqlite.getSQLtableColumnName(tableName)
            print("删除前所有列名(不包括ID)：", value)
            self.sqlite.delSQLtableColumn(tableName, "io")
            value = self.sqlite.getSQLtableColumnName(tableName)
            print("删除后所有列名(不包括ID)", value)
            self.sqlite.ClearSQLtableRowValue(tableName)
            self.sqlite.delSQLtable(tableName)

    start_time = time.time()

    projectName = "k2206"
    grpNum = 1
    chNum = 20
    PathFile = "./{}.db".format(projectName)
    sql = sqliteOps(PathFile)
    sql.createTable(projectName, grpNum, chNum)
    end_time = time.time()
    execution_time = end_time - start_time
    print("创建程序运行时间：", execution_time, "秒")         
    for grp in range(1, grpNum + 1):
        for ch in range(1, chNum + 1):
            tableName = "{}_Grp{}_Ch{}".format(projectName, grp, ch)
            data = []
            for row in range(20):
                data.append((row, datetime.now().strftime("%Y-%m-%d_%H-%M-%S"), row, row, row))
            sql.addMultipleRowData(tableName, data)
            sql.getData(tableName)
    
    end_time = time.time()
    execution_time = end_time - start_time
    print("写入程序运行时间：", execution_time, "秒")