生信log39|使用sqlite构建临时数据库-附方法和使用sql&python

最近的任务需要做一个工具，这个工具会定期查询文件或者数据库，原本打算从日志中提取，但日志中太多其他标准输出的内容了，提取信息可能很困难，也很容易导致错误，因此考虑了sqlit3。此篇记录sqlite3的常用命令。

1、为什么选择它？

• 无需设置用户鉴权
• 开箱即用，相较于文本log日志而言,数据库存储的结构比较好
• 可以用SQL语句搜索和读写数据非常方便，无需额外再写一堆复杂的linux命令提取数据

2、软件安装

• 环境：linux/macOS

sudo apt-get install sqlite

• 语法结构：
  sqlite3 数据库 "sql语句"

3、命令行常见用法

• 查看表格结构

sqlite3 tmp.db ".schema"

• 创建表格

CREATE TABLE tasks (filename TEXT, vcfMD5 TEXT, task_id TEXT, anno_status TEXT, resultMD5 TEXT, error_info TEXT, time TIMESTAMP DEFAULT CURRENT_TIMESTAMP);

• 查看表格数据

sqlite3 tmp.db "SELECT * FROM tasks"
test_1.passed.indel.vcf.gz|0ce5cf61a9ba9a16372e2e342f8b62b6|annotation-1781e5036598e000||||2023-08-22 07:24:19
test_2.passed.indel.vcf.gz|4b7c2b9cb689df046ad6b3a13cc30c97|annotation-1781e5036618e000||||2023-08-22 07:24:19

• 修改更新，插入数据

# 更新数据表的值
UPDATE users SET email = 'updated@example.com' WHERE id = 2;

#插入数据 
INSERT INTO users (username, email) VALUES ('Charlie', 'charlie@example.com');

• 导出表格按照格式

sqlite3 -header -separator $'\t' phyloP_hg38_chrM.db "SELECT * FROM phyloP;" | sed 's/NULL/NA/g' > test.tsv

4、在python中使用sqlite3

与mysql的使用类似

• 主要的动作都是
  1、连接数据库，创建指针
  2、数据增删查改命令
  3、提交
  4、关闭数据库
• 下面的代码给出具体的数据插入例子

import sqlite3

conn = sqlite3.connect("databsae") #连接数据库
cursor = conn.cursor() #创建指针
 #数据增删查改
user_data = [('Alice', 'alice@example.com'),
             ('Bob', 'bob@example.com'),
             ('Charlie', 'charlie@example.com')]

cursor.executemany('INSERT INTO users (username, email) VALUES (?, ?)', user_data)
conn.commit() #提交
conn.close()

5、评价

• 其他数据库如mysql对于一个随用随删除的流程而言，使用和设置都过于复杂，sqlite3的轻便让我觉得就像把数据通过> log.txt一样方便。
  美中不足
• 不能并行写入数据，锁是数据库级别的不能只锁表、行或者某个数据格。
  痛定思痛，决定用字典，能跑就行～

参考
python sqlite3文档