예제 #1
0
def save_venue_photo_4sq(photo, venue_id):
    """Save photos from 4sq into table venue_photo_4sq
    See table 'venue_photo_4sq' for columns detail.
    Keyword arguments
    photo - photos from 4sq 
    venue_id - venue id for this venue. Used as a foreign key"""
    cursor = mysql_connect.connect_to_mysql()
    for pic in photo['photos']['items']:
        print pic
        print '\n\n'
        id = pic['id']
        createdAt = pic.get('createdAt', None)
        source_name = pic['source'].get('name', None)
        source_url = pic['source'].get('url', None)
        url = pic.get('url', None)
        user_id = pic['user'].get('id', None)
        user_firstName = pic['user'].get('firstName', None)
        user_lastName = pic['user'].get('lastName', None)
        user_photo = pic['user'].get('photo', None)
        user_gender = pic['user'].get('gender', None)
        user_homeCity = pic['user'].get('homeCity', None)
        user_tips = pic['user']['tips'].get('count', None)
        cursor.execute(
            "REPLACE INTO venue_photo_4sq (venue_id, id, createdAt, source_name, source_url, url, user_id, user_firstName, user_lastName, user_photo, user_gender, user_homeCity, user_tips) values (%s"
            + ",%s" * 12 + ")",
            (venue_id, id, createdAt, source_name, source_url, url, user_id,
             user_firstName, user_lastName, user_photo, user_gender,
             user_homeCity, user_tips))
예제 #2
0
def save_venue_tip(tips, venue_id):
    """Save tips from 4sq into table venue_tips.
    See table 'venue_tips' for columns detail.
    Keyword arguments
    tips - tips from 4sq given a venue id
    venue_id - venue id for this venue. This is used as a foreign key so that we could fetch venue details from venue_meta table"""
    cursor = mysql_connect.connect_to_mysql()
    for tip in tips['tips'].get('items', None):
        id = tip['id']
        createdAt = tip.get('createdAt', None)
        text = tip.get('text', None)
        likesCount = tip['likes'].get('count', None)
        user_id = tip['user'].get('id', None)
        user_firstName = tip['user'].get('firstName', None)
        user_lastName = tip['user'].get('lastName', None)
        user_photo = tip['user'].get('photo', None)
        user_gender = tip['user'].get('gender', None)
        user_homeCity = tip['user'].get('homeCity', None)
        user_tips = tip['user']['tips'].get('count', None)
        cursor.execute(
            "REPLACE INTO venue_tips (venue_id, id, createdAt, text, likesCount, user_id, user_firstName, user_lastName, user_photo, user_gender, user_homeCity, user_tips) values (%s"
            + ",%s" * 11 + ")",
            (venue_id, id, createdAt, text, likesCount, user_id,
             user_firstName, user_lastName, user_photo, user_gender,
             user_homeCity, user_tips))
예제 #3
0
def get_all_stats():
    sql = """
    select * from venue_stats;
    """
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(sql)
    return cursor.fetchall()
def get_all_stats():
    sql = """
    select * from venue_stats;
    """
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(sql)
    return cursor.fetchall()
예제 #5
0
def save_venue_meta(venue, crawl_user_id):
    """Save meta data for a single venue into mysql (table venue_meta)
See table 'venue_meta' for details of fields
Keyword arguments
venue - Venue object """
    #print venue['venue'].keys()
    venue_dic = venue['venue']
    id = venue_dic['id']
    name = venue_dic.get('name',None)
    lat = venue_dic['location']['lat']
    lng = venue_dic['location']['lng']
    categoriesID = venue_dic['categories'][0].get('id', None)
    categoriesName = venue_dic['categories'][0].get('name', None)

    postalCode = venue_dic['location'].get('postalCode',None)
    city = venue_dic['location'].get('city',None)
    state = venue_dic['location'].get('state',None)
    country = venue_dic['location'].get('country',None)
    if venue_dic['verified'] == True:
        verified = 1
    else:
        verified = 0
    checkinsCount = venue_dic['stats'].get('checkinsCount', None)
    usersCount = venue_dic['stats'].get('usersCount',None)
    tipCount = venue_dic['stats'].get('tipCount',None)
    url = venue_dic.get('url', None)
    likesCount = venue_dic['likes'].get('count',None)
    rating = venue_dic.get('rating',None)
    ratingSignals = venue_dic.get('ratingSignals',None)
    photoCount = venue_dic['photos'].get('count',None)
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute("REPLACE INTO venue_meta"+str(crawl_user_id)+" (id, name, lat, lng, categoriesID, categoriesName, postalCode, city, state, country, verified, checkinsCount, usersCount, tipCount, url, likesCount, rating, ratingSignals, photoCount) values (%s" + ",%s"*18 + ")", (id, name, lat, lng, categoriesID, categoriesName, postalCode, city, state, country, verified, checkinsCount, usersCount, tipCount, url, likesCount, rating, ratingSignals, photoCount) )
예제 #6
0
def save_venue_meta(venue):
    """Save meta data for a single venue into mysql (table venue_meta)
See table 'venue_meta' for details of fields
Keyword arguments
venue - Venue object """
    #print venue['venue'].keys()
    venue_dic = venue['venue']
    id = venue_dic['id']
    name = venue_dic.get('name', None)
    lat = venue_dic['location']['lat']
    lng = venue_dic['location']['lng']
    if len(venue_dic['categories']) > 0:
        categoriesID = venue_dic['categories'][0].get('id', None)
        categoriesName = venue_dic['categories'][0].get('name', None)
    else:
        categoriesID = categoriesName = None
    postalCode = venue_dic['location'].get('postalCode', None)
    city = venue_dic['location'].get('city', None)
    state = venue_dic['location'].get('state', None)
    country = venue_dic['location'].get('country', None)
    if venue_dic['verified'] == True:
        verified = 1
    else:
        verified = 0
    checkinsCount = venue_dic['stats'].get('checkinsCount', None)
    usersCount = venue_dic['stats'].get('usersCount', None)
    tipCount = venue_dic['stats'].get('tipCount', None)
    likesCount = venue_dic['likes'].get('count', None)
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(
        "REPLACE INTO manhattan_venues_meta (id, name, lat, lng, categoriesID, categoriesName, postalCode, city, state, country, verified, checkinsCount, usersCount, tipCount, likesCount) values (%s"
        + ",%s" * 14 + ")", (id, name, lat, lng, categoriesID, categoriesName,
                             postalCode, city, state, country, verified,
                             checkinsCount, usersCount, tipCount, likesCount))
def save_region_instagram(region):
    mid_lat = region[0]
    mid_lng = region[1]
    radius = region[2]
    curtime = datetime.datetime.fromtimestamp(region[3])
    pics_count = region[4]
    print curtime
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute("INSERT INTO pics_count(mid_lat, mid_lng, radius,time, pics_count) values(%s,%s,%s,%s,%s)" ,( mid_lat, mid_lng, radius, curtime, pics_count) )
예제 #8
0
def get_all_foursquare_ids():
    sql = """
    select distinct(foursquare_venue_id) as id, plazas_meta.name as name  from plazas_instaphoto, plazas_meta where plazas_instaphoto.foursquare_venue_id = plazas_meta.id
    """
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(sql)
    venue_id_name_dic = {}
    for r in cursor.fetchall():
        venue_id_name_dic[r['id']] = r['name']
    return venue_id_name_dic
예제 #9
0
def get_all_photo_fetched_venue_id_instagram():
    sql = """
    select distinct foursquare_venue_id from venue_photo_instagram;
    """
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(sql)
    ids = set()
    for r in cursor.fetchall():
        ids.add(r['foursquare_venue_id'])
    return ids
def get_all_foursquare_ids():
    sql = """
    select distinct(foursquare_venue_id) as id, plazas_meta.name as name  from plazas_instaphoto, plazas_meta where plazas_instaphoto.foursquare_venue_id = plazas_meta.id
    """
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(sql)
    venue_id_name_dic = {}
    for r in cursor.fetchall():
        venue_id_name_dic[r['id']] = r['name']
    return venue_id_name_dic
예제 #11
0
def save_venue_stats(venue_dic, foursquare_id):
    venue_dic = venue_dic['venue']
    checkinsCount = venue_dic['stats'].get('checkinsCount', None)
    usersCount = venue_dic['stats'].get('usersCount',None)
    tipCount = venue_dic['stats'].get('tipCount',None)
    photoCount = venue_dic['photos'].get('count',None)
    now_date = datetime.datetime.now()
    print now_date
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute("INSERT INTO venue_stats (id, checkinsCount, usersCount, tipCount, photoCount , time) values (%s, %s, %s, %s, %s, %s) ", (foursquare_id, checkinsCount, usersCount, tipCount, photoCount, now_date) )
예제 #12
0
def get_all_foursquare_ids():
    sql = """
    select name,id from venue_meta
    """
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(sql)
    venue_id_name_dic = {}
    for r in cursor.fetchall():
        venue_id_name_dic[r['id']] = r['name']
    return venue_id_name_dic
예제 #13
0
def get_all_foursquare_ids():
    sql = """
    select name,id from venue_meta
    """
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(sql)
    venue_id_name_dic = {}
    for r in cursor.fetchall():
        venue_id_name_dic[r['id']] = r['name']
    return venue_id_name_dic
예제 #14
0
def get_all_photo_fetched_venue_id_instagram():
    sql = """
    select distinct foursquare_venue_id from venue_photo_instagram;
    """
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(sql)
    ids = set()
    for r in cursor.fetchall():
        ids.add(r['foursquare_venue_id'])
    return ids
예제 #15
0
def save_region_instagram(region):
    mid_lat = region[0]
    mid_lng = region[1]
    radius = region[2]
    curtime = datetime.datetime.fromtimestamp(region[3])
    pics_count = region[4]
    print curtime
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(
        "INSERT INTO pics_count(mid_lat, mid_lng, radius,time, pics_count) values(%s,%s,%s,%s,%s)",
        (mid_lat, mid_lng, radius, curtime, pics_count))
예제 #16
0
def save_region(region):
    """Save region data for a single venue into mysql (table venue_meta)
See table 'region_foursquare' for details of fields
Keyword arguments
venue - Venue object """
    curtime = datetime.datetime.now()
    mid_lat = region[0]
    mid_lng = region[1]
    lat_length = region[2]
    lng_length = region[3]
    herenow = region[4]
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute("INSERT INTO herenow_region(mid_lat, mid_lng, lat_length, lng_length, time, herenow) values (%s" + ",%s"*5 + ")",(mid_lat, mid_lng, lat_length, lng_length, curtime, herenow) ) 
예제 #17
0
def save_venue_stats(venue_dic, foursquare_id):
    venue_dic = venue_dic['venue']
    checkinsCount = venue_dic['stats'].get('checkinsCount', None)
    usersCount = venue_dic['stats'].get('usersCount', None)
    tipCount = venue_dic['stats'].get('tipCount', None)
    photoCount = venue_dic['photos'].get('count', None)
    now_date = datetime.datetime.now()
    print now_date
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(
        "INSERT INTO venue_stats (id, checkinsCount, usersCount, tipCount, photoCount , time) values (%s, %s, %s, %s, %s, %s) ",
        (foursquare_id, checkinsCount, usersCount, tipCount, photoCount,
         now_date))
예제 #18
0
def save_venue_meta(venue):
    """Save meta data for a single venue into mysql (table venue_meta)
See table 'venue_meta' for details of fields
Keyword arguments
venue - Venue object """
    # print venue['venue'].keys()
    venue_dic = venue["venue"]
    id = venue_dic["id"]
    name = venue_dic.get("name", None)
    lat = venue_dic["location"]["lat"]
    lng = venue_dic["location"]["lng"]
    if len(venue_dic["categories"]) > 0:
        categoriesID = venue_dic["categories"][0].get("id", None)
        categoriesName = venue_dic["categories"][0].get("name", None)
    else:
        categoriesID = categoriesName = None
    postalCode = venue_dic["location"].get("postalCode", None)
    city = venue_dic["location"].get("city", None)
    state = venue_dic["location"].get("state", None)
    country = venue_dic["location"].get("country", None)
    if venue_dic["verified"] == True:
        verified = 1
    else:
        verified = 0
    checkinsCount = venue_dic["stats"].get("checkinsCount", None)
    usersCount = venue_dic["stats"].get("usersCount", None)
    tipCount = venue_dic["stats"].get("tipCount", None)
    likesCount = venue_dic["likes"].get("count", None)
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(
        "REPLACE INTO manhattan_venues_meta (id, name, lat, lng, categoriesID, categoriesName, postalCode, city, state, country, verified, checkinsCount, usersCount, tipCount, likesCount) values (%s"
        + ",%s" * 14
        + ")",
        (
            id,
            name,
            lat,
            lng,
            categoriesID,
            categoriesName,
            postalCode,
            city,
            state,
            country,
            verified,
            checkinsCount,
            usersCount,
            tipCount,
            likesCount,
        ),
    )
예제 #19
0
def save_photo_instagram(photos, foursquare_venue_id, instagram_venue_id,
                         table):
    """Save photos from instagram into table venue_photo_instagram
    See table venue_photo_instagram for columns detail.
    Notice, this table structure is different from venue_photo_4sq.
    Keyword arguments
    photos - photos from instagram
    venue_id venue id for this venue. This is used as a foreign key
    table - the table you want to save to"""
    cursor = mysql_connect.connect_to_mysql()
    for photo in photos:
        _id = photo.id
        _filter = photo.filter
        _tags = []
        if 'tags' in dir(photo):
            for t in photo.tags:
                _tags.append(t.name)
            #_tags = photo.tags
        if len(_tags) == 0:
            _tags = None
        else:
            _tags = json.dumps(_tags)
        _comments = []
        for c in photo.comments:
            _comments.append((c.user.username, c.text))
        if len(_comments) == 0:
            _comments = None
        else:
            _comments = json.dumps(_comments)
            #print _comments
        if len(photo.likes) == 0:
            _likes_count = 0
        else:
            _likes_count = len(photo.likes)
        caption = None
        if 'caption' in dir(photo):
            if 'text' in dir(photo.caption):
                caption = photo.caption.text
        _link = photo.link
        _username = photo.user.username
        _profile_picture = photo.user.profile_picture
        _standard_resolution = photo.images['standard_resolution']
        _created_time = photo.created_time
        cursor.execute(
            "REPLACE INTO " + table +
            " (foursquare_venue_id, instagram_venue_id, id, filter, tags, comments, likes_count, link, username, profile_picture, standard_resolution, created_time, caption) values(%s"
            + ",%s" * 12 + ")",
            (foursquare_venue_id, instagram_venue_id, _id, _filter, _tags,
             _comments, _likes_count, _link, _username, _profile_picture,
             _standard_resolution, _created_time, caption))
예제 #20
0
def save_herenow(venue):
    """Save hereNow data for a single venue into mysql (table venue_meta)
See table 'hereNow_foursquare' for details of fields
Keyword arguments
venue - Venue object """
    curtime = datetime.datetime.now()
    cursor = mysql_connect.connect_to_mysql()
    venue_dic = venue
    venue_id = venue_dic['id']
    name = venue_dic['name']
    hereNow = venue_dic['hereNow']['count']
    lat = venue_dic['location']['lat']
    lng = venue_dic['location']['lng']
    cursor.execute("INSERT INTO herenow(venue_id, hereNow, lat, lng, time, name) values (%s" + ",%s"*5 + ")",(venue_id, hereNow, lat, lng, curtime, name) ) 
    """
예제 #21
0
def save_region(region):
    """Save region data for a single venue into mysql (table venue_meta)
See table 'region_foursquare' for details of fields
Keyword arguments
venue - Venue object """
    curtime = datetime.datetime.now()
    mid_lat = region[0]
    mid_lng = region[1]
    lat_length = region[2]
    lng_length = region[3]
    herenow = region[4]
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(
        "INSERT INTO herenow_region(mid_lat, mid_lng, lat_length, lng_length, time, herenow) values (%s"
        + ",%s" * 5 + ")",
        (mid_lat, mid_lng, lat_length, lng_length, curtime, herenow))
예제 #22
0
def save_herenow(venue):
    """Save hereNow data for a single venue into mysql (table venue_meta)
See table 'hereNow_foursquare' for details of fields
Keyword arguments
venue - Venue object """
    curtime = datetime.datetime.now()
    cursor = mysql_connect.connect_to_mysql()
    venue_dic = venue
    venue_id = venue_dic['id']
    name = venue_dic['name']
    hereNow = venue_dic['hereNow']['count']
    lat = venue_dic['location']['lat']
    lng = venue_dic['location']['lng']
    cursor.execute(
        "INSERT INTO herenow(venue_id, hereNow, lat, lng, time, name) values (%s"
        + ",%s" * 5 + ")", (venue_id, hereNow, lat, lng, curtime, name))
    """
예제 #23
0
def save_region_photos(photos, mid_lat, mid_lng):
    """Save photos from instagram into table venue_photo_instagram
    See table venue_photo_instagram for columns detail.
    Notice, this table structure is different from venue_photo_4sq.
    Keyword arguments
    photos - photos from instagram
    venue_id venue id for this venue. This is used as a foreign key"""
    cursor = mysql_connect.connect_to_mysql()
    for photo in photos:
        _id = photo.id
        _filter = photo.filter
        _tags = []
        if 'tags' in dir(photo):
            for t in photo.tags:
                _tags.append(t.name)
            #_tags = photo.tags
        if len(_tags) == 0:
            _tags = None
        else:
            _tags = json.dumps(_tags)
        _comments = []
        for c in photo.comments:
            _comments.append((c.user.username, c.text))
        if len(_comments) == 0:
            _comments = None
        else:
            _comments = json.dumps(_comments)
            #print _comments
        if len(photo.likes) == 0:
            _likes_count = 0
        else:
            _likes_count = len(photo.likes)
        _link = photo.link
        _username = photo.user.username
        _profile_picture = photo.user.profile_picture
        _standard_resolution = photo.images['standard_resolution']
        _created_time = photo.created_time
        _mid_lat = mid_lat
        _mid_lng = mid_lng
        #print _id, _filter, _tags, _comments, _likes_count, _link, _username, _profile_picture, _standard_resolution, _created_time
        cursor.execute(
            "REPLACE INTO region_photos (  id, filter, tags, comments, likes_count, link, username, profile_picture, standard_resolution, created_time, mid_lat, mid_lng) values(%s"
            + ",%s" * 11 + ")",
            (_id, _filter, _tags, _comments, _likes_count, _link, _username,
             _profile_picture, _standard_resolution, _created_time, _mid_lat,
             _mid_lng))
def save_photo_instagram(photos, foursquare_venue_id, instagram_venue_id, table):
    """Save photos from instagram into table venue_photo_instagram
    See table venue_photo_instagram for columns detail.
    Notice, this table structure is different from venue_photo_4sq.
    Keyword arguments
    photos - photos from instagram
    venue_id venue id for this venue. This is used as a foreign key
    table - the table you want to save to"""
    cursor = mysql_connect.connect_to_mysql()
    for photo in photos:
        _id = photo.id
        _filter = photo.filter
        _tags = []
        if 'tags' in dir(photo):
            for t in photo.tags:
                _tags.append(t.name)
            #_tags = photo.tags
        if len(_tags)==0:
            _tags = None
        else:
            _tags = json.dumps(_tags)
        _comments = []
        for c in photo.comments:
            _comments.append( (c.user.username, c.text) )
        if len(_comments)==0:
            _comments = None
        else:
            _comments = json.dumps(_comments)
            #print _comments
        if len(photo.likes)==0:
            _likes_count = 0
        else:
            _likes_count = len(photo.likes)
        caption = None
        if 'caption' in dir(photo):
            if 'text' in dir(photo.caption):
                caption = photo.caption.text
        _link = photo.link
        _username = photo.user.username
        _profile_picture = photo.user.profile_picture
        _standard_resolution = photo.images['standard_resolution']
        _created_time = photo.created_time
        cursor.execute("REPLACE INTO "+table+" (foursquare_venue_id, instagram_venue_id, id, filter, tags, comments, likes_count, link, username, profile_picture, standard_resolution, created_time, caption) values(%s" + ",%s"*12 + ")",(foursquare_venue_id,instagram_venue_id, _id, _filter, _tags, _comments, _likes_count, _link, _username, _profile_picture, _standard_resolution, _created_time, caption) )
예제 #25
0
def save_venue_meta(venue, table):
    """Save meta data for a single venue into mysql (table venue_meta)
See table 'venue_meta' for details of fields
Keyword arguments
venue - Venue object """
    #print venue['venue'].keys()
    venue_dic = venue['venue']
    id = venue_dic['id']
    name = venue_dic.get('name', None)
    lat = venue_dic['location']['lat']
    lng = venue_dic['location']['lng']
    categoriesID = venue_dic['categories'][0].get('id', None)
    categoriesName = venue_dic['categories'][0].get('name', None)

    postalCode = venue_dic['location'].get('postalCode', None)
    city = venue_dic['location'].get('city', None)
    state = venue_dic['location'].get('state', None)
    country = venue_dic['location'].get('country', None)
    if venue_dic['verified'] == True:
        verified = 1
    else:
        verified = 0
    checkinsCount = venue_dic['stats'].get('checkinsCount', None)
    usersCount = venue_dic['stats'].get('usersCount', None)
    tipCount = venue_dic['stats'].get('tipCount', None)
    url = venue_dic.get('url', None)
    likesCount = venue_dic['likes'].get('count', None)
    rating = venue_dic.get('rating', None)
    ratingSignals = venue_dic.get('ratingSignals', None)
    photoCount = venue_dic['photos'].get('count', None)
    tmp_list = [
        id, name, lat, lng, postalCode, city, state, country, verified,
        checkinsCount, usersCount, tipCount, url, likesCount, rating,
        ratingSignals, photoCount
    ]
    cursor = mysql_connect.connect_to_mysql()
    cursor.execute(
        "REPLACE INTO " + table +
        " (id, name, lat, lng, categoriesID, categoriesName, postalCode, city, state, country, verified, checkinsCount, usersCount, tipCount, url, likesCount, rating, ratingSignals, photoCount) values (%s"
        + ",%s" * 18 + ")",
        (id, name, lat, lng, categoriesID, categoriesName, postalCode, city,
         state, country, verified, checkinsCount, usersCount, tipCount, url,
         likesCount, rating, ratingSignals, photoCount))
예제 #26
0
def save_venue_tip(tips, venue_id):
    """Save tips from 4sq into table venue_tips.
    See table 'venue_tips' for columns detail.
    Keyword arguments
    tips - tips from 4sq given a venue id
    venue_id - venue id for this venue. This is used as a foreign key so that we could fetch venue details from venue_meta table"""
    cursor = mysql_connect.connect_to_mysql()
    for tip in tips['tips'].get('items', None):
        id = tip['id']
        createdAt = tip.get('createdAt',None)
        text = tip.get('text', None)
        likesCount = tip['likes'].get('count', None)
        user_id = tip['user'].get('id',None)
        user_firstName = tip['user'].get('firstName',None)
        user_lastName = tip['user'].get('lastName', None)
        user_photo = tip['user'].get('photo',None)
        user_gender = tip['user'].get('gender',None)
        user_homeCity = tip['user'].get('homeCity',None)
        user_tips = tip['user']['tips'].get('count',None)
        cursor.execute("REPLACE INTO venue_tips (venue_id, id, createdAt, text, likesCount, user_id, user_firstName, user_lastName, user_photo, user_gender, user_homeCity, user_tips) values (%s"+",%s"*11+")", (venue_id, id, createdAt, text, likesCount, user_id, user_firstName, user_lastName, user_photo, user_gender, user_homeCity, user_tips) )
예제 #27
0
def save_region_photos(photos, mid_lat, mid_lng):
    """Save photos from instagram into table venue_photo_instagram
    See table venue_photo_instagram for columns detail.
    Notice, this table structure is different from venue_photo_4sq.
    Keyword arguments
    photos - photos from instagram
    venue_id venue id for this venue. This is used as a foreign key"""
    cursor = mysql_connect.connect_to_mysql()
    for photo in photos:
        _id = photo.id
        _filter = photo.filter
        _tags = []
        if 'tags' in dir(photo):
            for t in photo.tags:
                _tags.append(t.name)
            #_tags = photo.tags
        if len(_tags)==0:
            _tags = None
        else:
            _tags = json.dumps(_tags)
        _comments = []
        for c in photo.comments:
            _comments.append( (c.user.username, c.text) )
        if len(_comments)==0:
            _comments = None
        else:
            _comments = json.dumps(_comments)
            #print _comments
        if len(photo.likes)==0:
            _likes_count = 0
        else:
            _likes_count = len(photo.likes)
        _link = photo.link
        _username = photo.user.username
        _profile_picture = photo.user.profile_picture
        _standard_resolution = photo.images['standard_resolution']
        _created_time = photo.created_time
        _mid_lat = mid_lat;
        _mid_lng = mid_lng;
        #print _id, _filter, _tags, _comments, _likes_count, _link, _username, _profile_picture, _standard_resolution, _created_time
        cursor.execute("REPLACE INTO region_photos (  id, filter, tags, comments, likes_count, link, username, profile_picture, standard_resolution, created_time, mid_lat, mid_lng) values(%s" + ",%s"*11 + ")",(_id, _filter, _tags, _comments, _likes_count, _link, _username, _profile_picture, _standard_resolution, _created_time, _mid_lat, _mid_lng) )
예제 #28
0
def save_venue_photo_4sq(photo, venue_id, crawl_user_id):
    """Save photos from 4sq into table venue_photo_4sq
    See table 'venue_photo_4sq' for columns detail.
    Keyword arguments
    photo - photos from 4sq 
    venue_id - venue id for this venue. Used as a foreign key"""
    cursor = mysql_connect.connect_to_mysql()
    for pic in photo['photos']['items']:
        print pic
        print '\n\n'
        id = pic['id']
        createdAt = pic.get('createdAt',None)
        source_name = pic['source'].get('name', None)
        source_url = pic['source'].get('url', None)
        url = pic.get('url', None)
        user_id = pic['user'].get('id',None)
        user_firstName = pic['user'].get('firstName',None)
        user_lastName = pic['user'].get('lastName', None)
        user_photo = pic['user'].get('photo',None)
        user_gender = pic['user'].get('gender',None)
        user_homeCity = pic['user'].get('homeCity',None)
        user_tips = pic['user']['tips'].get('count',None)
        cursor.execute("REPLACE INTO venue_photo_4sq"+str(crawl_user_id)+" (venue_id, id, createdAt, source_name, source_url, url, user_id, user_firstName, user_lastName, user_photo, user_gender, user_homeCity, user_tips) values (%s" + ",%s"*12+")", (venue_id, id, createdAt, source_name, source_url, url ,user_id, user_firstName, user_lastName, user_photo, user_gender, user_homeCity, user_tips) )