Python DB.query方法代码示例

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
    def make_short_url(self, url):

        key = 'AIzaSyBwCHhPcVAdwZJH-hlTU4WM_sHe8-_SGYU'
        if(self.is_safe(key, url) == True):

            db = DB()

            sql = '''SELECT id, short_url FROM links WHERE long_url=%s'''
            query = db.query(sql, (url, ))

            link = query.fetchone()

            if link is not None:
                db.close()
                return self.format_result("http://goo.rs/"+link['short_url'], 1, "Links already exists")

            else:

                sql = '''SELECT MAX(id) as id FROM links'''
                query = db.query(sql)
                id = query.fetchone()
                if id is not None:
                    short_url = encode_url(id['id'])
                else:
                    short_url = encode_url(1)
                sql = '''INSERT INTO links(id, long_url, short_url, clicks , u_id, created) VALUES (NULL, %s, %s, %s ,%s, %s )'''
                db.query(sql, (url, short_url, 0, 2, time.strftime('%Y-%m-%d %H:%M:%S') ))

            return self.format_result("http://goo.rs/"+short_url, 1, 'Url created')
        return self.format_result({}, 0, "This url contains malware and can not be shortened.")

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
 def getNearbyFacilities(self):
     hotel_list = DB.query("select * from hotel");
     facility_type = DB.query("select * from facility_type")
     types = {}
     for type in facility_type:
         types[type["name"]] = type["id"]
     for hotel in hotel_list:
         hotel_id = hotel["originalHotelID"]
         print hotel_id
         hotel_url = self.hotel_page_prefix + hotel_id + ".html"
         page_code = requests.get(hotel_url)
         soup = BeautifulSoup(page_code.text, "html.parser")
         div_list = soup.find_all('div',{'class':'htl_info_table'})
         if len(div_list) == 0:
             continue
         div = div_list[len(div_list) -1]
         #print div
         tr_list = div.find_all('tr')
         for tr in tr_list:
             type = types[tr.find('th').string]
             print tr.find('th').string
             ctt = tr.find_all('li')
             if len(ctt) != 0:
                 for li in ctt:
                     sql = "insert into hotel_nearby_facility(hotel_id,type,name,sub_type) values("+str(hotel["id"])+","+str(type)+",\""+li.string+"\",0)"
                     print sql
                     DB.insert(sql)

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
 def getNearbyCateringInfo(self):
     hotel_list = DB.query("select * from hotel where id > 570");
     for hotel in hotel_list:
         hotel_id = hotel["id"]
         hotel_name = hotel["name_cn"]
         #hotel_name = "桔子水晶酒店（北京总部基地店)(原双赢酒店）"
         print hotel_name
         headers = {
             'User-Agent': 'Mozilla/5.0 (X11; U; Linux i686; zh-CN; rv:1.9.1.2) Gecko/20090803 Fedora/3.5.2-2.fc11 Firefox/3.5.2'
         }
         dianping_search_url = self.dianping_search_hotel_url+ hotel_name
         page_code = requests.get(dianping_search_url,headers = headers)
         soup = BeautifulSoup(page_code.text, "html.parser")
         #print soup
         ul = soup.find('ul',{'class':'hotelshop-list'})
         h2 = ul.find('h2',{'class':'hotel-name'})
         title = ''
         url = ''
         if h2 is not None:
             a = h2.find('a',{'class':'hotel-name-link'})
             title = a.string
             url = a['href']
             print title
             print url
         else:
             title = 'n/a'
             url = 'n/a'
         sql = "insert into hotel_dianping_info(id,name_cn,dianping_url,dianping_name) values("+str(hotel_id)+",\""+hotel_name+"\",\""+url+"\",\""+title+"\")"
         print sql
         DB.insert(sql)

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
 def getroles(self):
         rs = []
         ts = DB.query("select name, desc from roles where owner = ? and projectname = ?" \
                      , args=(self.owner, self.name))
         for t in ts:
                 rs.append(Role(t[0], t[1]))
         return rs

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
 def getpersons(self):
         ps = []
         ts = DB.query("select name, desc, profile, joke from persons where owner = ? and projectname = ?" \
                       , args=(self.owner, self.name))
         for t in ts:
                 ps.append(Person(t[0], t[1], t[2], t[3]))
         return ps

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
        def query(self, q, singlets=False, only=False, args=()):
                if db_debug:
                        print("'" + ddupw(q).format(table=self.name) + "'", args)

                ret = DB.query(q.format(table=self.name), singlets=singlets, \
                                only=only, args=args)
                if db_debug:
                        print("returns:", ret, end='\n\n')

                return ret

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
    def redirect(self, url):

        db = DB()

        sql = '''SELECT id, long_url FROM links WHERE short_url=%s'''

        query = db.query(sql, (url,))

        link = query.fetchone()
        if link is not None :

            sql = '''UPDATE links SET clicks=clicks+1 WHERE id=%s'''
            db.query(sql, (link['id'], ))
            db.close()

            key = 'AIzaSyBwCHhPcVAdwZJH-hlTU4WM_sHe8-_SGYU'
            if(self.is_safe(key, link['long_url']) == True):
                return (link['long_url'])
            return False
        return False

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
    def get_url_stats(self, short_url):

        db = DB()

        parts = short_url.split("/",3)

        sql = '''SELECT clicks, short_url FROM links WHERE short_url=%s'''

        query = db.query(sql, (parts[3], ))

        link = query.fetchone()
        if link is not None:
            db.close()
            return self.format_result(link['clicks'], 1, "Clicks on link "+short_url)

        return self.format_result(None, 0, "Error"), 404

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
 def makeUserData(self):
     users = ['Yw','Kevin','Martin','Lee','David','Hj','Amy','Nick','Deniel',
             'Zack','William','Darcy','Emma','Emily','James','Michael','Olivia',
             'Sophia','Isebella','Ava','Mia','Abigail','Madison','Charlotte','Harper',
             'Sofia','Avery','Elizabeth','Amelia','Evelyn','Ella','Chloe','Victoria',
             'Aubrey','Grace','Zoey','Natalie','Addison','Lillian','Brooklyn',
             'Lily','Hannah','Layla','Scarlett','Aria','Zoe','Samantha','Anna','Leah',
             'Audrey','Ariana','Allison','Savannah','Arianna','Camila','Penelope',
             'Gabriella','Claire','Aaliyah','Sadie','Riley','Skylar','Nora','Sarah',
             'Hailey','Kaylee','Paisley','Kennedy','Ellie','Peyton','Annabelle',
             'Caroline','Madelyn','Serenity','Aubree','Lucy','Alexa','Alexis',
             'Nevaeh','Stella','Violet','Genesis','Mackenzie','Bella','Autumn',
             'Mila','Kylie','Maya','Piper','Alyssa','Taylor','Eleanor','Melanie',
             'Naomi','Faith','Eva','Katherine','Lydia','Brianna','Julia','Ashley','Khloe',
             'Madeline','Ruby','Sophie','Alexandra','London','Lauren','Gianna','Isabelle','Alice',
             'Vivian','Hadley','Jasmine','Morgan','Kayla','Cora','Bailey','Kimberly','Reagan','Hazel',
             'Clara','Sydney','Trinity','Natalia','Valentina','Rylee','Jocelyn',
             'Maria','Aurora','Eliana','Brielle','Liliana']
     hotel_list = DB.query("select c.hotel_id as id, h.rating_cn as rating, max(score) as mx from hotel_nearby_catering c, hotel h where c.hotel_id = h.id group by c.hotel_id");
     for user in users:
         score = 0
         count = 0
         for hotel in hotel_list:
             mx = hotel["mx"]
             rate = hotel["rating"]
             score = random.uniform(0,1)
             if score >= 0.7:
                 #count -= 1
                 #if count == 0:
                 #    break
                 if rate >= 4.2:
                     avg = random.randint(0,10)
                     if avg >= 4:
                         score = 1
                     else:
                         score = -1
                 else:
                     score = 0
             else:
                 score = 0
             if score != 0:
                 sql = "insert into user_hotel_rating(user_id,hotel_id,rating) values(\""+str(user)+"\","+str(hotel["id"])+","+str(score)+")"
                 #print sql
                 DB.insert(sql)
                 if score == 1:
                     count += 1
         sql_cuisine = "select * from ( select h.type as type, sum(h.score) as total from hotel_nearby_catering h where h.hotel_id in " \
                       "( select u.hotel_id from user_hotel_rating u where u.user_id = \""+str(user)+"\"" \
                         " and u.rating = 1 ) group by h.type) t order by t.total desc"
         min_score = 3.0 * count
         stats = DB.query(sql_cuisine)
         cuisine_list = []
         for stat in stats:
             if float(stat["total"]) >= min_score:
                 cuisine_list.append(stat["type"])
         l = len(cuisine_list)
         cuisines = []
         cuisines.append("北京菜")
         if l > 1:
             cuisines = random.sample(cuisine_list,2)
             print 'large'
         elif l == 1:
             cuisines = random.sample(cuisine_list,1)
             print 'equal'
         else:
             print 'zero'
         for cs in cuisines:
             sql_cuisine = "insert into user_favoriate_cuisine(user_id,cuisine) values(\""+str(user)+"\",\""+cs+"\")"
             print sql_cuisine
             DB.insert(sql_cuisine)

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
 def getgetNearbyCateringInfo_other(self):
     hotel_list = DB.query("select * from hotel_dianping_info where id > 616 and dianping_url != 'n/a'");
     around_url = "http://www.dianping.com/search/around/2/10_"
     type={}
     type["火锅"] = "/g110d1"
     type["咖啡厅"] = "/g132d1"
     type["烧烤"] = "/g508d1"
     type["面包甜点"]="/g117d1"
     type["自助餐"]="/g111d1"
     type["日本菜"]="/g113d1"
     type["西餐"]="/g116d1"
     type["北京菜"]="/g311d1"
     type["韩国料理"]="/g114d1"
     type["海鲜"]="/g251d1"
     type["江浙菜"]="/g101d1"
     type["粤菜"]="/g103d1"
     type["清真菜"]="/g108d1"
     type["素菜"]="/g109d1"
     type["川菜"]="/g102d1"
     type["湘菜"]="/g104d1"
     type["新疆菜"]="/g3243d1"
     type["西北菜"]="/g26481d1"
     type["家常菜"]="/g1783d1"
     type["东北菜"]="/g106d1"
     proxy_support = urllib2.ProxyHandler({'http':'184.82.45.37:3128'})
     opener = urllib2.build_opener(proxy_support)
     urllib2.install_opener(opener)
     for hotel in hotel_list:
         hotel_id = hotel["id"]
         hotel_dianping_id = hotel["dianping_url"]
         hotel_dianping_id = hotel_dianping_id[6:]
         print hotel_dianping_id
         headers = {
             'User-Agent': 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_9_5) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/49.0.2623.87 Safari/537.36',
             'Referer':'http://www.dianping.com/'
         }
         for (k,v) in type.items():
             #default: dianping_search_url = around_url + hotel_dianping_id + v
             #ordered by comment from customer
             dianping_search_url = around_url + hotel_dianping_id + v +"o3"
             #content = urllib2.urlopen(dianping_search_url).read()
             page_code = requests.get(dianping_search_url,headers = headers)
             soup = BeautifulSoup(page_code.text, "html.parser")
             #print soup
             div = soup.find('div',{'class':'bread J_bread'})
             count = div.find('span',{'class':'num'}).string
             count = str(count)[1:]
             count = count[:-1]
             score = 0
             if count != "0":
                 div = soup.find('div',{'id':'shop-all-list'})
                 li = div.find('li')
                 div = li.find('div',{'class':'comment'})
                 span = div.find('span')
                 comment = span['title']
                 if comment[0:1] == u'准':
                     score = -0.5
                     comment = comment[1:]
                 if comment[0:1] == u'五':
                     score += 5.0
                 elif comment[0:1] == u'四':
                     score += 4.0
                 elif comment[0:1] == u'三':
                     score += 3.0
                 elif comment[0:1] == u'二':
                     score += 2.0
                 elif comment[0:1] == u'一':
                     score += 1.0
                 else:
                     score = 0.0
             sql = "insert into hotel_nearby_catering(hotel_id,type,count,score) values("+str(hotel_id)+",\""+k+"\","+count+","+str(score)+")"
             print sql
             DB.insert(sql)

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
# check if hotel related information has already been grabbed from somewhere
# m = - nlogp / (log2)^2
# false positive probability =  m/n log(2)
# m = number of bits for the bloom filter
# n = how many hotels we are planning to keep in the bloom filter
# p = desired false positive probability

import DB

from pybloom import BloomFilter

n = 5000
p = 0.01
bf = BloomFilter(n,p)

#init bf by selecting data from database
list = DB.query("select name_cn, latitude, longitude from hotel")

for l in list:
	hotelStr = l["name_cn"] + l["latitude"] + l["longitude"]
	#print(hotelStr)
	bf.add(hotelStr)

def isExist(test_str):
	return (test_str in bf)

def addItem(itemStr):
	return bf.add(itemStr)

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
def item2item():
    #construct data
    #item dictionary
    item_dict = {}
    #item to customer dictionary
    item2C_dict = {}
    #customer to item dictionary
    c2Item_dict = {}
    item_list = DB.query("select * from hotel")
    print "item amount:" + str(len(item_list))
    for item in item_list:
        detail = {}
        detail["name"] = item["name_cn"]
        detail["latitude"] = item["latitude"]
        detail["longitude"] = item["longitude"]
        item_dict[item["id"]] = detail
        item2C_list = DB.query("select * from reviews_1 where hotelId = " + str(item["id"]))
        print "hotelId:" + str(item["id"]) + ", review amount:" + str(len(item2C_list))
        customers = set()
        for customer in item2C_list:
            if customer["user"] not in c2Item_dict:
                cItem_dict = {}
                cItem_dict[item["id"]] = customer["score"]
                c2Item_dict[customer["user"]] = cItem_dict
            else:
                if customer["user"] not in customers:
                    cItem_dict = c2Item_dict[customer["user"]]
                    cItem_dict[item["id"]] = customer["score"]
                    c2Item_dict[customer["user"]] = cItem_dict
            customers.add(customer["user"])
        item2C_dict[item["id"]] = customers
    # item to item recommendation using strategy like:
    # For each item in item_dict, I1
    #   For each customer C who purchased I1
    #       For each item I2 purchased by customer C
    #           Record that a customer purchased I1 and I2
    #   For each item I2
    #       Compute the similarity between I1 and I2 using cosine measure
    similarity = {}
    for item in item_dict:
        customers = item2C_dict[item]
        similarItem_dict = {}
        for customer in customers:
            cItems = c2Item_dict[customer]
            for cItem in cItems:
                if cItem != item:
                    if cItem not in similarItem_dict:
                        cList = []
                        cList.append(customer)
                        similarItem_dict[cItem] = cList
                    else:
                        cList = similarItem_dict[cItem]
                        cList.append(customer)
                        similarItem_dict[cItem] = cList
        #calculate similarity
        similar_dict = {}
        for sItem in similarItem_dict:
            sim = 0
            if sItem in similarity:
                sim_dict = similarity[sItem]
                sim = sim_dict[item]
            else:
                customers = similarItem_dict[sItem]
                i1 = []
                i2 = []
                for customer in customers:
                    cItems = c2Item_dict[customer]
                    i1.append(cItems[item])
                    i2.append(cItems[sItem])
                sim = __sim(i1,i2)
            similar_dict[sItem] = sim
        similarity[item] = similar_dict
    #print similarity
    item2item_recom = {}
    for sim in similarity:
        if similarity[sim]:
            lst = __bubbleSort(similarity[sim],10)
            item2item_recom[sim] = lst
    for recom in item2item_recom:
        print "recommendation for " + item_dict[recom]["name"].encode("utf-8") + ":"
        for item in item2item_recom[recom]:
            print item_dict[item]["name"]

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
def evaluate():
    users_ratings = DB.query("select user_id, hotel_id, rating from user_hotel_rating")
    hotels_list = DB.query("SELECT hotel_id FROM user_hotel_rating group by hotel_id")
    hotels = []
    for htl in hotels_list:
        hotels.append(htl["hotel_id"])
    total = len(users_ratings)
    kf_total = cross_validation.ShuffleSplit(total, n_iter=20, test_size=0.1,random_state=0)
    hotel_len = len(hotels)
    index = 0
    oa_tp = 0
    oa_tn = 0
    oa_fp = 0
    oa_fn = 0
    oa_n_tp = 0
    oa_n_tn = 0
    oa_n_fp = 0
    oa_n_fn = 0
    for train, test in kf_total:
        train_data = []
        test_data = []
        users_rating = set([])
        users = []
        for idx in test:
            test_data.append(users_ratings[idx])
        for idx in train:
            if users_ratings[idx]["rating"] == 1:
                users_rating.add(str(users_ratings[idx]["user_id"]) + "-" + str(users_ratings[idx]["hotel_id"]))
            if users_ratings[idx]["user_id"] not in users:
                users.append(users_ratings[idx]["user_id"])
        users_len = len(users)
        #standard logistical regression
        data = [[(1 if (str(i) + "-" + str(j) in users_rating) else 0) for j in hotels] for i in users]
        tp = 0
        tn = 0
        fp = 0
        fn = 0
        for t in test_data:
            u = 0
            if t["user_id"] in users:
                #old user
                #initialize data
                u = users.index(t["user_id"])
            else:
                #new user
                data.append([0 for q in hotels])
                u = users_len
            m = hotels.index(t["hotel_id"])
            x = []
            y = []
            for row in range(0,users_len):
                if row != u:
                    x.append(data[row][:m]+data[row][m+1:])
                    y.append(data[row][m])
            pred = 0
            try:
                lr = LogisticRegression()
                lr.fit(x,y)
                val = lr.predict_proba(data[u][:m]+data[u][m+1:])
                pred = round(val[0][1],2)
            except Exception as e:
                pred = 0
            #print pred
            if pred >= 0.3:
                if t["rating"] == 1:
                    tp += 1
                    oa_tp +=1
                else:
                    fp += 1
                    oa_fp +=1
            else:
                if t["rating"] == 1:
                    fn += 1
                    oa_fn +=1
                else:
                    tn += 1
                    oa_tn +=1
        precision = float(tp) / (tp + fp)
        recall = float(tp) / (fn + tp)
        f_measure = 2 * precision *recall/(precision + recall)
        DB.insert("insert into evaluation(pcision,recall,f_measure,pair_idx,new_method) values("+str(precision)+","+str(recall)+","+str(f_measure)+","+str(index)+",\"old\")")
        #favoriate-based logistical regression
        n_tp = 0
        n_tn = 0
        n_fp = 0
        n_fn = 0
        for t in test_data:
            limit_hotel_sql = "select hotel_id, sum(score) as rating, count(1) as ct from hotel_nearby_catering where type in (select cuisine from user_favoriate_cuisine where user_id = \""+t["user_id"]+"\") group by hotel_id"
            #print limit_hotel_sql
            hotels_rating = DB.query(limit_hotel_sql)
            potential_hotels = []
            for hr in hotels_rating:
                if hr["rating"] >= 2.5*hr["ct"]:
                    potential_hotels.append(hr["hotel_id"])
            hotel_len = len(potential_hotels)
            data = [[(1 if (str(i) + "-" + str(j) in users_rating) else 0) for j in potential_hotels] for i in users]
            u = 0
            if t["user_id"] in users:
                #old user
                #initialize data
#.........这里部分代码省略.........

# 需要导入模块: import DB [as 别名]
# 或者: from DB import query [as 别名]
def recommendWithFavor():
    hotel_sql = "select id,name_cn from hotel_dianping_info where dianping_name != 'n/a'"
    catering_count_sql = "select hotel_id, sum(count) as ct from hotel_nearby_catering group by hotel_id"
    user_sql = "select user_id from user_hotel_rating group by user_id"
    user_rating = "select user_id, hotel_id from user_hotel_rating where rating = 1"
    hotels_list = DB.query(hotel_sql)
    hotels_catering = DB.query(catering_count_sql)
    users_list = DB.query(user_sql)
    rating_list = DB.query(user_rating)
    hotels = {}
    caterings = {}
    users = []
    users_rating = set([])
    for htl in hotels_list:
        hotels[htl["id"]] = htl["name_cn"]
    for c in hotels_catering:
        caterings[c["hotel_id"]] = c["ct"]
    for user in users_list:
        users.append(user["user_id"])
    users_set = set(users)
    users_len = len(users)
    gv_len = len(gv.cuisine)
    for r in rating_list:
        users_rating.add(str(r["user_id"]) + "-" + str(r["hotel_id"]))
    while True:
        #name = "Yw"
        name = raw_input("Enter your name:")

        favor = raw_input("Below are several categories of cuisine, which one or more of them do you like."
                      "\n1 - 粤菜, 2 - 家常菜, 3 - 咖啡厅, 4 - 川菜, 5 - 海鲜, 6 - 湘菜, 7 - 新疆菜,"
                      "\n8 - 江浙菜, 9 - 韩国料理, 10 - 烧烤, 11 - 东北菜,12 - 西餐, 13 - 火锅,"
                      "\n14 - 北京菜, 15 - 面包甜点, 16 - 西北菜, 17 - 自助餐, 18 - 清真菜, 19 - 素菜, 20 - 日本菜."
                      "\nenter numbers with commas between:")
        #favor = "1,15"
        favors = favor.split(",")
        #get hotels which has a total score more than 2.5 * count(favors)

        limit_hotel_sql = "select hotel_id, sum(score) as rating from hotel_nearby_catering where type in ("
        valid_count = 0
        try:
            for f in favors:
                if int(f) <= gv_len:
                    limit_hotel_sql += "\""+gv.cuisine[int(f)-1] + "\","
                    valid_count += 1
        except Exception as e:
            print "error format in \""+favor+"\" , please enter valid parameters!"
            continue
        limit_hotel_sql = limit_hotel_sql[:-1] + ") group by hotel_id"
        print limit_hotel_sql
        hotels_rating = DB.query(limit_hotel_sql)
        potential_hotels = []
        min_score = 2.5 * valid_count
        for hr in hotels_rating:
            if hr["rating"] >= min_score:
                potential_hotels.append(hr["hotel_id"])
        hotel_len = len(potential_hotels)
        data = [[(1 if (str(i) + "-" + str(j) in users_rating) else 0) for j in potential_hotels] for i in users]
        u = 0
        if name in users_set:
            #old user
            #initialize data
            u = users.index(name)
        else:
            #new user
            data.append([0 for q in potential_hotels])
            u = users_len
        pred = copy.deepcopy(data)
        for m in range(0,hotel_len):
            if pred[u][m] == 0 :
                x = []
                y = []
                for row in range(0,users_len):
                    if row != u:
                        x.append(data[row][:m]+data[row][m+1:])
                        y.append(data[row][m])
                try:
                    lr = LogisticRegression()
                    lr.fit(x,y)
                    val = lr.predict_proba(data[u][:m]+data[u][m+1:])
                    pred[u][m] = round(val[0][1],2)
                except Exception as e:
                    pred[u][m] = 0
        #recommend hotels
        recommend_hotel_unsorted = []
        for idx in range(0,hotel_len):
            if data[u][idx] == 0:
                recommend_hotel_unsorted.append(hotel(potential_hotels[idx], pred[u][idx], caterings[potential_hotels[idx]]))
        #for rhu in recommend_hotel_unsorted:
        #    print rhu.score
        result = __topN(recommend_hotel_unsorted, 10)
        for res in result:
            print hotels[res.id]
            print res.score