def sql_database():
from functions.sqlquery import sql_query, sql_query3
flowers_table = sql_query('''SELECT * FROM Flowers''')
#sightings_results = sql_query3('''SELECT * FROM SIGHTINGS WHERE NAME='Draperia' ORDER BY SIGHTED DESC''', 10)
sightings_table = sql_query('''SELECT * FROM Sightings''')
return render_template(
'sqldatabase.html',
#sightings_results=sightings_results,
sightings_table=sightings_table,
flowers_table=flowers_table)
def sql_editlink():
from functions.sqlquery import sql_query, sql_query2
if request.method == 'GET':
word = request.args.get('word')
# efname = request.args.get('efname')
eresults1 = sql_query2(
''' SELECT DISTINCT * FROM words where word = ?''', (word, ))
# Decode base 64
eresults = []
for result in eresults1:
resultx = dict()
payload = base64.decodestring(str(result['payload']))
resultx['word'] = word
resultx['payload'] = payload
resultx['created_time'] = result['created_time']
eresults.append(resultx)
results1 = sql_query(''' SELECT * FROM words''')
# Decode base 64
results = []
for result in results1:
resultx = dict()
payload = json.loads(
base64.decodestring(str(result['payload']).encode()))
mean = json.loads(base64.decodestring(str(payload['means']).encode()))
resultx['payload'] = mean
resultx['word'] = result['word']
resultx['created_time'] = result['created_time']
results.append(resultx)
return render_template('sqldatabase.html',
eresults=eresults,
results=results)
def push_Data():
user_id = request.form['user_id']
time_array = [
'if_opted_in', 'if_zoomed_in', 'if_checked_details',
'if_checked_reviews'
]
time_dict = {
'if_opted_in': 2,
'if_zoomed_in': 2,
'if_checked_details': 3,
'if_checked_reviews': 3
}
total_time = 0
for k in range(len(time_array)):
var = int(request.form[time_array[k]])
if var != 0:
total_time += time_dict.get(time_array[k])
search_id = request.form['search_id']
from functions.sqlquery import sql_edit_insert, sql_query
results = sql_edit_insert(
''' INSERT INTO searches (user_id,search1_id, time_spent) VALUES (?,?,?)''',
(user_id, search_id, total_time))
answer = sql_query(''' SELECT * from searches''')
df_new = pd.DataFrame(answer)
df_new.columns = ['user_id', 'search1_id', 'time_spent']
df_new.to_csv('user_history.csv', index=False)
return jsonify(results)
def sql_editlink():
if request.method == 'GET':
elname = request.args.get('elname')
efname = request.args.get('efname')
eresults = sql_query2(''' SELECT * FROM data_table where first_name = ? and last_name = ?''', (efname,elname))
results = sql_query(''' SELECT * FROM data_table''')
return render_template('sqldatabase.html', eresults=eresults, results=results)
def hide_data():
from functions.sqlquery import sql_query
results = sql_query(''' SELECT * FROM data_table''')
msg = 'SELECT * FROM data_table'
# Check if user is loggedin
# User is loggedin show them the home page
return render_template('sqldatabase.html', results=results, msg=msg)#, username=session['username'])
def sql_datainsert():
from functions.sqlquery import sql_edit_insert, sql_query
if request.method == 'POST':
word = request.form['word']
payload = base64.encodestring(request.form['payload'])
created_time = int(round(time.time() * 1000))
sql_edit_insert(
''' INSERT INTO words (word,payload,created_time) VALUES (?,?,?) ''',
(word, payload, str(created_time)))
results1 = sql_query(''' SELECT * FROM words''')
msg = 'INSERT INTO words (word, payload, created_time) VALUES (' + word + ',' + payload + ',' + str(
created_time) + ')'
results = []
for result in results1:
resultx = dict()
payload = json.loads(
base64.decodestring(str(result['payload']).encode()))
mean = json.loads(base64.decodestring(str(payload['means']).encode()))
resultx['payload'] = mean
resultx['word'] = result['word']
resultx['created_time'] = result['created_time']
results.append(resultx)
return render_template('sqldatabase.html', results=results, msg=msg)
def sql_editlink():
from functions.sqlquery import sql_query, sql_query2
if request.method == 'GET':
edname = request.args.get('edname')
eresults = sql_query2(''' SELECT * FROM data_table where drink_name = ?''', (edname))
results = sql_query(''' SELECT * FROM data_table''')
return render_template('sqldatabase.html', eresults=eresults, results=results)
def sql_datadelete():
from functions.sqlquery import sql_delete, sql_query
if request.method == 'GET':
zip = request.args.get('zipcode')
sql_delete(''' DELETE FROM data_table where zip = ? ''', (zip))
results = sql_query(''' SELECT * FROM data_table''')
msg = 'DELETE FROM data_table WHERE zipcode = ' + zip
return render_template('sqldatabase.html', results=results, msg=msg)
def sql_datadelete():
from functions.sqlquery import sql_delete, sql_query
if request.method == 'GET':
dname = request.args.get('dname')
sql_delete(''' DELETE FROM data_table where drink_name = ?''', (dname) )
results = sql_query(''' SELECT * FROM data_table''')
msg = 'DELETE FROM data_table WHERE drink_name = ' + dname
return render_template('sqldatabase.html', results=results, msg=msg)
def sql_database():
if not session.get('logged_in'):
return render_template('login.html')
else:
from functions.sqlquery import sql_query
results = sql_query(''' SELECT * FROM dataset''')
msg = 'SELECT * FROM dataset'
return render_template('t_board.html', results=results, msg=msg)
def sql_datainsert():
from functions.sqlquery import sql_edit_insert, sql_query
if request.method == 'POST':
name = request.form['name']
person = request.form['person']
location = request.form['location']
sighted = request.form['sighted']
# TODO: SET THIS EQUAL TO SOMETHING MAYBE IT'LL WORK
sql_edit_insert(
''' INSERT OR REPLACE INTO Sightings (name,person,location,sighted) VALUES (?,?,?,?) ''',
(name, person, location, sighted))
flowers_table = sql_query('''SELECT * FROM Flowers''')
sightings_table = sql_query('''SELECT * FROM Sightings''')
return render_template('sqldatabase.html',
sightings_table=sightings_table,
flowers_table=flowers_table)
def sql_datadelete():
from functions.sqlquery import sql_delete, sql_query
if request.method == 'GET':
lname = request.args.get('lname')
fname = request.args.get('fname')
sql_delete(''' DELETE FROM data_table where first_name = ? and last_name = ?''', (fname,lname) )
results = sql_query(''' SELECT * FROM data_table''')
msg = 'DELETE FROM data_table WHERE first_name = ' + fname + ' and last_name = ' + lname
return render_template('sqldatabase.html', results=results, msg=msg)
def sql_editlink():
from functions.sqlquery import sql_query, sql_query2
if request.method == 'GET':
e_artist = request.args.get('e_artist')
e_title = request.args.get('e_artist')
e_results = sql_query2(''' SELECT * FROM record WHERE artist = ? and
title = ?''', (e_artist,e_title))
results = sql_query(''' SELECT * FROM record''')
return render_template('sqldatabase.html', eresults=eresults,
results=results)
def sql_datadelete():
from functions.sqlquery import sql_delete, sql_query
if request.method == 'GET':
lreply = request.args.get('lreply')
fquery = request.args.get('fquery')
sql_delete(''' DELETE FROM dataset where query = ? and reply = ?''',
(fquery, lreply))
results = sql_query(''' SELECT * FROM dataset''')
msg = 'DELETE FROM dataset WHERE query = ' + fquery + ' and reply = ' + lreply
return render_template('t_board.html', results=results, msg=msg)
def sql_editlink():
from functions.sqlquery import sql_query, sql_query2
if request.method == 'GET':
elreply = request.args.get('elreply')
efquery = request.args.get('efquery')
eresults = sql_query2(
''' SELECT * FROM dataset where query = ? and reply = ?''',
(efquery, elreply))
results = sql_query(''' SELECT * FROM dataset''')
return render_template('t_board.html', eresults=eresults, results=results)
def button_callback(channel):
print("Button was pushed!")
results = sql_query(''' SELECT * FROM data_table''')
for result in results:
x = threading.Thread(target=thread_servo, args=(result,))
threads.append(x)
x.start()
for result, thread in enumerate(threads):
thread.join()
print("Done")
def sql_datadelete():
from functions.sqlquery import sql_delete, sql_query
if request.method == 'GET':
s_artist = request.args.get('s_artist')
s_title = request.args.get('s_title')
sql_delete(''' DELETE FROM record WHERE artist = ? and title = ?''',
(s_artist,s_title))
results = sql_query(''' SELECT * FROM record''')
msg = 'DELETE FROM record WHERE artist = ' + s_artist + ' and title = ' +
s_title
return render_template('sqldatabase.html', results=results, msg=msg)
def predict_url_2():
from functions.sqlquery import sql_query_search, sql_query
if request.method == "POST":
about = request.form['about']
#print("******************USER ENTERED****************************" + sdg)
#print(sdg)
#sdg = '%'+sdg+' %'
#print("########################################## "+sdg+" ##########################")
#######THIS RESULTS BELOW ACTUALLY DOES NOT MATTER AT ALL--------PASS ANY QUERY, WHATEVER1111
results = sql_query(''' SELECT * FROM data_table_2 ''')
return render_template('prediction_page_2.html', results=results)
def sort_by_date():
from functions.sqlquery import sql_query_search, sql_query
#if request.method == "POST":
#entered_by = request.form['entered_by']
#print("******************USER ENTERED****************************" + sdg)
#print(sdg)
#sdg = '%'+sdg+' %'
#print("########################################## "+sdg+" ##########################")
#######THIS RESULTS BELOW ACTUALLY DOES NOT MATTER AT ALL--------PASS ANY QUERY, WHATEVER1111
results = sql_query(''' SELECT * FROM data_table_2 ORDER BY date ASC''')
return render_template('new_view.html', results=results)
def sql_datainsert():
from functions.sqlquery import sql_edit_insert, sql_query
if request.method == 'POST':
reply = request.form['reply']
query = request.form['query']
intent = request.form['intent']
sql_edit_insert(
''' INSERT INTO dataset (query,reply,intent) VALUES (?,?,?) ''',
(query, reply, intent))
results = sql_query(''' SELECT * FROM dataset''')
msg = 'INSERT INTO dataset (query,reply,intent) VALUES (' + query + ',' + reply + ',' + intent + ')'
return render_template('t_board.html', results=results, msg=msg)
def sql_datainsert():
from functions.sqlquery import sql_edit_insert, sql_query
if request.method == 'POST':
artist = request.form['artist']
title = request.form['title']
release_year = request.form['release_year']
sql_edit_insert(''' INSERT INTO record (artist,title,release_year)
VALUES (?,?,?) ''', (artist,title,release_year))
results = sql_query('''SELECT * FROM record''')
msg = 'INSERT INTO record (artist,title,release_year) VALUES
('+artist+','+title+','+release_year+')'
return render_template('sqldatabase.html', results=results, msg=msg)
def sql_dataedit():
from functions.sqlquery import sql_edit_insert, sql_query
if request.method == 'POST':
old_artist = request.form['old_artist']
old_title = request.form['old_title']
sql_edit_insert(''' UPDATE record SET artist = ?, title = ?,
release_year = ? WHERE artist = ? and title = ? ''',
(artist,title,old_artist,old_title))
results = sql_query(''' SELECT * FROM record''')
msg = 'UPDATE record SET artist = ' + artist + ', ' + title + ', ' +
release_year + ' WHERE artist = ' + old_artist + ' and title = ' + old_title
return render_template('sqldatabase.html', results=results, msg=msg)
def sql_datainsert():
from functions.sqlquery import sql_edit_insert, sql_query
if request.method == 'POST':
drink_name = request.form['drink_name']
spirit = request.form['spirit']
ingredient_1 = request.form['ingredient_1']
ingredient_2 = request.form['ingredient_2']
ingredient_3 = request.form['ingredient_3']
garnish = request.form['garnish']
sql_edit_insert(''' INSERT INTO data_table (drink_name,spirit,ingredient_1,ingredient_2,ingredient_3,garnish) VALUES (?,?,?,?,?,?) ''', (drink_name,spirit,ingredient_1,ingredient_2,ingredient_3,garnish) )
results = sql_query(''' SELECT * FROM data_table''')
msg = 'INSERT INTO data_table (drink_name,spirit,ingredient_1,ingredient_2,ingredient_3,garnish) VALUES ('+drink_name+','+spirit+','+ingredient_1+','+ingredient_2+','+ingredient_3+','+garnish+')'
return render_template('sqldatabase.html', results=results, msg=msg)
def sql_datainsert():
from functions.sqlquery import sql_edit_insert, sql_query
if request.method == 'POST':
last_name = request.form['last_name']
first_name = request.form['first_name']
address = request.form['address']
city = request.form['city']
state = request.form['state']
zip = request.form['zip']
sql_edit_insert(''' INSERT INTO data_table (first_name,last_name,address,city,state,zip) VALUES (?,?,?,?,?,?) ''', (first_name,last_name,address,city,state,zip) )
results = sql_query(''' SELECT * FROM data_table''')
msg = 'INSERT INTO data_table (first_name,last_name,address,city,state,zip) VALUES ('+first_name+','+last_name+','+address+','+city+','+state+','+zip+')'
return render_template('sqldatabase.html', results=results, msg=msg)
def GetCustNames():
cust_list = []
customers = sql_query(''' SELECT * FROM Customers''')
for result in customers:
result_dict = {
'ID': result['id'],
'Name': result['Name'],
'firebaseid': result['firebase_id'],
'IsSignificant': result['IsSignificant'],
'mobile number': result['mobile']
}
cust_list.append(result_dict)
return simplejson.dumps(cust_list)
def sql_dataedit():
if request.method == 'POST':
old_last_name = request.form['old_last_name']
old_first_name = request.form['old_first_name']
last_name = request.form['last_name']
first_name = request.form['first_name']
school = request.form['school']
degree = request.form['degree']
field = request.form['field']
year = request.form['year']
current_employer = request.form['current_employer']
job_title = request.form['job_title']
user_name = request.form['user_name']
contact = request.form['contact']
sql_edit_insert(''' UPDATE data_table set first_name=?,last_name=?,school=?,degree=?,field=?,year=?,current_employer=?,job_title=?,user_name=?,contact=? WHERE user_name=? ''', (first_name,last_name,school,degree,field,year,current_employer,job_title,user_name,contact,user_name))
print('SUCCESS!')
msg = 'Updated!'
results = sql_query(''' SELECT * FROM data_table''')
return redirect(url_for('profile', uname=user_name, aresults=results))
else:
results = sql_query(''' SELECT * FROM data_table''')
msg='Not Updated'
return render_template('update.html', uname=user_name, msg=msg)
def sql_dataedit():
from functions.sqlquery import sql_edit_insert, sql_query
if request.method == 'POST':
old_reply = request.form['old_reply']
old_query = request.form['old_query']
reply = request.form['reply']
query = request.form['query']
intent = request.form['intent']
sql_edit_insert(
''' UPDATE dataset set query=?,reply=?,intent=? WHERE query=? and reply=? ''',
(query, reply, intent, old_query, old_reply))
results = sql_query(''' SELECT * FROM dataset''')
msg = 'UPDATE dataset set query = ' + query + ', reply = ' + reply + ', intent = ' + intent + ' WHERE query = ' + old_query + ' and reply = ' + old_reply
return render_template('t_board.html', results=results, msg=msg)
def sql_dataedit():
from functions.sqlquery import sql_edit_insert, sql_query, sql_query3
if request.method == 'POST':
old_genus = request.form['old_genus']
old_species = request.form['old_species']
old_comname = request.form['old_comname']
genus = request.form['genus']
species = request.form['species']
comname = request.form['comname']
# eresults = sql_query2(''' SELECT * FROM Flowers WHERE genus = ? AND species = ? ''',(genus,species))
sql_edit_insert(
''' UPDATE Flowers set genus=?,species=?,comname=? WHERE genus=? and species=? and comname=?''',
(genus, species, comname, old_genus, old_species, old_comname))
flowers_table = sql_query('''SELECT * FROM Flowers''')
sightings_results = sql_query3(
'''SELECT * FROM SIGHTINGS WHERE NAME=? ORDER BY SIGHTED DESC''',
(comname, ), 10)
sightings_table = sql_query('''SELECT * FROM Sightings''')
return render_template('sqldatabase.html',
sightings_results=sightings_results,
sightings_table=sightings_table,
flowers_table=flowers_table)
def sql_editlink():
from functions.sqlquery import sql_query, sql_query2, sql_query3
if request.method == 'GET':
egenus = request.args['egenus']
especies = request.args['especies']
ecomname = request.args['ecomname']
eresults = sql_query2(
''' SELECT * FROM Flowers WHERE genus = ? AND species = ? ''',
(egenus, especies))
sightings_results = sql_query3(
'''SELECT * FROM SIGHTINGS WHERE NAME=? ORDER BY SIGHTED DESC''',
(ecomname, ), 10)
for each in sightings_results:
print(each['person'])
flowers_table = sql_query('''SELECT * FROM Flowers''')
sightings_table = sql_query('''SELECT * FROM Sightings''')
return render_template('sqldatabase.html',
eresults=eresults,
sightings_results=sightings_results,
sightings_table=sightings_table,
flowers_table=flowers_table)
def sql_dataedit():
from functions.sqlquery import sql_edit_insert, sql_query
if request.method == 'POST':
old_drink_name = request.form['old_drink_name']
spirit = request.form['spirit']
drink_name = request.form['drink_name']
ingredient_1 = request.form['ingredient_1']
ingredient_2 = request.form['ingredient_2']
ingredient_3 = request.form['ingredient_3']
garnish = request.form['garnish']
sql_edit_insert(''' UPDATE data_table set drink_name=?,spirit=?,ingredient_1=?,ingredient_2=?,ingredient_3=?,garnish=? WHERE drink_name=? and spirit=? ''', (drink_name,spirit,ingredient_1,ingredient_2,ingredient_3,garnish,old_drink_name) )
results = sql_query(''' SELECT * FROM data_table''')
msg = 'UPDATE data_table set drink_name = ' + drink_name + ', spirit = ' + spirit + ', ingredient_1 = ' + ingredient_1 + ', ingredient_2 = ' + ingredient_2 + ', ingredient_3 = ' + ingredient_3 + ', garnish = ' + garnish + ' WHERE drink_name = ' + old_drink_name
return render_template('sqldatabase.html', results=results, msg=msg)
