def profile_post():
if not session:
return redirect(url_for('auth.url_login'))
name = request.form.get('name')
bd, connection = getConnection()
if (request.form.get('password')):
password = crypt.crypt(request.form.get('password'), 'SALT_KEY')
sql = "UPDATE users_sis SET nombre = %s, password = %s WHERE id = %s"
bd.execute(sql, (name, password, session['id']))
else:
sql = "UPDATE users_sis SET nombre = %s WHERE id = %s"
bd.execute(sql, (name, session['id']))
try:
connection.commit()
flash(['Datos Actualizados...', 'bg-success'])
except Exception as e:
flash(['Error...' + str(e) + '', 'bg-warning'])
bd.close()
return redirect(url_for('auth.url_profile'))
def configuracion_general():
if not session:
return redirect(url_for('auth.url_login'))
try:
bd, connection = getConnection()
sql = "SELECT * FROM conf"
bd.execute(sql)
config = bd.fetchone()
except Exception as e:
print(e)
return render_template('configuracion/config.html', config=config)
def index():
if not session:
return redirect(url_for('auth.url_login'))
try:
bd, connection = getConnection()
sql = "SELECT * FROM redes_sociales"
bd.execute(sql)
redes=bd.fetchall()
except Exception as e:
redes = ""
flash(['Error...'+str(e)+'', 'bg-warning'])
return render_template('redes_sociales/inicio.html',redes=redes)
def MostrarAnalisisTwitter_Menciones():
screen_name = request.form.get('cuenta')
if not session:
return redirect(url_for('auth.url_login'))
try:
bd, connection = getConnection()
sql = "SELECT classification, count(*) as c FROM(SELECT screen_name FROM CitizenLab.data_twitter WHERE screen_name = '" + screen_name + "') as J1 JOIN data_twitter_detalle ON J1.screen_name=data_twitter_detalle.is_mentioned group by classification LIMIT 0, 1000"
bd.execute(sql)
detalle = bd.fetchall()
except Exception as e:
detalle = str(e)
return json.dumps(detalle)
def MostrarAnalisisTwitter_Polaridad():
screen_name = request.form.get('cuenta')
if not session:
return redirect(url_for('auth.url_login'))
try:
bd, connection = getConnection()
sql = "SELECT classification,count(*) as c,tp.total FROM data_twitter_detalle as d1,data_twitter as d2,(SELECT count(*) as total FROM data_twitter_detalle as d1,data_twitter as d2 where d1.usuario=d2.id AND screen_name='" + screen_name + "') as tp where d1.usuario=d2.id AND screen_name='" + screen_name + "' GROUP by classification,tp.total"
bd.execute(sql)
detalle = bd.fetchall()
except Exception as e:
detalle = str(e)
return json.dumps(detalle)
def index():
if not session:
return redirect(url_for('auth.url_login'))
try:
bd, connection = getConnection()
sql = "SELECT cuentas.id,cuentas.cuenta,redes_sociales.nombre as red FROM cuentas,redes_sociales where cuentas.red_social=redes_sociales.id"
bd.execute(sql)
cuentas = bd.fetchall()
except Exception as e:
cuentas = ""
flash(['Error...' + str(e) + '', 'bg-warning'])
try:
bd, connection = getConnection()
sql = "SELECT * FROM redes_sociales"
bd.execute(sql)
redes = bd.fetchall()
except Exception as e:
redes = ""
flash(['Error...' + str(e) + '', 'bg-warning'])
return render_template('cuentas/inicio.html', cuentas=cuentas, redes=redes)
def profile():
if not session:
return redirect(url_for('auth.url_login'))
try:
bd, connection = getConnection()
sql = "SELECT * FROM users_sis where id=%s"
bd.execute(sql, (session['id']))
profile = bd.fetchone()
bd.close()
except Exception as e:
flash(['Error...' + str(e) + '', 'bg-warning'])
return render_template('auth/profile.html', profile=profile)
def MostrarAnalisisTwitter_Visitas():
screen_name = request.form.get('cuenta')
if not session:
return redirect(url_for('auth.url_login'))
try:
bd, connection = getConnection()
sql = "SELECT place FROM data_twitter_detalle as d1,data_twitter as d2 where d1.usuario=d2.id AND screen_name=%s and place<>'' group by place"
bd.execute(sql, screen_name)
places = bd.fetchall()
print(places)
except Exception as e:
places = str(e)
return json.dumps(places)
def MostrarAnalisisTwitter_Subjetividad():
'''
TO DO: Leer documentación de TextBlob y la clase Blobber
'''
screen_name = request.form.get('cuenta')
if not session:
return redirect(url_for('auth.url_login'))
try:
bd, connection = getConnection()
sql = "SELECT subjectivity, count(subjectivity) as freq FROM(SELECT id FROM CitizenLab.data_twitter WHERE screen_name = '" + screen_name + "') as J1 JOIN data_twitter_detalle ON J1.id=data_twitter_detalle.usuario group by subjectivity LIMIT 0, 1000"
bd.execute(sql)
detalle = bd.fetchall()
print("I was correctly called", detalle)
except Exception as e:
detalle = str(e)
return json.dumps(detalle, default=decimal_default)
def create_redes_sociales():
if not session:
return redirect(url_for('auth.url_login'))
red_social = request.form.get('red_social')
bd, connection = getConnection()
try:
sql = "INSERT INTO redes_sociales VALUES (NULL,%s,NULL,NULL)"
bd.execute(sql, (red_social))
connection.commit()
bd.close()
flash(['Red Social Guardada...', 'bg-success'])
except Exception as e:
flash(['Error...' + str(e) + '', 'bg-warning'])
return redirect(url_for('redes_sociales.url_redes_sociales'))
def config_tw_post():
if not session:
return redirect(url_for('auth.url_login'))
apikey = request.form.get('apikey')
apisecretkey = request.form.get('apisecretkey')
Cantidad_data_tw = request.form.get('Cantidad_data_tw')
bd, connection = getConnection()
try:
sql = "UPDATE conf SET API_key_tw = %s, API_secret_key_tw = %s, Cantidad_data_tw = %s WHERE id = 1"
bd.execute(sql, (apikey, apisecretkey, Cantidad_data_tw))
connection.commit()
flash(['1', 'Datos Actualizados'])
except Exception as e:
flash(['0', e])
return redirect(url_for('conf.url_config'))
def obtenerdata_twitter():
cuenta = request.form.get('cuenta')
bd, connection = getConnection()
sql = "SELECT Cantidad_data_tw FROM conf where id=1"
bd.execute(sql)
cantidad = bd.fetchone()
tweets = json.dumps({"estados": "304", "mensaje": "Sin cambios"})
try:
tweets = get_all_tweets(cuenta, cantidad['Cantidad_data_tw'])
except TweepError as ex:
if ex.reason == "Not authorized.":
print("La cuenta tiene todos sus tweets protegidos")
tweets = json.dumps({
"estados":
"401",
"mensaje":
"Error, los tuits de esta cuenta estan protegidos"
})
return tweets
def login_post():
email = request.form.get('email')
password = crypt.crypt(request.form.get('password'), 'SALT_KEY')
try:
bd, connection = getConnection()
sql = "SELECT id,correo FROM users_sis WHERE correo = %s and password = %s"
bd.execute(sql, (email, password))
account = bd.fetchone()
bd.close()
except Exception as e:
flash(['Error...' + str(e) + '', 'bg-warning'])
if account:
session['loggedin'] = True
session['id'] = account['id']
session['username'] = account['correo']
return redirect(url_for('app.url_home'))
flash(['Datos incorrectos...', 'bg-danger'])
return redirect(url_for('auth.url_login'))
def signup_post():
email = request.form.get('email')
bd, connection = getConnection()
sql = "SELECT id,correo FROM users_sis WHERE correo =%s"
bd.execute(sql, (email))
account = bd.fetchone()
if account:
flash(['Correo en uso...', 'bg-danger'])
return redirect(url_for('auth.url_signup'))
name = request.form.get('name')
password = crypt.crypt(request.form.get('password'), 'SALT_KEY')
try:
sql = "INSERT INTO users_sis VALUES (NULL, %s, %s, %s)"
bd.execute(sql, (name, email, password))
connection.commit()
bd.close()
flash(['Digita tus datos para entrar...', 'bg-success'])
except Exception as e:
flash(['Error...' + str(e) + '', 'bg-warning'])
return redirect(url_for('auth.url_login'))
def home():
if not session:
return redirect(url_for('auth.url_login'))
try:
bd, connection = getConnection()
sql = "SELECT cuentas.id,cuentas.cuenta,redes_sociales.nombre as red FROM cuentas,redes_sociales where cuentas.red_social=redes_sociales.id"
bd.execute(sql)
cuentas = bd.fetchall()
except Exception as e:
cuentas = ""
flash(['Error...' + str(e) + '', 'bg-warning'])
try:
bd, connection = getConnection()
sql = "SELECT count(*) as c FROM cuentas"
bd.execute(sql)
c_cuentas = bd.fetchone()
except Exception as e:
c_cuentas = ""
flash(['Error...' + str(e) + '', 'bg-warning'])
try:
bd, connection = getConnection()
sql = "SELECT count(*) as c FROM cuentas where red_social=1"
bd.execute(sql)
c_cuentas_t = bd.fetchone()
except Exception as e:
c_cuentas_t = ""
flash(['Error...' + str(e) + '', 'bg-warning'])
try:
bd, connection = getConnection()
sql = "SELECT count(*) as c FROM cuentas where red_social=2"
bd.execute(sql)
c_cuentas_f = bd.fetchone()
except Exception as e:
c_cuentas_f = ""
flash(['Error...' + str(e) + '', 'bg-warning'])
try:
bd, connection = getConnection()
sql = "SELECT count(*) as c FROM cuentas where red_social=3"
bd.execute(sql)
c_cuentas_i = bd.fetchone()
except Exception as e:
c_cuentas_i = ""
flash(['Error...' + str(e) + '', 'bg-warning'])
try:
bd, connection = getConnection()
sql = "SELECT count(*) as c FROM data_twitter_detalle"
bd.execute(sql)
c_cuentas_t_d = bd.fetchone()
except Exception as e:
c_cuentas_t_d = ""
flash(['Error...' + str(e) + '', 'bg-warning'])
return render_template('home.html',
cuentas=cuentas,
c_cuentas=c_cuentas,
c_cuentas_t=c_cuentas_t,
c_cuentas_f=c_cuentas_f,
c_cuentas_i=c_cuentas_i,
c_cuentas_i_d=0,
c_cuentas_f_d=0,
c_cuentas_t_d=c_cuentas_t_d)
def get_all_tweets(screen_name, limit_number):
traductor = Translator()
limit_number = int(limit_number) # 3240
auth = tweepy.OAuthHandler(consumer_key, consumer_secret)
auth.set_access_token(access_key, access_secret)
api = tweepy.API(auth)
alltweets = []
new_tweets = api.user_timeline(screen_name=screen_name, count=1)
alltweets.extend(new_tweets)
# Si no hay tweets en la cuenta, no hacer nada
if not new_tweets:
diccionario = {'estados': '202', 'msg': "Terminado"}
return json.dumps(diccionario)
oldest = alltweets[-1].id - 1
emoji_pattern = re.compile(
"["
u"\U0001F600-\U0001F64F" # emoticons
u"\U0001F300-\U0001F5FF" # symbols & pictographs
u"\U0001F680-\U0001F6FF" # transport & map symbols
u"\U0001F1E0-\U0001F1FF" # flags (iOS)
u"\U00002500-\U00002BEF" # chinese char
u"\U00002702-\U000027B0"
u"\U00002702-\U000027B0"
u"\U000024C2-\U0001F251"
u"\U0001f926-\U0001f937"
u"\U00010000-\U0010ffff"
u"\u2640-\u2642"
u"\u2600-\u2B55"
u"\u200d"
u"\u23cf"
u"\u23e9"
u"\u231a"
u"\ufe0f" # dingbats
u"\u3030"
"]+",
flags=re.UNICODE)
bd, connection = getConnection()
while len(new_tweets) > 0 and len(alltweets) <= limit_number:
texto = filter_text(new_tweets[0]._json['text'], emoji_pattern,
traductor)
descripcion = filter_text(new_tweets[0]._json['user']['description'],
emoji_pattern, traductor)
sql = "SELECT * FROM data_twitter WHERE id=%s"
bd.execute(sql, (new_tweets[0]._json['user']['id_str']))
data = bd.fetchone()
if data:
sql = "UPDATE data_twitter SET screen_name=%s,name=%s,location=%s,description=%s WHERE id=%s"
bd.execute(sql,
(new_tweets[0]._json['user']['screen_name'],
new_tweets[0]._json['user']['name'],
new_tweets[0]._json['user']['location'], descripcion,
new_tweets[0]._json['user']['id_str']))
else:
sql = "INSERT INTO data_twitter VALUES (%s, %s, %s, %s, %s)"
bd.execute(sql,
(new_tweets[0]._json['user']['id_str'],
new_tweets[0]._json['user']['screen_name'],
new_tweets[0]._json['user']['name'],
new_tweets[0]._json['user']['location'], descripcion))
connection.commit()
sql = "SELECT * FROM data_twitter_detalle WHERE id=%s"
bd.execute(sql, (new_tweets[0]._json['id_str']))
twit = bd.fetchone()
if twit:
sql = "UPDATE data_twitter_detalle SET text=%s WHERE id=%s"
bd.execute(sql, (texto, new_tweets[0]._json['id_str']))
else:
sql = "INSERT INTO data_twitter_detalle VALUES (%s, %s, %s, %s, %s, %s,NULL,NULL,NULL,NULL,NULL,NULL)"
if (new_tweets[0]._json['place']):
bd.execute(sql, (new_tweets[0]._json['id_str'],
new_tweets[0]._json['user']['id_str'], texto,
new_tweets[0]._json['created_at'],
new_tweets[0]._json['place']['place_type'],
new_tweets[0]._json['place']['name']))
else:
bd.execute(sql, (new_tweets[0]._json['id_str'],
new_tweets[0]._json['user']['id_str'], texto,
new_tweets[0]._json['created_at'], "", ""))
connection.commit()
new_tweets = api.user_timeline(screen_name=screen_name,
count=1,
max_id=oldest)
alltweets.extend(new_tweets)
oldest = alltweets[-1].id - 1
print(str(len(alltweets) - 1) + " tweets descargados hasta el momento")
# Extracción de menciones
search_query = f'@{screen_name}'
retweet_filter = '-filter:retweets'
mentions = api.search(q=search_query + retweet_filter, count=limit_number)
polarity_list = []
numbers_list = []
number = 1
for tweet in alltweets:
try:
text = filter_text(tweet._json['text'], emoji_pattern, traductor)
analysis = TextBlob(text)
analysis2 = tb(text)
# https://textblob.readthedocs.io/en/dev/quickstart.html#sentiment-analysis
sentiment = analysis.sentiment
polarity = sentiment.polarity
subjectivity = sentiment.subjectivity
polarity_list.append(polarity)
numbers_list.append(number)
number = number + 1
sql = "SELECT * FROM data_twitter_detalle WHERE id=%s"
bd.execute(sql, (tweet._json['id_str']))
test = bd.fetchone()
if test:
sql = "UPDATE data_twitter_detalle SET polarity=%s,subjectivity=%s ,classification=%s ,p_pos=%s ,p_neg=%s WHERE id = %s"
bd.execute(sql, (polarity, subjectivity,
analysis2.sentiment.classification,
analysis2.sentiment.p_pos,
analysis2.sentiment.p_neg, tweet.id))
else:
sql = "INSERT INTO data_twitter_detalle VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s)"
if (tweet._json['place']):
bd.execute(
sql,
(tweet._json['id_str'], tweet._json['user']['id_str'],
text, tweet._json['created_at'],
tweet._json['place']['place_type'],
tweet._json['place']['name'], polarity, subjectivity,
analysis2.sentiment.classification,
analysis2.sentiment.p_pos, analysis2.sentiment.p_neg,
""))
else:
bd.execute(
sql,
(tweet._json['id_str'], tweet._json['user']['id_str'],
text, tweet._json['created_at'], "", "", polarity,
subjectivity, analysis2.sentiment.classification,
analysis2.sentiment.p_pos, analysis2.sentiment.p_neg,
""))
connection.commit()
except tweepy.TweepError as e:
print(e.reason)
except StopIteration:
break
#TODO: REFACTOR THIS
for tweet in mentions:
try:
text = filter_text(tweet._json['text'], emoji_pattern, traductor)
analysis = TextBlob(text)
analysis2 = tb(text)
# https://textblob.readthedocs.io/en/dev/quickstart.html#sentiment-analysis
sentiment = analysis.sentiment
polarity = sentiment.polarity
subjectivity = sentiment.subjectivity
polarity_list.append(polarity)
numbers_list.append(number)
number = number + 1
sql = "SELECT * FROM data_twitter_detalle WHERE id=%s"
bd.execute(sql, (tweet._json['id_str']))
test = bd.fetchone()
if test:
sql = "UPDATE data_twitter_detalle SET polarity=%s,subjectivity=%s ,classification=%s ,p_pos=%s ,p_neg=%s, is_mentioned=%s WHERE id = %s"
bd.execute(sql,
(polarity, subjectivity,
analysis2.sentiment.classification,
analysis2.sentiment.p_pos,
analysis2.sentiment.p_neg, screen_name, tweet.id))
else:
sql = "INSERT INTO data_twitter_detalle VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s, %s)"
if (tweet._json['place']):
bd.execute(
sql,
(tweet._json['id_str'], tweet._json['user']['id_str'],
text, tweet._json['created_at'],
tweet._json['place']['place_type'],
tweet._json['place']['name'], polarity, subjectivity,
analysis2.sentiment.classification,
analysis2.sentiment.p_pos, analysis2.sentiment.p_neg,
screen_name))
else:
bd.execute(
sql,
(tweet._json['id_str'], tweet._json['user']['id_str'],
text, tweet._json['created_at'], "", "", polarity,
subjectivity, analysis2.sentiment.classification,
analysis2.sentiment.p_pos, analysis2.sentiment.p_neg,
screen_name))
connection.commit()
except tweepy.TweepError as e:
print(e.reason)
except StopIteration:
break
diccionario = {'estados': '202', 'msg': "Terminado"}
return json.dumps(diccionario)
