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)