Python DBConnection.query方法代码示例

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def add_timestamp():
    db = DBConnection().db
    redis = MyRedis()
    
    e_sql = 'select id, tid, timestamp, lid from T_EVENT where timestamp = 0 order by id desc  limit 3000000'
    #e_sql = 'select id, tid, timestamp, lid from T_EVENT where timestamp = 0 '
    #e_sql = 'select id, tid, timestamp, lid from T_EVENT where timestamp = 0 limit 1000000'
    l_sql = 'select id, tid, timestamp from T_LOCATION where id = %s'
    print 'e_sql', e_sql
    event = db.query(e_sql)
    print 'len ', len(event)
    for i, e in enumerate(event):
        if not (i % 10000):
            print 'now, it is ', i
            time.sleep(2)

        print '-----i: %s, e: %s' % (i, e)

        lid = e.lid
        eid = e.id
         
        location = db.get(l_sql, lid)
        if location:
            gps_time = location.get('timestamp', 0)  
            if gps_time:
                print 'update time', gps_time
                db.execute('UPDATE T_EVENT SET timestamp = %s WHERE id = %s',
                           gps_time, eid)

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
class MGroupMixin(object):

    def __init__(self):
        self.mysql_db = DBConnection().db

    def retrieve_mixin(self, citylist=None):
        results = []
        for city in citylist:
            infos = self.mysql_db.query("SELECT thp.province_id AS id, thp.province_name AS province_name,"
                                        "       thc.city_name AS city_name"
                                        "  FROM T_HLR_CITY AS thc, T_HLR_PROVINCE AS thp"
                                        "  WHERE thc.region_code = %s"
                                        "    AND thc.province_id = thp.province_id", city)
            for info in infos:
                groups = self.mysql_db.query("SELECT txg.id, txg.xxt_id, txg.name,"
                                             " txa.xxt_id AS agency_id, txa.name AS agency_name"
                                             " FROM T_XXT_GROUP AS txg LEFT JOIN T_XXT_AGENCY AS txa"
                                             "   ON txa.xxt_id = txg.agency_id"
                                             " WHERE txg.city_id = %s", city)
                for group in groups:
                    #total_parents = self.mysql_db.get("SELECT count(*) AS total"
                    #                                  " FROM T_XXT_USER AS txu"
                    #                                  " WHERE txu.group_id = %s", group.xxt_id)
                    total_parents = self.mysql_db.get("call P_GROUP_TOTAL_PARENTS(%s)",
                                                      group.xxt_id) 
                    #total_children = self.mysql_db.get("SELECT count(*) AS total"
                    #                                   " FROM T_XXT_TARGET AS txt"
                    #                                   " WHERE txt.group_id = %s", group.xxt_id)
                    total_children = self.mysql_db.get("call P_GROUP_TOTAL_CHILDREN(%s)",
                                                       group.xxt_id) 
                    if not group.agency_name:
                        group.agency_name = u'暂无'

                    res = {'_id': None,
                           'province_id': info.id,
                           'city_id': city,
                           'id': group.id,
                           'group_id': group.xxt_id,
                           'group_name': group.name,
                           'agency_id': group.agency_id,
                           'agency': group.agency_name,
                           'total_parents': total_parents.total,
                           'total_children': total_children.total,
                           'city': info.city_name,
                           'province': info.province_name}
                    results.append(res)
        return results

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def block_test():
    db = DBConnection().db
    redis = MyRedis()

    #terminals = db.query("select * from T_TERMINAL_INFO where mobile = 14778741722 " )
    terminals = db.query("select * from T_TERMINAL_INFO where mobile in (14778473468 ) " )
    for terminal in terminals: 
        tid = terminal.tid
        t = db.query("SELECT tid from T_TERMINAL_INFO where test !=0 and tid=%s", tid)  
        if not t:
            continue

        #print 'clear', terminal 
        db.execute("UPDATE T_TERMINAL_INFO SET test=0 WHERE tid = %s", 
                   tid)
        print 'tid: %s test is closed.' % tid
        sessionID_key = get_terminal_sessionID_key(tid)
        old_sessionid = redis.get(sessionID_key)
        if old_sessionid:
            redis.delete(sessionID_key)
            print "Termianl %s delete session in redis." % tid

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
class MOACB(object):
    
    
    def __init__(self):
        ConfHelper.load(options.conf)
        self.db = DBConnection().db
    
    
    def fetch_mo_sms(self):
        status = ErrorCode.SUCCESS
        result = None
        try:
            mos = self.db.query("SELECT id, msgid, mobile, content "
                                "  FROM T_SMS "
                                "  WHERE category = %s "
                                "  AND send_status = %s"
                                "  ORDER BY id ASC"
                                "  LIMIT 10",
                                SMS.CATEGORY.MO, SMS.SENDSTATUS.PREPARING)
            
            for mo in mos:
                mobile = mo["mobile"]
                content = mo["content"]
                id = mo["id"]
                msgid = mo["msgid"]
                
                result = self.send_mo_to_acb(mobile, content, msgid)
                if result["status"] == ErrorCode.SUCCESS:
                    if int(result["ret"]) == ErrorCode.SUCCESS:
                        logging.info("SMS-->ACB success mobile = %s, content = %s", mobile, content)
                        self.db.execute("UPDATE T_SMS "
                                       "  SET send_status = %s"
                                       "  WHERE id = %s",
                                       SMS.SENDSTATUS.SUCCESS, id)
                        status = ErrorCode.SUCCESS
                    elif int(result["ret"]) == ErrorCode.FAILED:
                        logging.info("SMS-->ACB failure mobile = %s, content = %s", mobile, content)
                        self.db.execute("UPDATE T_SMS "
                                       "  SET send_status = %s"
                                       "  WHERE id = %s",
                                       SMS.SENDSTATUS.FAILURE, id)
                        status = ErrorCode.FAILURE
                    else:
                        #sms-->acb reponse error result
                        status = ErrorCode.FAILURE
                else:
                    # http response is None
                    status = ErrorCode.FAILURE
            
        except Exception, msg:
            status = ErrorCode.FAILURE
            logging.exception("Fetch mo sms exception : %s", msg)
        finally:

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def wash_location():
    db = DBConnection().db
    redis = MyRedis()
    
    #sql = "select id, tid,  mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where tid = '36E2400480' "
    sql = "select id, tid,  mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO"
    #sql = "select id, tid,  mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where login = 0"
    #print 'sql', sql
    terminals  = db.query(sql)
    print 'len ', len(terminals)
    count = 0
    cnt = 0
    for i, t in enumerate(terminals):
        tid = t.tid
        key = 'location:%s' % tid
        location = redis.get(key) 
        if not location:
        #if True:
            print 'tid', tid

            location = db.get("SELECT timestamp, MAX(timestamp) as maxtime"
                              "  FROM T_LOCATION"
                              "  WHERE tid = %s"
                              "    AND type = 0"
                              "    AND latitude != 0",
                              tid)
            
            if location:
                if location.timestamp != location.maxtime:
                    print 'timestamp != maxtime, tid', tid
                    location = db.get("SELECT * FROM T_LOCATION where timestamp = %s AND tid = %s limit 1", location.maxtime, tid)
                else:
                    continue
                mem_location = DotDict({'id':location.id,
                                        'latitude':location.latitude,
                                        'longitude':location.longitude,
                                        'type':location.type,
                                        'clatitude':location.clatitude,
                                        'clongitude':location.clongitude,
                                        'timestamp':location.timestamp,
                                        'name':location.name,
                                        'degree':float(location.degree),
                                        'speed':float(location.speed),
                                        'locate_error':int(location.locate_error)})

                redis.setvalue(key, mem_location, 86400*356*2)
                count = count +1 
                print 'handled tid:', tid
            else:
                cnt = cnt + 1    
    print 'total hanlded count:', count
    print 'total not hanlded count:', cnt

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def wash_location():
    db = DBConnection().db
    redis = MyRedis()
    
    sql = "select tmobile, from_unixtime(add_time) as add_time, from_unixtime(del_time) as del_time from T_BIND_LOG  where group_id in (239, 419)"
    sql_1 = "select tmobile, add_time, del_time from T_BIND_LOG  where op_type = 1 and tmobile = %s"
    sql_2 = "select tmobile, add_time, del_time from T_BIND_LOG  where op_type = 2 and tmobile = %s"
    #print 'sql', sql
    res  = db.query(sql)
    #print 'len ', len(terminals)
    for i, t in enumerate(res):
        tmobile = t.tmobile
        add = db.query(sql_1, tmobile)
        add_num = len(add)

        unbind = db.query(sql_2, tmobile)
        del_num = len(unbind)


        print del_num,  add_num,  t.del_time , t.add_time
        if del_num > add_num:
            print del_num,  add_num,  t.del_time , t.add_time,

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def modify_terminal():

    db = DBConnection().db
    redis = MyRedis()
    
    mobile='14778746907'
    #mobile='14778741845'
    #mobile='14778749172'
    #mobile='14778746786'
    #mobile='14778740942'
    #mobile='14778745985'
    #mobile='14778744628'
    #mobile='14778744861'
    #mobile='14778742261'
    #mobile='14778744473'
    #mobile='14778747112'
    #mobile='14778745219'
    #mobile='14778742290'
    #mobile='14778749137'
    #mobile='14778742587'
    #mobile='14778745073'
    #mobile='14778747467'
    #mobile='14778741340'
    #mobile='14778748943'
    #mobile='14778743681'
    sql = "select id, tid, mobile, owner_mobile, begintime, login_time from T_TERMINAL_INFO where mobile= %s"
    
    terminals  = db.query(sql, mobile)
    #print 'len ', len(terminals)
    count = 0
    cnt = 0
    no_loc = 0
    for i, t in enumerate(terminals):
        tid = t.tid
        mobile = t.mobile
        owner_mobile = t.owner_mobile
        terminal_info_key = get_terminal_info_key(tid) 
        terminal_info = redis.getvalue(terminal_info_key) 
        if terminal_info: 
            print 'umobile in redis:%s, umobile in db:%s' % (terminal_info['owner_mobile'], owner_mobile)
            if terminal_info['owner_mobile'] != owner_mobile:
                print 'mobile: %s, umobile in redis:%s, umobile in db:%s' % (mobile, terminal_info['owner_mobile'], owner_mobile)
                cnt = cnt + 1    
                terminal_info['owner_mobile'] = owner_mobile 
                redis.setvalue(terminal_info_key, terminal_info)
        else:
            pass

    print 'count:', count
    print 'cnt:', cnt

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def statistic():
    db = DBConnection().db
    sql = 'select from_unixtime(timestamp), terminal_online, terminal_offline from T_STATISTIC where type=2 and  timestamp between  1378051140  and 1380556741'

    t = db.query(sql)
    sum_on = 0
    sum_off = 0
    for i in t:  
        print 'i', i
        on = i.terminal_online
        off = i.terminal_offline
        sum_on += on
        sum_off += off 
    print 'sum_on: %s, sum_off: %s, sum: %s' % (sum_on, sum_off, sum_on+sum_off)

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def execute():
    db = DBConnection().db
    redis = MyRedis()
    terminals = db.query("SELECT tid, mobile FROM T_TERMINAL_INFO ")
    print "len ", len(terminals)
    for t in terminals:
        tid = t.tid
        print "tid", tid
        car = db.get("select * from T_CAR where tid = %s", tid)
        if car:
            print "has car "
            pass
        else:
            print "insert car"

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def block_test():
    db = DBConnection().db
    redis = MyRedis()

    #terminals = db.query("SELECT tid, mobile, group_id, cid FROM V_TERMINAL WHERE group_id != '-1'")
    terminals = db.query("SELECT tid, tmobile, group_id, cid FROM V_TERMINAL WHERE cid = '13600335550'")
    for terminal in terminals: 
        tid = terminal.tid
        ttype = get_terminal_type_by_tid(tid) 
        print 'ttype', ttype
        if ttype == 'zj200':
            t = db.query("SELECT tid from T_TERMINAL_INFO where test !=0 and tid=%s", tid)  
            if not t:
                continue

            #print 'clear'
            db.execute("UPDATE T_TERMINAL_INFO SET test=0 WHERE tid = %s", 
                       tid)
            print 'tid: %s test is closed.' % tid
            sessionID_key = get_terminal_sessionID_key(tid)
            old_sessionid = redis.get(sessionID_key)
            if old_sessionid:
                redis.delete(sessionID_key)
                print "Termianl %s delete session in redis." % tid

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def send_all(content):
    db = DBConnection().db

    terminals = db.query("SELECT * FROM V_TERMINAL WHERE cid in ( '15207610001' )")
    # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0")
    # terminals = db.query("SELECT mobile FROM T_TERMINAL_INFO where login=0")
    # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0")
    # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0 and offline_time>1408197915")
    # terminals = db.query("select id, tid, mobile, offline_time, from_unixtime(offline_time) from T_TERMINAL_INFO where login = 0 and offline_time>1411452000")
    print "len: %s", len(terminals)

    for t in terminals:
        terminal = db.get("select login, tid, mobile from T_TERMINAL_INFO where tid = %s", t.tid)
        if terminal.login == 0:  # offline
            print "--", terminal
            send(content, terminal.mobile)

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def block_test():
    db = DBConnection().db
    redis = MyRedis()

    sms = db.query("SELECT uid FROM T_SMS_OPTION")
    count = 0
    for s in sms:
        uid = s.uid
        user = db.get("select * from T_USER where uid = %s", uid)
        if user:
            pass
            # print 'pass'
        else:
            db.execute("DELETE FROM T_SMS_OPTION WHERE uid = %s", uid)
            print "delete ....", uid
            count += 1
    print "-------count: %s" % count

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
class MLocationMixin(object):
   
    def __init__(self):
        self.mysql_db = DBConnection().db
 
    def retrieve_mixin(self, city_list=None, start_time=None, end_time=None):
       
        results = []
        d = datetime.date.fromtimestamp(start_time/1000)
        year = d.year
        month = d.month
        cities = city_info(city_list, self.mysql_db)
        for city in cities:
            groups = self.mysql_db.query("SELECT T_XXT_GROUP.name as group_name, T_XXT_GROUP.xxt_id"
                                         " FROM T_XXT_GROUP WHERE T_XXT_GROUP.city_id = %s",
                                         city.id)
            for group in groups: 
                for item, category in [ #('custom', LOCATION.CATEGORY.CUSTOM),
                                       ('schedule', LOCATION.CATEGORY.SCHEDULE),
                                       ('realtime', LOCATION.CATEGORY.REALTIME)]:
                    #data = self.mysql_db.get("SELECT count(T_LOCATION.id) as total"
                    #                         " FROM T_LOCATION, T_XXT_TARGET, T_LBMP_TERMINAL"
                    #                         " WHERE T_LOCATION.timestamp BETWEEN %s AND %s" 
                    #                         " AND T_LOCATION.category = %s"
                    #                         " AND T_LOCATION.sim = T_LBMP_TERMINAL.sim"
                    #                         " AND T_LBMP_TERMINAL.sim = T_XXT_TARGET.mobile"
                    #                         " AND T_XXT_TARGET.group_id = %s",
                    #                         start_time, end_time, category, group.xxt_id)
                    data = self.mysql_db.get("call P_LOCATION(%s, %s, %s, %s)",
                                             start_time, end_time, category, group.xxt_id) 
                    group[item] = data.total

                res = {'_id': None,
                       'province_id': city.p_id,
                       'city_id': city.id,
                       'group_id': group.xxt_id,
                       'province': city.p_name,
                       'city': city.name,
                       'group_name': group.group_name,
                       # 'custom': group.custom,
                       'schedule': group.schedule,
                       'realtime': group.realtime,
                       'year': year,
                       'month': month}
                results.append(res)
        return results

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def block_test():
    db = DBConnection().db
    redis = MyRedis()

    #terminals = db.query("select tid from T_TERMINAL_INFO where group_id= '438';")
    #terminals = db.query("select tid from T_TERMINAL_INFO where group_id= '438'")
    terminals = db.query("select id, tid,  test from T_TERMINAL_INFO where mobile = '14778748087' ")
    
    for terminal in terminals: 
        tid = terminal.tid
        #db.execute("UPDATE T_TERMINAL_INFO SET trace_para='60:0' WHERE tid = %s", 
        #           tid)
        #print 'tid: %s test is closed.' % tid
        sessionID_key = get_terminal_sessionID_key(tid)
        old_sessionid = redis.get(sessionID_key)
        if old_sessionid:
            redis.delete(sessionID_key)
            print "Termianl %s delete session in redis." % tid

# 需要导入模块: from db_.mysql import DBConnection [as 别名]
# 或者: from db_.mysql.DBConnection import query [as 别名]
def export_excel():
    db = DBConnection().db
    online_style = xlwt.easyxf('font: colour_index green, bold off; align: wrap on, vert centre, horiz center;')
    offline_style = xlwt.easyxf('font: colour_index brown, bold off; align: wrap on, vert centre, horiz center;')

    wt = xlwt.Workbook()   
    ws = wt.add_sheet(u'jia') 
    
    sql = 'select id, mobile, owner_mobile, tid, begintime from T_TERMINAL_INFO'
    #sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO'
    #sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO limit 5'
    #sql = 'select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO  where service_status=1'
    #sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO  where service_status=1 and mobile like '%%%%14778%%%%' "
    #sql = "select id, mobile, owner_mobile, begintime from T_TERMINAL_INFO  where group_id = 310 "
    #sql = "select mobile, owner_mobile, begintime from T_TERMINAL_INFO  where service_status != 0 and begintime between 1356969600 and 1388505599"
    #sql = "select mobile, owner_mobile, begintime from T_TERMINAL_INFO  where service_status != 0 and begintime between 1356969600 and 1388505599"

    #sql = "select mobile, owner_mobile, begintime from T_TERMINAL_INFO  where service_status != 0 and  (mobile  like '1477874%%' or mobile like '1477847%%' )"
    print 'sql', sql
    terminals  = db.query(sql)
    print 'len ', len(terminals)
    for i, t in enumerate(terminals):
        print 't', t
        reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime))
        ws.write(i,0,i)
        ws.write(i,1,t['tid'])
        ws.write(i,2,reg_time)

        #print 't', t
        #reg_time = time.strftime('%Y-%m-%d-%H:%M:%S',time.localtime(t.begintime))
        #ws.write(i,0,t.owner_mobile)
        #ws.write(i,1,t.mobile)
        #ws.write(i,2,reg_time)
    #lst += "'" + mobile + "',"
    #print 'select id, mobile, login, tid,owner_mobile,domain from T_TERMINAL_INFO where mobile = ' + mobile + ';'
    #db.execute("INSERT INTO T_BIZ_WHITELIST(id, mobile)"
    #           "  VALUES(NULL, %s)"
    #           "  ON DUPLICATE KEY"
    #           "  UPDATE mobile = values(mobile)", mobile)
    #content = ':SIM ' + umobile + ':' + mobile
    #print content
    #SMSHelper.send_to_terminal(mobile, content)
    #print '%s sucessfully.' % mobile
    wt.save('./jia_new.xls')