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()