def populate_foursquare(cur):
cur.execute("SELECT Coupons.id,name,title,value,discount,url,lat,lng,phone,yelp_r \
FROM Coupons JOIN Additional ON Coupons.id=Additional.id")
coupons = cur.fetchall()
errors = ""
places, reviews, mItems = [],[],[]
for coupon in coupons:
( ids, name, title, value, discount, url, lat, lng, phone, yelp_r) = coupon
print 'Preparing to phone match.'
sleep(randint(2,4))
foursquare_id = fs.phoneMatch( lat, lng, phone, name)
if foursquare_id[0] == "no_id":
errors += ','.join(foursquare_id) +'\n'
continue
sleep(randint(2,4))
( name, revs, menuItems ) = fs.localInfo( foursquare_id )
for item in menuItems:
if (len(str(item))<2):
continue
itemKey = hasher( item )
try:
(iName, iDesc, iPrice) = item
except KeyError:
print item
continue
except ValueError:
print item
continue
mItems.append( ( itemKey, foursquare_id , iName, iPrice, iDesc) )
reviews.extend( [ ( hasher( (review,foursquare_id) ), foursquare_id, review, -1 , 'unk' ) for review in revs if (len(review.strip())>0)] )
places.append( ( ids, foursquare_id, name, lat, lng, yelp_r ) )
with open('populate_foursquare.log','w') as outfile:
outfile.write(errors)
cur.executemany('INSERT IGNORE INTO Menus\
(itemId, restaurantId, itemName, itemPrice, itemDesc) \
VALUES (%s,%s,%s,%s,%s)',mItems)
cur.executemany( "INSERT IGNORE INTO Reviews(reviewId, restaurantId, review, itemId, sentiment) \
VALUES (%s,%s,%s,%s,%s)", reviews )
cur.executemany( "INSERT IGNORE INTO Restaurants(idCoupon, restaurantId, name, lat, lng, rating)\
VALUES (%s,%s,%s,%s,%s,%s)",places)
def populate_coupons(cur):
#Populate coupon tables from scrapped data.
cur.execute("SELECT * FROM ls_links WHERE city LIKE 'San Francisco%'")
ls_coupons = []
links = cur.fetchall()
for link in links:
sleep(randint(1,5))
ls_coupons.extend( ls.dealExtractorURL( link[1]+'/food-deals' ) )
unique_ls = list(set(ls_coupons))
gp_coupons = gp.dealExtractor( open(gp.LOCAL, 'r'))
coupons = unique_ls + gp_coupons
sql_coupon = []
for coupon in coupons:
sql_coupon.append( (hasher(coupon),) + coupon )
cur.executemany("INSERT INTO Coupons (id, name, title, value, discount, url, site) \
VALUES (%s,%s,%s,%s,%s,%s,%s)",sql_coupon)