1 import json, os, sys, time 2 3 import pymysql 4 from DBUtils import PooledDB 5 6
class
BaseDao(object): 7
""
" 8 简便的数据库操作基类 9 "
""
10 __config = {} # 数据库连接配置 11 __conn = None # 数据库连接 12 __cursor = None # 数据库游标 13 __database = None # 用于临时村塾查询数据库 14 __tableName = None # 用于临时存储查询表名 15 __fields = [] # 用于临时存储查询表的字段列表 16 __primaryKey_dict = {} # 用于存储配置中的数据库中所有表的主键 17 18 def __init__(self, creator=pymysql, host=
"localhost"
, user=None, password=
""
, database=None, port=3306, charset=
"utf8"
): 19
if
host is None: 20 raise Exception(
"Parameter [host] is None."
) 21
if
user is None: 22 raise Exception(
"Parameter [user] is None."
) 23
if
password is None: 24 raise Exception(
"Parameter [password] is None."
) 25
if
database is None: 26 raise Exception(
"Parameter [database] is None."
) 27
if
port is None: 28 raise Exception(
"Parameter [port] is None."
) 29 self.__config = dict({ 30
"creator"
: creator,
"charset"
:charset, 31
"host"
:host,
"port"
:port,
32
"user"
:user,
"password"
:password,
"database"
:database 33 }) 34 self.__conn = PooledDB.connect(**self.__config) 35 self.__cursor = self.__conn.cursor() 36 self.__database = self.__config[
"database"
] 37 self.__init_primaryKey() 38
print
(get_time(),
"数据库连接初始化成功。"
) 39 40 def __del__(self): 41
'重写类被清除时调用的方法'
42
if
self.__cursor: 43 self.__cursor.close() 44
print
(get_time(),
"游标关闭"
) 45
if
self.__conn: 46 self.__conn.close() 47
print
(get_time(),
"连接关闭"
) 48 49 def select_one(self, tableName=None, filters={}): 50
''
' 51 查询单个对象 52 @tableName 表名 53 @filters 过滤条件 54 @return 返回字典集合,集合中以表字段作为 key,字段值作为 value 55 '
''
56 self.__check_params(tableName) 57 sql = self.__query_util(filters) 58 self.__cursor.execute(sql) 59 result = self.__cursor.fetchone() 60
return
self.__parse_result(result) 61 62 def select_pk(self, tableName=None, primaryKey=None): 63
''
' 64 按主键查询 65 @tableName 表名 66 @primaryKey 主键值 67 '
''
68 self.__check_params(tableName) 69 filters = {} 70 filters.setdefault(str(self.__primaryKey_dict[tableName]), primaryKey) 71 sql = self.__query_util(filters) 72 self.__cursor.execute(sql) 73 result = self.__cursor.fetchone() 74
return
self.__parse_result(result) 75 76 def select_all(self, tableName=None, filters={}): 77
''
' 78 查询所有 79 @tableName 表名 80 @filters 过滤条件 81 @return 返回字典集合,集合中以表字段作为 key,字段值作为 value 82 '
''
83 self.__check_params(tableName) 84 sql = self.__query_util(filters) 85 self.__cursor.execute(sql) 86 results = self.__cursor.fetchall() 87
return
self.__parse_results(results) 88 89 def
count
(self, tableName=None): 90
''
' 91 统计记录数 92 '
''
93 self.__check_params(tableName) 94 sql =
"SELECT count(*) FROM %s"
%(self.__tableName) 95 self.__cursor.execute(sql) 96 result = self.__cursor.fetchone() 97
return
result[0] 98 99 def select_page(self, tableName=None, pageNum=1, limit=10, filters={}):100
''
'101 分页查询102 @tableName 表名103 @return 返回字典集合,集合中以表字段作为 key,字段值作为 value104 '
''
105 self.__check_params(tableName)106 totalCount = self.
count
()107
if
totalCount / limit == 0 :108 totalPage = totalCount / limit109
else
:110 totalPage = totalCount
236
"password"
:
"root"
,237
"database"
:
"test"
,
238 #
"port"
: 3306,239 #
"charset"
:
'utf8'
240 }241 base = BaseDao(**config)242 ########################################################################243 # user = base.select_one(
"user"
)244 #
print
(user)245 ########################################################################246 # users = base.select_all(
"user"
)247 #
print
(users)248 ########################################################################249 # filter1 = {250 #
"sex"
:0,251 #
"_in_id"
:
"1,2,3,4,5"
,252 #
"_like_name"
:
"zhang"
,253 #
"_ne_name"
:
"wangwu"
254 # }255 # user_filters = base.select_all(tableName=
"user"
, filters=filter1)256 #
print
(user_filters)257 ########################################################################258 # menu = base.select_one(tableName=
"menu"
)259 #
print
(menu)260 ########################################################################261 # user_pk = base.select_pk(
"user"
, 2)262 #
print
(user_pk)263 ########################################################################264 # filter2 = {265 #
"_in_id"
:
"1,2,3,4"
,266 #
"_like_name"
:
"test"
267 # }268 # user_limit = base.select_page(
"user"
, 2, 10, filter2) #未实现269 #
print
(user_limit)270 ########################################################################