Python带动态参数功能的sqlite工具类
本文实例讲述了Python带动态参数功能的sqlite工具类。分享给大家供大家参考，具体如下：
最近在弄sqlite和python
在网上参考各教程后，结合以往javajdbc数据库工具类写出以下python连接sqlite的工具类
写得比较繁琐主要是想保留一种类似java的Object…args动态参数写法并兼容数组/list方式传递不定个数参数并且返回值是List形式dict字典以便和JSON格式互相转换
在python中有一些区别经过该工具类封装之后可以有以下用法：
db.executeQuery("s*ftwid=?andname=?","id01","name01");//动态参数形式
db.executeQuery("s*ftwid=?andname=?",("id01","name01"));//tuple元组式等价上面括号可省略
db.executeQuery("s*ftwid=?andname=?",["id01","name01"]);//list数组形式
完整Python代码如下：
#!/usr/bin/python
#-*-coding:utf-8-*-
importsqlite3
importos
#
#连接数据库帮助类
#eg:
#db=database()
#count,listRes=db.executeQueryPage("select*fromstudentwhereid=?andnamelike?",2,10,"id01","%name%")
#listRes=db.executeQuery("select*fromstudentwhereid=?andnamelike?","id01","%name%")
#db.execute("deletefromstudentwhereid=?","id01")
#count=db.getCount("select*fromstudent")
#db.close()
#
classdatabase:
dbfile="sqlite.db"
memory=":memory:"
conn=None
showsql=True
def__init__(self):
self.conn=self.getConn()
#输出工具
defout(self,outStr,*args):
if(self.showsql):
forvarinargs:
if(var):
outStr=outStr+","+str(var)
print("db."+outStr)
return
#获取连接
defgetConn(self):
if(self.connisNone):
conn=sqlite3.connect(self.dbfile)
if(connisNone):
conn=sqlite3.connect(self.memory)
if(connisNone):
print("dbfile:"+self.dbfile+"isnotfound&&thememoryconnecterror!")
else:
conn.row_factory=self.dict_factory#字典解决方案
self.conn=conn
self.out("dbinitconnok!")
else:
conn=self.conn
returnconn
#字典解决方案
defdict_factory(self,cursor,row):
d={}
foridx,colinenumerate(cursor.description):
d[col[0]]=row[idx]
returnd
#关闭连接
defclose(self,conn=None):
res=2
if(notconnisNone):
conn.close()
res=res-1
if(notself.connisNone):
self.conn.close()
res=res-1
self.out("dbcloseres:"+str(res))
returnres
#加工参数tupleorlist获取合理参数list
#把动态参数集合tuple转为list并把单独的传递动态参数list从tuple中取出作为参数
defturnArray(self,args):
#args(1,2,3)直接调用型exe("selectxx",1,2,3)
#return[1,2,3]<-list(args)
#args([1,2,3],)list传入型exe("selectxx",[1,2,3])len(args)=1&&type(args[0])=list
#return[1,2,3]
if(argsandlen(args)==1and(type(args[0])islist)):
res=args[0]
else:
res=list(args)
returnres
#分页查询查询page页每页num条返回分页前总条数和当前页的数据列表count,listR=db.executeQueryPage("selectxx",1,10,(args))
defexecuteQueryPage(self,sql,page,num,*args):
args=self.turnArray(args)
count=self.getCount(sql,args)
pageSql="select*from("+sql+")limit5offset0"
#args.append(num)
#args.append(int(num)*(int(page)-1))
self.out(pageSql,args)
conn=self.getConn()
cursor=conn.cursor()
listRes=cursor.execute(sql,args).fetchall()
return(count,listRes)
#查询列表array[map]eg:[{'id':u'id02','birth':u'birth01','name':u'name02'},{'id':u'id03','birth':u'birth01','name':u'name03'}]
defexecuteQuery(self,sql,*args):
args=self.turnArray(args)
self.out(sql,args)
conn=self.getConn()
cursor=conn.cursor()
res=cursor.execute(sql,args).fetchall()
returnres
#执行sql或者查询列表并提交
defexecute(self,sql,*args):
args=self.turnArray(args)
self.out(sql,args)
conn=self.getConn()
cursor=conn.cursor()
#sql占位符填充args可以是tuple(1,2)(动态参数数组)也可以是list[1,2]list(tuple)tuple(list)
res=cursor.execute(sql,args).fetchall()
conn.commit()
#self.close(conn)
returnres
#查询列名列表array[str]eg:['id','name','birth']
defgetColumnNames(self,sql,*args):
args=self.turnArray(args)
self.out(sql,args)
conn=self.getConn()
if(notconnisNone):
cursor=conn.cursor()
cursor.execute(sql,args)
res=[tuple[0]fortupleincursor.description]
returnres
#查询结果为单streg:'xxxx'
defgetString(self,sql,*args):
args=self.turnArray(args)
self.out(sql,args)
conn=self.getConn()
cursor=conn.cursor()
listRes=cursor.execute(sql,args).fetchall()
columnNames=[tuple[0]fortupleincursor.description]
#print(columnNames)
res=""
if(listResandlen(listRes)>=1):
res=listRes[0][columnNames[0]]
returnres
#查询记录数量自动附加count(*)eg:3
defgetCount(self,sql,*args):
args=self.turnArray(args)
sql="selectcount(*)ccfrom("+sql+")"
resString=self.getString(sql,args)
res=0
if(resString):
res=int(resString)
returnres
####################################测试
defmain():
db=database()
db.execute(
'''
createtableifnotexistsstudent(
idtextprimarykey,
nametextnotnull,
birthtext
)
'''
)
foriinrange(10):
db.execute("insertintostudentvalues('id1"+str(i)+"','name1"+str(i)+"','birth1"+str(i)+"')")
db.execute("insertintostudentvalues('id01','name01','birth01')")
db.execute("insertintostudentvalues('id02','name02','birth01')")
db.execute("insertintostudentvalues('id03','name03','birth01')")
print(db.getColumnNames("select*fromstudent"))
print(db.getCount("select*fromstudent"))
print(db.getString("selectnamefromstudentwhereid=?","id02"))
print(db.executeQuery("select*fromstudentwhere1=?and2=?",1,2))
print(db.executeQueryPage("select*fromstudentwhereidlike?",1,5,"id0%"))
db.execute("updatestudentsetname='nameupdate'whereid=?","id02")
db.execute("deletefromstudentwhereid=?or1=1","id01")
db.close()
if__name__=='__main__':
main()
更多关于Python相关内容感兴趣的读者可查看本站专题：《Python操作SQLite数据库技巧总结》、《Python常见数据库操作技巧汇总》、《Python数据结构与算法教程》、《Python函数使用技巧总结》、《Python字符串操作技巧汇总》、《Python入门与进阶经典教程》及《Python文件与目录操作技巧汇总》
希望本文所述对大家Python程序设计有所帮助。