def retrieve_sentiment(sym):
sym = str(sym)
c, conn = connection.connection()
c.execute("SELECT * FROM sentiment WHERE symbol = %s", (sym,))
data = c.fetchall()
symbol = []
sentiment = []
time = []
for i in data:
symbol.append(i[0])
sentiment.append(i[1])
# time.append(datetime.datetime.fromtimestamp(int(i[2])).strftime('%Y-%m-%d %H:%M'))
time.append(datetime.datetime.fromtimestamp(int(i[2])))
df = pd.DataFrame(symbol)
df['sentiment'] = sentiment
df['time'] = time
return df
def get_dashboard_sentiment():
c, conn = connection()
c.execute("SELECT * FROM dashboard")
data = c.fetchall()
symbol = []
name = []
industry = []
sector = []
sentiment = []
sentiment_volume = []
for i in data:
sent_data = c.fetchall()
symbol.append(i[0])
name.append(i[1])
industry.append(i[2])
sector.append(i[3])
sentiment.append(i[4])
sentiment_volume.append(i[5])
return jsonify(result = [symbol,
name,
industry,
sector,
sentiment,
sentiment_volume])
def login():
error = ''
try:
if request.method == "POST":
c, conn = connection()
c.execute("SELECT * FROM users WHERE username = (%s)",
thwart(request.form['username']))
data = c.fetchone()
if data is None:
error = "Invalid credentials, try again."
else:
if sha256_crypt.verify(request.form['password'], data[2]):
session['logged_in'] = True
session['username'] = request.form['username']
flash("You are now logged in")
return redirect(url_for("sentiment_dashboard"))
else:
error = "Invalid credentials, try again."
gc.collect()
return render_template("login.html", error=error)
except Exception as e:
return str(e)
def signup():
c, conn = connection()
c.execute("use example")
name = request.form['user']
email = request.form['email']
passwd = sha256_crypt.encrypt(request.form['passwd'])
c.execute(
"INSERT INTO user (name,email,passwd) VALUES ('{0}','{1}','{2}')".
format(name, email, passwd))
conn.commit()
c.close()
conn.close()
return "submitted"
def sentiment_analysis(sym):
c, conn = connection()
c.execute("SELECT * FROM stocks WHERE symbol = %s", (sym,))
data = c.fetchall()
symbol = data[0][0]
name = data[0][1]
description = data[0][4]
return render_template("sentiment_analysis.html",
symbol=symbol,
name=name,
description=description)
def download_dashboard():
c, conn = connection()
c.execute("SELECT * FROM dashboard")
data = c.fetchall()
df = pd.DataFrame(list(data))
columns = ['Symbol', 'Security Name', 'Sector', 'Industry', 'Recent Sentiment', 'Sentiment Volume']
df.columns = columns
csv = df.to_csv()
response = make_response(csv)
response.headers["Content-Disposition"] = "attachment; filename=export.csv"
response.headers["Content-type"] = "text/csv"
return response
def login():
if request.method == "POST":
name = request.form['user']
passwd = request.form['passwd']
c, conn = connection()
c.execute("use example")
udata = c.execute("select * from user where name='{0}'".format(name))
udata = c.fetchone()
if sha256_crypt.verify(passwd, udata[3]):
msg = Message('From Manish Pandey',
sender='*****@*****.**',
recipients=[udata[2]])
msg.body = "You logged in now"
mail.send(msg)
return str(udata)
else:
return "wrong credential"
def registration():
try:
form = RegistrationForm(request.form)
if request.method == "POST" and form.validate():
username = form.username.data
email = form.email.data
password = sha256_crypt.encrypt((str(form.password.data)))
c, conn = connection()
x = c.execute("SELECT * FROM users WHERE username = (%s)",
(thwart(username)))
if int(x) > 0:
flash("That username is already taken, please choose another")
status = "Fail"
return render_template('register.html', form=form, status=status)
elif str(form.password.data) != str(form.confirm.data):
flash("Passwords must match!")
status = "Fail"
return render_template('register.html', form=form, status=status)
else:
c.execute("INSERT INTO users (username, password, email) VALUES (%s, %s, %s)",
(thwart(username), thwart(password), thwart(email)))
conn.commit()
flash("Thanks for registering!")
c.close()
conn.close()
gc.collect()
session['logged_in'] = True
session['username'] = username
return redirect(url_for('sentiment_dashboard'))
return render_template("register.html", form=form)
except Exception as e:
return(str(e))
def homepage():
c, conn = connection.connection()
c.execute("SELECT * FROM sentiment WHERE symbol = 'xom'")
data = c.fetchall()
sentiment = []
for i in data:
array = []
array.append(i[0])
array.append(i[1])
array.append(datetime.datetime.fromtimestamp(int(i[2])).strftime('%Y-%m-%d %H:%M'))
sentiment.append(array)
# return jsonify(str(sent))
return render_template("main.html",
sentiment=sentiment)
def login():
if request.method == "POST":
name = request.form['user']
passwd = request.form['passwd']
try:
check = request.cookies.get("name")
if check == name:
return "already logged in as " + name
except:
pass
c, conn = connection()
c.execute("use example")
udata = c.execute("select * from user where name='{0}'".format(name))
udata = c.fetchone()
if sha256_crypt.verify(passwd, udata[3]):
response = make_response("hello you are logged in now!!!")
response.set_cookie("name", udata[1])
return response
else:
return "wrong credential"
def get_sentiment_sym(sym):
c, conn = connection()
c.execute("SELECT * FROM daily WHERE symbol = %s", (sym,))
data = c.fetchall()
year = []
month = []
day = []
stock_open = []
stock_close = []
stock_high = []
stock_low = []
sent = []
sent_volume = []
sent = []
for i in data:
date = datetime.datetime.fromtimestamp(i[0])
year.append(date.year)
month.append(date.month)
day.append(date.day)
stock_open.append(i[2])
stock_close.append(i[3])
stock_high.append(i[4])
stock_low.append(i[5])
sent.append(i[6])
sent_volume.append(i[7])
return jsonify(result = [year,
month,
day,
stock_open,
stock_close,
stock_high,
stock_low,
sent,
sent_volume])
def assign_security(tweet, sentiment):
tokens = nltk.word_tokenize(tweet)
clean_tokens = [word for word in tokens if word not in stopwords]
for i in desc:
value = 0
for d in range(len(desc[i])):
for a in desc[i][d]:
for t in clean_tokens:
if t == a:
if d == 0:
value = 1.0
break
elif d == 1 or d == 2:
value = 0.5
break
elif d == 3:
value += 0.1
continue
if value >= 0.3:
sent = value * sentiment
# print(sent)
# print(i)
c, conn = connection.connection()
c.execute("INSERT INTO sentiment (symbol, sentiment, time) VALUES (%s, %s, %s)",
(i, sent, time.time()))
conn.commit()
def download_sentiment(sym):
c, conn = connection()
c.execute("SELECT * FROM hourly WHERE symbol = %s", (sym.lower(),))
data = c.fetchall()
df = pd.DataFrame(list(data))
columns = ['Date', 'Symbol', 'Sentiment', 'Volume']
df.columns = columns
symbol = df['Symbol'].iloc[0]
df = df.drop('Symbol', 1)
df['Date'] = pd.to_datetime(df['Date'], unit = 's')
csv = df.to_csv()
response = make_response(csv)
response.headers["Content-Disposition"] = "attachment; filename=" + symbol + ".csv"
response.headers["Content-type"] = "text/csv"
return response
#! /usr/bin/python
from mysqldb import connection
import pandas as pd
import pandas.io.data as web
import time
import datetime
#Query MySQL and find values within that last day
time_after = datetime.datetime.now() - datetime.timedelta(days=1)
time_after = datetime.datetime(time_after.year, time_after.month, time_after.day)
unix_time_after = time.mktime(time_after.timetuple())
c, conn = connection()
c.execute("SELECT * FROM sentiment WHERE time > %s", (unix_time_after,))
data = c.fetchall()
#Push data to DataFrame to perform GroupBy operations
symbol = []
sentiment = []
date = []
for i in data:
symbol.append(i[0])
sentiment.append(i[1])
date.append(datetime.datetime.fromtimestamp(int(i[2])))
df = pd.DataFrame(symbol)
df['sentiment'] = sentiment
