最近的任務(wù)需要做一個(gè)工具，這個(gè)工具會(huì)定期查詢文件或者數(shù)據(jù)庫，原本打算從日志中提取，但日志中太多其他標(biāo)準(zhǔn)輸出的內(nèi)容了，提取信息可能很困難，也很容易導(dǎo)致錯(cuò)誤，因此考慮了sqlit3。此篇記錄sqlite3的常用命令。

1、為什么選擇它？

• 無需設(shè)置用戶鑒權(quán)
• 開箱即用，相較于文本log日志而言,數(shù)據(jù)庫存儲(chǔ)的結(jié)構(gòu)比較好
• 可以用SQL語句搜索和讀寫數(shù)據(jù)非常方便，無需額外再寫一堆復(fù)雜的linux命令提取數(shù)據(jù)

2、軟件安裝

• 環(huán)境：linux/macOS

sudo apt-get install sqlite

• 語法結(jié)構(gòu)：
  sqlite3 數(shù)據(jù)庫 "sql語句"

3、命令行常見用法

• 查看表格結(jié)構(gòu)

sqlite3 tmp.db ".schema"

• 創(chuàng)建表格

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

• 查看表格數(shù)據(jù)

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

• 修改更新，插入數(shù)據(jù)

# 更新數(shù)據(jù)表的值
UPDATE users SET email = 'updated@example.com' WHERE id = 2;

#插入數(shù)據(jù) 
INSERT INTO users (username, email) VALUES ('Charlie', 'charlie@example.com');

• 導(dǎo)出表格按照格式

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

4、在python中使用sqlite3

與mysql的使用類似

• 主要的動(dòng)作都是
  1、連接數(shù)據(jù)庫，創(chuàng)建指針
  2、數(shù)據(jù)增刪查改命令
  3、提交
  4、關(guān)閉數(shù)據(jù)庫
• 下面的代碼給出具體的數(shù)據(jù)插入例子

import sqlite3

conn = sqlite3.connect("databsae") #連接數(shù)據(jù)庫
cursor = conn.cursor() #創(chuàng)建指針
 #數(shù)據(jù)增刪查改
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、評(píng)價(jià)

• 其他數(shù)據(jù)庫如mysql對于一個(gè)隨用隨刪除的流程而言，使用和設(shè)置都過于復(fù)雜，sqlite3的輕便讓我覺得就像把數(shù)據(jù)通過> log.txt一樣方便。
  美中不足
• 不能并行寫入數(shù)據(jù)，鎖是數(shù)據(jù)庫級(jí)別的不能只鎖表、行或者某個(gè)數(shù)據(jù)格。
  痛定思痛，決定用字典，能跑就行～

參考
python sqlite3文檔