I've been working on sqlite and python recently
After referring to the tutorials on the Internet
Combined with the previous java jdbc database tool classes, write the following python connection sqlite tool classes
The main reason for this is to keep a java like Object args dynamic parameter writing method is compatible with array / List mode to pass indefinite number parameters and return value is List dict dictionary for mutual conversion with JSON format
There are some differences in python that can be used after being encapsulated by this utility class:
db.executeQuery("s * f t w id=? and name=?", "id01", "name01");//Dynamic parameter form
db.executeQuery("s * f t w id=? and name=?", ("id01", "name01"));//Tuple tuple equivalent parentheses above can be omitted
db.executeQuery("s * f t w id=? and name=?", ["id01", "name01"]);//list array form
#!/usr/bin/python
#-*- coding:utf-8 -*-
import sqlite3
import os
#
# Connect database help class
# 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()
#Output tool
def out(self, outStr, *args):
if(self.showsql):
for var in args:
if(var):
outStr = outStr + ", " + str(var)
print("db. " + outStr)
return
#Get connection
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 #Dictionary solution
self.conn = conn
self.out("db init conn ok ! ")
else:
conn = self.conn
return conn
#Dictionary solution
def dict_factory(self, cursor, row):
d = {}
for idx, col in enumerate(cursor.description):
d[col[0]] = row[idx]
return d
#Close connection
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
#Get the reasonable parameter list from the tuple or list
#Turn the dynamic parameter set tuple into a list and take the list of individual dynamic parameters passed as parameters from the tuple
def turnArray(self, args):
#args (1, 2, 3) direct call type exe("select x x", 1, 2, 3)
#return [1, 2, 3] <- list(args)
#Args ([1, 2, 3],) list incoming 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 query query page page num entries per page returns the total number of entries before the page and the data list count of the current page, 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)
#Query list 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
#Execute sql or query list and submit
def execute(self, sql, *args):
args = self.turnArray(args)
self.out(sql, args)
conn = self.getConn()
cursor = conn.cursor()
#sql placeholder filling args can be tuple (1, 2) (dynamic parameter array) or list[1, 2] list(tuple) tuple(list)
res = cursor.execute(sql, args).fetchall()
conn.commit()
#self.close(conn)
return res
#Query column name list 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
#The query result is single 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
#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
####################################test
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) + "', 'birth1" + 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()