示例#1
0
 def __init__(self, clusterobj, dbname=''):
     self.ip = clusterobj.foreign_ip
     self.user = clusterobj.plat_user
     self.passwd = clusterobj.plat_user_pass
     self.db = dbname
     self.port = int(clusterobj.foreign_port)
     self._sql = MySQL(self.ip, self.port, self.db, self.user, self.passwd)
示例#2
0
    def put(self, request, format=None):
        try:
            data_id = request.data['id']
            foreign_ip = request.data['foreign_ip']
            foreign_port = request.data['foreign_port']
            plat_user = request.data['plat_user']
            plat_user_passwd = request.data['plat_user_pass']

        except Exception as e:
            print(e)
            json_data = {'code': 500, 'msg': '数据有错误获取不到id'}
            return Response(json_data,status=500)
        else:
            DATA_MODEL = self.get_object(data_id)
            s = MysqlClusterSerializer(DATA_MODEL, data=request.data)

            if s.is_valid(raise_exception=True):
                try:
                    testconn = MySQL(ip=foreign_ip, port=foreign_port, user=plat_user, passwd=plat_user_passwd)
                    testconn.close()
                    msg = '数据库连接成功'
                    s.save()
                    json_data = {'code': 200, 'msg': '更新成功'}
                    return Response(json_data)
                except Exception as e:
                    json_data = {'code': 500, 'msg': '更新失败,新的用户名密码或者网络不通过'}
                    return Response(json_data)
            json_data = {'code': 500, 'msg': '更新失败,数据格式和内部表不一致'}
            return Response(json_data, status=status.HTTP_500_INTERNAL_SERVER_ERROR)
示例#3
0
    def post(self, request, format=None):
        s = MysqlClusterSerializer(data=request.data)
        foreign_ip = request.data['foreign_ip']
        foreign_port = request.data['foreign_port']
        plat_user = request.data['plat_user']
        plat_user_passwd = request.data['plat_user_pass']

        msg = ""

        if s.is_valid():  # 验证
            try:
                testconn = MySQL(ip=foreign_ip,
                                 port=foreign_port,
                                 user=plat_user,
                                 passwd=plat_user_passwd)
                testconn.close()
                msg = '数据库连接成功'

            except Exception as e:
                print(e)
                json_data = {'code': 500, 'msg': '数据添加失败,测试连接失败'}
                return Response(json_data, content_type="application/json")

            s.save()  #连接成功后保存数据
            json_data = {'code': 200, 'msg': '数据保存成功,' + msg}
            json_data['data'] = s.data
            return Response(json_data, content_type="application/json")
            # return api_response.JsonResponse(s.data,code=status.HTTP_200_OK,msg='success')
        json_data = {'code': 500, 'msg': '数据添加失败,请检查数据格式'}
        return Response(json_data, content_type="application/json")
示例#4
0
    def mysql_update_instance(self):

        if self.arch == '主从':
            rs = self._conn.getSlaveIP()
            conn_slave = MySQL(ip=rs[1],
                               port=self.port,
                               user=self.user,
                               passwd=self.passwd)
            rs2 = conn_slave.getReplStatus()
            myinstance = self._cluster.mysql_instance
            if rs2[3] == self.port:  #代表主从是一致的
                slave = myinstance.get_or_create(dbcluster_id=self.cluster_id,
                                                 m_ip=rs[1],
                                                 port=self.port)
                slave[0].master_ip = rs2[1]
                slave[0].master_port = rs2[3]
                slave[0].role = u'从库'
                # slave[0].db_status=u'仅从库'
                slave[0].save()
                master = myinstance.get_or_create(dbcluster_id=self.cluster_id,
                                                  m_ip=rs2[1],
                                                  port=self.port)
                master[0].vist_ip = self.ip
                master[0].role = u'主库'
                master[0].db_status = u'服务中'
                master[0].save()

        return True
示例#5
0
 def initdb(self):
     self.db = MySQL(config.MOBGI_HOUSEAD['host'],
                     config.MOBGI_HOUSEAD['user'],
                     config.MYSQL['passwd'],
                     port=config.MOBGI_HOUSEAD['port'],
                     db=config.MOBGI_HOUSEAD['db'])
     return self.db
示例#6
0
class MySQLTask(TaskThread):
    def __init__(self, *args, **kwargs):
        self._mysql = MySQL(**kwargs["db_config"])
        super(MySQLTask,self).__init__(*args, **kwargs)
        self.daemon = True

    def get_handler(self):
        return self._mysql

    def close(self):
        self._mysql.close()
示例#7
0
 def initdb(self):
     self.houseadDb = MySQL(config.MOBGI_HOUSEAD['host'],
                            config.MOBGI_HOUSEAD['user'],
                            config.MOBGI_HOUSEAD['passwd'],
                            port=config.MOBGI_HOUSEAD['port'],
                            db=config.MOBGI_HOUSEAD['db'])
     self.mobgiCharge = MySQL(config.MOBGI_CHARGE['host'],
                              config.MOBGI_CHARGE['user'],
                              config.MOBGI_CHARGE['passwd'],
                              port=config.MOBGI_CHARGE['port'],
                              db=config.MOBGI_CHARGE['db'])
     return self.houseadDb, self.mobgiCharge
示例#8
0
 def __init__(self, clusterid):
     self._cluster = MySQLCluster.objects.get(id=clusterid)
     self.cluster_id = clusterid
     self.ip = self._cluster.foreign_ip
     self.port = self._cluster.foreign_port
     self.user = self._cluster.plat_user
     self.arch = self._cluster.arch
     self.passwd = self._cluster.plat_user_pass
     self._conn = MySQL(ip=self.ip,
                        port=self.port,
                        user=self.user,
                        passwd=self.passwd)
 def trysql(self, sql):
     try:
         self.mylog("select sql:" + sql)
         self.clickdb.queryNotCatch(sql)
     except MySQLdb.Error as m:
         self.mylog('masterdb reconnecting now')
         self.clickdb = MySQL(self.host,
                              self.user,
                              self.passwd,
                              port=self.port,
                              db=self.db)
         self.clickdb.query(sql)
     return self.clickdb.fetchAll()
class MainExample:
    def __init__(self):
        # Selenium headless
        self.crawl = Crawl(delay=20, telegram_notify=False, gui=True)
        self.aws = Resource()
        self.mysql = MySQL()

    @context_manager
    def transaction(self, keyword, control_instances):
        # Base page, 생략된 결과를 포함하여 검색
        self.crawl.fetch('https://www.google.co.kr/search?q={}&filter=0'.format(keyword))

        # 페이지 탐색
        self.navigate_page(5)

        # AWS 'Auxiliary' 인스턴스 실행
        self.aws.ec2_on_off(control_instances)

    def navigate_page(self, max_page):
        # paginate pattern
        # nav > tbody > tr > td.cur > span
        # nav > tbody > tr > td:nth-child([next]) > a > span
        cur_page = None
        for _ in range(max_page):
            # <a> 태그를 가진 Selenium Web Element 들의 객체 리스트
            a_tags_elements = self.crawl.chrome.browser.find_elements_by_css_selector('#rso div.rc h3.r a')

            # <a> 태그 추출
            a_tags = [a.get_attribute('href') for a in a_tags_elements]

            # 현재 페이지 데이터베이스 저장
            self.mysql.upload_page_lists(a_tags)

            # 현재 페이지 찾기
            paginate = self.crawl.chrome.browser.find_elements_by_css_selector('#nav > tbody > tr > td')
            for i, e in enumerate(paginate):
                if e.get_attribute('class') == 'cur':
                    cur_page = i
                    break

            # 다음 페이지 없음
            try:
                next_element = paginate[cur_page + 1].find_element_by_css_selector('a')
                next_page = next_element.get_attribute('href')
            except NoSuchElementException:
                break

            # 다음 페이지 요청
            self.crawl.fetch(next_page)
示例#11
0
 def query(self, force=False):
     print('[INFO] Querying data:')
     for table, columns in self.metadata.items():
         ti = time()
         self[table] = MySQL.query("SELECT {} FROM {}".format(
             ', '.join(columns), table))
         self[table].rename(columns={'id': '{}_id'.format(table[:-1])},
                            inplace=True)
         print('[INFO] > {} - {}s'.format(table, round(time() - ti, 2)))
示例#12
0
def main():
    """
    Youtube Main Crawler. Expected to run once a day
    """
    api_list = [
        os.environ['API_1'], os.environ['API_2'], os.environ['API_3'],
        os.environ['API_4'], os.environ['API_5']
    ]

    yc = YoutubeCrawler(api_list, processes=50)

    conn = MySQL(host=os.environ['host'],
                 db=os.environ['db'],
                 user=os.environ['user'],
                 port=os.environ['port'],
                 passwd=os.environ['pw'],
                 charset='utf8mb4',
                 auto_commit=False)

    conn_02 = MySQL(host=os.environ['host'],
                    db=os.environ['db'],
                    user=os.environ['user'],
                    port=os.environ['port'],
                    passwd=os.environ['pw'],
                    charset='utf8mb4',
                    auto_commit=False)

    upsert_cd = 'INSERT INTO YOUTUBE.t_ch_desc \
          (ch_title, ch_id, ch_desc, ch_published_at, ch_thumb) \
          VALUES (%(title)s, %(ch_id)s, %(description)s, %(publishedAt)s, %(ch_thumb)s) \
          ON DUPLICATE KEY UPDATE ch_title=%(title)s, ch_desc=%(description)s, ch_published_at=%(publishedAt)s, ch_thumb=%(ch_thumb)s'

    upsert_cc = 'INSERT INTO YOUTUBE.t_ch_cstats \
              (ch_id, ch_n_sub, ch_n_view, ch_n_video, ch_n_cmt) \
              VALUES (%(ch_id)s, %(subscriberCount)s, %(viewCount)s, %(videoCount)s, %(comment_count)s) \
              ON DUPLICATE KEY UPDATE ch_n_sub=%(subscriberCount)s, ch_n_view=%(viewCount)s, ch_n_video=%(videoCount)s, ch_n_cmt=%(comment_count)s'

    insert_vid_desc = 'INSERT IGNORE INTO YOUTUBE.t_ch_vid_desc \
                       (ch_id, upload_id, vid_id, vid_title, vid_desc, vid_published_at, vid_th) \
                       VALUES (%(ch_id)s, %(upload_id)s, %(vid_id)s, %(vid_title)s, %(vid_desc)s, %(vid_published_at)s, %(vid_th)s) \
                       '

    check = update_check()
    youtube_main_crawler(**check)
class AuxExample:
    def __init__(self):
        # Selenium headless
        self.crawl = Crawl(delay=20, telegram_notify=False, gui=False)
        self.mysql = MySQL()

    @context_manager
    def transaction(self):
        while True:
            # 데이터베이스에서 URL 가져오기
            url = self.mysql.select_one_for_update()
            if not url:
                break

            # 해당 URL 요청
            html = self.crawl.fetch(url)

            # raw_html 테이블에 업로드
            self.mysql.upload_html(url, html)

        # 정상적으로 완료된 경우 자동으로 인스턴스 종료
        os.system('poweroff')
示例#14
0
def __get_mysql():
    """
    加载MySQL数据库连接池
    """

    if services.mysql is None:
        services.mysql = dict()
        with open('%s/mysql.json' % account_name, 'r') as f:
            mysql_account = json.load(f)
        for name, info in mysql_account.items():
            try:
                services.mysql[name] = MySQL(**info)
            except mysql_repetitive:
                services.logger.exception('MySQL数据库(%s)重复连接!' % name)
示例#15
0
class MySQL_OPS(MySQL):
    def __init__(self, clusterobj, dbname=''):
        self.ip = clusterobj.foreign_ip
        self.user = clusterobj.plat_user
        self.passwd = clusterobj.plat_user_pass
        self.db = dbname
        self.port = int(clusterobj.foreign_port)
        self._sql = MySQL(self.ip, self.port, self.db, self.user, self.passwd)
        # self._conn = self.connect(self.ip, self.port, self.db, self.user, self.passwd)
        # self._cursor = self._conn.cursor()
    def getBinaryLog(self):
        result, col = self._sql.getBinaryLog()
        return result, col

    def getTables(self):
        if self.db:
            sql = 'show tables;'
            rc, rs = self._sql.queryAll(sql=sql)
            return rc, rs
        else:
            rc = 0
            rs = None
            return rc, rs
示例#16
0
文件: fast_sql.py 项目: z00107647/roe
def MyfastSQL_result(request):
    if request.method=='GET':
        id = request.GET['id']
        Myfastsql = MysqlFastSQL.objects.get(id=id)
        sql = request.GET['sql']

        cluster = MySQLCluster.objects.get(id=request.GET['mysqlcluster_id'])
        if Myfastsql.exec_posi == u'从库':
            pass
        else:
            try:
                ip = cluster.foreign_ip
                port = cluster.foreign_port
                user = cluster.plat_user
                arch = cluster.arch
                passwd = cluster.plat_user_pass
                conn = MySQL(ip=ip, port=port, user=user, passwd=passwd)
                count, datalist, colName = conn.execute(sql=sql)
                return render(request, 'dbops/mysql/MysqlfastSQL_result.html', locals())
            except Exception as e:
                colName=[u'报错']
                datalist=[[u'请检查sql语句的错误,查不出来']]
                return render(request, 'dbops/mysql/MysqlfastSQL_result.html', locals())
示例#17
0
import os

from utils.mysql import MySQL
from YoutubeData.YoutubeCrawler import YoutubeCrawler

api_list = [
    os.environ['API_1'], os.environ['API_2'], os.environ['API_3'],
    os.environ['API_4'], os.environ['API_5']
]

conn = MySQL(host=os.environ['host'],
             db=os.environ['db'],
             user=os.environ['user'],
             port=os.environ['port'],
             passwd=os.environ['pw'],
             charset='utf8mb4',
             auto_commit=False)


def th_url_join(video_list, thumb, t):

    for video in video_list:
        th_list = []
        for key in video[thumb].keys():
            th_list.append(video[thumb][key]['url'])
        video[thumb] = ', '.join(th_list)

        video[t] = video[t][:-1]

    return video_list
示例#18
0
class RecoveryExpiredVirtualMoney(object):
    """回调广告商通知激活"""
    def __init__(self):
        self.initdb()
        self.logfileHandle=open(config.LOGPATH + "recovery_expired_virtualmoney_setting.txt", 'a')

    def initdb(self):
        self.db = MySQL(config.MOBGI_HOUSEAD['host'], config.MOBGI_HOUSEAD['user'], config.MOBGI_HOUSEAD['passwd'], port=config.MOBGI_HOUSEAD['port'], db=config.MOBGI_HOUSEAD['db'])
        return self.db

    def mylog(self,msg):
        print time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())+ " " + msg
        self.logfileHandle.write(time.strftime("%Y-%m-%d %H:%M:%S",time.localtime())+" " + msg + '\n')

    def getRows(self,select_sql):
        try:
            self.db.queryNotCatch(select_sql)
        except MySQLdb.Error as m:
            self.mylog("db reconnect!")
            self.initdb()
            self.db.query(select_sql)
        result = self.db.fetchAll();
        return result

    #新增记录
    def insertRows(self,insert_sql):
        try:
            self.db.queryNotCatch(insert_sql)
            return self.db.commit()
        except MySQLdb.Error as m:
            self.mylog("db reconnect!")
            self.initdb()
            self.db.query(insert_sql)
            return self.db.commit()

    #更新记录
    def updateRows(self,update_sql):
        try:
            self.db.queryNotCatch(update_sql)
            return self.db.commit()
        except MySQLdb.Error as m:
            self.mylog("db reconnect!")
            self.initdb()
            self.db.query(update_sql)
            return self.db.commit()

    def getExpiredVirtualdetail(self):
        curtime = str(time.time())
        expired_sql = "select * from advertiser_account_virtual_detail where status='normal' and expire_time <  '" + curtime + "'"
        expired_result = self.getRows(expired_sql)
        return expired_result

    def getAccount(self, uid, account_type):
        account_sql = "select * from advertiser_account_detail where uid= '" + str(uid) + "' and account_type='" + account_type +"'"
        account_result = self.getRows(account_sql)
        if len(account_result) == 0:
            self.mylog('cant get account_result. sql:' + account_sql)
            return False
        else:
            return account_result[0]

    def run(self):
        self.mylog('start RecoveryExpiredVirtualMoney.')

        self.expired_records = self.getExpiredVirtualdetail()

        if len(self.expired_records) == 0:
            self.mylog('len(self.expired_records) == 0')
            
            self.mylog('sleep ' + str(config.SLEEPSECONDS) + ' seconds!\n')
            time.sleep(config.SLEEPSECONDS)
            return False;
        curtime = time.time()
        self.mylog('curtime:' + str(curtime))
        if config.REDIS.has_key('password') is True:
            poolr=redis.ConnectionPool(host=config.REDIS["host"],port=config.REDIS["port"],password=config.REDIS["password"])
        else:
            poolr=redis.ConnectionPool(host=config.REDIS["host"],port=config.REDIS["port"])
        r=redis.Redis(connection_pool=poolr)

        for item in self.expired_records:
            accountdetail = self.getAccount(item['uid'], item['account_type'])
            self.mylog('account detail: '  + 'uid:' + accountdetail['uid'] + ", account_type:" + accountdetail['account_type']+ ", balance:" + accountdetail['balance'])
            self.mylog( 'account virtual detail: ' + 'id:' + item['id'] + ", uid:" + item['uid']+ ", account_type:" + item['account_type']+ ", balance:" + item['balance']+ ", status:" + item['status']+ ", taskdetailid:" + item['taskdetailid'] + ", expire_time:" + item['expire_time'])
            if float(accountdetail['balance'])<float(item['balance']):
                new_balance = 0
                real_fee = float(accountdetail['balance'])
            else:
                new_balance = float(accountdetail['balance']) - float(item['balance'])
                real_fee = float(item['balance'])
            #(1)更改虚拟帐呢余额
            update_account_detail_sql = 'update advertiser_account_detail set balance = "' + str(new_balance) + '" where uid="' + str(accountdetail['uid']) + '" and account_type="' +accountdetail['account_type'] + '"'
            self.updateRows(update_account_detail_sql)
            self.mylog('real_fee:'+ str(real_fee) + ', new_balance:' + str(new_balance) )
            self.mylog('update_account_detail_sql:' + update_account_detail_sql)
            #(2)更改帐户虚拟金过期状态
            update_virtual_detail_sql = 'update advertiser_account_virtual_detail set status = "expired", operator="py_script",update_time="' + str(curtime) + '" where id="' + str(item['id'] + '"')
            self.updateRows(update_virtual_detail_sql)
            self.mylog('update_virtual_detail_sql:' + update_virtual_detail_sql)
            #(3)添加帐户流水
            account_log_value_str = "'" + item['uid'] + "', '" + item['account_type'] + "', 'recovery', '" + str(real_fee) + "', '虚拟金过期回收', '" + str(curtime)+ "'"
            add_account_log_sql = 'insert into advertiser_account_log(uid, account_type, operate_type, trade_balance, description, create_time) value(' +account_log_value_str+ ')'
            self.updateRows('set names utf8;')#处理编码问题
            self.insertRows(add_account_log_sql)
            self.mylog('add_account_log_sql:' + add_account_log_sql)
            #(4)删除帐户余额缓存
            total_balance_rediskey = REDIS.REDIS_CACHEKEY['ACCOUNT_TOTAL_BALANCE'] + '_'+ item['uid']
            r.delete(total_balance_rediskey)
            self.mylog('delete redis cache:' + total_balance_rediskey + "\n")
        self.mylog('end RecoveryExpiredVirtualMoney.')
示例#19
0
 def select(self, table, fields=['*'], where_dict={}, callback=None):
     sql = MySQL.construct_select(table, fields, where_dict)
     return (sql,)
示例#20
0
class AccountConsume(object):
    CHACHE_EPRIE = 900
    """回调广告商通知激活"""
    def __init__(self):
        self.initdb()
        self.logfileHandle = open(config.LOGPATH + "account_consume.txt", 'a')
        self.confname = config.CONSUME_CONF
        self.consume_log_fieldstr = '`uid`,  `account_type`, `balance_before`, `balance`,  `batchdeductionid`,  `price`,  `real_price`, `need_price`,  `create_time`'
        self.CONSUME_LOG_FIELD = "(" + self.consume_log_fieldstr + ")"

    def initdb(self):
        self.houseadDb = MySQL(config.MOBGI_HOUSEAD['host'],
                               config.MOBGI_HOUSEAD['user'],
                               config.MOBGI_HOUSEAD['passwd'],
                               port=config.MOBGI_HOUSEAD['port'],
                               db=config.MOBGI_HOUSEAD['db'])
        self.mobgiCharge = MySQL(config.MOBGI_CHARGE['host'],
                                 config.MOBGI_CHARGE['user'],
                                 config.MOBGI_CHARGE['passwd'],
                                 port=config.MOBGI_CHARGE['port'],
                                 db=config.MOBGI_CHARGE['db'])
        return self.houseadDb, self.mobgiCharge

    def mylog(self, msg):
        print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg
        self.logfileHandle.write(
            time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg +
            '\n')

    def getRows(self, select_sql, db):
        try:
            db.queryNotCatch(select_sql)
        except MySQLdb.Error as m:
            self.mylog("db reconnect!")
            self.initdb()
            db.query(select_sql)
        result = db.fetchAll()
        return result

    def getOne(self, sql, db):
        try:
            db.fetchRow(sql)
        except MySQLdb.Error as m:
            self.mylog("db reconnect!")
            self.initdb()
            db.query(sql)
        result = db.fetchRow()
        return result

    # 新增记录
    def insertRows(self, insert_sql, db):
        self.cur_sql = insert_sql
        try:
            db.queryNotCatch(insert_sql)
            # return self.db.commit()
        except MySQLdb.Error as m:
            self.mylog("db reconnect!")
            self.initdb()
            db.queryNotCatch(insert_sql)
            return db.commit()

    # 更新记录
    def updateRows(self, update_sql, db):
        self.cur_sql = update_sql
        try:
            db.queryNotCatch(update_sql)
        # return self.db.commit()
        except MySQLdb.Error as m:
            self.mylog("db reconnect!")
            self.initdb()
            db.queryNotCatch(update_sql)
        return db.commit()

    def getUidByAdid(self, ad_id):
        ad_sql = "select account_id from delivery_ad_conf_list where id= '" + ad_id + "'"
        ad_result = self.getRows(ad_sql, self.houseadDb)
        if len(ad_result) == 0:
            self.mylog('cant get account_id. sql:' + ad_sql)
            return False
        else:
            return ad_result[0]['account_id']

    def getUidUnitidByOriginalityid(self, originality_id):
        key = config.REDIS_CACHEKEY['ORIGINALITY_INFO'] + originality_id
        if self.redisCache.exists(key) is False:
            sql = "select account_id,unit_id, ad_id from delivery_originality_relation where id= '" + originality_id + "'"
            result = self.getRows(sql, self.houseadDb)
            if len(result) == 0:
                self.mylog('cant get account_id. sql:' + sql)
                return False, False, False
            cacheData = result[0]
            self.redisCache.hmset(key, cacheData)
            self.redisCache.expire(key, self.CHACHE_EPRIE)
        else:
            cacheData = self.redisCache.hgetall(key)
        return cacheData['account_id'], cacheData['unit_id'], cacheData[
            'ad_id']

    def getAccountDayconsumptionlimitByUid(self, uid):
        key = config.REDIS_CACHEKEY['ACCOUNT_CONSUME_LIMIT'] + uid
        if self.redisCache.exists(key) is False:
            sql = "select day_consumption_limit from advertiser_account_consumption_limit where uid= '" + uid + "'"
            result = self.getRows(sql, self.houseadDb)
            if len(result) == 0:
                self.mylog('cant get day_consumption_limit. sql:' + sql)
                return 0
            cacheData = result[0]
            self.redisCache.hmset(key, cacheData)
            self.redisCache.expire(key, self.CHACHE_EPRIE)
        else:
            cacheData = self.redisCache.hgetall(key)
        return cacheData['day_consumption_limit']

    def getUnitInfoByUnitid(self, unit_id):
        # limit_range
        key = config.REDIS_CACHEKEY['UNIT_INFO'] + unit_id
        if self.redisCache.exists(key) is False:
            sql = "select limit_type, limit_range,unit_type from delivery_unit_conf where id= '" + unit_id + "'"
            result = self.getRows(sql, self.houseadDb)
            if len(result) == 0:
                self.mylog('cant get unit_info. sql:' + sql)
                return False
            cacheData = result[0]
            self.redisCache.hmset(key, cacheData)
            self.redisCache.expire(key, self.CHACHE_EPRIE)
        else:
            cacheData = self.redisCache.hgetall(key)
        return cacheData

    def getAdInfoByid(self, adId):
        # limit_range
        key = config.REDIS_CACHEKEY['AD_INFO'] + adId
        if self.redisCache.exists(key) is False:
            sql = "select * from delivery_ad_conf_list where id= '" + adId + "'"
            result = self.getRows(sql, self.houseadDb)
            if len(result) == 0:
                self.mylog('cant get ad_info. sql:' + sql)
                return False
            cacheData = result[0]
            self.redisCache.hmset(key, cacheData)
            self.redisCache.expire(key, self.CHACHE_EPRIE)
        else:
            cacheData = self.redisCache.hgetall(key)
        return cacheData

    def getAccountdetailByUid(self, uid):
        sql = "select * from advertiser_account_detail where uid= '" + uid + "'"
        result = self.getRows(sql, self.houseadDb)
        if len(result) == 0:
            self.mylog('cant get account_detail. sql:' + sql)
            return False
        else:
            retrunData = {}
            for account in result:
                retrunData[account['account_type']] = account
            return retrunData

    def getVirtualAccountdetailByUidType(self, uid, account_type):
        virtual_account_sql = "select * from advertiser_account_virtual_detail where uid= '" + uid + "' and account_type='" + account_type + "' and status='normal' and balance !='0' order by expire_time asc"
        virtual_account_result = self.getRows(virtual_account_sql,
                                              self.houseadDb)
        if len(virtual_account_result) == 0:
            self.mylog('cant get virtual_account_detail. sql:' +
                       virtual_account_sql)
            return False
        else:
            return virtual_account_result

    # 获取帐户所有子帐户的今日消费
    def getAccountDayConsumptions(self, uid, date):
        result = {}
        get_uid_date_consumption_sql = "select * from advertiser_account_day_consumption where uid='" + str(
            uid) + "' and date='" + str(date) + "'"
        day_consumption_result = self.getRows(get_uid_date_consumption_sql,
                                              self.houseadDb)
        if len(day_consumption_result) == 0:
            self.mylog('cant get day_consumption_result. sql:' +
                       get_uid_date_consumption_sql)
            return result
        else:
            for consumption_item in day_consumption_result:
                result[consumption_item['account_type']] = consumption_item
            return result

    # 获取指定子帐户的日消耗额
    def getAccountUidAccounttypeDayConsumption(self, uid, account_type, date):
        get_uid_accounttype_date_consumption_sql = "select * from advertiser_account_day_consumption where uid='" + str(
            uid) + "' and account_type='" + str(
                account_type) + "' and date='" + str(date) + "'"
        day_consumption_result = self.getRows(
            get_uid_accounttype_date_consumption_sql, self.houseadDb)
        if len(day_consumption_result) == 0:
            self.mylog(
                'cant get uid_accounttype_day_consumption_result. sql:' +
                get_uid_accounttype_date_consumption_sql)
            return False
        else:
            return day_consumption_result[0]['consumption']

    # 获取指定帐号的今日消费总额
    def getUidConsumptiontotal(self, uid, date):
        day_consumption_results = self.getAccountDayConsumptions(uid, date)
        if len(day_consumption_results) == 0:
            consumption = 0
        else:
            consumption = 0
            for consumption_item in day_consumption_results:
                consumption += float(
                    day_consumption_results[consumption_item]['consumption'])
        return consumption

    def getUnitOriginalityDayConsumption(self, unit_id, originality_id, date):
        sql = "select * from advertiser_unit_originality_day_consumption where unit_id='" + str(
            unit_id) + "' and originality_id='" + str(
                originality_id) + "' and date='" + str(date) + "'"
        results = self.getRows(sql, self.houseadDb)
        if len(results) == 0:
            self.mylog(
                'cant get unit_originality_day_consumption_result. sql:' + sql)
            return False
        else:
            return results[0]['consumption']

    def getUnitConsumptiontotal(self, unit_id, date):
        results = {}
        sql = "select * from advertiser_unit_originality_day_consumption where unit_id='" + str(
            unit_id) + "' and date='" + str(date) + "'"
        results = self.getRows(sql, self.houseadDb)
        if len(results) == 0:
            consumption = 0
        else:
            consumption = 0
            for item in results:
                consumption += float(item['consumption'])
        return consumption

    def getAdInfoConsumptiontotal(self, adId, date):
        results = {}
        sql = "select * from advertiser_unit_originality_day_consumption where ad_id='" + str(
            adId) + "' and date='" + str(date) + "'"
        results = self.getRows(sql, self.houseadDb)
        if len(results) == 0:
            consumption = 0
        else:
            consumption = 0
            for item in results:
                consumption += float(item['consumption'])
        return consumption

    # 虚拟金详情扣费(同步扣除虚拟帐号详情的金额,优先扣除到期时间最近的虚拟金.)
    def updateVirtualAccountDetail(self, uid, account_type, real_fee):
        if account_type != 'redisCache':
            virtual_account_details = self.getVirtualAccountdetailByUidType(
                uid, account_type)
            if virtual_account_details != False:
                need_virtual_fee = float(real_fee)
                for virtual_item in virtual_account_details:
                    if float(
                            virtual_item['balance']) > float(need_virtual_fee):
                        real_virtual_fee = need_virtual_fee
                        new_virtual_balance = float(
                            virtual_item['balance']) - real_virtual_fee
                        need_virtual_fee = 0
                        update_virtual_account_sql = "update advertiser_account_virtual_detail set balance='" + str(
                            new_virtual_balance
                        ) + "' where  uid='" + str(
                            uid
                        ) + "' and account_type='" + account_type + "' and taskdetailid='" + virtual_item[
                            'taskdetailid'] + "'"
                    else:
                        real_virtual_fee = float(virtual_item['balance'])
                        new_virtual_balance = 0
                        need_virtual_fee = need_virtual_fee - real_virtual_fee
                        update_virtual_account_sql = "update advertiser_account_virtual_detail set balance='" + str(
                            new_virtual_balance
                        ) + "', status='runout' where  uid='" + str(
                            uid
                        ) + "' and account_type='" + account_type + "' and taskdetailid='" + virtual_item[
                            'taskdetailid'] + "'"
                    self.updateRows(update_virtual_account_sql, self.houseadDb)
                    self.mylog('uid:' + str(uid) + ", " + 'account_type:' +
                               account_type + ", " + 'taskdetailid:' +
                               virtual_item['taskdetailid'] + ', balance: ' +
                               str(new_virtual_balance) +
                               ',current real_virtual_fee:' +
                               str(real_virtual_fee) + ', need_virtual_fee:' +
                               str(need_virtual_fee))
                    self.mylog(update_virtual_account_sql)
                    if need_virtual_fee == 0:
                        break

    # 更新具体子帐号的日消耗
    def updateAccountDayConsumption(self, uid, date, account_type, real_fee):
        old_accounttype_consumption = self.getAccountUidAccounttypeDayConsumption(
            uid, account_type, date)
        if old_accounttype_consumption != False:
            new_accounttype_consumption = float(
                old_accounttype_consumption) + real_fee
            update_uid_date_consumption_sql = "update advertiser_account_day_consumption set consumption='" + str(
                new_accounttype_consumption) + "', update_time='" + str(
                    self.curtime) + "' where uid='" + str(
                        uid) + "' and account_type='" + str(
                            account_type) + "' and date='" + str(date) + "'"
            self.updateRows(update_uid_date_consumption_sql, self.houseadDb)
            self.mylog(update_uid_date_consumption_sql)
        else:
            new_accounttype_consumption = real_fee
            add_uid_date_consumption_sql = "insert into advertiser_account_day_consumption" \
                                           "(`uid`, `account_type`, `date`, `consumption`, `create_time`, `update_time`) " \
                                           "value" \
                                           "('" + str(uid) + "','" + account_type + "', '" + str(date) + "', '" + str(
                new_accounttype_consumption) + "','" + str(self.curtime) + "','" + str(self.curtime) + "')"
            self.insertRows(add_uid_date_consumption_sql, self.houseadDb)
            self.mylog(add_uid_date_consumption_sql)
        self.mylog('uid:' + str(uid) + ", date:" + str(date) +
                   ", account_type:" + str(account_type) +
                   ", account_consumption:" + str(new_accounttype_consumption))

    def updateAdDayConsumption(self, unit_id, ad_id, date, originality_id,
                               real_fee):
        old_originality_consumption = self.getUnitOriginalityDayConsumption(
            unit_id, originality_id, date)
        if old_originality_consumption != False:
            new_originality_consumption = float(
                old_originality_consumption) + real_fee
            update_unit_date_consumption_sql = "update advertiser_unit_originality_day_consumption set consumption='" + str(
                new_originality_consumption) + "', update_time='" + str(
                    self.curtime) + "' where unit_id='" + str(
                        unit_id) + "' and originality_id='" + str(
                            originality_id) + "' and date='" + str(date) + "'"
            self.updateRows(update_unit_date_consumption_sql, self.houseadDb)
            self.mylog(update_unit_date_consumption_sql)
        else:
            new_originality_consumption = real_fee
            add_unit_date_consumption_sql = "insert into advertiser_unit_originality_day_consumption" \
                                            "(`unit_id`, `ad_id`, `originality_id`, `date`, `consumption`, `create_time`, `update_time`) " \
                                            "value" \
                                            "('" + str(unit_id) + "','" + ad_id + "','" + originality_id + "', '" + str(
                date) + "', '" + str(new_originality_consumption) + "','" + str(self.curtime) + "','" + str(
                self.curtime) + "')"
            self.insertRows(add_unit_date_consumption_sql, self.houseadDb)
            self.mylog(add_unit_date_consumption_sql)
        self.mylog('unit_id:' + str(unit_id) + ", date:" + str(date) +
                   ", originality_id:" + str(originality_id) +
                   ", account_consumption:" + str(new_originality_consumption))

    def getLastConfigId(self):
        cf = self.getConfigObject()
        minId = cf.getint("account_consume", 'batchid')
        return minId

    def getConfigObject(self):
        # 读取配置中已经处理过了的processid
        cf = ConfigParser.ConfigParser()
        cf.read(config.CONSUME_CONF)
        return cf

    def getChargeDataCurrentId(self, lastId):
        sql = 'select * from adx_charge_minute where id > ' + str(
            lastId) + ' order by id desc limit 1'
        result = self.getRows(sql, self.mobgiCharge)
        if len(result) <= 0:
            return 0
        return result[0]['id']

    def initRedis(self):
        if config.REDIS.has_key('password') is True:
            poolr = redis.ConnectionPool(host=config.REDIS["host"],
                                         port=config.REDIS["port"],
                                         password=config.REDIS["password"])
        else:
            poolr = redis.ConnectionPool(host=config.REDIS["host"],
                                         port=config.REDIS["port"])
        self.redisCache = redis.Redis(connection_pool=poolr)

    def getChargeDataList(self, lastId, currentId):
        sql = 'select * from adx_charge_minute where id > ' + str(
            lastId) + ' and id<=' + str(
                currentId) + ' order by id asc limit ' + str(
                    config.PROCESS_BATCH_NUM)
        result = self.getRows(sql, self.mobgiCharge)
        return result

    def updateMinuteCharge(self, is_mobgi, is_charged, id):
        sql = "update adx_charge_minute set is_charged= '" + str(
            is_charged) + "' ,is_mobgi ='" + str(
                is_mobgi) + "' ,update_time='" + str(
                    self.curDatetime) + "' where id='" + str(id) + "'"
        self.updateRows(sql, self.mobgiCharge)
        self.mylog(sql)

    def run(self):
        self.mylog('start AccountConsume.')
        lastId = self.getLastConfigId()
        currentId = self.getChargeDataCurrentId(lastId)
        # print new_result
        if currentId <= 0:
            self.mylog("len(news_result) = 0")
            self.mylog('sleep ' + str(config.SLEEPSECONDS) + ' seconds.')
            self.mylog('end AccountConsume. \n')
            time.sleep(config.SLEEPSECONDS)
            return True

        while (int(lastId) < int(currentId)):
            self.initRedis()
            num = 0
            chargeDataList = self.getChargeDataList(lastId, currentId)
            originalityIdReluidList = {}
            originalityIdRelUnitIdList = {}
            originalityIdRelAdIdList = {}
            accountConsumptionLimitList = {}  # 今日消耗限额
            accountConsumptionList = {}  # 今日消耗
            unitInfo = {}
            adInfo = {}
            unitIdConsumptionList = {}
            adInfoConsumptionList = {}

            for item in chargeDataList:
                self.cur_sql = 'none'
                try:
                    # 预防多次扣除
                    if item['is_charged'] == '1':
                        self.mylog('id:' + str(item['id'] + ', is_charged:' +
                                               item['is_charged']) +
                                   ', continue!')
                        continue

                    # 定位上报数据的帐号id
                    if originalityIdReluidList.has_key(item['originality_id']):
                        uid = originalityIdReluidList[item['originality_id']]
                        unitId = originalityIdRelUnitIdList[
                            item['originality_id']]
                        adId = originalityIdRelAdIdList[item['originality_id']]
                    else:
                        uid, unitId, adId = self.getUidUnitidByOriginalityid(
                            item['originality_id'])
                        if uid != False:
                            originalityIdReluidList[
                                item['originality_id']] = uid
                            originalityIdRelUnitIdList[
                                item['originality_id']] = unitId
                            originalityIdRelAdIdList[
                                item['originality_id']] = adId
                        else:
                            self.mylog('uid unit_id ad_id is False, continue!')
                            continue

                    # 获取账号消费限制
                    if accountConsumptionLimitList.has_key(uid):
                        accountConsumptionLimit = accountConsumptionLimitList[
                            uid]
                    else:
                        accountConsumptionLimit = self.getAccountDayconsumptionlimitByUid(
                            uid)
                        accountConsumptionLimitList[
                            uid] = accountConsumptionLimit

                    # 获取单元信息
                    if unitInfo.has_key(unitId):
                        unitIdConsumptionLimit = unitInfo[unitId][
                            'limit_range']
                    else:
                        unitInfoCacheData = self.getUnitInfoByUnitid(unitId)
                        if unitInfoCacheData != False:
                            unitInfo[unitId] = unitInfoCacheData
                            unitIdConsumptionLimit = unitInfoCacheData[
                                'limit_range']
                        else:
                            self.mylog('unitId:' + str(unitId) +
                                       ', unitInfo is False, continue!')
                            continue
                    # 获取广告信息todo
                    # 获取单元信息
                    if adInfo.has_key(adId) is False:
                        adInfoInfoCacheData = self.getAdInfoByid(adId)
                        if adInfoInfoCacheData != False:
                            adInfo[adId] = adInfoInfoCacheData
                        else:
                            self.mylog('adId:' + str(adId) +
                                       ', adInfo is False, continue!')
                            continue

                    # 获取帐号信息(需要锁表)
                    accountDetailInfo = self.getAccountdetailByUid(uid)
                    if accountDetailInfo == False:
                        self.mylog('accountDetailInfo is False, continue!')
                        continue

                    # 账号的总金额
                    accountTotalAmount = 0.0
                    # print accountDetailInfo
                    for accounType in config.DEDUCTION_ORDER:
                        if accountDetailInfo.has_key(accounType):
                            accountTotalAmount += float(
                                accountDetailInfo[accounType]['balance'])
                    self.mylog('uid:' + str(uid) + "," +
                               'accountTotalAmount:' +
                               str(accountTotalAmount) +
                               ', deductibleAmount = ' + str(item['amount']))
                    if accountTotalAmount == 0:
                        self.mylog('关闭广告\n')
                        continue

                    # 定位日期
                    startTimeStamp = int(
                        time.mktime(
                            time.strptime(item['create_time'][0:10],
                                          "%Y-%m-%d")))
                    date = time.strftime("%Y%m%d",
                                         time.localtime(startTimeStamp))
                    self.curtime = str(time.time())
                    self.curDatetime = time.strftime("%Y-%m-%d %H:%M:%S")

                    # 内外部标志
                    isMobgi = int(unitInfo[unitId]['unit_type']) - 1
                    self.updateMinuteCharge(isMobgi, 0, item['id'])

                    # 账号每天的消耗
                    if accountConsumptionList.has_key(uid):
                        accountDayConsumption = accountConsumptionList[uid]
                    else:
                        accountDayConsumption = self.getUidConsumptiontotal(
                            uid, date)
                        accountConsumptionList[uid] = accountDayConsumption

                    if float(accountConsumptionLimit) != 0.0 and float(
                            accountDayConsumption) >= float(
                                accountConsumptionLimit):
                        self.mylog('accountConsumptionLimit:' +
                                   str(accountConsumptionLimit) +
                                   ', accountDayConsumption:' +
                                   str(accountDayConsumption))
                        self.mylog('\n')
                        continue

                    # 广告计划每天消耗
                    if unitIdConsumptionList.has_key(unitId):
                        unitIdDayConsumption = unitIdConsumptionList[unitId]
                    else:
                        unitIdDayConsumption = self.getUnitConsumptiontotal(
                            unitId, date)
                        unitIdConsumptionList[unitId] = unitIdDayConsumption
                    # 达到限制金额
                    if unitInfo[unitId]['limit_type'] == '1' and float(
                            unitIdDayConsumption) >= float(
                                unitIdConsumptionLimit):
                        self.mylog('unitIdDayConsumption:' +
                                   str(unitIdDayConsumption) +
                                   ', unitIdConsumptionLimit:' +
                                   str(unitIdConsumptionLimit))
                        self.mylog('\n')
                        continue

                    # 广告活动每天消耗
                    if adInfoConsumptionList.has_key(adId):
                        adInfoDayConsumption = adInfoConsumptionList[adId]
                    else:
                        adInfoDayConsumption = self.getAdInfoConsumptiontotal(
                            adId, date)
                        adInfoConsumptionList[adId] = adInfoDayConsumption

                    # 达到限制金额
                    if adInfo[adId]['ad_limit_type'] == '1' and float(
                            adInfoDayConsumption) >= float(
                                adInfo[adId]['ad_limit_amount']):
                        self.mylog('adInfoDayConsumption:' +
                                   str(adInfoDayConsumption) +
                                   ', adInfoConsumptionLimit:' +
                                   str(adInfo[adId]['ad_limit_amount']))
                        self.mylog('\n')
                        continue

                    # 应扣的总金额,应扣金额
                    deductibleTotalAmount = 0
                    deductibleAmount = float(item['amount'])
                    consume_log_sql_valuelist = []
                    for accounType in config.DEDUCTION_ORDER:
                        if accountDetailInfo.has_key(accounType):
                            if float(accountDetailInfo[accounType]
                                     ['balance']) == 0:
                                self.mylog('uid:' + str(uid) + ", " +
                                           'account_type:' + accounType +
                                           ', balance = 0, continue')
                                continue
                            self.mylog(
                                'uid:' + str(uid) + ", " + 'account_type:' +
                                accounType + ', balance: ' +
                                accountDetailInfo[accounType]['balance'])
                            if float(accountDetailInfo[accounType]
                                     ['balance']) > deductibleAmount:
                                # 当次扣费金额
                                curDeductibleAmount = deductibleAmount
                                deductibleTotalAmount += curDeductibleAmount
                                # 当前的余额
                                curBalance = float(
                                    accountDetailInfo[accounType]
                                    ['balance']) - curDeductibleAmount
                                deductibleAmount = 0
                            else:
                                curDeductibleAmount = float(
                                    accountDetailInfo[accounType]['balance'])
                                deductibleTotalAmount += curDeductibleAmount
                                curBalance = 0
                                deductibleAmount = deductibleAmount - curDeductibleAmount
                            update_account_sql = "update advertiser_account_detail set balance='" + str(
                                curBalance) + "' where  uid='" + str(
                                    uid
                                ) + "' and account_type='" + accounType + "'"
                            consume_log_sql_valueitem = "('" + str(
                                uid
                            ) + "', '" + accounType + "', '" + accountDetailInfo[
                                accounType]['balance'] + "', '" + str(
                                    curBalance
                                ) + "', '" + item['id'] + "', '" + item[
                                    'amount'] + "', '" + str(
                                        curDeductibleAmount) + "', '" + str(
                                            deductibleAmount) + "', '" + str(
                                                self.curtime) + "')"
                            consume_log_sql_valuelist.append(
                                consume_log_sql_valueitem)

                            self.updateRows(update_account_sql, self.houseadDb)
                            self.mylog('uid:' + str(uid) + ", " +
                                       'account_type:' + accounType +
                                       ', curBalance: ' + str(curBalance) +
                                       ', deductibleTotalAmount: ' +
                                       str(deductibleTotalAmount) +
                                       ',curDeductibleAmount:' +
                                       str(curDeductibleAmount) +
                                       ', deductibleAmount:' +
                                       str(deductibleAmount))
                            self.mylog(update_account_sql)

                            # 同步扣除虚拟帐号详情的金额,优先扣除到期时间最近的虚拟金.
                            self.updateVirtualAccountDetail(
                                uid, accounType, curDeductibleAmount)

                            # 更新日消耗数据
                            # (1.1)更新db日消耗数据
                            self.updateAccountDayConsumption(
                                uid, date, accounType, curDeductibleAmount)
                            # (1.2)更新db下的单元创意的消耗数据
                            self.updateAdDayConsumption(
                                unitId, adId, date, item['originality_id'],
                                curDeductibleAmount)

                            if deductibleAmount == 0:
                                break
                        else:
                            continue

                    # 更新redis缓存,帐号余额
                    accountTotalBalance = accountTotalAmount - deductibleTotalAmount
                    accountTotalBalanceRedisKey = config.REDIS_CACHEKEY[
                        'ACCOUNT_TOTAL_BALANCE'] + '_' + str(uid)
                    self.redisCache.set(accountTotalBalanceRedisKey,
                                        accountTotalBalance)
                    self.redisCache.expire(accountTotalBalanceRedisKey, 600)

                    # 更新批量数据的是否外部订单状态,是否扣费过
                    self.updateMinuteCharge(isMobgi, 1, item['id'])

                    # (2.1)更新redis缓存,日账号消耗数据
                    currentAccountDayConsumption = accountDayConsumption + float(
                        deductibleTotalAmount)
                    accountConsumptionList[uid] = currentAccountDayConsumption
                    accountDayConsumptionRediskey = config.REDIS_CACHEKEY[
                        'ACCOUNT_DAY_CONSUMPTION'] + '_' + str(
                            uid) + '_' + str(date)
                    self.redisCache.set(accountDayConsumptionRediskey,
                                        currentAccountDayConsumption)
                    self.redisCache.expire(accountDayConsumptionRediskey, 600)
                    self.mylog('uid:' + str(uid) + ", date:" + str(date) +
                               ", consumption:" +
                               str(currentAccountDayConsumption))

                    # (2.2)更新redis单元日消耗数据
                    curUnitIdConsumption = unitIdDayConsumption + float(
                        deductibleTotalAmount)
                    unitIdConsumptionList[unitId] = curUnitIdConsumption
                    unitIdDayConsumptionRedisKey = config.REDIS_CACHEKEY[
                        'UNIT_DAY_CONSUMPTION'] + '_' + str(
                            unitId) + '_' + str(date)
                    self.redisCache.set(unitIdDayConsumptionRedisKey,
                                        curUnitIdConsumption)
                    self.redisCache.expire(unitIdDayConsumptionRedisKey, 600)
                    self.mylog('unitId:' + str(unitId) + ", date:" +
                               str(date) + ", consumption:" +
                               str(curUnitIdConsumption))

                    curAdinfoConsumption = adInfoDayConsumption + float(
                        deductibleTotalAmount)
                    adInfoConsumptionList[adId] = curAdinfoConsumption
                    adInfoDayConsumptionRedisKey = config.REDIS_CACHEKEY[
                        'ADINFO_DAY_CONSUMPTION'] + '_' + str(
                            adId) + '_' + str(date)
                    self.redisCache.set(adInfoDayConsumptionRedisKey,
                                        curAdinfoConsumption)
                    self.redisCache.expire(adInfoDayConsumptionRedisKey, 600)
                    self.mylog('adId:' + str(adId) + ", date:" + str(date) +
                               ", consumption:" + str(curAdinfoConsumption))

                    # 添加各个帐号消耗日志
                    if len(consume_log_sql_valuelist) != 0:
                        consume_log_sql_valuestr = ','.join(
                            consume_log_sql_valuelist)
                        consume_log_sql = 'insert into advertiser_account_consume_log ' + self.CONSUME_LOG_FIELD + 'values' + consume_log_sql_valuestr + ';'
                        self.insertRows(consume_log_sql, self.houseadDb)

                    # 提交db操作的增,改
                    self.houseadDb.commit()
                    self.mobgiCharge.commit()
                except MySQLdb.Error as e:
                    self.mylog('exception: ' + "Mysql Error %d: %s" %
                               (e.args[0], e.args[1]))
                    self.mylog('sql:' + self.cur_sql)
                    self.mylog('rollback now!')
                    self.houseadDb.rollback()
                    self.mobgiCharge.rollback()

                if accountTotalAmount - float(item['amount']) <= 0:
                    # need to do
                    self.mylog('关闭广告\n')

                self.mylog('finish\n')
                # print account_detail_info

            lastId = int(item['id'])
            cf = self.getConfigObject()
            cf.set("account_consume", 'batchid', lastId)
            cf.write(open(self.confname, "w"))
        self.mylog('end AccountConsume.')
 def __init__(self):
     # Selenium headless
     self.crawl = Crawl(delay=20, telegram_notify=False, gui=False)
     self.mysql = MySQL()
示例#22
0
 def update(self, table, update_dict, where_dict={}, callback=None):
     sql = MySQL.construct_update(table, update_dict, where_dict)
     return (sql, )
示例#23
0
class ImportDirect(object):
    """回调广告商通知激活"""
    def __init__(self, filename, filetype):
        self.initdb()
        self.logfileHandle = open(config.LOGPATH + "import_direct.txt", 'a')
        self.import_direct_fieldstr = '`imei`,  `appkey`, `app_interest`, `pay_ability`,  `game_frequency`'
        self.filename = filename
        self.filetype = filetype

    def initdb(self):
        self.db = MySQL(config.MOBGI_HOUSEAD['host'],
                        config.MOBGI_HOUSEAD['user'],
                        config.MYSQL['passwd'],
                        port=config.MOBGI_HOUSEAD['port'],
                        db=config.MOBGI_HOUSEAD['db'])
        return self.db

    def mylog(self, msg):
        print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg
        self.logfileHandle.write(
            time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg +
            '\n')

    def getRows(self, select_sql):
        try:
            self.db.queryNotCatch(select_sql)
        except MySQLdb.Error as m:
            self.mylog("db reconnect!")
            self.initdb()
            self.db.query(select_sql)
        result = self.db.fetchAll()
        return result

    #新增记录
    def insertRows(self, insert_sql):
        self.cur_sql = insert_sql
        try:
            self.db.queryNotCatch(insert_sql)
            return self.db.commit()
        except MySQLdb.Error as m:
            self.mylog("db reconnect!")
            self.initdb()
            self.db.queryNotCatch(insert_sql)
            return self.db.commit()

    def run(self):
        self.mylog('start ImportDirect.')
        appkey = config.APPKEY[self.filetype]
        self.mylog('filename:' + self.filename)
        data = xlrd.open_workbook(self.filename)
        table = data.sheets()[0]
        nrows = table.nrows  #行数
        ncols = table.ncols  #列数
        print nrows, ncols
        for i in range(0, nrows):
            imei = table.cell(i, 0).value
            if type(imei) is types.FloatType:  #科学计数转成int型
                imei = int(imei)
            interest = table.cell(i, 1).value
            user_type = table.cell(i, 2).value
            pay_type = table.cell(i, 3).value

            for key in config.INTEREST:
                # print key, config.INTEREST[key]
                interest = interest.replace(str(key),
                                            str(config.INTEREST[key]))

            select_sql = 'select * from delivery_device_direct where imei="' + str(
                imei) + '" and appkey= "' + appkey + '"'
            result = self.getRows(select_sql)
            if len(result):
                self.mylog('imei:' + str(imei) + ', appkey:' + appkey +
                           " exist!")
            else:
                curTime = time.time()
                insert_sql = 'insert into delivery_device_direct(`imei`, `appkey`, `app_interest`, `pay_ability`, `game_frequency`, `create_time`, `update_time`) value("' + str(
                    imei) + '", "' + str(appkey) + '","' + str(
                        interest) + '","' + str(pay_type) + '","' + str(
                            user_type) + '","' + str(curTime) + '","' + str(
                                curTime) + '");'
                print insert_sql
                self.insertRows(insert_sql)
            print i

        self.mylog('end ImportDirect.')
示例#24
0
 def delete(self, table, where_dict={}, callback=None):
     sql = MySQL.construct_delete(table, where_dict)
     return (sql,)
class ThirdReportRecordStore(object):
    def __init__(self):
        # 数据库参数
        self.host = third_party_setting.MYSQL_REQUST['host']
        self.user = third_party_setting.MYSQL_REQUST['user']
        self.passwd = third_party_setting.MYSQL_REQUST['passwd']
        self.port = third_party_setting.MYSQL_REQUST['port']
        self.db = third_party_setting.MYSQL_REQUST['db']
        self.table = third_party_setting.MYSQL_REQUST['table']
        # 文件存放路径
        self.sqlpath = os.path.join(os.getcwd(),
                                    third_party_setting.SQLFILEPATH)
        self.fieldlist = third_party_setting.FIELD_LIST

    # 不用logging日志是因为上个目录已经使用logging了,非继承关系被覆盖
    def mylog(self, msg):
        print time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg
        self.logfileHandle.write(
            time.strftime("%Y-%m-%d %H:%M:%S", time.localtime()) + " " + msg +
            '\n')

    # 重连db
    def trysql(self, sql):
        try:
            self.mylog("select sql:" + sql)
            self.clickdb.queryNotCatch(sql)
        except MySQLdb.Error as m:
            self.mylog('masterdb reconnecting now')
            self.clickdb = MySQL(self.host,
                                 self.user,
                                 self.passwd,
                                 port=self.port,
                                 db=self.db)
            self.clickdb.query(sql)
        return self.clickdb.fetchAll()

    def saveDataToFile(self, streamlist):
        try:
            MIN = time.strftime("%Y%m%d%H%M", time.localtime())
            cf = ConfigParser.ConfigParser()
            confname = 'third_report_record.conf'
            cf.read(confname)
            curtable = 'record_id'
            reocrd_id = cf.getint("third_report_data", curtable)
            reocrd_id = int(reocrd_id)
            #按年_月_日_时_分创建文件,一分钟一个文件
            self.filename = self.sqlpath + "sql_content_" + MIN + ".sql"
            file_object = codecs.open(self.filename, 'a', 'utf-8')
            for stream in streamlist:
                print stream
                sqlcontent = ''
                valueStr = ""
                # 增加id
                valueStr += str(reocrd_id) + '\t'
                reocrd_id += 1
                for field_key in self.fieldlist:
                    if stream.has_key(field_key):
                        valueStr += str(stream[field_key]) + '\t'
                    else:
                        valueStr += "" + '\t'
                sqlcontent += valueStr.strip('\t') + "\n"
                print sqlcontent
                if sqlcontent != "":
                    file_object.write(sqlcontent)
            file_object.close()
            cf.set("third_report_data", curtable, reocrd_id)
            cf.write(open(confname, "w"))
        except Exception, e:
            self.mylog("step1 save data to file failed! error:" + str(e))
示例#26
0
 def count(self, table, where_dict={}, callback=None):
     sql = MySQL.construct_count(table, where_dict)
     return (sql,)
示例#27
0
    def mysql_update_instance(self):

        if self.arch == '主从':
            rs = self._conn.getSlaveIP()
            conn_slave = MySQL(ip=rs[1],
                               port=self.port,
                               user=self.user,
                               passwd=self.passwd)
            slavestatus = conn_slave.getReplStatus()  #嗲用的是show slave status
            myinstance = self._cluster.mysql_instance
            if slavestatus[3] == self.port:  #代表主从是一致的
                slave = myinstance.get_or_create(dbcluster_id=self.cluster_id,
                                                 m_ip=rs[1],
                                                 port=self.port)
                slave[0].master_ip = slavestatus[1]
                slave[0].master_port = slavestatus[3]
                slave[0].role = u'从库'
                # slave[0].db_status=u'仅从库'
                slave[0].save()
                master = myinstance.get_or_create(dbcluster_id=self.cluster_id,
                                                  m_ip=slavestatus[1],
                                                  port=self.port)
                master[0].vist_ip = self.ip
                master[0].role = u'主库'
                master[0].db_status = u'服务中'
                master[0].save()
            else:
                print(u'主从添加实例不对阿')
        elif self.arch == u'单库':
            myinstance = self._cluster.mysql_instance
            single = myinstance.get_or_create(dbcluster_id=self.cluster_id,
                                              m_ip=self.ip,
                                              port=self.port)
            single[0].vist_ip = self.ip
            single[0].role = u'单库'
            single[0].db_status = u'服务中'
            single[0].save()
        elif self.arch == u'innodb_cluster':
            count, result, columns = self._conn.getInnoClusterInstance()
            myinstance = self._cluster.mysql_instance
            masterip = ''
            master_port = ''
            for i in range(count):
                ip = result[i][2].split(':')[0]

                port = result[i][1]
                role_0 = result[i][3]
                db_status = result[i][4]

                clusertI = myinstance.get_or_create(
                    dbcluster_id=self.cluster_id, m_ip=ip, port=port)
                clusertI[0].vist_ip = ip
                if role_0 == u'PRIMARY':
                    clusertI[0].role = u'主库'
                    masterip = ip
                    master_port = port
                else:
                    clusertI[0].role = u'从库'
                    clusertI[0].master_ip = masterip
                    clusertI[0].master_port = master_port
                if db_status == u'ONLINE':
                    clusertI[0].db_status = u'服务中'
                else:
                    clusertI[0].db_status = u'故障'

                clusertI[0].save()

        return True
示例#28
0
 def insert(self, table, value_dict, callback=None):
     sql = MySQL.construct_insert(table, value_dict)
     return (sql,)
示例#29
0
 def __init__(self, *args, **kwargs):
     self._mysql = MySQL(**kwargs["db_config"])
     super(MySQLTask,self).__init__(*args, **kwargs)
     self.daemon = True
示例#30
0
class MySQL_update(object):
    def __init__(self, clusterid):
        self._cluster = MySQLCluster.objects.get(id=clusterid)
        self.cluster_id = clusterid
        self.ip = self._cluster.foreign_ip
        self.port = self._cluster.foreign_port
        self.user = self._cluster.plat_user
        self.arch = self._cluster.arch
        self.passwd = self._cluster.plat_user_pass
        self._conn = MySQL(ip=self.ip,
                           port=self.port,
                           user=self.user,
                           passwd=self.passwd)

    def mysql_update_db(self):
        #取出数据库信息
        sql = '''
        select
        table_schema as 'databases',
        sum(table_rows) as 'allrows',
        sum(truncate(data_length / 1024 / 1024, 2)) as 'dbsize(MB)',
        sum(truncate(index_length / 1024 / 1024, 2)) as 'indsize(MB)'
        from information_schema.tables where TABLE_SCHEMA not in (
        'mysql','sys','information_schema','performance_schema')
        group  by table_schema  order
        by  sum(data_length)  desc, sum(index_length) desc;
        '''
        rc, rs = self._conn.queryAll(sql=sql)
        mysqldb = self._cluster.mysql_db

        for ii in rs:  #获取用户,更新用户
            aa = mysqldb.get_or_create(dbname=ii[0],
                                       dbcluster_id=self.cluster_id)
            # aa[0].objects.update(db_rows=ii[1],db_size=ii[2],index_size=ii[3])
            aa[0].db_rows = ii[1]
            aa[0].db_size = ii[2]
            aa[0].index_size = ii[3]
            aa[0].save()

        return True

    def mysql_update_user(self):
        #取出用户信息,这里过滤DBA使用的用户
        sql = '''
        select 
        user,
        host from mysql.user where user not in('root','dba','backup','monitor','slave') and user not like 'mysql%' and user not like 'repli%';
        '''
        rc, rs = self._conn.queryAll(sql=sql)
        mysql_user = self._cluster.mysql_user
        for ii in rs:
            sql2 = "show grants for %s@`%s`;" % (ii[0], ii[1])
            rc2, rs2 = self._conn.queryAll(sql=sql2)
            priv = ''
            for jj in rs2:
                priv = priv + jj[0] + ' ;  '
            aa = mysql_user.get_or_create(db_user=ii[0],
                                          db_host=ii[1],
                                          dbcluster_id=self.cluster_id)
            aa[0].privlige = priv
            aa[0].save()

        return True

#

    def mysql_update_instance(self):

        if self.arch == '主从':
            rs = self._conn.getSlaveIP()
            conn_slave = MySQL(ip=rs[1],
                               port=self.port,
                               user=self.user,
                               passwd=self.passwd)
            rs2 = conn_slave.getReplStatus()
            myinstance = self._cluster.mysql_instance
            if rs2[3] == self.port:  #代表主从是一致的
                slave = myinstance.get_or_create(dbcluster_id=self.cluster_id,
                                                 m_ip=rs[1],
                                                 port=self.port)
                slave[0].master_ip = rs2[1]
                slave[0].master_port = rs2[3]
                slave[0].role = u'从库'
                # slave[0].db_status=u'仅从库'
                slave[0].save()
                master = myinstance.get_or_create(dbcluster_id=self.cluster_id,
                                                  m_ip=rs2[1],
                                                  port=self.port)
                master[0].vist_ip = self.ip
                master[0].role = u'主库'
                master[0].db_status = u'服务中'
                master[0].save()

        return True
示例#31
0
#!coding=utf8

import time
import io
import utils.datetime as datetime
from utils.mysql import MySQL

pds = []
dns_db = MySQL(host="127.0.0.1",
               user="******",
               passwd="rootofmysql",
               port=3307,
               db="IPCIS_DNS_DB")


def read_data():
    # 从文件中读取恶意域名信息
    with io.open("../data/3mths.txt", "r", encoding="latin1") as f:
        for line in f.readlines():
            temp = line.strip().split("=")
            temp[0] = temp[0].strip()
            pds.append(temp[0])


def create_db():
    sql = (
        "CREATE TABLE IF NOT EXISTS domain_static ( domain_id INT(10) UNSIGNED NOT NULL DEFAULT 0,"
        "primary_domain VARCHAR(128) NOT NULL DEFAULT '', "
        "is_dga INT(10) UNSIGNED NOT NULL, "
        "ttl INT(10) UNSIGNED NOT NULL, "
        "credit INT(10) UNSIGNED, "