db_help.py

最新推荐文章于 2024-11-09 21:51:27 发布
weixin_33756418
最新推荐文章于 2024-11-09 21:51:27 发布
阅读量109
点赞数
文章标签： python
原文链接：https://my.oschina.net/xxjbs001/blog/278848
版权
2019独角兽企业重金招聘Python工程师标准>>>
#!/usr/bin/env python
# coding=utf-8
from optparse import OptionParser
import sys, time, os, re, logging
from pprint import pprint, pformat
from traceback import format_exc
from copy import deepcopy
import MySQLdb
from datetime import datetime


class DBI():
    """
    """
    _host = ''
    _port = ''
    _user = ''
    _passwd = ''
    _dbname = ''
    _conn_timeout = 30
    _logger = None

    def __init__(self, host='127.0.0.1', port='3306', user='root', passwd='123qaz', dbname='automation_test', conn_timeout=30, logger=None):
        """
        """
        (self._host, self._port, self._user, self._passwd, self._dbname, self._conn_timeout) = (host, port, user, passwd, dbname, conn_timeout)
        self._logger = logger
        if not logger:
            logger = logging.getLogger("TEST")
            logger.setLevel(logging.DEBUG)
            stdhdlr = logging.StreamHandler(sys.stdout)
            # FORMAT = '[%(levelname)s] %(message)s'
            FORMAT = '%(message)s'
            stdhdlr.setFormatter(logging.Formatter(FORMAT))
            logger.addHandler(stdhdlr)
            self._logger = logger
            pass
        else:
            self._logger = logger

        pass

    def _openDB(self):
        """
        """
        conn = None
        try:
            conn = MySQLdb.connect(host=self._host, user=self._user, passwd=self._passwd, port=int(self._port), connect_timeout=self._conn_timeout)
            conn.select_db(self._dbname)
        except Exception, e:
            self._logger.error('open db failed', exc_info=1)
            pass

        return conn

    def exec_SQL(self, sql):
        """
        """
        conn = self._openDB()
        r = False
        result_set = None
        if conn:
            try:
                cursor = conn.cursor()
                r = cursor.execute(sql)
                conn.commit()
                result_set = cursor.fetchall()
                cursor.close()
                conn.close()
                pass
            except Exception, e:
                self._logger.error("exec_SQL except :", exc_info=1)
        else:
            pass

        return (r, result_set)

    def exec_SQLs(self, sqls):
        """
        """
        conn = self._openDB()
        r = False
        if conn:
            try:
                cursor = conn.cursor()
                r = []
                for sql in sqls:
                    _r = cursor.execute(sql)
                    r.append(_r)
                    pass

                conn.commit()
                cursor.close()
                conn.close()
                # return r
                pass
            except Exception, e:
                self._logger.error("exec_SQLs except :", exc_info=1)
                pass
        else:
            pass

        return r

    def batch_SQL(self, sql_str, values):
        """
        """
        conn = self._openDB()
        r = False
        if conn:
            try:
                cursor = conn.cursor()
                r = cursor.executemany(sql_str, values)
                conn.commit()
                cursor.close()
                conn.close()
            except Exception, e:
                self._logger.error("batch_SQL except :", exc_info=1)
                pass

            # return r
        else:
            pass

        return r

def insert_variable(testbed='', key='', value=''):
    """
    """
    pass
    host = os.getenv('U_CUSTOM_AT_DB_HOST', '192.168.20.108')
    port = os.getenv('U_CUSTOM_AT_DB_PORT', '3306')
    user = os.getenv('U_CUSTOM_AT_DB_USERNAME', 'root')
    passwd = os.getenv('U_CUSTOM_AT_DB_PASSWORD', '123qaz')
    dbname = os.getenv('U_CUSTOM_AT_DB_NAME', 'automation_test')
    dbi = DBI(host=host, port=port, user=user, passwd=passwd, dbname=dbname)
    sql = "SELECT * FROM t_am_mediaroom_variables where testbed ='" + str(testbed) + "' AND var_item='" + str(key) + "'"
    r = dbi.exec_SQL(sql)
    if int(r[0]):
        sql = "UPDATE t_am_mediaroom_variables SET var_value='" + value + "' WHERE testbed='" + str(testbed) + "' AND var_item='" + str(key) + "'"
    else:
        fmt = "INSERT INTO t_am_mediaroom_variables VALUES(NULL,'%s','%s','%s')"
        sql = fmt % (testbed, key, value)
    r = dbi.exec_SQL(sql)
    pass

def delete_variable(id=''):
    """
    """
    pass
    id = str(id)
    host = os.getenv('U_CUSTOM_AT_DB_HOST', '192.168.20.108')
    port = os.getenv('U_CUSTOM_AT_DB_PORT', '3306')
    user = os.getenv('U_CUSTOM_AT_DB_USERNAME', 'root')
    passwd = os.getenv('U_CUSTOM_AT_DB_PASSWORD', '123qaz')
    dbname = os.getenv('U_CUSTOM_AT_DB_NAME', 'automation_test')
    dbi = DBI(host=host, port=port, user=user, passwd=passwd, dbname=dbname)
 
    fmt = "DELETE FROM t_am_mediaroom_variables WHERE var_id=('%s')"
    sql = fmt % (id)
    # print('AT_INFO : sql(%s)' % (sql))
 
    r = dbi.exec_SQL(sql)
    pass



def insert_sample(task_start_time='', testbed='', product='', dut_br0_mac='', case='', sample_time='', sample_item='', sample_value='', logger=None):
    """
    """
    pass
    host = os.getenv('U_CUSTOM_AT_DB_HOST', '192.168.20.108')
    port = os.getenv('U_CUSTOM_AT_DB_PORT', '3306')
    user = os.getenv('U_CUSTOM_AT_DB_USERNAME', 'root')
    passwd = os.getenv('U_CUSTOM_AT_DB_PASSWORD', '123qaz')
    dbname = os.getenv('U_CUSTOM_AT_DB_NAME', 'automation_test')
    dbi = DBI(host=host, port=port, user=user, passwd=passwd, dbname=dbname, logger=logger)
    if not task_start_time :
        task_start_time = os.getenv('U_CUSTOM_TEST_TASK_START_TIME', 'UNKNOWN')
        pass
 
    if not testbed :
        testbed = os.getenv('G_TBNAME', 'UNKNOWN')
        pass
    if not product :
        product = os.getenv('U_DUT_TYPE', 'UNKNOWN')
        pass
    if not dut_br0_mac :
        dut_br0_mac = os.getenv('G_PROD_MAC_BR0_0_0', 'UNKNOWN')
        pass
    if not case :
        case = os.getenv('U_CUSTOM_CURRENT_CASE_ID', 'MediaRoomTest')
        pass
    if not sample_time :
        sample_time = datetime.now()
        pass
 
    fmt = "INSERT INTO t_am_mediaroom_sample VALUES(0,'%s','%s','%s','%s','%s','%s','%s','%s')"
    sql = fmt % (task_start_time, testbed, product, dut_br0_mac, case, sample_time, sample_item, sample_value)
    # print('AT_INFO : sql(%s)' % (sql))
 
    r = dbi.exec_SQL(sql)
    pass
def insert_roy(item_name='',item_value='',task_start_time=''):
    """
    """
def update_traffic(iperf_num='', iperf_band='', curl_num='', curl_band='', testbed='', logger=None):
    """
    """
    host = os.getenv('U_CUSTOM_AT_DB_HOST', '192.168.20.108')
    port = os.getenv('U_CUSTOM_AT_DB_PORT', '3306')
    user = os.getenv('U_CUSTOM_AT_DB_USERNAME', 'root')
    passwd = os.getenv('U_CUSTOM_AT_DB_PASSWORD', '123qaz')
    dbname = os.getenv('U_CUSTOM_AT_DB_NAME', 'automation_test')
    dbi = DBI(host=host, port=port, user=user, passwd=passwd, dbname=dbname, logger=logger)
    sql = "UPDATE t_am_mediaroom_iperf SET "
    flag = False
    if iperf_band:
        sql += "iperf_band=" + "'" + str(iperf_band) + "'"
        flag = True
        
    if iperf_num:
        if flag:
            sql += ",iperf_num=" + "'" + str(iperf_num) + "'"
        else:
            sql += "iperf_num=" + "'" + str(iperf_num) + "'"
            flag = True
            
    if curl_num:
        if flag:
            sql += ",curl_num=" + "'" + str(curl_num) + "'"
        else:
            sql += "curl_num=" + "'" + str(curl_num) + "'"
            flag = True
    
    if curl_band:
        if flag:
            sql += ",curl_band=" + "'" + str(curl_band) + "'"
        else:
            sql += "curl_band=" + "'" + str(curl_band) + "'"
            flag = True
            
    sql += " where testbed ='" + str(testbed) + "'"
    
    if flag:
        r = dbi.exec_SQL(sql)

def get_env(testbed='', logger=None):
    """
    """
    host = os.getenv('U_CUSTOM_AT_DB_HOST', '192.168.20.108')
    port = os.getenv('U_CUSTOM_AT_DB_PORT', '3306')
    user = os.getenv('U_CUSTOM_AT_DB_USERNAME', 'root')
    passwd = os.getenv('U_CUSTOM_AT_DB_PASSWORD', '123qaz')
    dbname = os.getenv('U_CUSTOM_AT_DB_NAME', 'automation_test')
    dbi = DBI(host=host, port=port, user=user, passwd=passwd, dbname=dbname, logger=logger)
    sql = "SELECT * FROM t_am_mediaroom_variables where testbed ='" + str(testbed) + "'"
    
    r = dbi.exec_SQL(sql)
    return r

def testDBI():
    """
    """
    dbi = DBI()
    print('--' * 16)

    # sql = 'INSERT INTO t_am_keywordlib VALUES(%s,%s,%s,%s,%s,%s,%s)'
    # values = []
    # values.append(('','raytest','','','','CMD : ls\nCMD : ifconfig','ray Test'))
    # r = dbi.batch_SQL(sql,values)
    # print(r)
    task_start_time = datetime.now()
    testbed = 'at_sh1'
    product = 'CTLC2KA'
    dut_br0_mac = 'aa:bb:cc:dd:ee:ff'
    case = '99900001'
    sample_time = datetime.now()
    sample_item = 'unit test'
    sample_value = 'Pass'
    comment = ''

    fmt = "INSERT INTO t_am_stability_sampling VALUES(0,'%s','%s','%s','%s','%s','%s','%s','%s')"
    sql = fmt % (task_start_time, testbed, product, dut_br0_mac, case, sample_time, sample_item, sample_value)

    r = dbi.exec_SQL(sql)

    pass


if __name__ == '__main__':
    """
    """
    testDBI()
    print('== DONE ==')
转载于:https://my.oschina.net/xxjbs001/blog/278848