1. 创建数据库表

创建数据库表需要明确表名、主键以及数据的类型

def create_table():

    connect = sqlite3.connect('./djangoProject/db.sqlite3')
    cur = connect.cursor()

    try: 
        sql = """
        CREATE TABLE hmt_sysdevicelatency (
            id integer primary key autoincrement,
            SysModelName varchar(100),
            Device varchar(100),
            Latency real,
            Energy real
        )
        ;"""
        cur.execute(sql)
        print("create table success")
        return True

    except OperationalError as o:
        print(str(o))

    except Exception as e:
        print(e)
        return False

    finally:
        cur.close()
        connect.close()

2. 插入数据

插入数据需要明确插入的表格、变量名以及需要插入的内容

def insert_data():

    connect = sqlite3.connect('./djangoProject/db.sqlite3')
    cur = connect.cursor()

    try:
        inset_sql = """
            insert into hmt_sysmodel 
            (id, SysModelName, Computation, Parameter, Storage, Latency, Energy, Accuracy, Infomation)
            values
            (
                1, "AlexNet", 738.68, 14.22, 54.26, -1, -1, -1, "Alexnet模型为8层深度网络, 由5个卷积层和3个全连接层构成, 不计LRN层和池化层. \
                    AlexNet中包含了几个比较新的技术点, 也首次在CNN中成功应用了ReLU、Dropout和LRN等Trick. 同时AlexNet也使用了GPU进行运算加速. \
                    AlexNet是2012年ImageNet竞赛冠军获得者Hinton和他的学生Alex Krizhevsky设计的, 该模型以很大\
                    的优势获得了2012年ISLVRC竞赛的冠军网络, 分类准确率由传统的 70%+提升到 80%+, 自那年之后, 深度学习开始迅速发展."
            );
        """
        cur.execute(inset_sql)

        print("insert success!")

        return True
    except Exception as e:
        print(str(e))
    finally:
        cur.close()
        connect.close()

3. 更新表内容

使用update来更改表内容，需要明确修改的变量和内容，并且确保插入数据不能冲突，通常需要根据主键来插入

def update_data():

    connect = sqlite3.connect('./djangoProject/db.sqlite3')

    try:
        update_sql = 'update hmt_sysdevicelatency set Latency = ? where SysModelName = ? and Device = ?;'
        datalist = [
            (
                24.1, "AlexNet", "Windows"
            ),
            (
                59.3, "MobileNet", "Windows"
            ),
            (
                38.8, "ResNet", "Windows"
            ),
            (
                24.3, "VGG", "Windows"
            ),
        ]

        connect.executemany(update_sql, datalist)

        connect.commit()

        print("update success!")

        return True
    except Exception as e:
        print(str(e))
    finally:
        connect.close()

4. 删除表格

使用drop语句来删除对应表格

def delete_table():

    connect = sqlite3.connect('./djangoProject/db.sqlite3')
    cur = connect.cursor()

    try:
        del_sql = """drop table hmt_sysmodel_latency;"""


        cur.execute(del_sql)
        print("delete table success")
        return True

    except OperationalError as o:
        print(str(o))

    except Exception as e:
        print(e)
        return False

    finally:
        cur.close()
        connect.close()