Python操作Sqlite的简单封装

---

一、安装依赖

pip install numpy

---

二、配置文件

config.config.py

############### 233 SQLITE Configuration ###############
SQLITE_PATH = './mysqlite.db'

---

三、实现类

utils.PostGreOp.py

# encoding: utf-8
import datetime
import json
import sqlite3
import numpy as np
from config import config


class SqliteOp(object):

    def __init__(self, db_path=config.SQLITE_PATH):
        print(f"db_path: {db_path}")
        self.db_path = db_path
        # conn = sqlite3.connect('./test.db')

    # 增删改
    def operate(self, sql, value_tup=None):
        db = sqlite3.connect(self.db_path)
        cur = db.cursor()
        try:
            # 执行sql语句
            if value_tup: cur.execute(sql, value_tup)
            else: cur.execute(sql)
            op_id = cur.lastrowid
            cur.close()
            # 提交到数据库执行
            db.commit()
        except Exception as e:
            print(e)

            op_id = None

            cur.close()
            # Rollback in case there is any error
            db.rollback()
        # 关闭数据库连接
        db.close()

        return op_id

    # 查
    def select(self, sql, value_tup=None):
        db = sqlite3.connect(self.db_path)
        cur = db.cursor()
        results = None
        try:
            # 执行sql语句
            if value_tup: cur.execute(sql, value_tup)
            else: cur.execute(sql)
            # 获取所有记录列表
            results = cur.fetchall()
            # print(results)
        except Exception as e:
            print(e)
            # 关闭数据库连接
        db.close()
        return results

    # @classmethod
    # def escape_str(cls, text):
    #     '''
    #     string类型数据导入时有可能出现单双引号等需要转义的字段，也可能出现nan这样的字段，需要先处理一下
    #     :return:
    #     '''
    #     if cls.isNaNo(text):
    #         return 'null'
    #     else:
    #         return "'" + json.dumps(str(text), ensure_ascii=False)[1:-1] + "'"
    #
    # @classmethod
    # def escape_num(cls, text):
    #     '''
    #     转一下整数，报错的话说明传入的不是数字，有sql注入风险
    #     :return:
    #     '''
    #     if str(text) == '0':
    #         return '0'
    #     elif cls.isNaNo(text):
    #         return 'null'
    #     else:
    #         # return json.dumps(str(text), ensure_ascii=False)
    #         try:
    #             int(text)
    #             return str(text)
    #         except Exception as e:
    #             raise Exception('传入不是数字，有sql注入风险')
    #
    #     # if cls.isNaNo(text):
    #     #     return 'null'
    #     # else:
    #     #     return json.dumps(str(text), ensure_ascii=False)
    #
    # @classmethod
    # def isNaNo(cls, sth):
    #     '''
    #     NaN、None或者空字符串返回True，其他情况返回False
    #     '''
    #     if not sth:
    #         return True
    #     if isinstance(sth, float):
    #         if np.isnan(sth):
    #             return True
    #     return False

    @classmethod
    def is_table_exist(cls, sqlt, table_name):
        '''
        判断某表是否存在
        :return:
        '''
        res = sqlt.select(f'''SELECT name FROM sqlite_master WHERE type='table';''')
        table_list = [r[0] for r in res] if res else []
        return True if table_name in table_list else False


if __name__ == '__main__':

    # 指定目录下没有数据库的话会自动创建
    sqlt = SqliteOp(db_path=config.SQLITE_PATH)

    # 查询当前数据库下的所有表
    res = sqlt.select(f'''SELECT name FROM sqlite_master WHERE type='table';''')
    table_list = [r[0] for r in res] if res else []
    print(table_list)
    # []

    table_name = 'test_table'
    if not table_name in table_list:
        # 建表
        sqlt.operate(f'''
            CREATE TABLE {table_name} (
                id INTEGER PRIMARY KEY AUTOINCREMENT,
                name TEXT NOT NULL,
                age INTEGER,
                createtime TEXT NOT NULL
            );
        ''')

    # 查询当前数据库下的所有表
    res = sqlt.select(f'''SELECT name FROM sqlite_master WHERE type='table';''')
    table_list = [r[0] for r in res] if res else []
    print(table_list)
    # ['test_table', 'sqlite_sequence']

    # # 带参数的检索语法示例
    # # print(sql)
    # name = 'ps'
    # age = 12
    # createtime = str(datetime.datetime.now())
    # sqlt.operate(f'''
    #     insert into {table_name} 
    #     (name, age, createtime, updatetime)
    #     values
    #     (?, ?, ?, ?)
    # ''', (name, age, createtime))