当前位置：首页 > news >正文

Python操作Sqlite的简单封装

news 来源：原创 2024/5/10 10:32:12

文章目录

一、安装依赖
二、配置文件
三、实现类

一、安装依赖

pip install numpy

二、配置文件

utils.config.py


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

三、实现类

utils.PostGreOp.py

# encoding: utf-8import json
import sqlite3
import numpy as np
from utils import config as cfclass 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.lastrowidcur.close()# 提交到数据库执行db.commit()except Exception as e:print(e)op_id = Nonecur.close()# Rollback in case there is any errordb.rollback()# 关闭数据库连接db.close()return op_id# 查def select(self, sql):db = sqlite3.connect(self.db_path)cur = db.cursor()results = Nonetry:# 执行sql语句cur.execute(sql)# 获取所有记录列表results = cur.fetchall()# print(results)except Exception as e:print(e)# 关闭数据库连接db.close()return results@classmethoddef escape_str(cls, text):'''string类型数据导入时有可能出现单双引号等需要转义的字段，也可能出现nan这样的字段，需要先处理一下:return:'''if cls.isNaNo(text):return 'null'else:return "'" + json.dumps(str(text), ensure_ascii=False)[1:-1] + "'"@classmethoddef 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)@classmethoddef isNaNo(cls, sth):'''NaN、None或者空字符串返回True，其他情况返回False'''if not sth:return Trueif isinstance(sth, float):if np.isnan(sth):return Truereturn Falseif __name__ == '__main__':# 指定目录下没有数据库的话会自动创建sqlt = SqliteOp(db_path='./mysqlite.db')# 查询当前数据库下的所有表res = sqlt.select(f'''SELECT name FROM sqlite_master WHERE type='table';''')print(res)# []