Guest User

Untitled

a guest
Sep 6th, 2018
107
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 4.63 KB | None | 0 0
  1. __author__ = 'pavan.tummalapalli'
  2.  
  3.  
  4. import mysql.connector.pooling
  5. import logging
  6.  
  7.  
  8. class MySQLPool:
  9. """
  10. create a pool, when connect to mysql, which will decrease the time spent in
  11. request connection, create connection and close connection.
  12. """
  13. def __init__(self, **db):
  14. self.logger = logging.getLogger(__name__)
  15. res = dict()
  16. self._host = db.get('host')
  17. self._port = db.get('port')
  18. self._user = db.get('user')
  19. self._password = db.get('password')
  20. self._database = db.get('database')
  21.  
  22. res["host"] = self._host
  23. res["port"] = self._port
  24. res["user"] = self._user
  25. res["password"] = self._password
  26. res["database"] = self._database
  27. self.dbconfig = res
  28. self.pool = self.create_pool(pool_name=db.get('pool_name'), pool_size=db.get('pool_size'))
  29.  
  30. def create_pool(self, pool_name=None, pool_size=2):
  31. """
  32. Create a connection pool, after created, the request of connecting
  33. MySQL could get a connection from this pool instead of request to
  34. create a connection.
  35. :param pool_name: the name of pool, default is "mypool"
  36. :param pool_size: the size of pool, default is 3
  37. :return: connection pool
  38. """
  39. try:
  40. pool = mysql.connector.pooling.MySQLConnectionPool(
  41. pool_name=pool_name,
  42. pool_size=pool_size,
  43. pool_reset_session=True,
  44. **self.dbconfig)
  45. except Exception as e:
  46. # logger.exception calls error(message, exc_info=1) internally
  47. self.logger.exception(" unable to create connection pooling")
  48. raise e
  49. return pool
  50.  
  51. def close(self, conn, cursor):
  52. """
  53. A method used to close connection of mysql.
  54. :param conn: connection object
  55. :param cursor: cursor object
  56. :return: None
  57. """
  58. cursor.close()
  59. conn.close()
  60.  
  61. def execute(self, sql, args=None, commit=False):
  62. """
  63. Execute a sql, it could be with args and with out args. The usage is
  64. similar with execute() function in module pymysql.
  65.  
  66. Insert example:
  67. ::
  68.  
  69. tomorrow = datetime.now().date() + timedelta(days=1)
  70.  
  71. add_employee = ("INSERT INTO employees "
  72. "(first_name, last_name, hire_date, gender, birth_date) "
  73. "VALUES (%s, %s, %s, %s, %s)")
  74.  
  75. add_salary = ("INSERT INTO salaries "
  76. "(emp_no, salary, from_date, to_date) "
  77. "VALUES (%(emp_no)s, %(salary)s, %(from_date)s, %(to_date)s)")
  78.  
  79. data_employee = ('Geert', 'Vanderkelen', tomorrow, 'M', date(1977, 6, 14))
  80. execute(add_employee, data_employee)
  81.  
  82. :param sql: sql clause
  83. :param args: args need by sql clause
  84. :param commit: whether to commit
  85. :return: if commit, return None, else, return result
  86. """
  87. # get connection form connection pool instead of create one.
  88. conn = self.pool.get_connection()
  89. cursor = conn.cursor()
  90. if args:
  91. cursor.execute(sql, args)
  92. else:
  93. cursor.execute(sql)
  94. if commit is True:
  95. conn.commit()
  96. self.close(conn, cursor)
  97. return None
  98. else:
  99. try:
  100. res = cursor.fetchall()
  101. except Exception as e:
  102. res = None
  103. finally:
  104. self.close(conn, cursor)
  105. return res
  106.  
  107. def executemany(self, sql, args, commit=False):
  108. """
  109. Example of mysql executemany insert:
  110. ::
  111. sqlquery = ("INSERT INTO employees "
  112. "(first_name, last_name, hire_date, gender, birth_date) "
  113. "VALUES (%s, %s, %s, %s, %s)")
  114.  
  115. data = [
  116. ('Jane', date(2005, 2, 12)),
  117. ('Joe', date(2006, 5, 23)),
  118. ('John', date(2010, 10, 3)),
  119. ]
  120.  
  121. obj.executemany(sqlquery, data)
  122.  
  123. Execute with many args. Similar with executemany() function in pymysql.
  124. args should be a sequence.
  125. :param sql: sql clause
  126. :param args: args
  127. :param commit: commit or not.
  128. :return: if commit, return None, else, return result
  129. """
  130. # get connection form connection pool instead of create one.
  131. conn = self.pool.get_connection()
  132. cursor = conn.cursor()
  133. cursor.executemany(sql, args)
  134. if commit is True:
  135. conn.commit()
  136. self.close(conn, cursor)
  137. return None
  138. else:
  139. res = cursor.fetchall()
  140. self.close(conn, cursor)
  141. return res
Add Comment
Please, Sign In to add comment