def compare_by_indiv():
df_2014_indiv = pd.read_sql(query_2014_indiv, get_database_connection(port = 2001))
df_2009_indiv = pd.read_sql(query_2009_indiv, get_database_connection(port = 2001))
combined = pd.merge(df_2014_indiv, df_2009_indiv, on = 'user_id', how = 'inner')
plt.scatter(combined['user_avg_score_x'], combined['user_avg_score_y'])
plt.grid()
plt.legend()
plt.xlabel('Average user sentiment score, 2014')
plt.ylabel('Average user sentiment score, 2009')
plt.rcParams['xtick.major.pad'] = 8
plt.rcParams['ytick.major.pad'] = 8
plt.savefig('./compare_2009_2014_indiv.png')
def publish_sentiment(algorithm, tweets): """Store a list of sentiment analysis results into the data base. :param algorithm: The algorithm used to classify tweets; can be a string or or an integer. :param tweets: An iterable list of (tweet_id, sentiment_score) tuples. """ rev = 'xxxxxxx' # rev = git_rev.git_current_revision() if isinstance(algorithm, str): algo = ALGORITHMS[algorithm] else: algo = algorithm conn = get_database_connection(port = 2001) cursor = conn.cursor() cursor.execute(CREATE_SQL) for tweet_id, score in tweets: cursor.execute(INSERT_SQL, [tweet_id, algo, rev, score]) conn.commit() cursor.close() conn.close()
def index():
"""Display Users"""
db = get_database_connection()
cursor = db[1]
cursor.execute('select * from unbabel_user')
users = db[1].fetchall()
return render_template('home.html', users=users)
def extract_classified_tweets(collapse_labels=True, limit=0):
"""Extract all tweets with at least one sentiment vote.
Tweets with ties in the majority vote count are omitted.
Return an iterator of tuples of the form (id, vote, text)
"""
conn = get_database_connection(port = 2001)
cursor = conn.cursor()
query = 'SELECT V.tweet_id, V.vote, T.text FROM majority_vote_unique AS V'
query += ' JOIN tweets_tweet AS T ON V.tweet_id=T.id'
if limit > 0:
query += ' LIMIT %d' % limit
cursor.execute(query)
try:
for _id, vote, text in cursor.fetchall():
if collapse_labels and vote != '-':
vote = 'X'
yield (_id, vote, text)
finally:
cursor.close()
conn.close()
def create_locations_table():
conn = get_database_connection()
cursor = conn.cursor()
cursor.execute(DELETE_SQL)
cursor.execute(CREATE_SQL)
conn.commit()
cursor.close()
conn.close()
def edit_user(user, user_id):
response.content_type = 'application/json'
data = request.json
db = get_database_connection()
db.users.update({'_id': ObjectId(user_id)},
{'$set': {
'name': data['name'],
'email': data['email']
}})
return json.dumps({'success': True, 'msg': 'Usuario editado'})
def get(self, slug):
set_active_org_project(slug)
db_conn = db.get_database_connection()
with db_conn.cursor() as cursor:
sql = 'SELECT * FROM `organisation` WHERE `slug`=%s'
cursor.execute(sql, (slug, ))
response = cursor.fetchone()
return render_template('organisation/edit_organisation.html',
organisation=response)
def remove_member(slug, member_name):
if request.method == "GET":
user = g.user
db_conn = db.get_database_connection()
with db_conn.cursor() as cursor:
sql = 'DELETE FROM `belongs_to` WHERE `belongs_to`.`username`=%s AND `belongs_to`.`slug`=%s'
cursor.execute(sql, (member_name, slug, ))
db_conn.commit()
return redirect(url_for('organisation.view_organisation',slug=slug))
def get_organisation(slug, username): db_conn = db.get_database_connection() with db_conn.cursor() as cursor: sql = 'SELECT u.username, w.logo, w.name as wname, p.project_id, p.name as pname \ FROM user u LEFT JOIN belongs_to b on u.username = b.username \ LEFT JOIN organisation w on b.slug = w.slug LEFT JOIN project p on w.slug = p.slug \ WHERE u.username=%s and b.slug=%s'; cursor.execute(sql, (username, slug, )) result = cursor.fetchall() return result
def post(self):
user = g.user
username = user["username"]
slug = request.form.get("slug", None)
name = request.form.get("name", None)
if 'logo' not in request.files:
flash("No file part", "danger")
return render_template('organisation/new_organisation.html')
logo = request.files['logo']
role = "Admin"
if slug and name and logo.filename:
_slug = self.get_slug(slug)
if _slug is not None:
flash("Slug already exists", "danger")
return render_template('organisation/new_organisation.html')
else:
if logo.filename and self.allowed_file(logo.filename):
filename = secure_filename(logo.filename)
logo.save(
os.path.join(app.app.config['UPLOAD_FOLDER'],
filename))
db_conn = db.get_database_connection()
with db_conn.cursor() as cursor:
cursor.execute(
"INSERT INTO `organisation`(`slug`,`name`,`logo`) Values (%s, %s, %s)",
(slug, name, filename))
db_conn.commit()
cursor.execute(
"INSERT INTO `belongs_to`(`slug`, `username`, `role`) \
Values (%s, %s, %s)", (slug, username, role))
db_conn.commit()
return redirect(url_for('organisation.organisation'))
else:
flash("logo is not a image", "danger")
return render_template(
'organisation/new_organisation.html')
else:
if not slug:
flash("Enter slug", "danger")
if not name:
flash("Enter name", "danger")
if not logo.filename:
flash("Select file", "danger")
return render_template('organisation/new_organisation.html')
def list_order(user,user_id):
if (user["id"] == user_id):
db = get_database_connection()
listOrders = db.orders.find({"user.id": user_id},{"date":1, "total":1})
if listOrders._Cursor__empty :
return json.dumps({'success': False, 'msg': 'Não existem pedidos para o usuario ' + user + "."})
else:
arrayRetorno = []
for item_list in listOrders:
arrayRetorno.append({"id":str(item_list["_id"]),"date":str(item_list["date"]),"total":item_list["total"]})
return json.dumps(arrayRetorno)
else:
return json.dumps({'success': False, 'msg': 'Identificadores dos usuários não conferem.'})
def create_user():
response.content_type = 'application/json'
data = request.json
name = data["name"]
email = data["email"]
password = hashlib.md5(data["password"].encode()).hexdigest()
db = get_database_connection()
user = db.users.find_one({'email': email})
if user:
return json.dumps({'success': True, 'msg': 'user already exists.'})
else:
db.users.insert({'name': name, 'email': email, 'password': password})
return json.dumps({'success': True, 'msg': 'user added.'})
def order(user,user_id,order_id):
if (user["id"] == user_id):
if (order_id == ""):
return json.dumps({'success': False, 'msg': 'Código do pedido não foi informado.'})
else:
db = get_database_connection()
order = db.orders.find_one({"_id": ObjectId(order_id),"user.id": user_id})
if order is None:
return json.dumps({'success': False, 'msg': 'Pedido não foi localizado para o código informado.'})
else:
return json.dumps({"id":str(order["_id"]),"user":order["user"],"total":order["total"],"items":order["items"],"data":str(order["date"])})
else:
return json.dumps({'success': False, 'msg': 'Identificadores dos usuários nao conferem.'})
def profile():
user = g.user
username = user["username"]
db_conn = db.get_database_connection()
with db_conn.cursor() as cursor:
sql = 'SELECT * FROM `user` WHERE `username`=%s'
cursor.execute(sql, (username, ))
profile = cursor.fetchone()
if request.method == "GET":
return render_template('profile.html', profile=profile)
elif request.method == "POST":
email = request.form.get("email", None)
first_name = request.form.get("first_name", None)
last_name = request.form.get("last_name", None)
password = request.form.get("password", None)
if email and first_name and last_name and password:
password_hash = generate_password_hash(password)
with db_conn.cursor() as cursor:
sql = 'UPDATE `user` SET `email`=%s, `first_name`=%s, `last_name`=%s, `password`=%s WHERE `user`.`username`=%s'
cursor.execute(sql, (
email,
first_name,
last_name,
password_hash,
username,
))
db_conn.commit()
return render_template('profile.html', profile=profile)
else:
if not email:
flash("Enter email", "danger")
if not first_name:
flash("Enter first_name", "danger")
if not last_name:
flash("Enter last_name", "danger")
if not password:
flash("Enter password", "danger")
return render_template('profile.html', profile=profile)
def execute_query(query):
conn = get_database_connection(port = 2001)
cursor = conn.cursor()
# Select all dates from 2014 tweets table.
cursor.execute(query)
# Results are returned as a tuple of tuples; making a list of tuples instead.
results = cursor.fetchall()
results_list = [x[0] for x in results]
cursor.close()
conn.close()
return results_list
def average_user_scores(query):
"""
Queries SQL database to return an object with average score
by user with their associated county and state locations.
"""
conn = get_database_connection(port = 2001)
cursor = conn.cursor()
cursor.execute(query)
results = cursor.fetchall()
cursor.close()
conn.close()
return results
def compare_by_county():
df_2014 = pd.read_sql(query_2014, get_database_connection(port = 2001))
stats_2014 = df_2014[['county', 'state', 'user_avg_score']].groupby(['county', 'state']).agg(['mean', 'count'])
stats_2014_20_users = stats_2014[stats_2014['user_avg_score']['count'] >= 20]
df_2009 = pd.read_sql(query_2009, get_database_connection(port = 2001))
stats_2009 = df_2009[['county', 'state', 'user_avg_score']].groupby(['county', 'state']).agg(['mean', 'count'])
stats_2009_20_users = stats_2009[stats_2009['user_avg_score']['count'] >= 20]
print stats_2009_20_users
combined = pd.merge(stats_2014_20_users, stats_2009_20_users, left_index = True, right_index = True, how = 'inner')
print combined.corr()
x = np.arange(0.70, 0.85, 0.01)
m, b = np.polyfit(combined['user_avg_score_x']['mean'], combined['user_avg_score_y']['mean'], 1)
plt.plot(x, m*x + b, '-', color = 'red', linewidth = 2)
plt.scatter(combined['user_avg_score_x']['mean'], combined['user_avg_score_y']['mean'])
plt.grid()
plt.xlabel('Average user sentiment score by county, 2014')
plt.ylabel('Average user sentiment score by county, 2009')
plt.rcParams['xtick.major.pad'] = 8
plt.rcParams['ytick.major.pad'] = 8
plt.savefig('./compare_2009_2014_county.png')
def extract_text(table, limit=0):
"""Extract (tweet_id, text) from the database for all tweets from given table of tweets.
If limit is non-zero, return this many tuples.
"""
conn = get_database_connection(port = 2001)
cursor = conn.cursor()
# SQL statement should read SELECT id, text FROM %s' % (table) if using old tweets
sql = 'SELECT tweet_id, text FROM %s' % (table)
cursor.execute(sql)
if limit > 0:
return cursor.fetchmany(limit)
else:
return cursor.fetchall()
def get(self, slug, project_id):
db_conn = db.get_database_connection()
set_active_org_project(slug, project_id)
with db_conn.cursor() as cursor:
sql = 'SELECT * FROM `project` WHERE `slug`=%s and `project_id`=%s'
cursor.execute(sql, (
slug,
project_id,
))
result = cursor.fetchone()
return render_template('organisation/edit_project.html',
slug=slug,
project=result)
def create_user():
"""Create User"""
db = get_database_connection()
conn = db[0]
cursor = db[1]
if request.method == 'POST':
name = request.form['name']
email = request.form['email']
cursor.execute("select id from unbabel_user order by id desc limit 1")
primary_key = int(cursor.fetchall()[0][0])
primary_key += 1
cursor.execute("insert into unbabel_user values(%d, '%s', '%s')" % (primary_key, name, email))
conn.commit()
return redirect('/')
def fetch_and_store_in_db(service, user_id):
conn = get_database_connection()
cur = conn.cursor()
messages = MessagesWithLabels(service, user_id, ['INBOX'])
for msg in messages:
result = GetMessage(service, user_id, msg['id'])
result['date'] = " ".join(result['date'].split()[:5])
conv = time.strptime(result['date'], "%a, %d %b %Y %H:%M:%S")
result['date'] = time.strftime("%Y-%m-%d %H:%M:%S", conv)
result['msg_id'] = msg['id']
# Insert data into tables.
cur.execute(
"INSERT INTO mail_data(email_from,email_to,email_subject,email_message,email_received,message_id) VALUES (%s, %s, %s, %s, %s, %s)",
(result['from'], result['to'], result['subject'],
result['message'], result['date'], str(result['msg_id'])))
conn.commit()
conn.close()
def create_session(user):
response.content_type = 'application/json'
data = request.json
if(not 'name_session' in data or not data["name_session"]):
return json.dumps({'success': False, 'msg': 'Nome da sessão não informado.'})
else:
db = get_database_connection();
nameSession = data["name_session"]
session = db.session.find_one({'name_session': nameSession})
if not session is None :
return json.dumps({'success': False, 'msg': 'Sessão já cadastrada.'})
else:
db.session.insert({
"name_session": data["name_session"],
})
return json.dumps({"success": True, "msg": "Sessão criada com sucesso"})
def get_api(slug, project_id):
if request.method == "GET":
response = {}
db_conn = db.get_database_connection()
while True:
api_key = uuid.uuid4()
api_key = api_key.hex
with db_conn.cursor() as cursor:
sql = 'SELECT `api_key` FROM `project` WHERE `api_key`=%s'
cursor.execute(sql, (api_key, ))
result = cursor.fetchone()
if result is None:
with db_conn.cursor() as cursor:
sql = 'UPDATE `project` SET `api_key`=%s WHERE `project`.`project_id`=%s'
cursor.execute(sql, (api_key, project_id, ))
db_conn.commit()
break
return redirect(url_for('organisation.view_project', slug=slug, project_id=project_id))
def create_user():
print("lala");
response.content_type='application/json'
data = request.json
name = data["name"] # obtem nome enviado por parametro postado.
email = data["email"] # obtem email enviado por parametro postado.
password = hashlib.md5(data["password"].encode()).hexdigest() # obtem hash md5 da senha enviada.
db = get_database_connection() # conecta com a base de dados e armazena a conexao em db.
user = db.users.find_one({'email': email}) # find_one retorna um documento,
# ou None se nao encontrar nenhum.
if user:
# usuario ja existe. retornar em formato JSON padrao com mensagem.
return json.dumps({'success': True, 'msg': 'Usuário já existente.'})
else:
# usuario nao existe. inserir novo usuario.
db.users.insert({'name': name, 'email': email, 'password': password,'is_admin':True})
# retornar em formato JSON padrao com mensagem.
return json.dumps({'success': True, 'msg': 'Usuário cadastrado.'})
def signup():
if 'username' in session:
return redirect(url_for('auth.my_details'))
if request.method == "GET":
return render_template('signup.html')
elif request.method == "POST":
username = request.form.get("username", None)
first_name = request.form.get("first_name", None)
last_name = request.form.get("last_name", None)
email = request.form.get("email", None)
password = request.form.get("password", None)
print(f'{username}')
if username and first_name and last_name and email and password:
user = db.get_user(username)
if user is not None:
flash("Username already exists","danger")
return render_template('signup.html')
else:
_email = get_email(email)
if _email is not None:
flash("Email already exists","danger")
return render_template('signup.html')
else:
password_hash = generate_password_hash(password)
db_conn = db.get_database_connection()
with db_conn.cursor() as cursor:
cursor.execute("INSERT INTO `user`(`username`,`email`,`first_name`,`last_name`,`password`) Values (%s, %s, %s, %s, %s)", (username, email, first_name, last_name, password_hash))
db_conn.commit();
session["username"] = username
return redirect(url_for('organisation.organisation'))
else:
if not username:
flash("Enter Username", "danger")
if not first_name:
flash("Enter First name", "danger")
if not last_name:
flash("Enter Last name", "danger")
if not email:
flash("Enter Email", "danger")
if not password:
flash("Enter Password", "danger")
return render_template('signup.html')
def user_resource():
"""Handle API HTTP Requests"""
db = get_database_connection()
conn = db[0]
cursor = db[1]
if request.method == 'GET':
cursor.execute('select * from unbabel_user')
records = cursor.fetchall()
"""Build dict of dicts, keyed on user id"""
json_dict = {}
for record in records:
info_dict = {'name':record[1],'email':record[2]}
json_dict[record[0]] = info_dict
return jsonify( json_dict ), 201
elif request.method == 'POST':
info = json.loads(request.data) #request.json is another option here
name = info['name']
email = info['email']
cursor.execute("select id from unbabel_user order by id desc limit 1")
primary_key = int(cursor.fetchall()[0][0])
primary_key += 1
cursor.execute("insert into unbabel_user values(%d, '%s', '%s')" % (primary_key, name, email))
conn.commit()
return jsonify( { 'primary_key':primary_key, 'name': name, 'email':email } ), 201
elif request.method == 'PATCH':
info = json.loads(request.data)
try:
name_update = info['name']
email_update = info['email']
except KeyError:
pass
cursor.execute("update unbabel_user set name = '%s', email = '%s' where id = %d" % (name_update, email_update, user_id))
conn.commit()
return jsonify( {'name':name_update, 'email':email_update} ), 201
def extract_labeled_tweets(limit=0):
"""Extract all tweets with a revised label.
Return an iterator of tuples of the form (id, vote, text)
"""
conn = get_database_connection(port = 2001)
cursor = conn.cursor()
query = 'SELECT L.id, L.label, T.text FROM revised_labels AS L'
query += ' JOIN tweets_tweet AS T ON L.id=T.id'
if limit > 0:
query += ' LIMIT %d' % limit
try:
cursor.execute(query)
return cursor.fetchall()
finally:
cursor.close()
conn.close()
def in_database(entry, column, table):
"""
Test whether entry is already observed in a given column of a given table.
"""
conn = get_database_connection(port = 2001)
cursor = conn.cursor()
query = 'SELECT * FROM %s WHERE %s = %s' % (table, column, entry)
cursor.execute(query)
results = cursor.fetchall()
cursor.close()
conn.close()
if len(results) == 0:
return False
else:
return True
def edit_user(user,user_id):
response.content_type = 'application/json'
data = request.json
if(not "name" in data or not data["name"]):
return json.dumps({'success': False, 'msg': 'Name is required'})
if(not "email" in data or not data["email"]):
return json.dumps({'success': False,'msg':'E-mail is required'})
name = data["name"]
email = data["email"]
db = get_database_connection() # conecta com a base de dados e armazena a conexao em db.
if (user["id"] == user_id):
db.users.update({"_id": ObjectId(user_id)}, {"$set": {'name': name, 'email': email}})
db.orders.update({"user.id": str(user_id)}, {"$set": {'user.name': name, 'user.email': email}},multi=True)
return json.dumps({'success': True, 'msg': 'Usuário alterado.'})
else:
return json.dumps({'success': False, 'msg': 'Identificadores dos usuários não conferem.'})
def extract_tweeters(table):
"""
Extract all users with geographic information.
"""
users = {}
conn = get_database_connection(port = 2001)
cursor = conn.cursor()
# This execute statement depends on which table you are trying to extract users from.
# If 2009 dataset, use 'SELECT user_name, id, location_string FROM tweeter_tweeter'
if table == 'users_2014':
cursor.execute('SELECT user_id, location FROM users_2014')
elif table == 'tweeter_tweeter':
cursor.execute('SELECT user_name, id, location_string FROM tweeter_tweeter')
if table == 'tweeter_tweeter':
for user_name, id, location_string in cursor.fetchall():
if location_string is not None:
user = {}
user['location'] = location_string
user['user_name'] = user_name
user['user_id'] = id
users[id] = user
elif table == 'users_2014':
for user_id, location in cursor.fetchall():
if location is not None:
user = {}
user['location'] = location
user['user_id'] = user_id
users[user_id] = user
cursor.close()
conn.close()
return users.values()
def decorated_function(*args, **kwargs):
username = session.get("username", None)
if not username:
return redirect(url_for('auth.login', next=request.url))
g.user = db.get_user(username)
if g.user is None:
return redirect(url_for('auth.login', next=request.url))
db_conn = db.get_database_connection()
with db_conn.cursor() as cursor:
sql = ("SELECT"
" belongs_to.slug, project.project_id, organisation.name"
" FROM"
" `user`"
" LEFT OUTER JOIN"
" belongs_to ON user.username = belongs_to.username"
" LEFT OUTER JOIN"
" project ON project.slug = belongs_to.slug"
" LEFT OUTER JOIN"
" organisation ON organisation.slug = belongs_to.slug"
" WHERE"
" user.username = %s;")
cursor.execute(sql, (username, ))
result = cursor.fetchall()
g.user["orgs"] = {}
g.orgs = {}
if result is not None:
for record in result:
organisation_slug = record["slug"]
project_id = record["project_id"]
org_data = g.orgs.get(organisation_slug, None) or {"name": record["name"]}
l = org_data.get("projects", None) or []
org_data["projects"] = l + [project_id]
g.orgs[organisation_slug] = org_data
return f(*args, **kwargs)
def insert_record(record, table): """ Given a dictionary, uses its keys to insert its values in mySQL table (assumes key names map to column names in table). """ tups = [(key, val) for key, val in record.iteritems()] keys = [key for key, val in tups] vals = [val for key, val in tups] conn = get_database_connection(port = 2001) cursor = conn.cursor() num_cols = len(keys) key_str = ','.join(keys) val_str = ','.join(['%s'] * num_cols) qry = "REPLACE INTO %s (%s) VALUES (%s)" % (table, key_str, val_str) cursor.execute(qry, vals) conn.commit() cursor.close() conn.close()
def extract_tweeters():
"""
Extract all users with geographic information.
"""
users = {}
conn = get_database_connection(port = 2001)
cursor = conn.cursor()
cursor.execute('SELECT user_name, twitter_user_id, location_string FROM tweeter_tweeter LIMIT 10')
for user_name, twitter_user_id, location_string in cursor.fetchall():
if location_string is not None:
user = users.get(twitter_user_id, Tweeter(twitter_user_id))
user.location = location_string
user.user_name = user_name
users[twitter_user_id] = user
cursor.close()
conn.close()
return users.values()
def post(self, slug, project_id):
name = request.form.get("name", None)
description = request.form.get("description", None)
db_conn = db.get_database_connection()
with db_conn.cursor() as cursor:
sql = 'SELECT * FROM `project` WHERE `slug`=%s and `project_id`=%s'
cursor.execute(sql, (
slug,
project_id,
))
result = cursor.fetchone()
if name and description:
with db_conn.cursor() as cursor:
sql = 'UPDATE `project` SET `name`=%s, `description`=%s \
WHERE `project`.`project_id`=%s'
cursor.execute(sql, (
name,
description,
project_id,
))
db_conn.commit()
return redirect(
url_for('organisation.view_project',
slug=slug,
project_id=project_id))
else:
if not name:
flash("Project name can't be empty", "danger")
if not description:
flash("Description can't be empty", "danger")
return render_template('organisation/edit_project.html',
slug=slug,
project=result)
INSERT INTO revised_labels(id, label) VALUES (%s, %s)
ON DUPLICATE KEY UPDATE label=%s;
"""
LABEL_MAP = {'z': '-', 'x': 'X'}
def get_records(philes):
for phile in philes:
with open(phile) as fh:
for line in fh:
toks = line.split(',')
yield (int(toks[0]), LABEL_MAP[toks[1].strip()])
if __name__ == '__main__':
philes = []
for root, dirs, files in os.walk('.'):
philes.extend([os.path.join(root, phile) for phile in files
if phile.startswith('LABELS')])
conn = get_database_connection(2001)
cursor = conn.cursor()
cursor.execute(CREATE_SQL)
for _id, label in get_records(philes):
print _id, label
cursor.execute(INSERT_SQL, [_id, label, label])
conn.commit()
cursor.close()
conn.close()
def register_new_event():
"""
This endpoint registers a new event for a project.
This endpoint should typically be called from an external site (i.e site of an user)
to log a new event onto our database.
parameters:
page_url: The current URL of the from which the event is to be registered
event_type: A specific tag for that particular event.
custom_params: (Optional) Any other data that the client developer wants to send along.
(Should be valid JSON)
api_key: The API key of the project for which the event is to be logged
origin_id: A unique tag identifying the end-user of the client-website.
"""
# The keys that should be present a request that comes in to register a new event
REGISTER_NEW_EVENT_KEYS = {"page_url", "event_type", "api_key"}
response = {"success": False}
if not request.is_json:
# Requests should be of type application/json
return response, 400
request_body = request.get_json()
missing = check_missing_keys(request_body, REGISTER_NEW_EVENT_KEYS)
if missing:
response["error"] = "Malformed request - missing parameters - " + \
str(missing)
return response, 400
user_agent = request.headers.get('User-Agent', None)
api_key = request_body["api_key"]
project_id = None
try:
origin_id_components = request.headers['Host'] + \
request.remote_addr + user_agent
except Exception as e:
# Some thing is missing
response["error"] = "Malformed request"
return response, 400
db_connection = db.get_database_connection()
with db_connection.cursor() as cursor:
# Search for key in Redis
project_id = None
project_id = db.get_redis_connection().get(api_key)
print('Cached', project_id)
if not project_id:
# we don't have the ID cached in Redis, so check in DB
get_project_from_api_key_sql = 'SELECT `project_id` FROM `project` WHERE `api_key`=%s'
cursor.execute(get_project_from_api_key_sql, (api_key, ))
result = cursor.fetchone()
if not result:
response["error"] = "Invalid API key"
return response, 404
# We have successful search in the database
project_id = result.get("project_id")
# Cache the same in Redis
db.get_redis_connection().set(api_key, project_id)
origin_id = sha256(origin_id_components.encode()).hexdigest()
custom_data = request_body.get('custom_params', {})
if (request_body["event_type"] == 'pageview'):
# If it is a pageview event, then along with user data, we also include our customised info.
pageview_custom_data = get_custom_data_from_user_agent(user_agent)
custom_data = {**custom_data, **pageview_custom_data}
custom_data = json.dumps(custom_data)
session_id = request_body.get("session_id", None)
if not session_id:
session_id = str(uuid.uuid4())
with db_connection.cursor() as cursor:
insert_new_session = "INSERT INTO `session` ( `session_id`, `origin_id`, \
`project_id`, `start_page`, `end_page`) VALUES \
(%s, %s, %s, %s, %s)"
cursor.execute(
insert_new_session,
(session_id, origin_id, project_id, request_body["page_url"],
request_body["page_url"]))
db_connection.commit()
insert_new_event_sql = "INSERT INTO `web_event` ( `session_id`, `user_agent`, \
`page_url`, `event_type`, `custom_data`) VALUES \
(%s, %s, %s, %s, %s)"
cursor.execute(insert_new_event_sql,
(session_id, user_agent, request_body["page_url"],
request_body["event_type"], custom_data))
db_connection.commit()
add_new_event(session_id, project_id, origin_id,
int(datetime.datetime.now().timestamp()), user_agent,
request_body["page_url"], request_body["page_url"],
'{}', request_body["event_type"], '', '',
json.loads(custom_data))
else:
end_time = datetime.datetime.now()
end_time = end_time.strftime("%Y-%m-%d %H:%M:%S")
with db_connection.cursor() as cursor:
# Validate session ID
sql = 'SELECT session_id FROM `session` WHERE session_id=%s AND project_id=%s;'
cursor.execute(sql, (session_id, project_id))
result = cursor.fetchone()
if not result:
# Not a valid session, possibly because someone is tampering with the session_id stored in the client
response["error"] = "Invalid request"
return response, 404
update_exist_session = "UPDATE `session` SET `end_time`=%s, `end_page`=%s WHERE `session_id`=%s"
cursor.execute(update_exist_session, (
end_time,
request_body["page_url"],
session_id,
))
db_connection.commit()
insert_new_event_sql = "INSERT INTO `web_event` ( `session_id`, `user_agent`, \
`page_url`, `event_type`, `custom_data`) VALUES \
(%s, %s, %s, %s, %s)"
cursor.execute(insert_new_event_sql,
(session_id, user_agent, request_body["page_url"],
request_body["event_type"], custom_data))
db_connection.commit()
add_new_event(session_id, project_id, origin_id,
int(datetime.datetime.now().timestamp()), user_agent,
request_body["page_url"], request_body["page_url"],
'{}', request_body["event_type"], '', '',
json.loads(custom_data))
response["success"] = True
response["session_id"] = session_id
return response
def post(self, slug):
user = g.user
username = user["username"]
project_id = request.form.get("project_id", None)
if project_id == "new" or project_id == "project":
flash("project_id is not applicable", "danger")
return render_template('organisation/new_project.html')
name = request.form.get("name", None)
description = request.form.get("description", None)
if project_id and name and description:
db_conn = db.get_database_connection()
with db_conn.cursor() as cursor:
sql = 'SELECT `slug`, `project_id` FROM `project` WHERE `slug`=%s and `project_id`=%s'
cursor.execute(sql, (
slug,
project_id,
))
result = cursor.fetchone()
if result is not None:
flash("project already exist in the organisation", "danger")
return render_template('organisation/new_project.html')
with db_conn.cursor() as cursor:
sql = 'SELECT `project_id` FROM `project` WHERE `project_id`=%s'
cursor.execute(sql, (project_id, ))
result = cursor.fetchone()
if result is not None:
flash("project_id already exist", "danger")
return render_template('organisation/new_project.html')
while True:
api_key = uuid.uuid4()
api_key = api_key.hex
with db_conn.cursor() as cursor:
sql = 'SELECT `api_key` FROM `project` WHERE `api_key`=%s'
cursor.execute(sql, (api_key, ))
result = cursor.fetchone()
if result is None:
break
with db_conn.cursor() as cursor:
cursor.execute(
"INSERT INTO `project`(`slug`,`project_id`,`name`,`description`,`api_key`) \
Values (%s, %s, %s, %s, %s)",
(slug, project_id, name, description, api_key))
db_conn.commit()
return redirect(
url_for('organisation.view_project',
slug=slug,
project_id=project_id))
else:
if not project_id:
flash("Enter project_id", "danger")
if not name:
flash("Enter name", "danger")
if not description:
flash("Enter description", "danger")
return render_template('organisation/new_project.html')
def list_user_from_admin(user): response.content_type='application/json' db = get_database_connection() # conecta com a base de dados e armazena a conexao em db. users = db.users.find() return mongo_dumps(users)
def list_user_from_admin(user):
response.content_type = 'application/json'
db = get_database_connection()
users = db.users.find()
return mongo_dumps(users)
def state_averages_2014():
df_2014 = pd.read_sql(query_2014, get_database_connection(port = 2001))
stats_2014 = df_2014[['state', 'user_avg_score']].groupby(['state']).agg(['mean', 'count'])
print stats_2014
stats_2014.to_csv('./average_sentiment_by_state_2014.csv')
def post(self, slug):
user = g.user
username = user["username"]
organisation = {}
organisation["slug"] = slug
set_active_org_project(slug)
db_conn = db.get_database_connection()
with db_conn.cursor() as cursor:
sql = 'SELECT name, logo FROM organisation WHERE slug=%s'
cursor.execute(sql, (slug, ))
result = cursor.fetchone()
organisation["logo"] = result["logo"]
organisation["name"] = result["name"]
with db_conn.cursor() as cursor:
sql = 'SELECT username ,role FROM belongs_to WHERE slug=%s'
cursor.execute(sql, (slug, ))
result = cursor.fetchall()
_member = []
for rows in result:
if rows is not None:
_member.append(rows)
organisation["members"] = _member
with db_conn.cursor() as cursor:
sql = 'SELECT project_id FROM project WHERE slug=%s'
cursor.execute(sql, (slug, ))
result = cursor.fetchall()
_projects = []
for rows in result:
if rows is not None:
_projects.append(rows["project_id"])
organisation["projects"] = _projects
username = request.form.get("username", None)
if username:
_username = db.get_user(username)
if _username is not None:
db_conn = db.get_database_connection()
with db_conn.cursor() as cursor:
sql = 'SELECT slug, username FROM belongs_to WHERE username=%s and slug=%s'
cursor.execute(sql, (
username,
slug,
))
result = cursor.fetchone()
if result is not None:
flash("user is already working in the organisation",
"danger")
return render_template(
'organisation/view_organisation.html',
user=user,
organisation=organisation,
show_results=True)
else:
with db_conn.cursor() as cursor:
cursor.execute(
"INSERT INTO belongs_to(username,slug,role) Values (%s, %s, %s)",
(username, slug, "Member"))
db_conn.commit()
with db_conn.cursor() as cursor:
sql = 'SELECT username, role FROM belongs_to WHERE slug=%s'
cursor.execute(sql, (slug, ))
result = cursor.fetchall()
_member = []
for rows in result:
if rows is not None:
_member.append(rows)
organisation["members"] = _member
return render_template(
'organisation/view_organisation.html',
user=user,
organisation=organisation,
show_results=True)
else:
flash("username does not exist", "danger")
return render_template('organisation/view_organisation.html',
user=user,
organisation=organisation,
show_results=True)
else:
flash("Enter username", "danger")
return render_template('organisation/view_organisation.html',
user=user,
organisation=organisation,
show_results=True)
def create_menu_item(user):
response.content_type = 'application/json'
data = request.json
if (not "name" in data or not data["name"]):
return json.dumps({'success': False, 'msg': 'Name is required'})
if (not "price" in data or not data["price"]):
return json.dumps({'success': False, 'msg': 'Price is required'})
if (not "name_session" in data or not data["name_session"]):
return json.dumps({'success': False, 'msg': 'Name session is required'})
db = get_database_connection()
name = data["name"]
price = data["price"]
name_session = data['name_session']
item_cad = db.menu_items.find_one({"name": name});
if item_cad:
return json.dumps({'success': False, 'msg': 'Item do menu já existente.'})
else:
db.menu_items.insert({'name': name,'price':price,'name_session':name_session})
return json.dumps({'success':True,'msg':'Item do menu cadastrado com sucesso.'})
#******** FIM NOVO ITEM CARDAPIO **************
#**********************************************
# LINK CHAMADAS POSTMAN: https://app.getpostman.com/run-collection/cda5179b0745beb3bc6b
# **************************************************
#************** ENTIDADES **************************
# users:{
# _id: "57b4f1e43162731724f15de4",
# name: "dayane",
# password: "******",
# email: "*****@*****.**"
# }
#
# menu_items:{
# _id: "57b50450316273152ccbdd55",
# name: "sprite",
# price: 10.6,
# name_session: "bebidas"
# }
#
# session:{
# _id: "57b506be316273152ccbdd57",
# name: "bebidas"
# }
#
# orders:{
# _id: "57b50702316273152ccbdd59",
# date: "2016-08-19 23:00:00",
# total: 100.00,
# user: {
# _id: "57b4f1e43162731724f15de4"
# }
# items: [
# {
# name: "sprite"
# },
# {
# name: "coca-cola"
# },
# ]
# }
#**************************************************
#**************************************************
def post(self, slug):
set_active_org_project(slug)
db_conn = db.get_database_connection()
with db_conn.cursor() as cursor:
sql = 'SELECT * FROM `organisation` WHERE `slug`=%s'
cursor.execute(sql, (slug, ))
response = cursor.fetchone()
name = request.form.get("name", None)
if 'logo' not in request.files:
flash("No file part", "danger")
return render_template('organisation/edit_organisation.html',
organisation=response)
logo = request.files['logo']
if logo.filename or name:
with db_conn.cursor() as cursor:
sql = 'SELECT `logo` FROM `organisation` WHERE `slug`=%s'
cursor.execute(sql, (slug, ))
result = cursor.fetchone()
if not logo.filename:
logo.filename = result["logo"]
if result["logo"] == logo.filename:
with db_conn.cursor() as cursor:
sql = 'UPDATE `organisation` SET `name`=%s WHERE `organisation`.`slug`=%s'
cursor.execute(sql, (
name,
slug,
))
db_conn.commit()
return redirect(
url_for('organisation.view_organisation', slug=slug))
else:
if logo.filename and allowed_file(logo.filename):
filename = secure_filename(logo.filename)
logo.save(
os.path.join(app.app.config['UPLOAD_FOLDER'],
filename))
with db_conn.cursor() as cursor:
sql = 'UPDATE `organisation` SET `name`=%s, `logo`=%s \
WHERE `organisation`.`slug`=%s'
cursor.execute(sql, (
name,
filename,
slug,
))
db_conn.commit()
return redirect(
url_for('organisation.view_organisation',
slug=slug))
else:
flash("logo selected should be a image", "danger")
return render_template(
'organisation/edit_orgainsation.html',
organisation=response)
else:
if not name:
flash("Enter name", "danger")
return render_template('organisation/edit_organisation.html',
organisation=response)
