python操作SQLite数据库

# !/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):
    local_all = '不支持的SQL语句'
    # 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
        curres = cur.execute(SQL)
        local_all = curres.fetchall()
    elif re.match('^UPDATE|^INSERT|^update|^insert', SQL):
        try:
            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__':
    # 新建表
    connsqlite(
        'CREATE TABLE info (id integer primary key autoincrement not null,hostname varchar(32),cpu varchar(32),memory varchar(32),disk varchar(32),createdatetime TimeStamp NOT NULL DEFAULT (datetime("now","localtime")));')
    # 表中插入数据的两种方式
    sqlres = connsqlite(
        "INSERT INTO info (hostname,cpu,memory,disk) VALUES ('{}','{}','{}','{}');".format('a', 'b', 'c', 'd'))
    sqlres1 = connsqlite(
        "INSERT INTO info VALUES (null,'{}','{}','{}','{}',datetime('now','localtime'));".format('a', 'b', 'c', 'd'))
    # 查询数据，返回字典格式的数据
    selres = connsqlite('select rowid,* from cpuinfo;')
    print(selres)

# 创建表
CREATE TABLE info ( 
id integer primary key autoincrement not null, 
hostname varchar(32),
cpu varchar(32),
memory varchar(32),
disk varchar(32),
createdatetime TEXT NOT NULL DEFAULT (datetime('now','localtime'))
); 
# 联合主键 ,在建表时 
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';

# 查看cpuinfo表的信息(命令行：sqlite> .schema cpuinfo)
SELECT sql FROM sqlite_master WHERE type = 'table' AND tbl_name = 'cpuinfo';