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