您好,登錄后才能下訂單哦!
本文實(shí)例講述了Python帶動(dòng)態(tài)參數(shù)功能的sqlite工具類。分享給大家供大家參考,具體如下:
最近在弄sqlite和python
在網(wǎng)上參考各教程后,結(jié)合以往java jdbc數(shù)據(jù)庫工具類寫出以下python連接sqlite的工具類
寫得比較繁瑣 主要是想保留一種類似java的Object…args動(dòng)態(tài)參數(shù)寫法 并兼容數(shù)組/list方式傳遞不定個(gè)數(shù)參數(shù) 并且返回值是List形式 dict字典 以便和JSON格式互相轉(zhuǎn)換
在python中有一些區(qū)別 經(jīng)過該工具類封裝之后可以有以下用法:
db.executeQuery("s * f t w id=? and name=?", "id01", "name01");//動(dòng)態(tài)參數(shù)形式 db.executeQuery("s * f t w id=? and name=?", ("id01", "name01"));//tuple元組式 等價(jià)上面 括號(hào)可省略 db.executeQuery("s * f t w id=? and name=?", ["id01", "name01"]);//list數(shù)組形式
完整Python代碼如下:
#!/usr/bin/python #-*- coding:utf-8 -*- import sqlite3 import os # # 連接數(shù)據(jù)庫幫助類 # eg: # db = database() # count,listRes = db.executeQueryPage("select * from student where id=? and name like ? ", 2, 10, "id01", "%name%") # listRes = db.executeQuery("select * from student where id=? and name like ? ", "id01", "%name%") # db.execute("delete from student where id=? ", "id01") # count = db.getCount("select * from student ") # db.close() # class database : dbfile = "sqlite.db" memory = ":memory:" conn = None showsql = True def __init__(self): self.conn = self.getConn() #輸出工具 def out(self, outStr, *args): if(self.showsql): for var in args: if(var): outStr = outStr + ", " + str(var) print("db. " + outStr) return #獲取連接 def getConn(self): if(self.conn is None): conn = sqlite3.connect(self.dbfile) if(conn is None): conn = sqlite3.connect(self.memory) if(conn is None): print("dbfile : " + self.dbfile + " is not found && the memory connect error ! ") else: conn.row_factory = self.dict_factory #字典解決方案 self.conn = conn self.out("db init conn ok ! ") else: conn = self.conn return conn #字典解決方案 def dict_factory(self, cursor, row): d = {} for idx, col in enumerate(cursor.description): d[col[0]] = row[idx] return d #關(guān)閉連接 def close(self, conn=None): res = 2 if(not conn is None): conn.close() res = res - 1 if(not self.conn is None): self.conn.close() res = res - 1 self.out("db close res : " + str(res)) return res #加工參數(shù)tuple or list 獲取合理參數(shù)list #把動(dòng)態(tài)參數(shù)集合tuple轉(zhuǎn)為list 并把單獨(dú)的傳遞動(dòng)態(tài)參數(shù)list從tuple中取出作為參數(shù) def turnArray(self, args): #args (1, 2, 3) 直接調(diào)用型 exe("select x x", 1, 2, 3) #return [1, 2, 3] <- list(args) #args ([1, 2, 3], ) list傳入型 exe("select x x",[ 1, 2, 3]) len(args)=1 && type(args[0])=list #return [1, 2, 3] if(args and len(args) == 1 and (type(args[0]) is list) ): res = args[0] else: res = list(args) return res #分頁查詢 查詢page頁 每頁num條 返回 分頁前總條數(shù) 和 當(dāng)前頁的數(shù)據(jù)列表 count,listR = db.executeQueryPage("select x x",1,10,(args)) def executeQueryPage(self, sql, page, num, *args): args = self.turnArray(args) count = self.getCount(sql, args) pageSql = "select * from ( " + sql + " ) limit 5 offset 0 " #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'}] def executeQuery(self, sql, *args): args = self.turnArray(args) self.out(sql, args) conn = self.getConn() cursor = conn.cursor() res = cursor.execute(sql, args).fetchall() return res #執(zhí)行sql或者查詢列表 并提交 def execute(self, sql, *args): args = self.turnArray(args) self.out(sql, args) conn = self.getConn() cursor = conn.cursor() #sql占位符 填充args 可以是tuple(1, 2)(動(dòng)態(tài)參數(shù)數(shù)組) 也可以是list[1, 2] list(tuple) tuple(list) res = cursor.execute(sql, args).fetchall() conn.commit() #self.close(conn) return res #查詢列名列表array[str] eg: ['id', 'name', 'birth'] def getColumnNames(self, sql, *args): args = self.turnArray(args) self.out(sql, args) conn = self.getConn() if(not conn is None): cursor = conn.cursor() cursor.execute(sql, args) res = [tuple[0] for tuple in cursor.description] return res #查詢結(jié)果為單str eg: 'xxxx' def getString(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] for tuple in cursor.description] #print(columnNames) res = "" if(listRes and len(listRes) >= 1): res = listRes[0][columnNames[0]] return res #查詢記錄數(shù)量 自動(dòng)附加count(*) eg: 3 def getCount(self, sql, *args): args = self.turnArray(args) sql = "select count(*) cc from ( " + sql + " ) " resString = self.getString(sql, args) res = 0 if(resString): res = int(resString) return res ####################################測試 def main(): db = database() db.execute( ''' create table if not exists student( id text primary key, name text not null, birth text ) ''' ) for i in range(10): db.execute("insert into student values('id1" + str(i) + "', 'name1" + str(i) + "', 'birth2" + str(i) + "')") db.execute("insert into student values('id01', 'name01', 'birth01')") db.execute("insert into student values('id02', 'name02', 'birth01')") db.execute("insert into student values('id03', 'name03', 'birth01')") print(db.getColumnNames("select * from student")) print(db.getCount("select * from student " )) print(db.getString("select name from student where id = ? ", "id02" )) print(db.executeQuery("select * from student where 1=? and 2=? ", 1, 2 )) print(db.executeQueryPage("select * from student where id like ? ", 1, 5, "id0%")) db.execute("update student set name='nameupdate' where id = ? ", "id02") db.execute("delete from student where id = ? or 1=1 ", "id01") db.close() if __name__ == '__main__': main()
更多關(guān)于Python相關(guān)內(nèi)容感興趣的讀者可查看本站專題:《Python操作SQLite數(shù)據(jù)庫技巧總結(jié)》、《Python常見數(shù)據(jù)庫操作技巧匯總》、《Python數(shù)據(jù)結(jié)構(gòu)與算法教程》、《Python函數(shù)使用技巧總結(jié)》、《Python字符串操作技巧匯總》、《Python入門與進(jìn)階經(jīng)典教程》及《Python文件與目錄操作技巧匯總》
希望本文所述對(duì)大家Python程序設(shè)計(jì)有所幫助。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。