DBUtils 和 pymysql 结合的简单封装
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了DBUtils 和 pymysql 结合的简单封装，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含16417字，纯文字阅读大概需要24分钟。
内容图文
import sys
2 import re
3 import pymysql
4 import xml.dom.minidom
5
6 from xml.dom.minidom import parse
7
8 class ConfigurationParser(object):
9 """
10 解析xml
11 - @return configDict = {"jdbcConnectionDictList":jdbcConnectionDictList,"tableList":tableList}
12 """
13 def __init__(self, configFilePath=None):
14 if configFilePath:
15 self.__configFilePath = configFilePath
16 else:
17 self.__configFilePath = sys.path[0] + "/config/config.xml"
18 pass
19
20 def parseConfiguration(self):
21 """
22 解析xml，返回jdbc配置信息以及需要生成python对象的表集合
23 """
24 # 解析xml文件,获取Document对象
25 DOMTree = xml.dom.minidom.parse(self.__configFilePath)
26 # 获取 generatorConfiguration 节点的NodeList对象
27 configDOM = DOMTree.getElementsByTagName("generatorConfiguration")[0]
28
29 # 获取 jdbcConnection 节点的 property 节点集合
30 jdbcConnectionPropertyList = configDOM.getElementsByTagName("jdbcConnection")[0].getElementsByTagName("property")
31 # 循环 jdbcConnection 节点的 property 节点集合，获取属性名称和属性值
32 jdbcConnectionDict = {}
33 for property in jdbcConnectionPropertyList:
34 name = property.getAttributeNode("name").nodeValue.strip().lower()
35 if property.hasAttribute("value"):
36 value = property.getAttributeNode("value").nodeValue
37 if re.match("[0-9]",value) and name != "password" and name != "host":
38 value = int(value)
39 else:
40 value = property.childNodes[0].data
41 if re.match("[0-9]",value) and name != "password" and name != "host":
42 value = int(value)
43 if name == "charset":
44 if re.match("utf-8|UTF8", value, re.I):
45 continue
46 elif name == "port":
47 value = int(value)
48 elif name == "creator":
49 if value == "pymysql":
50 value = pymysql
51 jdbcConnectionDict[name] = value
52 # print(jdbcConnectionDict)
53 return jdbcConnectionDict
54
55 if __name__ == "__main__":
56 print(ConfigurationParser().parseConfiguration())
config.xml
1 <?xml version="1.0" encoding="utf-8"?> 2 <generatorConfiguration> 3 <jdbcConnection> 4 <property name="creator">pymysql</property> 5 <property name="host">127.0.0.1</property> 6 <property name="database">rcddup</property> 7 <property name="port">3306</property> 8 <property name="user">root</property> 9 <property name="password">root</property> 10 <property name="charset">Utf-8</property> 11 <property name="mincached">0</property> 12 <property name="maxcached">10</property> 13 <property name="maxshared">0</property> 14 <property name="maxconnections">20</property> 15 </jdbcConnection> 16 </generatorConfiguration>
通过调用Python内置的 xml.dom.minidom 对 xml 文件进行解析，获取xml内容。
2.BaseDao
BaseDao是在 DBUtils 的基础上对 pymysql 操作数据库进行了一些简单的封装。
其中 queryUtil 用于拼接SQL语句，log4py用于控制台输出信息，page 分页对象。
1 import pymysql 2 import time 3 import json 4 5 from DBUtils.PooledDB import PooledDB 6 from configParser import ConfigurationParser 7 from queryUtil import QueryUtil 8 from log4py import Logger 9 from page import Page 10 11 12 global PRIMARY_KEY_DICT_LIST 13 PRIMARY_KEY_DICT_LIST = [] 14 15 class BaseDao(object): 16 """ 17 Python 操作数据库基类方法 18 - @Author RuanCheng 19 - @UpdateDate 2017/5/17 20 """ 21 __logger = None 22 __parser = None # 获取 xml 文件信息对象 23 __poolConfigDict = None # 从 xml 中获取的数据库连接信息的字典对象 24 __pool = None # 数据库连接池 25 __obj = None # 实体类 26 __className = None # 实体类类名 27 __tableName = None # 实体类对应的数据库名 28 __primaryKeyDict = {} # 数据库表的主键字典对象 29 __columnList = [] 30 31 def __init__(self, obj=None): 32 """ 33 初始化方法： 34 - 1.初始化配置信息 35 - 2.初始化 className 36 - 3.初始化数据库表的主键 37 """ 38 if not obj: 39 raise Exception("BaseDao is missing a required parameter --> obj(class object).\nFor example [super().__init__(User)].") 40 else: 41 self.__logger = Logger(self.__class__) # 初始化日志对象 42 self.__logger.start() # 开启日志 43 if not self.__parser: # 解析 xml 44 self.__parser = ConfigurationParser() 45 self.__poolConfigDict = self.__parser.parseConfiguration() 46 self.__pool = PooledDB(**self.__poolConfigDict) 47 # 初始化参数 48 if (self.__obj == None) or ( self.__obj != obj): 49 global PRIMARY_KEY_DICT_LIST 50 if (not PRIMARY_KEY_DICT_LIST) or (PRIMARY_KEY_DICT_LIST.count == 0): 51 self.__init_primary_key_dict_list() # 初始化主键字典列表 52 self.__init_params(obj) # 初始化参数 53 self.__init_columns() # 初始化字段列表 54 self.__logger.end() # 结束日志 55 pass 56 ################################################# 外部调用方法 ################################################# 57 def selectAll(self): 58 """ 59 查询所有 60 """ 61 sql = QueryUtil.queryAll(self.__tableName, self.__columnList) 62 return self.__executeQuery(sql) 63 64 def selectByPrimaryKey(self, value): 65 """ 66 按主键查询 67 - @Param: value 主键 68 """ 69 if (not value) or (value == ""): 70 raise Exception("selectByPrimaryKey() is missing a required paramter ‘value‘.") 71 sql = QueryUtil.queryByPrimaryKey(self.__primaryKeyDict, value, self.__columnList) 72 return self.__executeQuery(sql) 73 74 def selectCount(self): 75 """ 76 查询总记录数 77 """ 78 sql = QueryUtil.queryCount(self.__tableName); 79 return self.__execute(sql)[0][0] 80 81 def selectAllByPage(self, page=None): 82 """ 83 分页查询 84 """ 85 if (not page) or (not isinstance(page,Page)): 86 raise Exception("Paramter [page] is not correct. Parameter [page] must a Page object instance. ") 87 sql = QueryUtil.queryAllByPage(self.__tableName, self.__columnList, page) 88 return self.__executeQuery(sql, logEnable=True) 89 90 def insert(self, obj): 91 """ 92 新增 93 - @Param: obj 实体对象 94 """ 95 if (not obj) or (obj == ""): 96 raise Exception("insert() is missing a required paramter ‘obj‘.") 97 sql = QueryUtil.queryInsert(self.__primaryKeyDict, json.loads(str(obj))) 98 return self.__executeUpdate(sql) 99 100 def delete(self, obj=None): 101 """ 102 根据实体删除 103 - @Param: obj 实体对象 104 """ 105 if (not obj) or (obj == ""): 106 raise Exception("delete() is missing a required paramter ‘obj‘.") 107 sql = QueryUtil.queryDelete(self.__primaryKeyDict, json.loads(str(obj))) 108 return self.__executeUpdate(sql) 109 110 def deleteByPrimaryKey(self, value=None): 111 """ 112 根据主键删除 113 - @Param: value 主键 114 """ 115 if (not value) or (value == ""): 116 raise Exception("deleteByPrimaryKey() is missing a required paramter ‘value‘.") 117 sql = QueryUtil.queryDeleteByPrimaryKey(self.__primaryKeyDict, value) 118 return self.__executeUpdate(sql) 119 120 def updateByPrimaryKey(self, obj=None): 121 """ 122 根据主键更新 123 - @Param: obj 实体对象 124 """ 125 if (not obj) or (obj == ""): 126 raise Exception("updateByPrimaryKey() is missing a required paramter ‘obj‘.") 127 sql = QueryUtil.queryUpdateByPrimaryKey(self.__primaryKeyDict, json.loads(str(obj))) 128 return self.__executeUpdate(sql) 129 130 ################################################# 内部调用方法 ################################################# 131 def __execute(self, sql="", logEnable=True): 132 """ 133 执行 SQL 语句(用于内部初始化参数使用)： 134 - @Param: sql 执行sql 135 - @Param: logEnable 是否开启输出日志 136 - @return 查询结果 137 """ 138 if not sql: 139 raise Exception("Execute method is missing a required parameter --> sql.") 140 try: 141 self.__logger.outSQL(sql, enable=logEnable) 142 conn = self.__pool.connection() 143 cur = conn.cursor() 144 cur.execute(sql) 145 result = cur.fetchall() 146 resultList = [] 147 for r in result: 148 resultList.append(r) 149 return resultList 150 except Exception as e: 151 conn.rollback() 152 raise Exception(e) 153 finally: 154 cur.close() 155 conn.close() 156 pass 157 158 def __executeQuery(self, sql="", logEnable=True): 159 """ 160 执行查询 SQL 语句： 161 - @Param: sql 执行sql 162 - @Param: logEnable 是否开启输出日志 163 - @return 查询结果 164 """ 165 if not sql: 166 raise Exception("Execute method is missing a required parameter --> sql.") 167 try: 168 self.__logger.outSQL(sql, enable=logEnable) 169 conn = self.__pool.connection() 170 cur = conn.cursor() 171 cur.execute(sql) 172 resultList = list(cur.fetchall()) 173 objList = [] 174 for result in resultList: 175 i = 0 176 obj = self.__obj() 177 for col in self.__columnList: 178 obj.__setattr__(col, result[i]) 179 objList.append(obj) 180 if not objList: 181 return None 182 elif objList and objList.__len__ == 1: 183 return objList[0] 184 else: 185 return objList 186 except Exception as e: 187 conn.rollback() 188 raise Exception(e) 189 finally: 190 cur.close() 191 conn.close() 192 pass 193 194 def __executeUpdate(self, sql=None, logEnable=True): 195 """ 196 执行修改 SQL 语句： 197 - @Param: sql 执行sql 198 - @Param: logEnable 是否开启输出日志 199 - @return 影响行数 200 """ 201 try: 202 self.__logger.outSQL(sql, enable=logEnable) 203 conn = self.__pool.connection() 204 cur = conn.cursor() 205 return cur.execute(sql) 206 pass 207 except Exception as e: 208 conn.rollback() 209 raise Exception(e) 210 pass 211 finally: 212 conn.commit() 213 cur.close() 214 conn.close() 215 pass 216 217 def __init_params(self, obj): 218 """ 219 初始化参数 220 - @Param：obj class 对象 221 """ 222 self.__obj = obj 223 self.__className = obj.__name__ 224 for i in PRIMARY_KEY_DICT_LIST: 225 if i.get("className") == self.__className: 226 self.__primaryKeyDict = i 227 self.__className = i["className"] 228 self.__tableName = i["tableName"] 229 break 230 231 def __init_primary_key_dict_list(self): 232 """ 233 初始化数据库主键集合： 234 - pk_dict = {"className": {"tableName":tableName,"primaryKey":primaryKey,"auto_increment":auto_increment}} 235 """ 236 global PRIMARY_KEY_DICT_LIST 237 sql = """ 238 SELECT 239 t.TABLE_NAME, 240 c.COLUMN_NAME, 241 c.ORDINAL_POSITION 242 FROM 243 INFORMATION_SCHEMA.TABLE_CONSTRAINTS as t, 244 INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS c 245 WHERE t.TABLE_NAME = c.TABLE_NAME 246 AND t.TABLE_SCHEMA = "%s" 247 AND c.CONSTRAINT_SCHEMA = "%s" 248 """%(self.__poolConfigDict.get("database"),self.__poolConfigDict.get("database")) 249 resultList = self.__execute(sql, logEnable=False) 250 for result in resultList: 251 pk_dict = dict() 252 pk_dict["tableName"] = result[0] 253 pk_dict["primaryKey"] = result[1] 254 pk_dict["ordinalPosition"] = result[2] 255 pk_dict["className"] = self.__convertToClassName(result[0]) 256 PRIMARY_KEY_DICT_LIST.append(pk_dict) 257 self.__logger.outMsg("initPrimaryKey is done.") 258 259 def __init_columns(self): 260 """ 261 初始化表字段 262 """ 263 sql = "SELECT column_name FROM Information_schema.columns WHERE table_Name = ‘%s‘"%(self.__tableName) 264 resultList = self.__execute(sql, logEnable=False) 265 for result in resultList: 266 self.__columnList.append(result) 267 self.__logger.outMsg("init_columns is done.") 268 # print(self.__columnList) 269 pass 270 271 def __convertToClassName(self, tableName): 272 """ 273 表名转换方法（配置自己特定的数据库表明前缀）： 274 - @Param: tableName 表名 275 - @return 转换后的类名 276 """ 277 result = None 278 if tableName.startswith("t_md_"): 279 result = tableName.replace("t_md_", "").replace("_","").lower() 280 elif tableName.startswith("t_ac_"): 281 result = tableName.replace("t_ac_","").replace("_","").lower() 282 elif tableName.startswith("t_"): 283 result = tableName.replace("t_","").replace("_","").lower() 284 else: 285 result = tableName 286 return result.capitalize()
3.简单应用 UserDao
创建以个 UserDao，继承BaseDao之后调用父类初始化方法，传递一个 User 对象给父类，我们就可以很方便的对 User 进行CRUD了。
1 import random 2 import math 3 4 from baseDao import BaseDao 5 from user import User 6 from page import Page 7 8 9 class UserDao(BaseDao): 10 11 def __init__(self): 12 super().__init__(User) 13 pass 14 15 userDao = UserDao() 16 ######################################## CRUD 17 18 # print(userDao.selectAll()) 19 # user = userDao.selectByPrimaryKey(1) 20 21 # print(userDao.insert(user)) 22 23 # print(userDao.delete(user)) 24 # print(userDao.deleteByPrimaryKey(4)) 25 26 # user = userDao.selectByPrimaryKey(1) 27 # print(userDao.updateByPrimaryKey()) 28 # print(userDao.update()) 29 30 ######################################## 根据主键更新 31 32 # strList = list("赵倩顺利王五张三赵丽历史李四八哥大哈萨克龙卷风很快乐节哀顺变风山东矿机感觉到付款了合法更不能") 33 # for index in range(1000): 34 # user = User() 35 # user.set_id(index+1) 36 # name = "" 37 # for i in range(random.randint(3,8)): 38 # r = random.randint(0, len(strList)-1) 39 # name += strList[r] 40 # user.set_name(name) 41 # user.set_status(1) 42 # i += 1 43 # userDao.updateByPrimaryKey(user) 44 45 ######################################## 更新 46 47 # user = User() 48 # user.set_id(2) 49 # user.set_name("测试更新") 50 # userDao.updateByPrimaryKey(user) 51 52 ######################################## 分页查询 53 54 # page = Page() 55 # pageNum = 1 56 # limit = 10 57 # page.set_page(pageNum) 58 # page.set_limit(limit) 59</s 本文系统来源：http://www.cnblogs.com/rcddup/p/6881944.html
内容总结
以上是互联网集市为您收集整理的DBUtils 和 pymysql 结合的简单封装全部内容，希望文章能够帮你解决DBUtils 和 pymysql 结合的简单封装所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。
来源：【匿名】