DataBaseUtils.py 3.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114
  1. """
  2. @desc 数据库连接
  3. @auth chenkai
  4. @date 2020/11/19
  5. """
  6. from model.DataBaseOperation import MysqlOperation
  7. from model.log import logger
  8. import yaml
  9. import os
  10. from clickhouse_driver.client import Client
  11. import pandas as pd
  12. log = logger()
  13. class MysqlUtils:
  14. _quchen_text = None
  15. def __init__(self):
  16. p_path =os.path.abspath(os.path.join(os.path.dirname(__file__), os.pardir))
  17. path = os.path.join(p_path,"config", "db_config.yaml")
  18. f = open(path, encoding="utf-8")
  19. self.config = yaml.load(f.read(), Loader=yaml.FullLoader)
  20. @property
  21. def quchen_text(self):
  22. conf = self.config['quchen_text']
  23. self._quchen_text = MysqlOperation(host=conf['host'],
  24. user=conf['user'],
  25. passwd=conf['passwd'],
  26. db=conf['db'])
  27. return self._quchen_text
  28. def find_db(self, db):
  29. if db == "quchen_text":
  30. self._quchen_text = self._quchen_text
  31. return self._quchen_text
  32. else:
  33. log.debug("输入数据库有误")
  34. def close(self):
  35. if self._quchen_text:
  36. self._quchen_text.cursor.close()
  37. self._quchen_text.conn.close()
  38. class CkUtils:
  39. def __init__(self):
  40. self.client = Client(host='cc-bp1h3yc7o3g3o7k64o.ads.aliyuncs.com',
  41. user='qucheng_ck',
  42. password='Qc123456',
  43. port='3306',
  44. send_receive_timeout=5)
  45. def execute(self, sql):
  46. return self.client.execute(sql)
  47. def getData_pd(self, sql, col):
  48. data = self.execute(sql)
  49. df = pd.DataFrame(data, columns=col)
  50. return df
  51. def getColumns(self, table, is_list=False):
  52. """默认返回列表"""
  53. data = self.execute("desc " + table)
  54. li = []
  55. str = ''
  56. for i in data:
  57. li.append(i[0])
  58. str += i[0] + ','
  59. if is_list:
  60. return li
  61. else:
  62. return str[:-1]
  63. def insertMany(self,table,col,data):
  64. """
  65. :param table: 表名 srt
  66. :param col: 字段名 srt eg: ”a,b,c“
  67. :param data: tuple/list
  68. :return:
  69. """
  70. max=200
  71. sql="insert into {} ({}) values ".format(table,col)
  72. if len(data) == 0:
  73. log.debug("data.len==0")
  74. return
  75. if len(data) <= max:
  76. sql = sql+str(data)[1:-1]
  77. # log.info(sql)
  78. # log.info("insert {} rows".format(len(data)))
  79. self.execute(sql)
  80. return
  81. else:
  82. sql2=sql+str(data[:max])[1:-1]
  83. # log.info(sql2)
  84. self.execute(sql2)
  85. # log.info("insert {} rows".format(max))
  86. self.insertMany(table,col,data[max:])
  87. if __name__ == '__main__':
  88. p_path = os.path.dirname(os.path.abspath(os.path.join(os.path.dirname(__file__), os.pardir)))
  89. print(os.path.abspath(os.path.join(os.path.dirname(__file__), os.pardir)))