def buscar_tweets_data_lugar(data, local):
resultado = [[], [], []]
resultado[0].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca, voudeque.lugar where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Positivo' and tweet.id_lugar = lugar.id and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and tweet.dataHora like '"
+ data + "%' and lugar.nome_lugar = '" + local +
"' and marca.nome = 'uber';")[0][0])
resultado[0].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca, voudeque.lugar where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Negativo' and tweet.id_lugar = lugar.id and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and tweet.dataHora like '"
+ data + "%' and lugar.nome_lugar = '" + local +
"' and marca.nome = 'uber';")[0][0])
resultado[0].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca, voudeque.lugar where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Neutro' and tweet.id_lugar = lugar.id and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and tweet.dataHora like '"
+ data + "%' and lugar.nome_lugar = '" + local +
"' and marca.nome = 'uber';")[0][0])
resultado[1].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca, voudeque.lugar where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Positivo' and tweet.id_lugar = lugar.id and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and tweet.dataHora like '"
+ data + "%' and lugar.nome_lugar = '" + local +
"' and marca.nome = 'cabify';")[0][0])
resultado[1].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca, voudeque.lugar where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Negativo' and tweet.id_lugar = lugar.id and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and tweet.dataHora like '"
+ data + "%' and lugar.nome_lugar = '" + local +
"' and marca.nome = 'cabify';")[0][0])
resultado[1].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca, voudeque.lugar where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Neutro' and tweet.id_lugar = lugar.id and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and tweet.dataHora like '"
+ data + "%' and lugar.nome_lugar = '" + local +
"' and marca.nome = 'cabify';")[0][0])
resultado[2].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca, voudeque.lugar where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Positivo' and tweet.id_lugar = lugar.id and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and tweet.dataHora like '"
+ data + "%' and lugar.nome_lugar = '" + local +
"' and marca.nome = '99pop';")[0][0])
resultado[2].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca, voudeque.lugar where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Negativo' and tweet.id_lugar = lugar.id and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and tweet.dataHora like '"
+ data + "%' and lugar.nome_lugar = '" + local +
"' and marca.nome = '99pop';")[0][0])
resultado[2].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca, voudeque.lugar where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Neutro' and tweet.id_lugar = lugar.id and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and tweet.dataHora like '"
+ data + "%' and lugar.nome_lugar = '" + local +
"' and marca.nome = '99pop';")[0][0])
return resultado
def buscarHashtags():
reUber = dao.Busca_SQL(BUSCA_HASHTAGS_UBER)
reCab = dao.Busca_SQL(BUSCA_HASHTAGS_CABIFY)
re99 = dao.Busca_SQL(BUSCA_HASHTAGS_99POP)
lista = []
listaUber = []
lista99 = []
listaCab =[]
for i in reUber:
listaUber.append(i[0])
lista.append(listaUber)
for i in reCab:
listaCab.append(i[0])
lista.append(listaCab)
for i in re99:
lista99.append(i[0])
lista.append(lista99)
return lista
def treinar(model,vector):
resposta = dao.Busca_SQL(BUSCAR_TEXTO_TWEETS)
tweets=[]
sentimentos=[]
for i in resposta:
tweets.append(i[0])
sentimentos.append(i[1])
freq_tweets = vector.fit_transform(tweets)
model.fit(freq_tweets, sentimentos)
def buscar_tweets():
###
# posicao 1 = positivo, 2 = negativo e 3 = neutro
# uber = 1, cabify = 2 uber = 3
###
resultado = [[], [], []]
resultado[0].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Positivo' and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and marca.nome = 'uber';"
)[0][0])
resultado[0].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Negativo' and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and marca.nome = 'uber';"
)[0][0])
resultado[0].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Neutro' and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and marca.nome = 'uber';"
)[0][0])
resultado[1].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Positivo' and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and marca.nome = 'cabify';"
)[0][0])
resultado[1].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Negativo' and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and marca.nome = 'cabify';"
)[0][0])
resultado[1].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Neutro' and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and marca.nome = 'cabify';"
)[0][0])
resultado[2].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Positivo' and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and marca.nome = '99pop';"
)[0][0])
resultado[2].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Negativo' and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and marca.nome = '99pop';"
)[0][0])
resultado[2].append(
dao.Busca_SQL(
"select count(*) from voudeque.tweet, voudeque.sentimento, voudeque.marca, voudeque.tweet_marca where tweet.id_sentimento = sentimento.id and sentimento.classe = 'Neutro' and tweet_marca.id_marca = marca.id and tweet_marca.id_tweet = tweet.id and marca.nome = '99pop';"
)[0][0])
return resultado
def Buscar(query):
resultado = dao.Busca_SQL(query)
print(resultado)
if len(resultado) == 0:
return False
return resultado
def pegarTweetsNovos():
hashtags = buscarHashtags()
id_marca = 0
for i in hashtags:
id_marca += 1
if (id_marca == 1 ):
marca = "uber"
elif id_marca == 2:
marca = "cabify"
elif id_marca == 3:
marca = "99pop"
elif id_marca == 4:
marca = "uber"
id_marca = 1
for j in i:
tweet = tweepy.Cursor(api.search, q=str(j), lang="pt").items(5)
time.sleep(10)
for twe in tweet:
try:
nome_usuario = str(twe.user.name).replace("\n", " ").replace('"', '')
screen_name_usuario = str(twe.user.screen_name).replace("\n", " ").replace('"', '')
lugar_usuario = twe.user.location.replace("\n", " ").replace('"', '')
id_tweet = twe.id
texto_tweet = str(twe.text).replace("\n", " ").replace('"', '')
freq_testes = vectorizer.transform([texto_tweet])
sent = modelo.predict(freq_testes)[0]
if(sent == "Positivo"):
sentimento = 1
elif(sent == "Negativo"):
sentimento = 2
else:
sentimento = 3
created_at_tweet = twe.created_at
hashtags = twe.entities.get('hashtags')
if(lugar_usuario != ""):
dao.Executa_SQL("insert into voudeque.usuario(nome, username, nome_lugar) values('" + str(nome_usuario) + "', '" + str(screen_name_usuario) + "', '" + str(lugar_usuario) + "');")
else:
dao.Executa_SQL("insert into voudeque.usuario(nome, username) values('" + str(nome_usuario) + "', '" + str(screen_name_usuario) + "');")
id_usuario = dao.Busca_SQL("select id from voudeque.usuario where usuario.nome = '"+ str(nome_usuario) +"';")[0][0]
id_lugar = "null"
if (twe.place != None):
coordenadas = twe.place.bounding_box.coordinates[0][0]
local = geolocator.reverse(query=str(coordenadas[1]) + ", " + str(coordenadas[0]),
language="pt")
cidade = local.raw["address"]["city"]
dao.Executa_SQL("insert into voudeque.lugar(latitude, longitude, nome_lugar) values('" + str(coordenadas[1]) + "', '" + str(coordenadas[0]) + "', '" + str(
cidade).lower() + "');")
id_lugar = dao.Busca_SQL("select id from voudeque.lugar where nome_lugar = '" + str(cidade).lower() + "'" + " and latitude = " + "'" + str(
coordenadas[1]) + "'" + "and longitude = " + "'" + str(coordenadas[0]) + "';")[0][0]
dao.Executa_SQL(
"insert into voudeque.tweet(id, texto, dataHora, id_sentimento, id_usuario, id_lugar) values('" + str(id_tweet) + "', '" + str(texto_tweet) + "', '" + str(created_at_tweet) + "', '" + str(sentimento)
+ "', '" + str(id_usuario) + "', " + str(id_lugar) + ");")
dao.Executa_SQL(
"insert into voudeque.tweet_marca(id_tweet, id_marca) values('" + str(id_tweet) + "', '" + str(marca) + "');")
for j1 in hashtags:
if marca in j1.get("text").lower():
dao.Executa_SQL("insert into voudeque.hashtag(texto,id_marca) values('" + str(j.get("text").lower()) + "', '" + str(id_marca) + "');")
else:
dao.Executa_SQL("insert into voudeque.hashtag(texto) values('" + str(j.get("text").lower()) + "');")
except:
print("Exception")
dao.Executa_SQL(
'DELETE FROM voudeque.usuario WHERE (id) IN (select * from(select usuario.id from voudeque.usuario left join voudeque.tweet on voudeque.usuario.id = voudeque.tweet.id_usuario where voudeque.tweet.id_usuario is null) as p);')
dao.Executa_SQL(
'DELETE FROM voudeque.lugar WHERE (id) IN (select * from(select lugar.id from voudeque.lugar left join voudeque.tweet on voudeque.lugar.id = voudeque.tweet.id_lugar where voudeque.tweet.id_lugar is null) as p);')
def buscar_locais():
resposta = dao.Busca_SQL("select DISTINCT nome_lugar from voudeque.lugar;")
cidades = []
for i in resposta:
cidades.append(i[0])
return cidades