Python操作Sqlite的简单封装

一、安装依赖

pip install numpy

二、配置文件

utils.config.py

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

三、实现类

utils.PostGreOp.py

# encoding: utf-8

import json
import sqlite3
import numpy as np
from utils import config as cf



class SqliteOp(object):

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

    # 增删改
    def operate(self, sql):
        db = sqlite3.connect(self.db_path)
        cur = db.cursor()
        try:
            # 执行sql语句
            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):
        db = sqlite3.connect(self.db_path)
        cur = db.cursor()
        results = None
        try:
            # 执行sql语句
            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



if __name__ == '__main__':

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

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