示例#1
0
    def query_doc(self,
                  index_name=None,
                  type_name=None,
                  cond_value=None,
                  params=None,
                  p_conn=None):
        if index_name is None or type_name is None or cond_value is None or (
                cond_value is not None and type(cond_value) != dict):
            log.log(log.set_loglevel().error(), index_name, type_name,
                    cond_value, p_conn)
            raise err.MyError(log.debug().position(), err.MyError.errcode[4],
                              err.MyError.errmsg[4])
        if p_conn is None:
            try:
                esdb = dbconn.db_es()
                conn = esdb.create_conn()
            except Exception as e:
                log.log(
                    log.set_loglevel().error(),
                    log.debug().position(),
                    e,
                )
                raise err.MyError(log.debug().position(),
                                  err.MyError.errcode[1],
                                  err.MyError.errmsg[1])
        else:
            conn = p_conn

        cond_value['version'] = True
        ret_val = conn.search(index_name, type_name, cond_value)
        if p_conn is None:
            if 'esdb' in locals().keys():
                esdb.close_conn()
                del esdb
        return ret_val
示例#2
0
    def new_table_record(self,
                         table_name=None,
                         column_name=None,
                         column_value=None,
                         p_conn=None):
        if column_name is None or column_value is None or table_name is None:
            log.log(log.set_loglevel().error(), table_name, column_name,
                    column_value, p_conn)
            raise err.MyError(log.debug().position(), err.MyError.errcode[4],
                              err.MyError.errmsg[4])

        if p_conn is None:
            try:
                # conn = mysql.connector.connect(**config.env['db_connection'])
                db = dbconn.db()
                conn = db.create_conn()
            except mysql.connector.Error as e:
                log.log(
                    log.set_loglevel().error(),
                    log.debug().position(),
                    e,
                )
                log.debugfile().printlog2(
                    log.debug().position(),
                    e,
                )
                raise err.MyError(log.debug().position(),
                                  err.MyError.errcode[1],
                                  err.MyError.errmsg[1])
        else:
            conn = p_conn

        insert_sql, column_value = component.c_db_dml.mysql().new_record_sql(
            table_name, column_name, column_value)

        cursor = conn.cursor()
        try:
            if config.env['env'] == 'dev':
                log.log(log.set_loglevel().debug(),
                        log.debug().position(), insert_sql, column_value)
            cursor.execute(insert_sql, column_value)
            new_id = cursor._last_insert_id
        except Exception as e:
            log.log(log.set_loglevel().error(),
                    log.debug().position(), e, insert_sql, column_value)
            raise err.MyError(
                log.debug().position(), err.MyError.errcode[1],
                err.MyError.errmsg[1] + insert_sql + ' ' + column_value)
        finally:
            if cursor:
                cursor.close()
            if p_conn is None:
                if conn:
                    conn.commit()
                    conn.close()
                    db.close_conn()
            if 'new_id' in locals():
                return new_id
示例#3
0
    def delete_table_record(self,
                            table_name=None,
                            column_name=None,
                            column_value=None,
                            p_conn=None):
        if column_name is None or column_value is None or table_name is None:
            raise err.MyError(log.debug().position(), err.MyError.errcode[4],
                              err.MyError.errmsg[4])
        if p_conn is None:
            try:
                # conn = mysql.connector.connect(**config.env['db_connection'])
                db = dbconn.db()
                conn = db.create_conn()
            except mysql.connector.Error as e:
                log.log(
                    log.set_loglevel().error(),
                    log.debug().position(),
                    e,
                )
                log.debugfile().printlog2(
                    log.debug().position(),
                    e,
                )
                raise err.MyError(log.debug().position(),
                                  err.MyError.errcode[1],
                                  err.MyError.errmsg[1])
        else:
            conn = p_conn

        delete_sql = component.c_db_dml.mysql().delete_record_sql(
            table_name, column_name, column_value)

        cursor = conn.cursor()
        try:
            if config.env['env'] == 'dev':
                log.log(log.set_loglevel().debug(),
                        log.debug().position(), delete_sql)
            cursor.execute(delete_sql)
            delete_count = cursor._rowcount
        except Exception as e:
            log.log(log.set_loglevel().error(),
                    log.debug().position(), e, delete_sql)
            raise err.MyError(log.debug().position(), err.MyError.errcode[1],
                              err.MyError.errmsg[1], delete_sql)
        finally:
            if cursor:
                cursor.close()
            if p_conn is None:
                if conn:
                    conn.commit()
                    conn.close()
                    db.close_conn()
            if 'delete_count' in locals():
                return delete_count
示例#4
0
    def create_conn(self):
        if self.conn:
            return self.conn

        if self.sshserver is not None:
            # mock prd environment
            sshserver = self.sshserver.sshserver
        try:
            if bc_info.env['outside'] == 'y' and bc_info.env['env'] == 'dev':
                # from sshtunnel import SSHTunnelForwarder
                # with SSHTunnelForwarder(
                #         ('139.224.68.177', 22),  # B机器的配置
                #         # ssh_password="******",
                #         ssh_username="******",
                #         ssh_pkey="/Users/kelly/Desktop/车挣/id_rsa",
                #         remote_bind_address=('rm-uf6dq61h877912xf8.mysql.rds.aliyuncs.com', 3306)) as server:  # A机器的配置
                #     config = {'host': '127.0.0.1',  # 默认127.0.0.1
                #               'user': '******',
                #               'passwd': '3ZYUmN3a',
                #               'port': server.local_bind_port,  # 默认即为3306
                #               'db': 'insdb',
                #               # 'charset': 'utf8'  # 默认即为utf8
                #               }
                #     self.conn = mysql.connector.connect(**config)
                #     cursor = self.conn.cursor()
                #     cursor.execute("select now()")
                #     print(cursor.fetchone())
                #     return self.conn

                config = {
                    'host': '127.0.0.1',  # 默认127.0.0.1
                    'user': bc_info.env['db_connection']['user'],  #'root',
                    'passwd': bc_info.env['db_connection']
                    ['password'],  #'123456',#'QJF9dDTU',
                    'port': sshserver.local_bind_port,  # 默认即为3306
                    'db': bc_info.env['db_connection']['database'],  #'darams',
                    'charset': bc_info.env['db_connection']
                    ['charset'],  #'utf8'  # 默认即为utf8
                }
                self.conn = mysql.connector.connect(**config)
                # cursor = self.conn.cursor()
                # cursor.execute("select now()")
                # print(cursor.fetchone())
                # return self.conn

            else:
                self.conn = mysql.connector.connect(
                    **bc_info.env['db_connection'])
        except mysql.connector.Error as e:
            basic_config.bc_log.debug().print_debug(
                bc_info.position(),
                e,
            )
            basic_config.bc_log.debugfile().printlog2(
                basic_config.bc_info.position(),
                e,
            )
            raise err.MyError(basic_config.bc_info.position(),
                              err.MyError.errcode[1], err.MyError.errmsg[1])
        return self.conn
示例#5
0
    def create_conn(self):
        if self.conn:
            return self.conn

        if self.sshserver_es is not None:
            # mock prd environment
            sshserver_es = self.sshserver_es.sshserver
        try:
            if bc_info.env['outside'] == 'y' and bc_info.env['env'] == 'dev':
                config = {
                    'host': '127.0.0.1',
                    'port': sshserver_es.local_bind_port,  # 默认即为3306
                }
                self.conn = elasticsearch.Elasticsearch([config])
            else:
                self.conn = elasticsearch.Elasticsearch([bc_info.env['db_es']])
        except mysql.connector.Error as e:
            basic_config.bc_log.debug().print_debug(
                bc_info.position(),
                e,
            )
            basic_config.bc_log.debugfile().printlog2(
                basic_config.bc_info.position(),
                e,
            )
            raise err.MyError(basic_config.bc_info.position(),
                              err.MyError.errcode[1], err.MyError.errmsg[1])
        return self.conn
示例#6
0
    def query_table_record(self, sql=None, p_conn=None):
        if sql is None:
            log.log(log.set_loglevel().error(), sql, p_conn)
            raise err.MyError(log.debug().position(), err.MyError.errcode[4],
                              err.MyError.errmsg[4])
        if p_conn is None:
            try:
                # conn = mysql.connector.connect(**config.env['db_connection'])
                db = dbconn.db()
                conn = db.create_conn()
            except mysql.connector.Error as e:
                log.log(
                    log.set_loglevel().error(),
                    log.debug().position(),
                    e,
                )
                log.debugfile().printlog2(
                    log.debug().position(),
                    e,
                )
                raise err.MyError(log.debug().position(),
                                  err.MyError.errcode[1],
                                  err.MyError.errmsg[1])
        else:
            conn = p_conn

        cursor = conn.cursor()
        try:
            if config.env['env'] == 'dev':
                log.log(log.set_loglevel().debug(),
                        log.debug().position(), sql)
            cursor.execute(sql)
            results = cursor.fetchall()
            column_names = cursor.column_names
        except Exception as e:
            log.log(log.set_loglevel().error(), log.debug().position(), e, sql)
            raise err.MyError(log.debug().position(), e, sql)
        finally:
            if cursor:
                cursor.close()
            if p_conn is None:
                if conn:
                    conn.commit()
                    conn.close()
                    db.close_conn()
            if 'results' in locals():
                return column_names, results
示例#7
0
    def add_memo(self,
                 ordercode='',
                 memo='',
                 operator='',
                 remark=None,
                 p_conn=None):
        """
        暂时不生效
        :param ordercode:
        :type ordercode:
        :param memo:
        :type memo:
        :param operator:
        :type operator:
        :param remark:
        :type remark:
        :param p_conn:
        :type p_conn:
        :return:
        :rtype:
        """
        if ordercode == '' or ordercode is None or memo == '' or memo is None or operator == '' or operator is None:
            log.log(log.set_loglevel().error(),
                    log.debug().position(), err.MyError.errcode[4],
                    err.MyError.errmsg[4])
            raise err.MyError(log.set_loglevel().error(),
                              log.debug().position(), err.MyError.errcode[4],
                              err.MyError.errmsg[4])

        if p_conn is None:
            vipdb = dbconn.vipdb()
            conn = vipdb.create_conn()
        else:
            conn = p_conn

        table_name = '%s.t_insurance_memo' % config.env['vipschema']
        column_name = ('ordercode', 'memo', 'operator', 'remark', 'cdt', 'udt')
        str_curr_time = datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        column_value = (ordercode, memo, operator, remark, str_curr_time,
                        str_curr_time)
        memo_id = self.new_table_record(table_name, column_name, column_value,
                                        conn)

        if p_conn is None:
            conn.rollback()
            del conn
            vipdb.close_conn()

        return memo_id
示例#8
0
    def update_table_record(self,
                            table_name=None,
                            column_name=None,
                            column_value=None,
                            p_conn=None):
        if column_name is None or column_value is None or table_name is None:
            raise err.MyError(log.debug().position(), err.MyError.errcode[4],
                              err.MyError.errmsg[4])
        if p_conn is None:
            try:
                # conn = mysql.connector.connect(**config.env['db_connection'])
                db = dbconn.db()
                conn = db.create_conn()
            except mysql.connector.Error as e:
                log.log(
                    log.set_loglevel().error(),
                    log.debug().position(),
                    e,
                )
                log.debugfile().printlog2(
                    log.debug().position(),
                    e,
                )
                raise err.MyError(log.debug().position(),
                                  err.MyError.errcode[1],
                                  err.MyError.errmsg[1])
        else:
            conn = p_conn

        update_sql, column_value = component.c_db_dml.mysql(
        ).update_record_sql(table_name, column_name, column_value)

        cursor = conn.cursor()
        try:
            try_count = 1
            while try_count <= 5:
                try:
                    if config.env['env'] == 'dev':
                        log.log(log.set_loglevel().debug(),
                                log.debug().position(), update_sql,
                                column_value)
                    cursor.execute(update_sql, column_value)
                    update_count = cursor._rowcount
                    break
                except Exception as e:
                    # log.log(log.set_loglevel().error(),log.debug().position(),err.err_catch().catch(sys.exc_info()), update_sql, column_name, column_value,e)
                    log.log(log.set_loglevel().error(),
                            log.debug().position(), update_sql, column_name,
                            column_value, e)
                    process_db = dbconn.db()
                    process_conn = process_db.create_conn()
                    process_cursor = process_conn.cursor()
                    process_cursor.execute("show full processlist;")
                    process_lists = process_cursor.fetchall()
                    log.log(log.set_loglevel().error(),
                            log.debug().position(), process_lists)
                    process_cursor.close()
                    process_conn.close()
                    process_db.close_conn()
                    cursor.close()
                    cursor = conn.cursor()
                    if str(e).count('Lock wait timeout exceeded') > 0:
                        try_count += 1
                        time.sleep(5)
                        if try_count > 5:
                            raise
                    else:
                        raise
        finally:
            if cursor:
                cursor.close()
            if p_conn is None:
                if conn:
                    conn.commit()
                    conn.close()
                    db.close_conn()
            if 'update_count' in locals():
                return update_count
示例#9
0
    def delete_doc(self,
                   index_name=None,
                   type_name=None,
                   id=None,
                   cond_value=None,
                   p_conn=None):
        """
        ES delete  id有值,condvalue没值,删除对象为id;condvalue有值,删除对象为condvalue,和id无关  a=db_es_dml().update_doc('log_test','python_core','AWVqRo0K5SxcWh31QmD5',version=None,cond_value=None,doc_value={'aaa':47})
        :param index_name:
        :type index_name: string
        :param type_name:
        :type type_name: string
        :param id:
        :type id:  string
        :param p_conn:
        :type p_conn:  object
        :return:
        :rtype:
        """
        if index_name is None or type_name is None or (
                cond_value is not None and type(cond_value) != dict):
            log.log(log.set_loglevel().error(), index_name, type_name, id,
                    cond_value, p_conn)
            raise err.MyError(log.debug().position(), err.MyError.errcode[4],
                              err.MyError.errmsg[4])
        if p_conn is None:
            try:
                esdb = dbconn.db_es()
                conn = esdb.create_conn()
            except Exception as e:
                log.log(
                    log.set_loglevel().error(),
                    log.debug().position(),
                    e,
                )
                raise err.MyError(log.debug().position(),
                                  err.MyError.errcode[1],
                                  err.MyError.errmsg[1])
        else:
            conn = p_conn

        body_value = {}
        continue_flag = True
        if id is not None:
            body_value['query'] = {'term': {'_id': id}}
            body_value['version'] = True

        if cond_value is not None:
            body_value.pop('query')
        elif id is None:
            body_value['query'] = {'match_all': {}}
        body_value_tmp = component.c_db_dml.es().delete_record_sql(
            index_name, type_name, cond_value)
        if 'query' in body_value_tmp.keys():
            if 'query' in body_value.keys():
                body_value['query'].update(body_value_tmp['query'])
            else:
                body_value['query'] = body_value_tmp['query']

        result = conn.delete_by_query(index=index_name,
                                      doc_type=type_name,
                                      body=body_value)
        ret_val = result['deleted']

        if p_conn is None:
            if 'esdb' in locals().keys():
                esdb.close_conn()
                del esdb
        return ret_val
示例#10
0
    def update_doc(self,
                   index_name=None,
                   type_name=None,
                   id=None,
                   version=None,
                   doc_value=None,
                   cond_value=None,
                   p_conn=None):
        """
        ES update  id version同时有值则会校验version;id有值,condvalue没值,更新对象为id;condvalue有值,更新对象为condvalue,和id无关  a=db_es_dml().update_doc('log_test','python_core','AWVqRo0K5SxcWh31QmD5',version=None,cond_value=None,doc_value={'aaa':47})
        :param index_name:
        :type index_name: string
        :param type_name:
        :type type_name: string
        :param id:
        :type id:  string
        :param version:
        :type version:  integer
        :param doc_value:
        :type doc_value:  dict
        :param p_conn:
        :type p_conn:  object
        :return:
        :rtype:
        """
        if index_name is None or type_name is None or doc_value is None or (
                version is not None and id is None) or (
                    cond_value is not None and type(cond_value) != dict) or (
                        len(doc_value) > 0 and type(doc_value) != dict):
            log.log(log.set_loglevel().error(), index_name, type_name, id,
                    version, doc_value, cond_value, p_conn)
            raise err.MyError(log.debug().position(), err.MyError.errcode[4],
                              err.MyError.errmsg[4])
        if p_conn is None:
            try:
                esdb = dbconn.db_es()
                conn = esdb.create_conn()
            except Exception as e:
                log.log(
                    log.set_loglevel().error(),
                    log.debug().position(),
                    e,
                )
                raise err.MyError(log.debug().position(),
                                  err.MyError.errcode[1],
                                  err.MyError.errmsg[1])
        else:
            conn = p_conn

        body_value = {}
        continue_flag = True
        if id is not None:
            body_value['query'] = {'term': {'_id': id}}
            body_value['version'] = True
            if version is not None:
                results = conn.search(index_name, type_name, body_value)
                result_version = results['hits']['hits'][0]['_version']
                if result_version != version:
                    continue_flag = False
                    ret_val = 0

        if cond_value is not None:
            body_value.pop('query')
        elif id is None:
            body_value['query'] = {'match_all': {}}
        if continue_flag:
            body_value_tmp = component.c_db_dml.es().update_record_sql(
                index_name, type_name, doc_value, cond_value)
            if 'query' in body_value_tmp.keys():
                if 'query' in body_value.keys():
                    body_value['query'].update(body_value_tmp['query'])
                else:
                    body_value['query'] = body_value_tmp['query']
            if 'script' in body_value.keys():
                body_value['script'].update(body_value_tmp['script'])
            else:
                body_value['script'] = body_value_tmp['script']
            if 'version' in body_value.keys():
                try:
                    body_value['version'].update(body_value_tmp['version'])
                except AttributeError as e:
                    body_value['version'] = body_value_tmp['version']
            else:
                body_value['version'] = body_value_tmp['version']

            result = conn.update_by_query(index=index_name,
                                          doc_type=type_name,
                                          body=body_value)
            ret_val = result['updated']

        if p_conn is None:
            if 'esdb' in locals().keys():
                esdb.close_conn()
                del esdb
        return ret_val
示例#11
0
    def new_doc(self,
                index_name=None,
                type_name=None,
                doc_value=None,
                p_conn=None):
        """
        ES insert
        :param index_name:
        :type index_name: string
        :param type_name:
        :type type_name: string
        :param doc_value:
        :type doc_value: dict  or  list for bulk
        :param p_conn:
        :type p_conn: es object
        :return: (created True or False, id , version)
        :rtype: array
        """
        if index_name is None or type_name is None or doc_value is None:
            log.log(log.set_loglevel().error(), index_name, type_name,
                    doc_value, p_conn)
            raise err.MyError(log.debug().position(), err.MyError.errcode[4],
                              err.MyError.errmsg[4])
        if p_conn is None:
            try:
                esdb = dbconn.db_es()
                conn = esdb.create_conn()
            except Exception as e:
                log.log(
                    log.set_loglevel().error(),
                    log.debug().position(),
                    e,
                )
                raise err.MyError(log.debug().position(),
                                  err.MyError.errcode[1],
                                  err.MyError.errmsg[1])
        else:
            conn = p_conn

        result = {
            'created': False,
            'result': 'created',
            '_id': None,
            '_version': None
        }
        try:
            if len(doc_value) == 0 or type(doc_value) == dict:
                result = conn.index(index=index_name,
                                    doc_type=type_name,
                                    body=doc_value)
                result['created'] = True
                # '_op_type':'delete'
                # result = elasticsearch.helpers.bulk(conn,[{'_index':index_name,'_type':type_name,'_source':doc_value}],raise_on_error=True)

            elif type(doc_value) == list:
                docs = []
                for doc in doc_value:
                    one_row = {
                        '_index': index_name,
                        '_type': type_name,
                        '_source': doc
                    }
                    docs.append(one_row)
                result = elasticsearch.helpers.bulk(conn,
                                                    docs,
                                                    raise_on_error=True)
                result = {
                    'created': True,
                    'result': 'created',
                    '_id': '' if result[0] > 0 else None,
                    '_version': '' if result[0] > 0 else None
                }
            else:
                log.log(log.set_loglevel().error(), index_name, type_name,
                        doc_value, p_conn)
                raise err.MyError(log.debug().position(),
                                  err.MyError.errcode[4],
                                  err.MyError.errmsg[4])
            # {'created': True, 'result': 'created', '_shards': {'successful': 1, 'failed': 0, 'total': 2}, '_version': 1, '_type': 'python_core', '_id': 'AWVqIzR35SxcWh31QmD1', '_index': 'log_test'}
        except Exception as e:
            log.log(log.set_loglevel().error(),
                    log.debug().position(), e, index_name, type_name,
                    doc_value, p_conn)
            raise e
        finally:
            if p_conn is None:
                if 'esdb' in locals().keys():
                    esdb.close_conn()
                    del esdb
        return result['created'], result['_id'], result['_version']