MysqlUtils.py 2.9 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111
  1. import pymysql
  2. from dbutils.pooled_db import PooledDB
  3. class MysqlUtils():
  4. # 连接池对象
  5. __pool = None
  6. def __init__(self,**config):
  7. self.host = config["host"]
  8. self.port = int(config["port"])
  9. self.user = config["user"]
  10. self.passwd = config["passwd"]
  11. self.database = None
  12. if "database" in config:
  13. self.database = config["database"]
  14. self.pool = self.get_conn()
  15. # self.pool = None
  16. def get_conn(self):
  17. if MysqlUtils.__pool is None:
  18. __pool = PooledDB(pymysql, mincached=1, maxcached=5, maxconnections=5,
  19. host=self.host, port=self.port, user=self.user, passwd=self.passwd,
  20. database=self.database,
  21. use_unicode=True, blocking=False, charset="utf8")
  22. self.pool = __pool
  23. return __pool
  24. def query(self,sql):
  25. con = None
  26. cur = None
  27. try:
  28. con = self.pool.connection()
  29. cur = con.cursor()
  30. count = cur.execute(sql)
  31. if count >= 0:
  32. result = cur.fetchall()
  33. result = [[j.decode() if isinstance(j, bytes) else j for j in i] for i in result]
  34. # print(result)
  35. else:
  36. result = False
  37. return result
  38. except Exception as e:
  39. print(e)
  40. finally:
  41. if cur:
  42. cur.close()
  43. if con:
  44. con.close()
  45. def update(self,sql):
  46. con = None
  47. cur = None
  48. try:
  49. con = self.pool.connection()
  50. cur = con.cursor()
  51. cur.execute(sql)
  52. con.commit()
  53. except Exception as e:
  54. con.rollback() # 事务回滚
  55. print(e)
  56. finally:
  57. if cur:
  58. cur.close()
  59. if con:
  60. con.close()
  61. def update_mult(self,sql1,sql2,sql3):
  62. con = None
  63. cur = None
  64. try:
  65. con = self.pool.connection()
  66. cur = con.cursor()
  67. cur.execute(sql1)
  68. cur.execute(sql2)
  69. cur.execute(sql3)
  70. con.commit()
  71. except Exception as e:
  72. con.rollback() # 事务回滚
  73. print(e)
  74. finally:
  75. if cur:
  76. cur.close()
  77. if con:
  78. con.close()
  79. def update_two(self,sql1,sql2):
  80. con = None
  81. cur = None
  82. try:
  83. con = self.pool.connection()
  84. cur = con.cursor()
  85. cur.execute(sql1)
  86. cur.execute(sql2)
  87. con.commit()
  88. except Exception as e:
  89. con.rollback() # 事务回滚
  90. print(e)
  91. finally:
  92. if cur:
  93. cur.close()
  94. if con:
  95. con.close()
  96. def close(self):
  97. if MysqlUtils.__pool:
  98. MysqlUtils.__pool.close()