轻量级嵌入式数据库SQLite

SQLite常用操作

创建数据库文件

进入到要创建数据库文件的目录下

sqlite3 DatabasesName.db

sqlite3 testDb.db

当前目录将会下会创建一个被SQLite引擎用作数据库的testDBb.db文件

查看已建立的数据库

.databases命令

sqlite> .databases
seq  name             file
---  ---------------  ----------------------------------------------------------
0    main             D:\微信公众号\Typora\PyQt5\sqlite\testDb.db

查看已创建的表

sqlite> .tables
people

删除已创建的表

sqlite> drop table people;
sqlite> .tables

打开已经建立的数据库

进入到要创建数据库文件的目录下

sqlite3 DatabasesName.db

sqlite3 testDb.db

sqlite3 testDb.db，如果数据库不存在，会创建，否则打开

查看帮助信息

.help

创建表

create table people(id integer primary key,name text);

添加数据

insert into people(id,name) values(1,'张三');
insert into people(id,name) values(2,'李四');
insert into people(id,name) values(3,'王二');

查询数据

select* from table

sqlite> select*from people;
1|张三
2|李四
3|王二

设置开启表头模式

.header on	

sqlite> .header on
sqlite> select*from people;
id|name
1|张三
2|李四
3|王二

查看表结构

.schema

sqlite> .schema people
CREATE TABLE people(id integer primary key,name text);

删除数据

DELETE FROM Teachers WHERE Age>30;

删除表中所有数据

delete from table

PyQt5操作SQLite数据库

# -*- coding: utf-8 -*-
'''
    【简介】
	PyQt5中  处理database 例子
'''

import sys
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
from PyQt5.QtSql import QSqlDatabase, QSqlQuery

def createDB():
    db = QSqlDatabase.addDatabase('QSQLITE')
    db.setDatabaseName('./db/database.db')

    if not db.open():
        QMessageBox.critical(None, ("无法打开数据库"),
                             ("无法建立到数据库的连接,这个例子需要SQLite 支持，请检查数据库配置。\n\n"
                              "点击取消按钮退出应用。"),
                             QMessageBox.Cancel)
        return False

    query = QSqlQuery()
    query.exec_("create table people(id int primary key, name varchar(20), address varchar(30))")
    query.exec_("insert into people values(1, 'zhangsan1', 'BeiJing')")
    query.exec_("insert into people values(2, 'lisi1', 'TianJing')")
    query.exec_("insert into people values(3, 'wangwu1', 'HenNan')")
    query.exec_("insert into people values(4, 'lisi2', 'HeBei')")
    query.exec_("insert into people values(5, 'wangwu2', 'shanghai')")
    db.close()
    return True

if __name__ == '__main__':
    app = QApplication(sys.argv)
    createDB()
    sys.exit(app.exec_())

./db/databases.db数据库将创建people信息表,插入5条数据

create table Device(channel integer primary key,
                    ip text,
                    status integer,
                    ctrlPort integer,
                    dataPort integer,
                    devModel text,
                    devChannel integer,
                    protocol text,
                    manufactor text,
                    serialNumber text,
                    softwareVersion text,
                    filePath text
                   );

insert into Device(channel,ip,status,ctrlPort,dataPort,devModel,devChannel
			,protocol,manufactor,serialNumber,softwareVersion,filePath) 
    		values(1,'127.0.0.1',3,7681,7681,'待定',7681,'蓝星','BSTAR',
                   '','','E:\\test\\test_get.bsr');

create table Record(channel integer not null,
                    start text,
                    end text,
                    duration integer,
                    size integer,
                    streamType text,
                    lockState integer,
                    fileType integer,
                    recPos text,
                    seq PRIMARY KEY
                   );

insert into Record(channel,start,end, duration ,size ,streamType ,lockState ,fileType ,recPos, seq) 
values(1,'2021-07-28 10:00:00','2021-07-28 10:01:00',86340,20000,'1,',2,1,'暂定',1);

CREATE TABLE PrmConfig (id INTEGER PRIMARY KEY AUTOINCREMENT,api TEXT NOT NULL UNIQUE,data TEXT NOT NULL);