Python实现sqlite3增删改查的封装，可直接调用接口
前些天发现了一个巨牛的人工智能学习网站，通俗易懂，风趣幽默，忍不住分享一下给大家。点击跳转到网站。
特性：
- 搭建通用增删查改模块，减少代码量。
- 让代码更加清晰、可读
- 即便进行了封装，也丝毫不影响其灵活性
sqlite3我也就不多介绍了，直接上代码。附上相关使用方法和测试用例！
import sqlite3 '''写一个类打包成库，通用于储存信息的sqlite''' '''函数返回值可优化''' '''使用：使用''' '''说明：1、单例模式连接数据库：避免数据库connect过多导致数据库down 2、根据数据库增删查改性能对比，统一使用execute进行常规数据库操作 3、且不做try操作：1、影响性能 2、若报错，外部调用无法确定问题所在，''' class LiteDb(object): _instance = None def __new__(cls, *args, **kw): if cls._instance is None: cls._instance = object.__new__(cls) return cls._instance def openDb(self, dbname): self.dbname = dbname self.conn = sqlite3.connect(self.dbname) self.cursor = self.conn.cursor() def closeDb(self): ''' 关闭数据库 :return: ''' self.cursor.close() self.conn.close() def createTables(self, sql): ''' example：'create table userinfo(name text, email text)' :return: result=[1,None] ''' self.cursor.execute(sql) self.conn.commit() result = [1, None] return result def dropTables(self, sql): ''' example:'drop table userinfo' :param sql: :return:result=[1,None] ''' self.cursor.execute(sql) self.conn.commit() result = [1, None] return result def executeSql(self, sql, value=None): ''' 执行单个sql语句，只需要传入sql语句和值便可 :param sql:'insert into user(name,password,number,status) values(?,?,?,?)' 'delete from user where name=?' 'updata user set status=? where name=?' 'select * from user where id=%s' :param value:[(123456,123456,123456,123456),(123,123,123,123)] value:'123456' value:(123,123) :return:result=[1,None] ''' '''增、删、查、改''' if isinstance(value,list) and isinstance(value[0],(list,tuple)): for valu in value: self.cursor.execute(sql, valu) else: self.conn.commit() result = [1, self.cursor.fetchall()] else: '''执行单条语句：字符串、整型、数组''' if value: self.cursor.execute(sql, value) else: self.cursor.execute(sql) self.conn.commit() result = [1, self.cursor.fetchall()] return result
测试用例
from dbUse import LiteDb '''对于二次封装的数据库进行测试''' '''增删查改''' #用例 ''' select name from sqlite_master where type='table select * from user 'select * from user where id = %s'%7 select * from user where id = ? , 7 select * from user where id=? or id=?, ['7','8'] insert into user(id) values(7) 'insert into user(id) values(%s)'%7 'insert into user(id) values(?)',[('10',),('11',)] delete from user where id=7 'delete from user where id=%s'%7 'delete from user where id=?',[('10',),('11',)] update user set id=7 where id=11 'update user set id=%s where id=%s'%(10,20) 'update user set id=? where id=?',[('21','11'),('20','10')]''' db=LiteDb() db.openDb('user.db') def close(): db.closeDb() def createTables(): result=db.createTables('create table if not exists user(id varchar(128))') def executeSQL(): '''增删查改''' result = db.executeSql('insert into user(id) values(?)',('99',)) result = db.executeSql('select * from user ') executeSQL() close()
