Python mysql_tools.mysqlQuery函数代码示例

def pop_from_queue(external_id=None, account_id=None):
    if account_id == None or external_id == None:
        return json.dumps([]);
    sql = "SELECT * FROM peepbuzz.twitter_queue WHERE external_id=" + str(external_id)
    tweetsQ = mysql_tools.mysqlQuery(sql, infoModule.info.site['dblink'])
    tweets = []
    while True:
        tweet = tweetsQ.fetch_row(1,1)
        if tweet == ():
            break
        urls = []
        urls = tweet[0]['urls'].split(',')
        hashtags = []
        hashtags = tweet[0]['hashtags'].split(',')
        #format time
        formattedTime=tweet[0]['created'].replace(' ', 'T')
        formattedTime=formattedTime+'0000'
        tweets.append({
            "stream_name" : 'twitter',
            "external_id" : tweet[0]['status_id'],
            "urls"        : urls,
            "created"     : formattedTime,
            "promoter_id" : tweet[0]['promoter_id'],
            "promoter"    : tweet[0]['promoter'],
            "thumbnail"   : tweet[0]['thumbnail'],
            "title"       : None,
            "summary"     : tweet[0]['summary'],
            "hashtags"    : hashtags,
            "discussion"  : [],
            "account_id"  : account_id,
        })
            
    sql = "DELETE FROM peepbuzz.twitter_queue WHERE external_id=" + str(external_id)
    tweetsQ = mysql_tools.mysqlQuery(sql, infoModule.info.site['dblink'])
    return json.dumps(tweets)

def cleanup(days):
    link = mysql_tools.db_connect()
    query = 'SELECT filament_id, story_id FROM peepbuzz.filaments WHERE created <= DATE_SUB(NOW(), INTERVAL '+str(days)+' DAY)'
    result = mysql_tools.mysqlQuery(query, link)
    while (1):
        row = result.fetch_row(1,1)
        if row == ():
            break
        query = 'DELETE from peepbuzz.filaments WHERE filament_id = "'+str(row[0]['filament_id'])+'"'
        try:
            result2 = mysql_tools.mysqlQuery(query, link)
        except:
            pprint.pprint(query)
            sys.exit(1)
        if row[0]['story_id'] != None:
            query = 'SELECT count(*) from peepbuzz.filaments WHERE story_id = "'+str(row[0]['story_id'])+'"'
            try:
                result2 = mysql_tools.mysqlQuery(query, link)
            except:
               pprint.pprint(query)
               sys.exit(1)
            row = result2.fetch_row(1,1)
            if row == None:
                break
            if row[0] == 0:
                query = 'DELETE FROM peepbuzz.stories WHERE story_id = "'+str(row[0]['story_id'])+'"'
                try:
                    result2 = mysql_tools.mysqlQuery(query, link)
                except:
                    pprint.pprint(query)
                    sys.exit(1)
    return True

 def on_status(self, status):
     global follow_list
         
     #format time into 2011-02-23T16:42:40+0000 format ala facebook
     #Twitter Format: Wed Mar 23 22:51:50 +0000 2011
     formattedTime = self.formatTime(status['created_at'])
     hashtags = []
     
     if len(status['entities']['hashtags']):
         for val in status['entities']['hashtags']:
             hashtags.append(val['text'].replace("'", "\\'"))
     
     hashtag = ','.join(hashtags)
     
     urls = []
     if len(status['entities']['urls']):
         for val in status['entities']['urls']:
             urls.append(val['url'].replace("'", "\\'"))
     
     url = ','.join(urls)
     #print status['text']
     text = status['text'].replace("'", "\\'")
     if text[-1] == '\\':
     	text = text + " "
     if str(status['user']['id']) in follow_list:
         file_put_contents(str(status['user']['screen_name']) + " posted something")
         infoModule.info.site['dblink'] = mysql_tools.db_connect()
         
         sql = u"INSERT INTO `peepbuzz`.`twitter_queue` SET `status_id` = '" + str(status['id']) + "', `created` = '" + formattedTime + "', `promoter_id` = '" + str(status['user']['id']) + "', `promoter` = '" + status['user']['screen_name'] + "', `thumbnail` = '" + str(status['user']['profile_image_url']) + "', `summary` = '" + text + "', `external_id` = '" + str(status['user']['id']) + "', `hashtags` = '" + hashtag + "', `urls` = '" + url + "'";
         mysql_tools.mysqlQuery(sql, infoModule.info.site['dblink'])
         infoModule.info.site['dblink'].close()
     else:
         pass        

 def tearDown(self):
     if infoModule.info.site['dblink'] == None:
         #need this code to recover from db disconnect test
         infoModule.info.site['dblink'] = self.dblink
         
     sql = "delete from peepbuzz.blocked_accounts where ba_id=" + str(self.ba_id)
     mysql_tools.mysqlQuery(sql, infoModule.info.site['dblink'])

def getNewEntities():
    ''' find latest id from story_entites to see when the last entity was ID'd'''
    query = 'select max(story_id) as max_story from peepbuzz.story_entities'
    try: 
        storyIDQ = mysql_tools.mysqlQuery(query, infoModule.info.site['dblink'])
    except:
        return False
    storyIDRow = storyIDQ.fetch_row(1,1)
    if storyIDRow == ():
        return False
    storyID = storyIDRow[0]['max_story']
    if storyID == None:
        storyID = 0
    else:
        storyID = 0
    query = 'select story_id, title, body from peepbuzz.stories where story_id > '+str(storyID)
    print query
    try:
        storiesQ = mysql_tools.mysqlQuery(query, infoModule.info.site['dblink'])
    except:
        return False
    while(1):
        row = storiesQ.fetch_row(1,1)
        if row==():
            break
        story_id = row[0]['story_id']
        title = row[0]['title']
        body = row[0]['body']
        if body == None or title == None:
            continue
        url = 'http://informifi.com/enhancer/APIGateway.php'
        request = {'command' : 'entities',
                   'byID' : 'true',
                   'title': title,
                   'searchText' : body}
        data = urllib.urlencode(request)
        req = urllib2.Request(url, data)
        response = urllib2.urlopen(req)
        res = response.read()
        try:
            entities = json.loads(res)
        except ValueError:
            continue
        entityCount = len(entities)
        print "found " + str(entityCount) + " entities"
        for ents in entities:
            primo = ents['primo']
            if(primo== "Y"):
                primo = 1
            elif(primo =="N"):
                primo = 10
            q = 'insert into peepbuzz.story_entities (story_id, entity_id, primo) values ("'+str(story_id)+'","'+ents['id']+'","'+str(primo)+'")'
            try:
                insertQ = mysql_tools.mysqlQuery(q, infoModule.info.site['dblink'])
            except:
                return False
    return True

def store_discussion(discussion_json, filament_id):
    discussions = json.loads(discussion_json)

    # get stream_id
    sql = "SELECT stream_id FROM peepbuzz.filaments WHERE filament_id = " + str(filament_id)
    stream_idQ = mysql_tools.mysqlQuery(sql, infoModule.info.site["dblink"])
    row = stream_idQ.fetch_row(1, 1)
    if row == ():
        infoModule.info.errorList.append("ERROR: no such filament (" + str(filament_id) + ")")
        return False
    stream_id = row[0]["stream_id"]

    discussion_ids = []
    for discussion in discussions:
        if "count" in discussion:
            continue
        else:
            account_id, table = accountFinder(
                stream_id, discussion["user_id"], discussion["user_name"], discussion["thumbnail"]
            )
            created = discussion["comment_created"]
            body = discussion["body"]
            body = body.replace("'", "\\'")

            if table == "accounts":
                field = "account_id"

            mysql_tools.mysqlQuery(
                u"INSERT INTO peepbuzz.discussions SET filament_id="
                + str(filament_id)
                + ", "
                + field
                + "="
                + str(account_id)
                + ', created="'
                + created
                + '", body="'
                + body
                + '"',
                infoModule.info.site["dblink"],
            )
            discussion_id = infoModule.info.site["dblink"].insert_id()

            discussion_ids.append(int(discussion_id))

    return discussion_ids

def getIds(sub_id):
    sql = 'SELECT celeb_id FROM ' + infoModule.info.site['database'] + '.subs_celebs WHERE sub_id = ' + str(sub_id)
    entityIdsQ = mysql_tools.mysqlQuery(sql, infoModule.info.site['dblink']) 
    entityIds = entityIdsQ.fetch_row(0,1)

    entityRows = []
    for row in entityIds:
        infoModule.info.entityList[row['celeb_id']] = {'position': None, 'frequency': 0, 'primo' : 'N'}

    def setUp(self):
        infoModule.info.site['dblink'] = mysql_tools.db_connect()
        dblink = infoModule.info.site['dblink']
        #set up accounts for test
        # get valid account
        sql = "SELECT user_id from peepbuzz.users limit 1"
        userQ = mysql_tools.mysqlQuery(sql, dblink)
        user = userQ.fetch_row(1,1)

        sql = "SELECT account_id from peepbuzz.accounts limit 1"
        accountQ = mysql_tools.mysqlQuery(sql, dblink)
        account = accountQ.fetch_row(1,1)
        self.account_id = account[0]['account_id']
        self.user_id = user[0]['user_id']
        sql = "insert into peepbuzz.blocked_accounts set user_id=" + self.user_id + ", unknown_account_id=" + self.unknown_account_id + ", account_id=" + self.account_id
        testQ = mysql_tools.mysqlQuery(sql, dblink)
        self.ba_id = dblink.insert_id()

def testUTF():
    dblink = infoModule.info.site["dblink"]
    sql = u"insert into peepbuzz.stories set title='foo faa \u2026 fum'"
    # mysql_tools.mysqlQuery(sql, dblink)
    sql = "select title from peepbuzz.stories"
    accountsQ = mysql_tools.mysqlQuery(sql, dblink)
    accounts = accountsQ.fetch_row(0, 1)
    print accounts[0]["title"]

def URLInStories(URL):
    dblink = infoModule.info.site["dblink"]
    sql = "select story_id from peepbuzz.stories where url='" + URL + "' or original_url='" + URL + "'"
    storyQ = mysql_tools.mysqlQuery(sql, dblink)
    story = storyQ.fetch_row(1, 1)
    if story == ():
        return False
    else:
        return int(story[0]["story_id"])

def entityLibraryByUrl(lookupUrl, field):
    celeb_idQ = mysql_tools.mysqlQuery('select celeb_id from db_topics.celebs where lookupUrl = "'+lookupUrl+'"', infoModule.info.site['dblink'])
    celeb_id=celeb_idQ.fetch_row(1,1)
    if celeb_id == ():
        log.plog('no celeb_id found for ' + lookupUrl, 5)
        return False
    else:
        cid = celeb_id[0]['celeb_id']
    liveEntities[cid] = liveEntity(cid)
    return liveEntities[cid].getData(field)

def addFollowing(user_id = None, account_id = None):
    # Make sure we have a user to check against
    if not user_id or not account_id:
        return False
        
    #Check to see if the follower exists yet
    check = mysql_tools.mysqlQuery("SELECT * FROM `peepbuzz`.`following` WHERE `user_id` = '" + str(user_id) + "' AND `account_id` = '" + str(account_id) + "'", infoModule.info.site['dblink'])
    if check.num_rows() > 0:
        return True
    
    # Since they did not exist let's add them
    sql = "INSERT INTO `peepbuzz`.`following` SET `user_id` = '" + str(user_id) + "', `account_id` = '" + str(account_id) + "'"
    print sql
    add = mysql_tools.mysqlQuery(sql, infoModule.info.site['dblink'])
    
    if not infoModule.info.site['dblink'].insert_id():
        return False
    
    return True

def getBlockedImages():
    blockedImages = []
    sql = "select * from " + infoModule.info.site['database'] + ".blocked_images"
    esr = mysql_tools.mysqlQuery(sql, infoModule.info.site['dblink'])
    while True:
        blocked = esr.fetch_row(1,1)
        if blocked == ():
            break
        blockedImages.append(blocked[0]['regex'])
    return blockedImages

def getEntityTotals(entity_id):
    
    entDict = {}
    sql = 'select celeb_id, stories_total, storiesWeighted_total, vertical from db_topics.celebStatsTotals where celeb_id =' + str(entity_id)
    statsQ = mysql_tools.mysqlQuery(sql, infoModule.info.site['dblink'])
    while (1):
        row = statsQ.fetch_row(1,1)
        if row == ():
            break;        
        idx = row[0]['vertical']
        entDict[idx] = {"stories_total": int(row[0]['stories_total']), "storiesWeighted_total": int(row[0]['storiesWeighted_total'])}
    return entDict

def accountFinder(stream_id, external_id, user_name, thumbnail):
    ''' Checking for external accounts in known accounts '''
    sql = u"select account_id, thumbnail from peepbuzz.accounts where external_id='" + str(external_id) + "' and stream_id=" + str(stream_id) + " LIMIT 1"
    known_account = mysql_tools.mysqlQuery(sql, infoModule.info.site['dblink'])
    if known_account.num_rows() > 0: 
        ''' return account_id and known_accounts table '''
        
        row = known_account.fetch_row(1,1)
        if row == ():
            pass
        else:
            account_id = row[0]['account_id']
            #check thumbnail against reported thumbnail and update if changed
            if len(thumbnail) > 0 and thumbnail != row[0]['thumbnail']:
                sql = "update peepbuzz.accounts set thumbnail='" + thumbnail + "' where account_id=" + str(account_id)
                mysql_tools.mysqlQuery(sql, infoModule.info.site['dblink'])
            return account_id, 'accounts'
    else:
        ''' no account, create it '''
        new_account = mysql_tools.mysqlQuery(u"insert into peepbuzz.accounts set external_id='"+str(external_id)+"', stream_id='"+str(stream_id)+"', user_name='"+user_name+"', thumbnail='" + thumbnail + "'", infoModule.info.site['dblink'])
        ''' return insert id from account with uaccounts table '''
        new_account_id = infoModule.info.site['dblink'].insert_id() 
        if len(thumbnail) > 0:
            sql = "update peepbuzz.accounts set thumbnail='" + thumbnail + "' where account_id=" + str(new_account_id)
            mysql_tools.mysqlQuery(sql, infoModule.info.site['dblink'])
        return str(new_account_id), 'accounts'