sqlite相关

前言

本文记录一些sqlite相关笔记，随时更新。

正文

1. 时间函数

• datetime()

-- 当前时间 2022-03-24 17:32:43
select datetime('now'); --2022-03-24 09:32:57
select datetime('now', 'unixepoch'); --1970-01-29 11:14:22
select datetime('now', 'utc'); --2022-03-24 01:34:34
select datetime('now', 'localtime'); --2022-03-24 17:35:05
select datetime('now', 'localtime', '-1 day'); --2022-03-23 17:35:05

• strftime()

-- 格式化字符串到指定格式
select strftime("%Y-%m-%d","2023-02-08 14:33:03"); --2023-02-08
-- 获取毫秒时间戳
select strftime('%s', '2023-02-13','utc')||substr(strftime('%f', '2023-02-13','utc'), 4); --1676217600000(2023-02-13 00:00:00)
select strftime('%s', '2023-02-13','localtime')||substr(strftime('%f', '2023-02-13','localtime'), 4); --1676275200000(2023-02-13 16:00:00)
select strftime('%s', '2023-02-13')||substr(strftime('%f', '2023-02-13'), 4); -- 1676246400000(2023-02-13 08:00:00)

• date()

--与datetime几乎相同，不过输出仅仅精确到天
select date("now","localtime"); --2023-02-08
select date("now","localtime","-1 days"); --2023-02-07
select date("now","unixepoch"); -- 1970-01-29

参考SQLite 日期 & 时间

• random()

-- 在一定规则下得到了数据集，将数据集随机排序，然后选择第一条数据
-- 这样的好处是满足某种条件下打乱查询结果的排列顺序；缺点是适合小范围数据集，频繁使用ORDER  BY  RANDOM()可能会导致性能问题，因为数据库需要为每一行生成一个随机数，然后基于这些随机数进行排序，这在大型数据集中会非常消耗资源。
SELECT * FROM table_name WHERE id > target_id ORDER BY RANDOM() LIMIT 1;

2. 字符串截取
   函数：substr(string string,num start,num length)
   用法：
   string为字符串；
   start为起始位置；字符串的第一个字符的位置为1，不是从0开始计算
   length为长度。
   参考：sqlite 截取字符串函数substr

3. 获取字符串长度
   length()
   参考：SQLite 常用函数

4. 字符串拼接
   "aaa"||"bbb"

5. 支持regexp正则表达式
   参考：python sqlite3 支持REGEXP语句 正则表达式 regular expression

import sqlite3
import re

def regexp(expr, item):
    reg = re.compile(expr)
    return reg.search(item) is not None

conn = sqlite3.connect(':memory:')
conn.create_function("REGEXP", 2, regexp)
cursor = conn.cursor()
cursor.execute('CREATE TABLE foo (bar TEXT)')
cursor.executemany('INSERT INTO foo (bar) VALUES (?)',[('aaa"test"',),('blah',)])
cursor.execute('SELECT bar FROM foo WHERE bar REGEXP ?',['"test"'])
data=cursor.fetchall()
print(data)

# 如果是在类中定义
def regexp(self, expr, item):
        '''
        正则表达式函数
        @params expr: 需要搜索的正则表达式
        @params item: sqlite 传入的需要搜索的内容
        '''
        if type(item).__name__ == "bytes":
            item = item.decode()
        reg = re.compile(expr)
        return reg.search(item) is not None
self.conn.create_function("regexp", 2, self.regexp)

6. 查询结果返回字典类型

# 在类中定义
def dict_factory(self, cursor, row):
        d = {}
        for index, col in enumerate(cursor.description):
            d[col[0]] = row[index]
        return d
self.conn = sqlite3.connect(self.dbname)
self.conn.row_factory = self.dict_factory

7. 获取uuid

select substr(u, 1, 8)||'-'||substr(u, 8, 4)||'-'||substr(u,12,4)||'-'||substr(u,16,4)||'-'||substr(u,20,12) as uuid from (select lower(hex(randomblob(16))) as u) t;

8. sqlite3 导出数据为 csv，导入csv数据
   导入的前提为数据库必须有这个表，没有表结构的还需要研究一下怎么导入
   导出device表保存为device.csv

#!/bin/bash
db=tst.db
sqlite3 $db << EOF
.output device.csv
select * from device;
.output stdout
.exit
EOF

导入device.csv到数据库

#!/bin/bash
db=tst.db
file=device.csv
sqlite3 $db << EOF
.import ./$file device
.exit
EOF

只是导出的话，也可以参考博客SQLite中.mode的演示说明设置导出格式
参考：
[1]. Sqlite 命令行导出、导入数据（直接支持CSV）
[2]. sqlite3: 用脚本批量导出sqlite数据库中的表格数据
[3]. SQLite中.mode的演示说明