def youtube_update_videos(idcanal, idlistavideos): logging.info('Iniciando função de atualização de vídeos do canal %d com até 90 dias.', idcanal) url = ('https://www.googleapis.com/youtube/v3/playlistItems?part=snippet' '&maxResults=50&playlistId=' + idlistavideos + '&key=' + api_key) search = get_request_and_jsonize(url) registros = search['items'] if len(registros) == 0: logging.info('Nenhum vídeo no canal de id %d', idcanal) return since = datetime.date.today() - datetime.timedelta(days=DIAS) sql = 'UPDATE yt_videos SET `qtdvisualizacoes` = (%s), `qtdcurtidas` = (%s), `qtddescurtidas` = (%s), ' \ '`qtdfavoritados` = (%s), `qtdcomentarios` = (%s) WHERE `idyoutube` = (%s)' count = 0 while True: for reg in registros: snippet = reg['snippet'] idyoutube = snippet['resourceId']['videoId'] datahora = datetime.datetime.strptime(snippet['publishedAt'], '%Y-%m-%dT%H:%M:%S.000Z') if datahora.date() < since: conn.commit() logging.info('Atualizados os últimos %d vídeos', count) return url = ('https://www.googleapis.com/youtube/v3/videos?part=statistics&id=' + idyoutube + '&key=' + api_key) details = get_request_and_jsonize(url) statistics = details['items'][0]['statistics'] if 'statistics' in details['items'][0] else None if statistics: qtdvisualizacoes = statistics['viewCount'] if 'viewCount' in statistics else 0 qtdcurtidas = statistics['likeCount'] if 'likeCount' in statistics else 0 qtddescurtidas = statistics['dislikeCount'] if 'dislikeCount' in statistics else 0 qtdfavoritados = statistics['favoriteCount'] if 'favoriteCount' in statistics else 0 qtdcomentarios = statistics['commentCount'] if 'commentCount' in statistics else 0 else: qtdvisualizacoes = qtdcurtidas = qtddescurtidas = qtdfavoritados = qtdcomentarios = 0 try: cur.execute(sql, (qtdvisualizacoes, qtdcurtidas, qtddescurtidas, qtdfavoritados, qtdcomentarios, idyoutube)) count += 1 except pymysql.err.IntegrityError as excpt: logging.error('%s', excpt) conn.commit() logging.info('Últimos %d vídeos do canal de id %d atualizados.', count, idcanal) if 'nextPageToken' in search: url = ('https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&maxResults=50&pageToken=' + search['nextPageToken'] + '&playlistId=' + idlistavideos + '&key=' + api_key) search = get_request_and_jsonize(url) registros = search['items'] else: break
def facebook_update_comments_post(idpost, idfacepost): url = (graph_url + api_version + idfacepost + '/comments?limit=5000&order=reverse_chronological&fields=id' 'reactions.limit(0).summary(total_count),' 'reactions.type(LOVE).limit(0).summary(total_count).as(reactions_love),' 'reactions.type(WOW).limit(0).summary(total_count).as(reactions_wow),' 'reactions.type(HAHA).limit(0).summary(total_count).as(reactions_haha),' 'reactions.type(LIKE).limit(0).summary(total_count).as(reactions_like),' 'reactions.type(SAD).limit(0).summary(total_count).as(reactions_sad),' 'reactions.type(ANGRY).limit(0).summary(total_count).as(reactions_angry),' 'reactions.type(THANKFUL).limit(0).summary(total_count).as(reactions_thankful).summary(total_count),' 'comment_count&filter=stream&access_token=' + access_token) comments = get_request_and_jsonize(url) if 'error' in comments: logging.error('%s', comments['error']) return -1 sql = 'UPDATE `fb_comentarios` SET `curtidas` = (%s), `reacoes_like` = (%s), " \ "`reacoes_haha` = (%s), `reacoes_wow` = (%s), `reacoes_sad` = (%s), `reacoes_love` = (%s), " \ "`reacoes_angry` = (%s), `reacoes_thankful` = (%s), `respostas` = (%s) WHERE `idfacebook` = (%s)' if 'data' not in comments: return if len(comments['data']) == 0: return while True: for comment in comments['data']: idfacebook = comment['id'] curtidas = comment['reactions']['summary']['total_count'] if 'reactions' in comment else 0 reacoes_like = comment['reactions_like']['summary']['total_count'] if 'reactions_like' in comment else 0 reacoes_haha = comment['reactions_haha']['summary']['total_count'] if 'reactions_haha' in comment else 0 reacoes_wow = comment['reactions_wow']['summary']['total_count'] if 'reactions_wow' in comment else 0 reacoes_sad = comment['reactions_sad']['summary']['total_count'] if 'reactions_sad' in comment else 0 reacoes_love = comment['reactions_love']['summary']['total_count'] if 'reactions_love' in comment else 0 reacoes_angry = comment['reactions_angry']['summary']['total_count'] if 'reactions_angry' in comment else 0 reacoes_thankful = comment['reactions_thankful']['summary']['total_count'] if 'reactions_thankful' in comment else 0 respostas = comment['comment_count'] cur.execute(sql, (curtidas, reacoes_like, reacoes_haha, reacoes_wow, reacoes_sad, reacoes_love, reacoes_angry, reacoes_thankful, respostas, idfacebook)) conn.commit() if 'next' in comments['paging']: comments = get_request_and_jsonize(comments['paging']['next']) else: break logging.info('Atualizados os comentários do post %d', idpost)
def instagram_get_perfil_stats(idperfil, idinstagram): hoje = datetime.date.today() sql = 'SELECT * FROM insta_perfis_stats WHERE `idperfil` = (%s) AND DATE(datahora) = (%s)' cur.execute(sql, (idperfil, hoje)) existe = cur.fetchone() if existe: logging.info('Estatísticas já capturadas hoje.') return logging.info('Iniciando a captura de estatísticas do perfil de id %d.', idperfil) url = ('https://api.instagram.com/v1/users/' + idinstagram + '?access_token=' + access_token) search = get_request_and_jsonize(url) perfil = search['data']['counts'] datahora = datetime.datetime.utcnow() qtdmidias = perfil['media'] qtdseguidores = perfil['followed_by'] qtdseguidos = perfil['follows'] sql = 'INSERT INTO `insta_perfis_stats` (`idperfil`, `datahora`, `qtdmidias`, `qtdseguidores`, `qtdseguidos`)' \ 'VALUES (%s, %s, %s, %s, %s)' cur.execute(sql, (idperfil, datahora, qtdmidias, qtdseguidores, qtdseguidos)) conn.commit()
def youtube_get_channel_stats(idcanal, idyoutube): hoje = datetime.date.today() sql = 'SELECT * FROM yt_canais_stats WHERE `idcanal` = (%s) AND DATE(datahora) = (%s)' cur.execute(sql, (idcanal, hoje)) existe = cur.fetchone() if existe: logging.info('Estatísticas já capturadas hoje.') return logging.info('Iniciando captura de estatísticas do canal de id %d.', idcanal) url = ('https://www.googleapis.com/youtube/v3/channels?part=statistics&id=' + idyoutube + '&key=' + api_key) search = get_request_and_jsonize(url) if len(search['items']) == 0: logging.warning('Canal %d não existe.', idcanal) return perfil = search['items'][0]['statistics'] qtdvisualizacoes = perfil['viewCount'] qtdseguidores = perfil['subscriberCount'] qtdvideos = perfil['videoCount'] datahora = datetime.datetime.utcnow() sql = 'INSERT INTO `yt_canais_stats` (`idcanal`, `datahora`, `qtdvisualizacoes`, `qtdseguidores`, `qtdvideos`)' \ 'VALUES (%s, %s, %s, %s, %s)' cur.execute(sql, (idcanal, datahora, qtdvisualizacoes, qtdseguidores, qtdvideos)) conn.commit()
def youtube_get_channel(idcanal, idyoutube, username): logging.info("Captura de informações do canal de id %d iniciada.", idcanal) if idyoutube is None: url = ('https://www.googleapis.com/youtube/v3/channels?part=id,snippet,contentDetails' '&forUsername='******'&key=' + api_key) else: url = ('https://www.googleapis.com/youtube/v3/channels?part=id,snippet,contentDetails' '&id=' + idyoutube + '&key=' + api_key) search = get_request_and_jsonize(url) perfil = search['items'][0] idyoutube = perfil['id'] nome = perfil['snippet']['title'] descricao = perfil['snippet']['description'] urlimagem = perfil['snippet']['thumbnails']['medium']['url'] if '.png' in urlimagem: nomeimagem = idyoutube + '.png' else: nomeimagem = idyoutube + '.jpg' idlistavideos = perfil['contentDetails']['relatedPlaylists']['uploads'] sql = 'UPDATE `yt_canais` SET `idyoutube` = (%s), `nome` = (%s), `descricao` = (%s), ' \ '`urlimagem` = (%s), `nomeimagem` = (%s), `idlistavideos` = (%s) WHERE `idcanal`= (%s)' try: cur.execute(sql, (idyoutube, nome, descricao, urlimagem, nomeimagem, idlistavideos, idcanal)) conn.commit() except pymysql.err.IntegrityError as excpt: logging.error('%s', excpt) return idyoutube, idlistavideos
def youtube_update_first_order_comments_video(idvideo, idvideo_youtube): url = ('https://www.googleapis.com/youtube/v3/commentThreads?part=id,snippet' '&maxResults=100&textFormat=plainText&videoId=' + idvideo_youtube + '&key=' + api_key) search = get_request_and_jsonize(url) if 'items' in search: registros = search['items'] else: logging.info('Comentários desativados para o vídeo de id %d', idvideo) return if len(registros) == 0: logging.info('Nenhum comentário no vídeo de id %d', idvideo) return sql = 'UPDATE yt_comentarios SET `qtdcurtidas` = (%s), `qtdrespostas` = (%s) WHERE `idyoutube` = (%s)' # Loop de captura de comentários de um dado vídeo count = 0 while True: for reg in registros: idyoutube = reg['id'] comentario = reg['snippet']['topLevelComment']['snippet'] qtdcurtidas = comentario['likeCount'] qtdrespostas = reg['snippet']['totalReplyCount'] try: cur.execute(sql, (qtdcurtidas, qtdrespostas, idyoutube)) count += 1 except pymysql.err.IntegrityError as excpt: logging.error('%s', excpt) conn.commit() logging.info('Atualizados os últimos %d comentários do vídeo de id %d', count, idvideo) if 'nextPageToken' in search: next_page_token = search['nextPageToken'] url = ('https://www.googleapis.com/youtube/v3/commentThreads?part=id,replies,snippet&maxResults=100' '&textFormat=plainText&videoId=' + idvideo_youtube + '&pageToken=' + next_page_token + '&key=' + api_key) search = get_request_and_jsonize(url) if 'items' in registros: registros = search['items'] else: break else: break
def instagram_get_perfil(idperfil, username): logging.info('Iniciando captura do perfil %s', username) url = ('https://api.instagram.com/v1/users/search/?q=' + username + '&count=10&access_token=' + access_token) search = get_request_and_jsonize(url) if len(search['data']) == 0: logging.error('O perfil %s não existe.', username) return for perfil in search['data']: if perfil['username'] == username: idinstagram = perfil['id'] nome = perfil['full_name'] urlimagem = perfil['profile_picture'] if '.png' in urlimagem: nomeimagem = idinstagram + '.png' else: nomeimagem = idinstagram + '.jpg' # TODO safe_retrieve(urlimagem, nomeimagem) url = ('https://api.instagram.com/v1/users/' + idinstagram + '?access_token=' + access_token) search = get_request_and_jsonize(url) perfil = search['data'] sobre = perfil['bio'] website = perfil['website'] sql = 'UPDATE `insta_perfis` SET `idinstagram` = (%s), `nome` = (%s), `sobre` = (%s), `website` = (%s), ' \ '`urlimagem` = (%s), `nomeimagem` = (%s) WHERE `idperfil` = (%s)' cur.execute(sql, (idinstagram, nome, sobre, website, urlimagem, nomeimagem, idperfil)) conn.commit() return idinstagram else: continue logging.critical('O perfil %s não existe.', username) return
def facebook_get_page(idempresa, idperfil, idorusername): logging.info('Iniciando captura da página: %s', idorusername) url = (graph_url + api_version + idorusername + '/?access_token=' + access_token + '&fields=id,about,username,description,name,link,website,picture.type(large)') page = get_request_and_jsonize(url) if 'error' in page: logging.critical('%s', page['error']['message']) raise Exception idfacebook = page['id'] nome = page['name'] sobre = page['about'] if 'about' in page else None descricao = page.get('description') website = page['website'] if 'website' in page else None urlimagem = page['picture']['data']['url'] if '.png' in urlimagem: nomeimagem = idfacebook + '.png' empresaimagem = str(idempresa) + '.png' else: empresaimagem = str(idempresa) + '.jpg' nomeimagem = idfacebook + '.jpg' # safe_retrieve(urlimagem, pathface + nomeimagem) safe_retrieve(urlimagem, pathempresa + empresaimagem) sql = 'UPDATE empresas SET `imagem` = (%s) WHERE `idempresa` = (%s)' try: cur.execute(sql, (empresaimagem, idempresa)) conn.commit() except Exception as excpt: logging.error('%s', excpt) link = page['link'] sql = "UPDATE `fb_perfis` SET `idfacebook` = (%s), `nome` = (%s), `sobre` = (%s), `descricao` = (%s)," \ "`urlimagem` = (%s), `nomeimagem` = (%s), `website` = (%s), `link` = (%s)" \ "WHERE `idperfil` = (%s)" try: cur.execute(sql, (idfacebook, nome, sobre, descricao, urlimagem, nomeimagem, website, link, idperfil)) conn.commit() except Exception as excpt: logging.critical('%s', excpt) raise Exception return idfacebook
def instagram_get_comentarios_post(idpost, idpost_instagram, datahoraultimocomentario): url = ('https://api.instagram.com/v1/media/' + idpost_instagram + '/comments?access_token=' + access_token) search = get_request_and_jsonize(url) if 'data' not in search: logging.error('Erro na captura de comentários do post %d', idpost) return comentarios = search['data'] if len(comentarios) == 0: logging.info('Nenhum comentário no post de id %d.', idpost) return novadatahoraultimocomentario = datetime.datetime.fromtimestamp(int(comentarios[-1]['created_time'])) if datahoraultimocomentario: if novadatahoraultimocomentario <= datahoraultimocomentario: logging.info('Nenhum comentário novo no post de id %d', idpost) return sql = 'UPDATE `insta_posts` SET `datahoraultimocomentario` = (%s) WHERE idpost = (%s)' cur.execute(sql, (novadatahoraultimocomentario, idpost)) conn.commit() sql = 'INSERT INTO `insta_comentarios`' \ '(`idpost`, `idinstagram`, `postagem`, `datahora`, `idusuario`, `nomeusuario`)' \ 'VALUES (%s, %s, %s, %s, %s, %s) ' count = 0 for registro in reversed(comentarios): datahora = datetime.datetime.fromtimestamp(int(registro['created_time'])) if datahoraultimocomentario: if datahora <= datahoraultimocomentario: conn.commit() logging.info('Capturados os últimos %d comentários do post %d', count, idpost) return idinstagram = registro['id'] postagem = registro['text'] idusuario = registro['from']['id'] nomeusuario = registro['from']['username'] try: cur.execute(sql, (idpost, idinstagram, postagem, datahora, idusuario, nomeusuario)) count += 1 except Exception as excpt: logging.error('%s', excpt) conn.commit() logging.info('Capturados os últimos %d comentários do post de id %d', count, idpost)
def youtube_update_replies_comment(idyoutube_comentario): url = ('https://www.googleapis.com/youtube/v3/comments?part=id,snippet' '&maxResults=100&textFormat=plainText&parentId=' + idyoutube_comentario + '&key=' + api_key) search = get_request_and_jsonize(url) if 'items' not in search: logging.info('Nenhum reply encontrado.') return registros = search['items'] sql = 'UPDATE yt_comentarios SET `qtdcurtidas` = (%s) WHERE `idyoutube` = (%s)' count = 0 while True: for reply in registros: idyoutube = reply['id'] snippet = reply['snippet'] qtdcurtidas = snippet['likeCount'] try: cur.execute(sql, (qtdcurtidas, idyoutube)) count += 1 except pymysql.err.IntegrityError as excpt: logging.error('%s', excpt) conn.commit() if 'nextPageToken' in search: next_page_token = search['nextPageToken'] url = ('https://www.googleapis.com/youtube/v3/comments?part=id,snippet' '&maxResults=100&textFormat=plainText&parentId=' + idyoutube_comentario + '&pageToken=' + next_page_token + '&key=' + api_key) search = get_request_and_jsonize(url) registros = search['items'] else: return count
def instagram_update_posts(idperfil, idinstagram): logging.info('Iniciando atualização de posts do perfil de id %d', idperfil) url = ('https://api.instagram.com/v1/users/' + idinstagram + '/media/recent/?count=33&access_token=' + access_token) search = get_request_and_jsonize(url) posts = search['data'] since = datetime.date.today() - datetime.timedelta(days=DIAS) sql = 'UPDATE `insta_posts` SET `qtdcomentarios` = (%s), `qtdcurtidas` = (%s) WHERE `idinstagram` = (%s)' count = 0 while True: for registro in posts: idinstagram = registro['id'] datahora = datetime.datetime.fromtimestamp(int(registro['created_time'])) if datahora.date() < since: conn.commit() logging.info('Atualizados os últimos %d posts', count) return qtdcomentarios = registro['comments']['count'] qtdcurtidas = registro['likes']['count'] count += 1 cur.execute(sql, (qtdcomentarios, qtdcurtidas, idinstagram)) conn.commit() if 'pagination' in search and 'next_url' in search['pagination']: search = get_request_and_jsonize(search['pagination']['next_url']) posts = search['data'] if len(posts) == 0: break else: break
def instagram_update_perfil(idperfil, idinstagram): logging.info('Iniciando atualização do perfil de id %d', idperfil) url = ('https://api.instagram.com/v1/users/' + idinstagram + '?access_token=' + access_token) perfil = get_request_and_jsonize(url)['data'] nome = perfil['full_name'] username = perfil['username'] urlimagem = perfil['profile_picture'] # nomefoto = username + ".jpg" # urllib.request.urlretrieve(urlfoto, nomefoto) sobre = perfil['bio'] website = perfil['website'] ultimaatualizacao = datetime.date.today() sql = 'UPDATE `insta_perfis` SET `nome` = (%s), `username` = (%s), `urlimagem` = (%s),' \ '`sobre`= (%s), `website` = (%s), `ultimaatualizacao` = (%s) WHERE idperfil = (%s)' cur.execute(sql, (nome, username, urlimagem, sobre, website, ultimaatualizacao, idperfil)) conn.commit()
def facebook_get_page_stats(idperfil, idfacebook): logging.info('Iniciando captura das estatísticas da página de id %d', idperfil) hoje = datetime.date.today() sql = 'SELECT * FROM fb_perfis_stats WHERE `idperfil` = (%s) AND DATE(datahora) = (%s)' cur.execute(sql, (idperfil, hoje)) existe = cur.fetchone() if existe: logging.info('Estatísticas já capturadas hoje.') return url = (graph_url + api_version + idfacebook + '/?access_token=' + access_token + '&fields=fan_count,overall_star_rating,rating_count,' 'talking_about_count') page = get_request_and_jsonize(url) if 'error' in page: logging.critical('%s', page['error']['message']) raise Exception datahora = datetime.datetime.utcnow() qtdlikes = page['fan_count'] qtdpessoasfalando = page['talking_about_count'] qtdestrelas = page['overall_star_rating'] if 'overall_star_rating' in page else 0 qtdavaliacoes = page['rating_count'] sql = "INSERT INTO `fb_perfis_stats` (`idperfil`, `datahora`, `qtdlikes`, `qtdestrelas`," \ "`qtdavaliacoes`, `qtdpessoasfalando`) VALUES (%s, %s, %s, %s, %s, %s)" try: cur.execute(sql, (idperfil, datahora, qtdlikes, qtdestrelas, qtdavaliacoes, qtdpessoasfalando)) conn.commit() except Exception as excpt: logging.critical('%s', excpt) raise Exception
def youtube_update_channel(idcanal, idyoutube): logging.info("Atualização de informações do canal de id %d iniciada.", idcanal) url = ('https://www.googleapis.com/youtube/v3/channels?part=id,snippet,contentDetails' '&id=' + idyoutube + '&key=' + api_key) search = get_request_and_jsonize(url) if len(search['items']) == 0: logging.error('Perfil possivelmente não existe mais.') return -1 perfil = search['items'][0] nome = perfil['snippet']['title'] descricao = perfil['snippet']['description'] urlimagem = perfil['snippet']['thumbnails']['medium']['url'] ultimaatualizacao = datetime.date.today() sql = 'UPDATE `yt_canais` SET `nome` = (%s), `descricao` = (%s), `urlimagem` = (%s),' \ '`ultimaatualizacao` = (%s) WHERE `idcanal` = (%s)' try: cur.execute(sql, (nome, descricao, urlimagem, ultimaatualizacao, idcanal)) conn.commit() except Exception as excpt: logging.error('%s', excpt)
def facebook_update_posts(idfaceperfil): logging.info("Iniciando função de atualização de posts com até 90 dias") since = datetime.date.today() - datetime.timedelta(days=DIAS) url = (graph_url + api_version + idfaceperfil + '/posts?limit=100&fields=shares,' 'reactions.limit(0).summary(total_count),' 'reactions.type(LOVE).limit(0).summary(total_count).as(reactions_love),' 'reactions.type(WOW).limit(0).summary(total_count).as(reactions_wow),' 'reactions.type(HAHA).limit(0).summary(total_count).as(reactions_haha),' 'reactions.type(LIKE).limit(0).summary(total_count).as(reactions_like),' 'reactions.type(SAD).limit(0).summary(total_count).as(reactions_sad),' 'reactions.type(ANGRY).limit(0).summary(total_count).as(reactions_angry),' 'reactions.type(THANKFUL).limit(0).summary(total_count).as(reactions_thankful).summary(total_count),' 'comments.limit(0).summary(total_count).filter(stream)&since=' + str(since) + '&access_token=' + access_token) posts = get_request_and_jsonize(url) if 'error' in posts: logging.error('%s', posts['error']['message']) return sql = "UPDATE `fb_posts` SET `compartilhamentos` = (%s), `reacoes` = (%s), `reacoes_like` = (%s), " \ "`reacoes_haha` = (%s), `reacoes_wow` = (%s), `reacoes_sad` = (%s), `reacoes_love` = (%s), " \ "`reacoes_angry` = (%s), `reacoes_thankful` = (%s), `comentarios` = (%s) WHERE `idfacebook` = (%s)" while True: if len(posts['data']) == 0: break for registro in posts['data']: idfacebook = registro['id'] compartilhamentos = registro['shares']['count'] if 'shares' in registro else 0 reacoes = registro['reactions']['summary']['total_count'] if 'reactions' in registro else 0 reacoes_like = registro['reactions_like']['summary']['total_count'] if 'reactions_like' in registro else 0 reacoes_haha = registro['reactions_haha']['summary']['total_count'] if 'reactions_haha' in registro else 0 reacoes_wow = registro['reactions_wow']['summary']['total_count'] if 'reactions_wow' in registro else 0 reacoes_sad = registro['reactions_sad']['summary']['total_count'] if 'reactions_sad' in registro else 0 reacoes_love = registro['reactions_love']['summary']['total_count'] if 'reactions_love' in registro else 0 reacoes_angry = registro['reactions_angry']['summary']['total_count'] if 'reactions_angry' in registro else 0 reacoes_thankful = registro['reactions_thankful']['summary']['total_count'] if 'reactions_thankful' in registro else 0 if 'comments' in registro and 'total_count' in registro['comments']['summary']: comentarios = registro['comments']['summary']['total_count'] else: comentarios = 0 try: cur.execute(sql, (compartilhamentos, reacoes, reacoes_like, reacoes_haha, reacoes_wow, reacoes_sad, reacoes_love, reacoes_angry, reacoes_thankful, comentarios, idfacebook)) except Exception as excpt: logging.error('%s', excpt) exit() conn.commit() if 'paging' in posts: if 'next' in posts['paging']: posts = get_request_and_jsonize(posts['paging']['next']) else: break else: break
def facebook_get_comments_post(idpost, idfacebook, datahoraultimocomentario): url = (graph_url + api_version + idfacebook + "/comments?limit=700&order=reverse_chronological&fields=message," "created_time,id," 'reactions.limit(0).summary(total_count),' 'reactions.type(LOVE).limit(0).summary(total_count).as(reactions_love),' 'reactions.type(WOW).limit(0).summary(total_count).as(reactions_wow),' 'reactions.type(HAHA).limit(0).summary(total_count).as(reactions_haha),' 'reactions.type(LIKE).limit(0).summary(total_count).as(reactions_like),' 'reactions.type(SAD).limit(0).summary(total_count).as(reactions_sad),' 'reactions.type(ANGRY).limit(0).summary(total_count).as(reactions_angry),' 'reactions.type(THANKFUL).limit(0).summary(total_count).as(reactions_thankful).summary(total_count),' "comment_count,from,attachment,parent{id}&filter=stream" + '&access_token=' + access_token) status = get_request_and_jsonize(url) if 'error' in status: logging.error('%s', status['error']['message']) return comments = status['data'] if len(comments) > 0: novadatahoraultimocomentario = datetime.datetime.strptime(comments[0]['created_time'], '%Y-%m-%dT%H:%M:%S+0000') else: logging.info('Nenhum comentário no post de id %d', idpost) return if datahoraultimocomentario: if novadatahoraultimocomentario <= datahoraultimocomentario: logging.info('Nenhum comentário novo no post de id %d', idpost) return sql = 'UPDATE `fb_posts` SET `datahoraultimocomentario` = (%s) WHERE idpost = (%s)' cur.execute(sql, (novadatahoraultimocomentario, idpost)) conn.commit() sql = "INSERT INTO `fb_comentarios` (`idfacebook`, `idpost`, `postagem`, `datahora`, " \ "`curtidas`, `reacoes_like`, `reacoes_haha`, `reacoes_wow`, `reacoes_sad`, `reacoes_love`, `reacoes_angry`, " \ "`reacoes_thankful`, `respostas`, `idcomentarioresposta`, `urlimagem`, `nomeimagem`, `nomeusuario`, `idusuario`)" \ "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" count = 0 while True: for comment in comments: datahora = datetime.datetime.strptime(comment['created_time'], '%Y-%m-%dT%H:%M:%S+0000') if datahoraultimocomentario: if datahora <= datahoraultimocomentario: conn.commit() logging.info('Capturados os últimos %d comentários do post %d', count, idpost) return tamanhopostagem = len(comment['message']) postagem = comment['message'] if tamanhopostagem < MAXCOMMENTLEN else comment['message'][:MAXCOMMENTLEN] idfacebook = comment['id'] curtidas = comment['reactions']['summary']['total_count'] if 'reactions' in comment else 0 reacoes_like = comment['reactions_like']['summary']['total_count'] if 'reactions_like' in comment else 0 reacoes_haha = comment['reactions_haha']['summary']['total_count'] if 'reactions_haha' in comment else 0 reacoes_wow = comment['reactions_wow']['summary']['total_count'] if 'reactions_wow' in comment else 0 reacoes_sad = comment['reactions_sad']['summary']['total_count'] if 'reactions_sad' in comment else 0 reacoes_love = comment['reactions_love']['summary']['total_count'] if 'reactions_love' in comment else 0 reacoes_angry = comment['reactions_angry']['summary']['total_count'] if 'reactions_angry' in comment else 0 reacoes_thankful = comment['reactions_thankful']['summary']['total_count'] if 'reactions_thankful' in comment else 0 if 'parent' in comment: respostas = None idcomentarioresposta = comment['parent']['id'] else: respostas = comment['comment_count'] idcomentarioresposta = None urlimagem = None nomeimagem = None if 'attachment' in comment and 'media' in comment['attachment']: urlimagem = comment['attachment']['media']['image']['src'] if '.png' in urlimagem: nomeimagem = idfacebook + '.png' else: nomeimagem = idfacebook + '.jpg' nomeusario = None idusuario = None if 'from' in comment: nomeusuario = comment['from']['name'] if 'name' in comment['from'] else None idusuario = comment['from']['id'] try: cur.execute(sql, (idfacebook, idpost, postagem, datahora, curtidas, reacoes_like, reacoes_haha, reacoes_wow, reacoes_sad, reacoes_love, reacoes_angry, reacoes_thankful, respostas, idcomentarioresposta, urlimagem, nomeimagem, nomeusuario, idusuario)) count += 1 except Exception as excpt: logging.error('%s', excpt) conn.commit() if 'next' in status['paging']: status = get_request_and_jsonize(status['paging']['next']) if 'error' in status: logging.error('%s', status['error']['message']) return if 'data' in status: comments = status['data'] else: break else: break logging.info('Capturados todos os %d comentários do post de id %d', count, idpost)
def facebook_get_posts(idperfil, idfacebook, datahoraultimopost): url = (graph_url + api_version + idfacebook + '/posts?limit=100&fields=message,shares,created_time,type,link,' 'reactions.limit(0).summary(total_count),' 'reactions.type(LOVE).limit(0).summary(total_count).as(reactions_love),' 'reactions.type(WOW).limit(0).summary(total_count).as(reactions_wow),' 'reactions.type(HAHA).limit(0).summary(total_count).as(reactions_haha),' 'reactions.type(LIKE).limit(0).summary(total_count).as(reactions_like),' 'reactions.type(SAD).limit(0).summary(total_count).as(reactions_sad),' 'reactions.type(ANGRY).limit(0).summary(total_count).as(reactions_angry),' 'reactions.type(THANKFUL).limit(0).summary(total_count).as(reactions_thankful).summary(total_count),' 'full_picture,permalink_url,comments.limit(0).summary(total_count).filter(stream)' + '&access_token=' + access_token) search = get_request_and_jsonize(url) if 'error' in search: logging.error('%s', search['error']['message']) return posts = search['data'] if len(posts) == 0: logging.info('Nenhum post na página de id %d', idperfil) return novadatahoraultimopost = datetime.datetime.strptime(posts[0]['created_time'], '%Y-%m-%dT%H:%M:%S+0000') if datahoraultimopost: if novadatahoraultimopost <= datahoraultimopost: logging.info('Nenhum post novo na página de id %d', idperfil) return sql = 'UPDATE `fb_perfis` SET `datahoraultimopost` = (%s) WHERE `idperfil` = (%s)' try: cur.execute(sql, (novadatahoraultimopost, idperfil)) conn.commit() except Exception as excpt: logging.critical('%s', excpt) raise Exception sql = "INSERT INTO `fb_posts` " \ "(`idperfil`, `idfacebook`, `postagem`, `datahora`, `compartilhamentos`," \ "`reacoes`, `reacoes_like`, `reacoes_haha`, `reacoes_wow`, `reacoes_sad`, `reacoes_love`, `reacoes_angry`, " \ "`reacoes_thankful`, `comentarios`, `tipo`, `urlimagem`, `nomeimagem`, `link`) " \ "VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)" count = 0 while True: for registro in posts: datahora = datetime.datetime.strptime(registro['created_time'], '%Y-%m-%dT%H:%M:%S+0000') if datahoraultimopost: if datahora <= datahoraultimopost: conn.commit() logging.info('Capturados os últimos %d posts da página de id %d', count, idperfil) return idfacebook = registro['id'] if 'message' in registro: postagem = registro['message'] if len(registro['message']) <= MAXPOSTLEN \ else registro['message'][:MAXPOSTLEN] else: postagem = None compartilhamentos = registro['shares']['count'] if 'shares' in registro else 0 reacoes = registro['reactions']['summary']['total_count'] if 'reactions' in registro else 0 reacoes_like = registro['reactions_like']['summary']['total_count'] if 'reactions_like' in registro else 0 reacoes_haha = registro['reactions_haha']['summary']['total_count'] if 'reactions_haha' in registro else 0 reacoes_wow = registro['reactions_wow']['summary']['total_count'] if 'reactions_wow' in registro else 0 reacoes_sad = registro['reactions_sad']['summary']['total_count'] if 'reactions_sad' in registro else 0 reacoes_love = registro['reactions_love']['summary']['total_count'] if 'reactions_love' in registro else 0 reacoes_angry = registro['reactions_angry']['summary']['total_count'] if 'reactions_angry' in registro else 0 reacoes_thankful = registro['reactions_thankful']['summary']['total_count'] if 'reactions_thankful' in registro else 0 if 'comments' in registro and 'total_count' in registro['comments']['summary']: comentarios = registro['comments']['summary']['total_count'] else: comentarios = 0 tipo = registro['type'] urlimagem = None nomeimagem = None if 'full_picture' in registro: urlimagem = registro['full_picture'] if len(registro['full_picture']) <= MAXURLIMG \ else registro['full_picture'][:MAXURLIMG] if '.png' in urlimagem: nomeimagem = idfacebook + '.png' else: nomeimagem = idfacebook + '.jpg' # safe_retrieve(urlimagem, pathface + nomeimagem) link = registro['permalink_url'] try: cur.execute(sql, (idperfil, idfacebook, postagem, datahora, compartilhamentos, reacoes, reacoes_like, reacoes_haha, reacoes_wow, reacoes_sad, reacoes_love, reacoes_angry, reacoes_thankful, comentarios, tipo, urlimagem, nomeimagem, link)) count += 1 except pymysql.err.IntegrityError as excpt: logging.error('%s', excpt) conn.commit() logging.info('Últimos %d posts da página de id %d capturados', count, idperfil) if 'paging' in search: if 'next' in search['paging']: search = get_request_and_jsonize(search['paging']['next']) else: break if 'data' in search: posts = search['data'] else: break else: break if len(posts) == 0: break logging.info('Todos os %d posts capturados. Saindo da função de captura de posts.', count)
def youtube_get_videos(idcanal, idlistavideos, datahoraultimovideo): logging.info('Iniciando captura de vídeos do canal de id %d.', idcanal) url = ('https://www.googleapis.com/youtube/v3/playlistItems?part=snippet' '&maxResults=50&playlistId=' + idlistavideos + '&key=' + api_key) search = get_request_and_jsonize(url) registros = search['items'] if len(registros) == 0: logging.info('Nenhum vídeo no canal de id %d', idcanal) return novadatahoraultimovideo = datetime.datetime.strptime(registros[0]['snippet']['publishedAt'], '%Y-%m-%dT%H:%M:%S.000Z') if datahoraultimovideo: if novadatahoraultimovideo <= datahoraultimovideo: logging.info('Nenhum vídeo novo no canal de id %d', idcanal) return sql = 'UPDATE `yt_canais` SET `datahoraultimovideo` = (%s) WHERE `idcanal` = (%s)' try: cur.execute(sql, (novadatahoraultimovideo, idcanal)) conn.commit() except Exception as excpt: logging.critical('%s', excpt) exit() count = 0 while True: for reg in registros: snippet = reg['snippet'] datahora = datetime.datetime.strptime(snippet['publishedAt'], '%Y-%m-%dT%H:%M:%S.000Z') if datahoraultimovideo: if datahora <= datahoraultimovideo: conn.commit() logging.info('Capturados os últimos %d vídeos da página de id %d', count, idcanal) return idyoutube = snippet['resourceId']['videoId'] titulo = snippet['title'] if len(snippet['title']) <= MAXTITULO else snippet['title'][:MAXTITULO] descricao = snippet['description'] urlimagem = snippet['thumbnails']['standard']['url'] if 'standard' in snippet['thumbnails'] \ else snippet['thumbnails']['high']['url'] if '.png' in urlimagem: nomeimagem = idyoutube + '.png' else: nomeimagem = idyoutube + '.jpg' # safe_retrieve(urlimagem, pathyoutube + nomeimagem) url = ('https://www.googleapis.com/youtube/v3/videos?part=statistics&id=' + idyoutube + '&key=' + api_key) details = get_request_and_jsonize(url) statistics = details['items'][0]['statistics'] if 'statistics' in details['items'][0] else None if statistics: qtdvisualizacoes = statistics['viewCount'] if 'viewCount' in statistics else 0 qtdcurtidas = statistics['likeCount'] if 'likeCount' in statistics else 0 qtddescurtidas = statistics['dislikeCount'] if 'dislikeCount' in statistics else 0 qtdfavoritados = statistics['favoriteCount'] if 'favoriteCount' in statistics else 0 qtdcomentarios = statistics['commentCount'] if 'commentCount' in statistics else 0 else: qtdvisualizacoes = qtdcurtidas = qtddescurtidas = qtdfavoritados = qtdcomentarios = 0 sql = 'INSERT INTO `yt_videos` (`idcanal`, `idyoutube`, `titulo`, `descricao`, `datahora`, ' \ '`qtdvisualizacoes`, `qtdcurtidas`, `qtddescurtidas`, `qtdfavoritados`, `qtdcomentarios`, ' \ '`urlimagem`, `nomeimagem`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ' try: cur.execute(sql, (idcanal, idyoutube, titulo, descricao, datahora, qtdvisualizacoes, qtdcurtidas, qtddescurtidas, qtdfavoritados, qtdcomentarios, urlimagem, nomeimagem)) count += 1 except pymysql.err.IntegrityError as excpt: logging.error('%s', excpt) conn.commit() logging.info('Últimos %d vídeos do canal de id %d capturados.', count, idcanal) if 'nextPageToken' in search: url = ('https://www.googleapis.com/youtube/v3/playlistItems?part=snippet&maxResults=50&pageToken=' + search['nextPageToken'] + '&playlistId=' + idlistavideos + '&key=' + api_key) search = get_request_and_jsonize(url) registros = search['items'] else: break
def instagram_get_posts(idperfil, userid, datahoraultimopost): if datahoraultimopost is None: logging.info('Iniciando captura de posts do perfil %d', idperfil) else: logging.info('Iniciando captura de novos post do perfil %d', idperfil) url = ('https://api.instagram.com/v1/users/' + userid + '/media/recent/?count=33&access_token=' + access_token) search = get_request_and_jsonize(url) if len(search['data']) == 0: logging.info('Perfil %d não possui nenhum post', idperfil) return posts = search['data'] novadatahoraultimopost = datetime.datetime.fromtimestamp(int(search['data'][0]['created_time'])) if datahoraultimopost: if novadatahoraultimopost <= datahoraultimopost: logging.info('Nenhum post novo na página de id %d', idperfil) return sql = 'UPDATE `insta_perfis` SET `datahoraultimopost` = (%s) WHERE `idperfil` = (%s)' try: cur.execute(sql, (novadatahoraultimopost, idperfil)) conn.commit() except Exception as excpt: logging.critical('%s', excpt) exit() sql = 'INSERT INTO `insta_posts` (`idperfil`, `idinstagram`, `postagem`, `datahora`, ' \ '`qtdcomentarios`, `qtdcurtidas`, `tipo`, `link`, `urlimagem`, `nomeimagem`)' \ ' VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s) ' count = 0 while True: for registro in posts: datahora = datetime.datetime.fromtimestamp(int(registro['created_time'])) if datahoraultimopost: if datahora <= datahoraultimopost: conn.commit() logging.info('Capturados os últimos %d posts do perfil de id %d', count, idperfil) return idinstagram = registro['id'] if registro['caption']: postagem = registro['caption']['text'] else: postagem = None qtdcomentarios = registro['comments']['count'] qtdcurtidas = registro['likes']['count'] tipo = registro['type'] link = registro['link'] urlimagem = registro['images']['standard_resolution']['url'] if '.png' in urlimagem: nomeimagem = idinstagram + '.png' else: nomeimagem = idinstagram + '.jpg' # safe_retrieve(urlimagem, pathinsta + nomeimagem) try: cur.execute(sql, (idperfil, idinstagram, postagem, datahora, qtdcomentarios, qtdcurtidas, tipo, link, urlimagem, nomeimagem)) count += 1 except Exception as excpt: logging.error('%s', excpt) conn.commit() logging.info('Capturados os últimos %d posts do perfil', count) if 'pagination' in search and 'next_url' in search['pagination']: search = get_request_and_jsonize(search['pagination']['next_url']) posts = search['data'] if len(posts) == 0: break else: break
def youtube_get_replies_comment(idcomentarioresposta, idyoutube_comentario, datahoraultimoreply, idvideo): url = ('https://www.googleapis.com/youtube/v3/comments?part=id,snippet' '&maxResults=100&textFormat=plainText&parentId=' + idyoutube_comentario + '&key=' + api_key) search = get_request_and_jsonize(url) if 'items' not in search: return registros = search['items'] if len(registros) == 0: return novadatahoraultimoreply = datetime.datetime.strptime(registros[0]['snippet']['publishedAt'], '%Y-%m-%dT%H:%M:%S.000Z') if datahoraultimoreply: if novadatahoraultimoreply <= datahoraultimoreply: logging.info('Nenhum reply novo no comentário de id %d', idcomentarioresposta) return sql = 'UPDATE `yt_comentarios` SET `datahoraultimoreply` = (%s) WHERE idcomentario = (%s)' cur.execute(sql, (novadatahoraultimoreply, idcomentarioresposta)) conn.commit() sql = 'INSERT INTO yt_comentarios (`idyoutube`, `idvideo`, `idcomentarioresposta`, `postagem`, `qtdcurtidas`, ' \ '`datahora`, `qtdrespostas`, `nomeusuario`, `idusuario`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)' count = 0 while True: for reply in registros: idyoutube = reply['id'] snippet = reply['snippet'] datahora = datetime.datetime.strptime(snippet['publishedAt'], '%Y-%m-%dT%H:%M:%S.000Z') if datahoraultimoreply: if datahora <= datahoraultimoreply: conn.commit() return count # idvideo já setado postagem = snippet['textDisplay'] if len(snippet['textDisplay']) <= MAXPOSTLENGTH \ else snippet['textDisplay'][:MAXPOSTLENGTH] qtdcurtidas = snippet['likeCount'] qtdrespostas = None nomeusuario = snippet['authorDisplayName'] idusuario = snippet['authorChannelId']['value'] try: cur.execute(sql, (idyoutube, idvideo, idcomentarioresposta, postagem, qtdcurtidas, datahora, qtdrespostas, nomeusuario, idusuario)) count += 1 except pymysql.err.IntegrityError as excpt: logging.error('%s', excpt) conn.commit() if 'nextPageToken' in search: next_page_token = search['nextPageToken'] url = ('https://www.googleapis.com/youtube/v3/comments?part=id,snippet' '&maxResults=100&textFormat=plainText&parentId=' + idyoutube_comentario + '&pageToken=' + next_page_token + '&key=' + api_key) search = get_request_and_jsonize(url) registros = search['items'] else: return count
def youtube_get_first_order_comments_video(idvideo, idvideo_youtube, datahoraultimocomentario): url = ('https://www.googleapis.com/youtube/v3/commentThreads?part=id,snippet' '&maxResults=100&textFormat=plainText&videoId=' + idvideo_youtube + '&key=' + api_key) search = get_request_and_jsonize(url) if 'items' in search: registros = search['items'] else: logging.info('Comentários desativados para o vídeo de id %d', idvideo) return if len(registros) > 0: novadatahoraultimocomentario = datetime.datetime.strptime(registros[0]['snippet']['topLevelComment'] ['snippet']['publishedAt'], '%Y-%m-%dT%H:%M:%S.000Z') else: logging.info('Nenhum comentário no vídeo de id %d', idvideo) return if datahoraultimocomentario: if novadatahoraultimocomentario <= datahoraultimocomentario: logging.info('Nenhum comentário novo no vídeo de id %d', idvideo) return sql = 'UPDATE `yt_videos` SET `datahoraultimocomentario` = (%s) WHERE idvideo = (%s)' cur.execute(sql, (novadatahoraultimocomentario, idvideo)) conn.commit() sql = 'INSERT INTO yt_comentarios (`idyoutube`, `idvideo`, `postagem`, `qtdcurtidas`, `datahora`, `qtdrespostas`,' \ '`nomeusuario`, `idusuario`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)' # Loop de captura de comentários de um dado vídeo count = 0 while True: for reg in registros: idyoutube = reg['id'] comentario = reg['snippet']['topLevelComment']['snippet'] postagem = comentario['textDisplay'] if len(comentario['textDisplay']) <= MAXPOSTLENGTH \ else comentario['textDisplay'][:MAXPOSTLENGTH] qtdcurtidas = comentario['likeCount'] datahora = datetime.datetime.strptime(comentario['publishedAt'], '%Y-%m-%dT%H:%M:%S.000Z') if datahoraultimocomentario: if datahora <= datahoraultimocomentario: conn.commit() logging.info('Capturados os últimos %d comentários do vídeo %d', count, idvideo) return qtdrespostas = reg['snippet']['totalReplyCount'] nomeusuario = comentario['authorDisplayName'] idusuario = comentario['authorChannelId']['value'] if 'authorChannelId' in comentario else None try: cur.execute(sql, (idyoutube, idvideo, postagem, qtdcurtidas, datahora, qtdrespostas, nomeusuario, idusuario)) count += 1 except pymysql.err.IntegrityError as excpt: logging.error('%s', excpt) conn.commit() logging.info('Capturados os últimos %d comentários do vídeo de id %d', count, idvideo) if 'nextPageToken' in search: next_page_token = search['nextPageToken'] url = ('https://www.googleapis.com/youtube/v3/commentThreads?part=id,replies,snippet&maxResults=100' '&textFormat=plainText&videoId=' + idvideo_youtube + '&pageToken=' + next_page_token + '&key=' + api_key) search = get_request_and_jsonize(url) if 'items' in search: registros = search['items'] else: return else: break