使用grafana展示SQLite数据库表中的数据
1、环境准备
yum -y install epel-release
yum -y install sqlite sqlite-devel
yum -y install python-pip
pip install --upgrade setuptools
yum list available python* | grep paramiko
yum -y install python-paramiko
yum -y install python2-requests
2、脚本
# !/usr/local/python3.8/bin/python3 # -*- coding:UTF-8 -*- import sqlite3 import re def dict_factory(cursor, row): # 将游标获取的数据处理成字典返回 # cursor.description:获取表头 d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d def connsqlite(SQL, DATA=None): """ :param SQL: :param DATA: tuple :return: """ local_all = '不支持的SQL语句' if not isinstance(DATA, tuple) and DATA: return 'DATA数据类型错误，该数据类型必需是元组' # 1.创建连接 check_same_thread=False开启多线程 # con = sqlite3.connect(database='/Users/liuqian/sqliteFiles/brush_question', check_same_thread=False) conn = sqlite3.connect(database='test.db', check_same_thread=False) # 获取cursor对象 cur = conn.cursor() if re.match('^SELECT|^select', SQL): # 使得查询结果以字典形式返回 cur.row_factory = dict_factory if DATA: curres = cur.execute(SQL, DATA) local_all = curres.fetchall() else: curres = cur.execute(SQL) local_all = curres.fetchall() elif re.match('^UPDATE|^update|^INSERT|^insert|^CREATE|create', SQL): try: if DATA: # local_all = cur.executemany(SQL, DATA) local_all = cur.execute(SQL, DATA) else: local_all = cur.execute(SQL) conn.commit() except Exception as f: # Rollback in case there is any error local_all = f conn.rollback() cur.close() conn.close() return local_all if __name__ == '__main__': # 新建表,设置默认当前时间格式为rfc3339,使用：strftime('%Y-%m-%dT%H:%M:%S+08:00','now','localtime'),+08:00表示东八区 creres1 = connsqlite( "CREATE TABLE info (id integer primary key autoincrement not null,hostname varchar(32),cpu varchar(32),memory varchar(32),disk varchar(32),datetime TimeStamp NOT NULL DEFAULT (datetime('now','localtime')));") creres2 = connsqlite( "CREATE TABLE info (id integer primary key autoincrement not null,hostname varchar(32),cpu varchar(32),memory varchar(32),disk varchar(32),datetime TimeStamp NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S+08:00','now','localtime')));") # 表中插入数据的两种方式，推荐使用第一种以防止SQL注入 insres1 = connsqlite( "INSERT INTO info (hostname,cpu,memory,disk) VALUES (?,?,?,?);", ('a', 'b', 'c', 'd')) insres2 = connsqlite( "INSERT INTO info VALUES (null,'{}','{}','{}','{}',datetime('now','localtime'));".format('master', '10', 'c', 'd')) # 使用cur.executemany(SQL, DATA)执行SQL语句的传参方式，不能使用该方法执行SELECT语句 # sqlres3 = connsqlite( # "INSERT INTO info (hostname,cpu1,cpu5,cpu15,memory) VALUES (?,?,?,?,?);", [('a', 'b', 'c', 'd', 'e')]) # 查询数据，返回字典格式的数据，推荐使用第一种方式 selres1 = connsqlite("select rowid,* from info where hostname=? and cpu=?;", ('master', '10')) selres2 = connsqlite("select rowid,* from info where hostname='{}' and cpu='{}';".format('master', '10')) print(selres1) print(selres2)
3、SQLite常用操作
# 创建表 CREATE TABLE info ( id integer primary key autoincrement not null, hostname varchar(32), cpu varchar(32), memory varchar(32), disk varchar(32), datetime TEXT NOT NULL DEFAULT (datetime('now','localtime')) ); CREATE TABLE info ( id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, hostname VARCHAR (32), cpu REAL, memory REAL, disk REAL, datetime TEXT NOT NULL DEFAULT (strftime('%Y-%m-%dT%H:%M:%S+08:00','now','localtime')) # 默认时间格式设为rfc3339，+08:00表示东八区 ); # 联合主键 ,在建表时 CREATE TABLE tb_test ( bh varchar(5), id integer, ch varchar(20), mm varchar(20), primary key (id,bh) ); 注意：在创建联合主键时，主键创建要放在所有字段最后面，否则也会创建失败 # 修改select语句显示格式 sqlite> .header on sqlite> .mode column sqlite> select rowid,* from username; rowid id name password ----- -- ---- -------- 1 1 abc 123456 2 2 张三 123456 注意： 必须显式的指定rowid才可以获取rowid列。查询rowid的效率非常高，所以直接使用rowid作为查询条件是一个优化查询的好方法。 rowid列作为主键，在极端情况下存在隐患。由于rowid值会一直递增，如果达到所允许的最大值9223372036854775807后，它会自动搜索没有被使用的值重新使用，并不会提示用户。
这时，使用rowid排序记录会产生乱序，并引入其他的逻辑问题。所以，如果用户的数据库存在这种可能的情况，就应该使用AUTOINCREMENT定义主键，从而避免这种问题。
使用AUTOINCREMENT设置自增主键，虽然也会遇到9223372036854775807问题，但是它会报错，提示用户避免产生rowid所引发的问题 # 查询数据库中有哪些表 SELECT tbl_name FROM sqlite_master WHERE type = 'table'; # 查看info表的信息(命令行：sqlite> .schema cpuinfo) SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'info'; # 清除info表的数据 DELETE FROM info; # 重置diskinfo表中的主键计数器为1 DELETE FROM sqlite_sequence WHERE name='info';
4、grafana配置
设置变量
拆线图
-- sqlite在grafana展示拆线图时需要转换时间格式为rfc3339 -- sqlite字符串截取函数：substr(column,num start,num length) -- 用法： -- column为表中的列，数据类型必需为字符串 -- start为起始位置；字符串的第一个字符的位置为1，不是从0开始计算 -- length为长度。 -- 示例：substr(datetime,1,10)，截取datetime列中的数据，从第一个字符开始截取10个字符 -- 字符串拼接：|| WITH converted AS ( -- a row looks like this (value, date): 1.45, '2020-12-12'。SELECT value, date || 'T00:00:00Z' AS datetime FROM raw_table -- a row looks like this (value, date): 1.45, '2020-12-12 00:00:00'。SELECT value, substr(date,1,10) || 'T' || substr(date,12) || '+08:00' AS datetime FROM raw_table -- a row looks like this (value, date): 1.45, '2020-12-12T00:00:00+08:00'。 SELECT value, date AS datetime FROM raw_table SELECT hostname,memory,datetime AS datetime FROM info ) -- 使用cast()函数转换数据类型 SELECT datetime as time,cast(memory as REAL) as value FROM converted WHERE hostname="${hostname}" ORDER BY datetime ASC
5、获取CPU，内存、硬盘使用率
# 获取CPU使用率 CPU=$(cat /proc/loadavg | awk '{printf "{\"cpu1\":\"%s\",\"cpu5\":\"%s\",\"cpu15\":\"%s\"}",$1,$2,$3}') cat /proc/loadavg | awk '{if ($1 <50 || $2 <50 || $3 <50){printf "{\"cpu1\":\"%s\",\"cpu5\":\"%s\",\"cpu15\":\"%s\",\"status\":\"正常\"}",$1,$2,$3}else{printf "{\"cpu1\":\"%s\",\"cpu5\":\"%s\",\"cpu15\":\"%s\",\"status\":\"异常\"}",$1,$2,$3}}' # 获取内存使用率 MEMORY=$(free -m | awk 'NR==2{printf "{\"memory\":%.2f}",$3/$2*100}') # 获取硬盘使用率 DISK=$(df -h | awk '/^\/dev/{printf "{\"partition\":\"%s\",\"usge\":%d} ",$6,$5}')
参考链接：
https://github.com/pawelsalawa/sqlitestudio/releases # SQLiteStudio下载
https://www.sqliteexpert.com/download.html # SQLite Expert下载
https://blog.csdn.net/u014644574/article/details/127745981 # sqlite日期和时间类型
https://m.runoob.com/sqlite/
https://grafana.com/grafana/plugins/frser-sqlite-datasource/ # grafana展示sqlite数据
https://cloud.tencent.com/developer/article/1582979?from=15425 # grafana展示MYSQL数据
https://ijmacd.github.io/rfc3339-iso8601/ # rfc3339时间格式
https://docs.python.org/zh-cn/2/library/sqlite3.html#sqlite3.Connection.row_factory
https://docs.python.org/zh-cn/3/library/sqlite3.html
