clickhouse基础操作类

最新推荐文章于 2024-08-15 10:10:16 发布

风雨lei电

最新推荐文章于 2024-08-15 10:10:16 发布

阅读量318

点赞数

分类专栏：数据库文章标签：数据库 python

本文链接：https://blog.csdn.net/kongxianglei0302/article/details/106251152

版权

数据库专栏收录该内容

2 篇文章 0 订阅

订阅专栏

import logging
import configparser
import os
from clickhouse_driver import Client
import traceback

cfg = configparser.ConfigParser()
cfg.read('cfg.cfg')
DB = "database"
user = cfg.get(DB, 'user')
host = cfg.get(DB, 'host')
DBName = cfg.get(DB, 'dbName')
TABLEName = cfg.get(DB, 'tableName')

path = os.path.join(os.getcwd(), 'database.log')
log_format = '%(asctime)s[%(levelname)s]: %(message)s'
logging.basicConfig(filename=path, level=logging.WARNING, format=log_format)
logger = logging.getLogger()


class DataBase(object):
    def __init__(self, dbname=None, table_name=None):
        self._logger = logger
        self._host = host
        self._user = user
        if dbname is None:
            self._db_name = DBName
        else:
            self._db_name = dbname
        if table_name is None:
            self._table_name = TABLEName
        else:
            self._table_name = table_name
        self.client = Client(host=self._host, port=9000, user=self._user, password='',
                            database=self._db_name, send_receive_timeout=5)
        try:
            self.client.execute('SHOW DATABASES;')
        except Exception:
            self._logger.error('DB connect error')
            self._logger.error(traceback.format_exc())
            return False
        return

    def insert_data(self, tuple_key, tuple_val):
        tuple_key=str(tuple_key)
        tuple_val=str(tuple_val)
        sql = 'insert into table ' + self._table_name + ' ' + tuple_key + ' values ' + \
              tuple_val + ';'
        try:
            self.client.execute(sql)
        except Exception:
            self._logger.error('DB insert data error')
            self._logger.error(traceback.format_exc())
            return False
        return

    def join_with_and(self, dic):
        dic_str = ''
        tag = True
        for key in dic:
            if tag:
                if type(dic[key]) is str:
                    dic_str += (key + '=\'' + dic[key] + '\'')
                else:
                    val_str = str(dic[key])
                    dic_str += (key + '=' + val_str)
                tag = False
            else:
                if type(dic[key]) is str:
                    dic_str += (' AND ' + key + '=\'' + dic[key] + '\'')
                else:
                    val_str = str(dic[key])
                    dic_str += (' AND ' + key + '=' + val_str)
        return dic_str

    def delete_data(self, dic):
        dic_str = self.join_with_and(dic)
        sql = 'alter table ' + self._table_name + ' delete where ' + dic_str + ';'
        try:
            self.client.execute(sql)
        except Exception:
            self._logger.error('DB insert data error')
            self._logger.error(traceback.format_exc())
            return False
        return

    def update_data(self, to_update_dic, condition_dic):
        to_update_dic_str = self.join_with_and(to_update_dic)
        condition_dic_str = self.join_with_and(condition_dic)
        print (to_update_dic_str)
        print (condition_dic_str)
        sql = 'alter table ' + self._table_name + ' update ' + to_update_dic_str + \
              ' where ' + condition_dic_str + ';'
        try:
            self.client.execute(sql)
        except Exception:
            self._logger.error('DB insert data error')
            self._logger.error(traceback.format_exc())
            return False
        return

    def select_data(self, dic):
        dic_str = self.join_with_and(dic)
        sql = 'select * from ' + self._table_name + ' where ' + dic_str + ';'
        try:
            ans = self.client.execute(sql)
        except Exception:
            self._logger.error('DB insert data error')
            self._logger.error(traceback.format_exc())
            return False
        return ans