轻量级嵌入式数据库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&＃xff0c;如果数据库不存在&＃xff0c;会创建&＃xff0c;否则打开

查看帮助信息

.help

创建表

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


添加数据

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


查询数据

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 -*-
&＃39;&＃39;&＃39;【简介】PyQt5中 处理database 例子
&＃39;&＃39;&＃39;import sys
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
from PyQt5.QtSql import QSqlDatabase, QSqlQuerydef createDB():db &＃61; QSqlDatabase.addDatabase(&＃39;QSQLITE&＃39;)db.setDatabaseName(&＃39;./db/database.db&＃39;)if not db.open():QMessageBox.critical(None, ("无法打开数据库"),("无法建立到数据库的连接,这个例子需要SQLite 支持&＃xff0c;请检查数据库配置。\n\n""点击取消按钮退出应用。"),QMessageBox.Cancel)return Falsequery &＃61; QSqlQuery()query.exec_("create table people(id int primary key, name varchar(20), address varchar(30))")query.exec_("insert into people values(1, &＃39;zhangsan1&＃39;, &＃39;BeiJing&＃39;)")query.exec_("insert into people values(2, &＃39;lisi1&＃39;, &＃39;TianJing&＃39;)")query.exec_("insert into people values(3, &＃39;wangwu1&＃39;, &＃39;HenNan&＃39;)")query.exec_("insert into people values(4, &＃39;lisi2&＃39;, &＃39;HeBei&＃39;)")query.exec_("insert into people values(5, &＃39;wangwu2&＃39;, &＃39;shanghai&＃39;)")db.close()return Trueif __name__ &＃61;&＃61; &＃39;__main__&＃39;:app &＃61; 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,&＃39;127.0.0.1&＃39;,3,7681,7681,&＃39;待定&＃39;,7681,&＃39;蓝星&＃39;,&＃39;BSTAR&＃39;,&＃39;&＃39;,&＃39;&＃39;,&＃39;E:\\test\\test_get.bsr&＃39;);


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,&＃39;2021-07-28 10:00:00&＃39;,&＃39;2021-07-28 10:01:00&＃39;,86340,20000,&＃39;1,&＃39;,2,1,&＃39;暂定&＃39;,1);


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