您现在的位置是:首页 > 博客日记 > Python Python

使用数据库封装pymysql及事例 (python的简单入门)

2019-11-21 18:37:50 【Python】 人已围观

安装扩展类

  1. pip3.7 install pymysql

基于pymysql的封装使用mysql

  1. # -*- coding:utf-8 -*-
  2. import re
  3. import pymysql as mdb
  4. class MysqldbHelper(object):
  5. """ 操作mysql数据库,基本方法 """
  6. def __init__(self, host: object = "127.0.0.1", username: object = "root", password: object = "root",
  7. port: object = 3306, database: object = "test") -> object:
  8. self.debug = False
  9. self.host = host
  10. self.username = username
  11. self.password = password
  12. self.database = database
  13. self.port = port
  14. self.con = None
  15. self.cur = None
  16. try:
  17. self.con = mdb.connect(
  18. host=self.host,
  19. user=self.username,
  20. passwd=self.password,
  21. port=self.port,
  22. db=self.database
  23. )
  24. # 所有的查询,都在连接 con 的一个模块 cursor 上面运行的
  25. self.cur = self.con.cursor()
  26. except:
  27. raise Exception("DataBase connect error,please check the db config.")
  28. """ 关闭数据库连接 """
  29. def close(self):
  30. if not self.con:
  31. self.con.close()
  32. else:
  33. raise Exception("DataBase doesn't connect,close connectiong error;please check the db config.")
  34. """ 获取数据库的版本号 """
  35. def getVersion(self):
  36. self.cur.execute("SELECT VERSION()")
  37. return self.getOneData()
  38. def getOneData(self):
  39. # 取得上个查询的结果,是单个结果
  40. data = self.cur.fetchone()
  41. return data
  42. """ 创建数据库表
  43. args:
  44. tablename :表名字
  45. attrdict :属性键值对,{'book_name':'varchar(200) NOT NULL'...}
  46. constraint :主外键约束,PRIMARY KEY(`id`)
  47. :param tablename:
  48. :param attrdict:
  49. :param constraint:
  50. :return:
  51. """
  52. def creatTable(self, tablename, attrdict, constraint):
  53. # 表是否存在
  54. if self.isExistTable(tablename) == True:
  55. return True
  56. sql = ''
  57. sql_mid = '`id` bigint(11) NOT NULL AUTO_INCREMENT,'
  58. for attr, value in attrdict.items():
  59. sql_mid = sql_mid + '`' + attr + '`' + ' ' + value + ','
  60. sql = sql + 'CREATE TABLE IF NOT EXISTS %s (' % tablename
  61. sql = sql + sql_mid
  62. sql = sql + constraint
  63. sql = sql + ') ENGINE=InnoDB DEFAULT CHARSET=utf8'
  64. self.deBug('creatTable:' + sql)
  65. self.execute(sql)
  66. """ 创建数据库表
  67. args:
  68. tablename :表名字
  69. key :属性键
  70. value :属性值
  71. """
  72. def insert(self, tablename, params):
  73. keys = [] # 字段名
  74. placeholders = [] # 字段值的占位符
  75. args = [] # 字段值
  76. for key, value in params.items():
  77. keys.append(key)
  78. args.append(value)
  79. placeholders.append('%s')
  80. # 拼接sql语句
  81. sql = 'insert into %s' % tablename + '(`' + '`,`'.join(keys) + '`)' + ' values(' + ','.join(placeholders) + ')'
  82. self.deBug(' sql insert : ' + sql + str(args))
  83. insertId = self.execute(sql=sql, args=args)
  84. return insertId
  85. """ 插入多条数据
  86. args:
  87. tablename :表名字
  88. attrs :属性键
  89. values :属性值
  90. example:
  91. table='test_mysqldb'
  92. key = ["id" ,"name", "age"]
  93. value = [[101, "liuqiao", "25"], [102,"liuqiao1", "26"], [103 ,"liuqiao2", "27"], [104 ,"liuqiao3", "28"]]
  94. mydb.insertMany(table, key, value)
  95. """
  96. def insertMany(self, table, attrs, values):
  97. values_sql = ['%s' for v in attrs]
  98. attrs_sql = '(' + ','.join(attrs) + ')'
  99. values_sql = ' values(' + ','.join(values_sql) + ')'
  100. sql = 'insert into %s' % table
  101. sql = sql + attrs_sql + values_sql
  102. self.deBug('insertMany:' + sql)
  103. try:
  104. self.deBug(sql)
  105. for i in range(0, len(values), 20000):
  106. self.cur.executemany(sql, values[i:i + 20000])
  107. except mdb.Error as e:
  108. self.con.rollback()
  109. error = "insertMany executemany failed! ERROR (%s): %s" % (e.args[0], e.args[1])
  110. self.deBug(error)
  111. """ 查询数据
  112. args:
  113. tablename :表名字
  114. where :查询条件
  115. order :排序条件
  116. example:
  117. print mydb.select(table)
  118. print mydb.select(table, fields=["name"])
  119. print mydb.select(table, fields=["name", "age"])
  120. print mydb.select(table, fields=["age", "name"])
  121. """
  122. def findOne(self, tablename, where='', order='', fields='*'):
  123. consql = ' '
  124. if where != '':
  125. for k, v in where.items():
  126. consql = consql + k + '="' + str(v) + '" and'
  127. consql = consql + ' 1=1 '
  128. if fields == "*":
  129. sql = 'select * from %s where ' % tablename
  130. else:
  131. if isinstance(fields, list):
  132. fields = ",".join(fields)
  133. sql = 'select %s from %s where ' % (fields, tablename)
  134. else:
  135. raise Exception("fields input error, please input list fields.")
  136. sql = sql + consql + order
  137. self.deBug('select:' + sql)
  138. return self.executeSql(sql, findOne=True)
  139. def findAll(self, tablename, where='', order='', fields='*'):
  140. consql = ' '
  141. if where != '':
  142. for k, v in where.items():
  143. consql = consql + k + '="' + str(v) + '" and'
  144. consql = consql + ' 1=1 '
  145. if(isinstance(fields,str)):
  146. sql = 'select * from %s where ' % tablename
  147. elif isinstance(fields, list):
  148. fields = ",".join(fields)
  149. sql = 'select %s from %s where ' % (fields, tablename)
  150. else:
  151. raise Exception("fields input error, please input list fields.")
  152. # 获取order
  153. order = order == '' and ' ' or ' order by ' + order
  154. sql = sql + consql + order
  155. self.deBug('select:' + sql)
  156. return self.executeSql(sql)
  157. """更新数据
  158. args:
  159. tablename :表名字
  160. attrs_dict :更新属性键值对字典
  161. where :更新条件字典
  162. example:
  163. params = {"name" : "caixinglong", "age" : "38"}
  164. where = {"name" : "liuqiao", "age" : "18"}
  165. mydb.update(table, params, where)
  166. """
  167. def update(self, tablename, attrs_dict, where):
  168. # 拼接修改属性
  169. attrs_list = []
  170. for tmpkey, tmpvalue in attrs_dict.items():
  171. attrs_list.append("`" + tmpkey + "`" + "=" + "\'" + tmpvalue + "\'")
  172. attrs_sql = ",".join(attrs_list)
  173. # 拼接where语句
  174. consql = ' '
  175. if where != '':
  176. for k, v in where.items():
  177. if isinstance(v, (str, int)):
  178. v = "\'" + str(v) + "\'"
  179. consql = consql + "`" + tablename + "`." + "`" + k + "`" + '=' + v + ' and '
  180. consql = consql + ' 1=1 '
  181. # 拼接sql
  182. sql = "UPDATE %s SET %s where%s" % (tablename, attrs_sql, consql)
  183. self.deBug(sql)
  184. return self.execute(sql)
  185. def delete(self, tablename, where):
  186. """删除数据
  187. args:
  188. tablename :表名字
  189. where :删除条件字典
  190. example:
  191. params = {"name" : "caixinglong", "age" : "38"}
  192. mydb.delete(table, params)
  193. """
  194. consql = ' '
  195. if where != '':
  196. for k, v in where.items():
  197. if isinstance(v, str):
  198. v = "\'" + v + "\'"
  199. consql = consql + tablename + "." + k + '=' + v + ' and '
  200. consql = consql + ' 1=1 '
  201. sql = "DELETE FROM %s where%s" % (tablename, consql)
  202. self.deBug(sql)
  203. try:
  204. result = {
  205. 'rows': self.cur.execute(sql),
  206. 'data': self.cur.fetchone(),
  207. }
  208. return result
  209. except mdb.Error as e:
  210. error = "MySQL execute failed! ERROR (%s): %s" % (e.args[0], e.args[1])
  211. self.deBug(error)
  212. def dropTable(self, tablename):
  213. """删除数据库表
  214. args:
  215. tablename :表名字
  216. """
  217. sql = "DROP TABLE %s" % tablename
  218. self.execute(sql)
  219. def deleteTable(self, tablename):
  220. """清空数据库表
  221. args:
  222. tablename :表名字
  223. """
  224. sql = "DELETE FROM %s" % tablename
  225. self.execute(sql)
  226. """判断数据表是否存在
  227. args:
  228. tablename :表名字
  229. Return:
  230. 存在返回True,不存在返回False
  231. """
  232. def isExistTable(self, tablename):
  233. sql = "show table status like '%s'" % tablename
  234. result = self.execute(sql)
  235. if result:
  236. print('table : ' + tablename + ' 已存在')
  237. return True
  238. else:
  239. print('table ' + tablename + ' 不存在')
  240. return False
  241. """ 执行sql语句,针对读操作返回结果集
  242. args:
  243. sql :sql语句
  244. """
  245. def executeSql(self, sql: object = '', findOne: object = False) -> object:
  246. try:
  247. result = {
  248. 'rows': self.cur.execute(sql),
  249. 'data': findOne and self.cur.fetchone() or self.cur.fetchall(),
  250. }
  251. return result
  252. except mdb.Error as e:
  253. error = "MySQL execute failed! ERROR (%s): %s" % (e.args[0], e.args[1])
  254. self.deBug(error)
  255. """ 执行数据库sql语句,针对更新,删除,事务等操作失败时回滚
  256. """
  257. def execute(self, sql='', args=[]):
  258. try:
  259. res = self.cur.execute(sql, args)
  260. insertId = self.con.insert_id()
  261. return insertId and insertId or res
  262. except mdb.Error as e:
  263. self.rollBack()
  264. error = 'MySQL execute failed! ERROR (%s): %s' % (e.args[0], e.args[1])
  265. self.deBug("error:" + error)
  266. return error
  267. # 事务提交
  268. def commit(self):
  269. return self.con.commit()
  270. # 事务回滚
  271. def rollBack(self):
  272. return self.con.rollback()
  273. # debug
  274. def deBug(self, message=''):
  275. if self.debug != False:
  276. print(message)

使用事例

  1. if __name__ == "__main__":
  2. mydb = mysql.MysqldbHelper(host="127.0.0.1", username="root", password="root", database='test')
  3. # 获取mysql版本号
  4. #print( mydb.getVersion())
  5. # 开启debug模式
  6. # mydb.debug = True
  7. table='test_mysqldb'
  8. # 创建mysql表
  9. # attrs={'name':'varchar(200) DEFAULT NULL','age':'int(11) DEFAULT NULL'}
  10. # constraint='PRIMARY KEY(`id`)'
  11. # print( mydb.creatTable(table, attrs, constraint))
  12. # 添加一条数据
  13. # params = {"name" : "ceshi", "age" : "1"}
  14. # mydb.insert('test_mysqldb', params)
  15. # 查询数据
  16. # print( mydb.select(table))
  17. # print( mydb.select(table, fields=["name", "age"]))
  18. # print( mydb.select(table, fields=["age", "name"]))
  19. # 批量添加
  20. # key = ["id" ,"name", "age"]
  21. # value = [[101, "liuqiao", "25"], [102,"liuqiao1", "26"], [103 ,"liuqiao2", "27"], [104 ,"liuqiao3", "28"]]
  22. # mydb.insertMany(table, key, value)
  23. # 删除
  24. # mydb.delete(table, params)
  25. # cond_dict = {"name" : "liuqiao", "age" : "18"}
  26. # 修改
  27. # mydb.update(table, params, cond_dict)
  28. # mydb.deleteTable(table)
  29. # mydb.dropTable(table)
  30. #不管使用什么操作一定要记得commit,否则数据库不会发生变化
  31. mydb.commit()


关注TinyMeng博客,更多精彩分享,敬请期待!
 

很赞哦! ()