mysql_utils.py 2.7 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116
  1. #!/usr/bin/env python
  2. # -*- encoding: utf-8 -*-
  3. """
  4. Author renyupeng
  5. coding=utf-8
  6. @Time : 2023/2/7 下午
  7. @Site :
  8. @File : mysql_utils.py
  9. @Software: PyCharm
  10. @contact: renyupeng@c-top.com.cn
  11. @Tel 1501435553
  12. encoding=utf-8
  13. """
  14. import pymysql
  15. # 导入所有Mysql配置常量,请自行指定文件
  16. from constant.ConfConstant import ConfConstant
  17. class MysqlUtils(object):
  18. """
  19. mysql操作类,对mysql数据库进行增删改查
  20. """
  21. def __init__(self):
  22. config = dict(
  23. host=ConfConstant.TIDB_HOST,
  24. user=ConfConstant.TIDB_USER,
  25. password=ConfConstant.TIDB_PASSWORD,
  26. port=ConfConstant.TIDB_PORT,
  27. charset='utf8mb4', # 编码要加上,否则可能出现中文乱码问题
  28. use_unicode=False,
  29. )
  30. # Connect to the database
  31. self.connection = pymysql.connect(**config)
  32. self.connection.autocommit(True)
  33. self.cursor = self.connection.cursor()
  34. def QueryAll(self, sql):
  35. """
  36. 查询所有数据
  37. :param sql:
  38. :return:
  39. """
  40. # 数据库若断开即重连
  41. self.reConnect()
  42. self.cursor.execute(sql)
  43. return self.cursor.fetchall()
  44. def QueryMany(self, sql, n):
  45. """
  46. 查询某几条数据数据
  47. :param sql:
  48. :return:
  49. """
  50. # 数据库若断开即重连
  51. self.reConnect()
  52. self.cursor.execute(sql)
  53. return self.cursor.fetchmany(n)
  54. def QueryOne(self, sql):
  55. """
  56. 查询某几条数据数据
  57. :param sql:
  58. :return:
  59. """
  60. # 数据库若断开即重连
  61. self.reConnect()
  62. self.cursor.execute(sql)
  63. return self.cursor.fetchone()
  64. # return self.cursor.fetchone()
  65. def reConnect(self):
  66. """
  67. 重连机制
  68. :return:
  69. """
  70. try:
  71. self.connection.ping()
  72. except:
  73. self.connection()
  74. def Operate(self, sql, params=None, DML=True):
  75. """
  76. 数据库操作:增删改查
  77. DML: insert / update / delete
  78. DDL: CREATE TABLE/VIEW/INDEX/SYN/CLUSTER
  79. """
  80. try:
  81. # 数据库若断开即重连
  82. self.reConnect()
  83. with self.connection.cursor() as cursor:
  84. cursor.execute(sql, params)
  85. self.connection.commit()
  86. except Exception as e:
  87. if DML:
  88. # 涉及DML操作时,若抛异常需要回滚
  89. self.connection.rollback()
  90. print(e)
  91. def __del__(self):
  92. """
  93. MysqlConnection实例对象被释放时调用此方法,用于关闭cursor和connection连接
  94. """
  95. self.cursor.close()
  96. self.connection.close()