def admin_project_gallery():
if request.method == 'POST':
project = request.form['optradio']
action = request.form['action']
if 'update' in action:
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM {0}".format(project))
rows = cursor.fetchall()
return render_template('admin-projects-update-gallery-post.html',data=rows,project = project)
elif 'delete' in action:
with connection.cursor() as cursor:
cursor.execute("SELECT * FROM {0}".format(project))
rows = cursor.fetchall()
return render_template('admin-projects-delete-gallery-post.html',data=rows,project = project)
else:
with connection.cursor() as cursor:
#select from <project_name>_info table
cursor.execute("SELECT * FROM {0}_info".format(project))
rows = cursor.fetchall()
#select from <project_name>_back table
cursor.execute("SELECT * FROM {0}_back".format(project))
results = cursor.fetchall()
return render_template('admin-projects-update-content-post.html',data=rows,project = project, result=results)
else:
return render_template('admin-projects-gallery-update.html')
def get_user_by_id(id):
with connection.cursor() as cursor:
rows_count = cursor.execute(get_user_by_id_query(id))
if rows_count == 0:
return jsonify('No User with id - ' + id), 404
else:
return jsonify(cursor.fetchone()), 200
def admin_project_update_content_img():
id = request.form['id']
project = request.form['project']
try:
if 'file' not in request.files:
flash('No file part','danger')
return redirect(request.url)
file = request.files['file']
# if user does not select file, browser also
# submit a empty part without filename
if file.filename == '':
flash('No selected file','danger')
return redirect(request.url)
if file and allowed_file(file.filename):
filename = secure_filename(file.filename)
file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
with connection.cursor() as cursor:
update_img_name_query = "UPDATE {0}_back set img_name = %s WHERE id = %s".format(project)
cursor.execute(update_img_name_query,(filename,id),)
connection.commit()
flash('You have updated successfully','success')
return redirect(url_for('admin_project_gallery'))
except Exception as e:
print(str(e))
flash('You have not updated successfully','danger')
return redirect(url_for('admin_project_gallery'))
def getFromUser(email: str, column: str):
"""
Fetch some columns from user table.
Column names are given as ',' separated string.
Example: city, post
Parameters
----------
email:str
User's email address
column:str
Desired column name separated by ','
Returns
-------
records: list
A list of rows
"""
cursor = connection.cursor()
statement = f"select {column} from user where email='{email}'"
cursor.execute(statement)
records = cursor.fetchall()
cursor.close()
return records
def edit_asset(asset_id):
c = connection.cursor()
c.execute("SELECT name, uri, md5, start_date, end_date, duration, mimetype FROM assets WHERE asset_id=?", (asset_id,))
asset = c.fetchone()
name = asset[0]
uri = asset[1]
md5 = asset[2]
if asset[3]:
start_date = datestring.date_to_string(asset[3])
else:
start_date = None
if asset[4]:
end_date = datestring.date_to_string(asset[4])
else:
end_date = None
duration = asset[5]
mimetype = asset[6]
asset_info = {
"name": name,
"uri": uri,
"duration": duration,
"mimetype": mimetype,
"asset_id": asset_id,
"start_date": start_date,
"end_date": end_date
}
#return str(asset_info)
return template('edit_asset', asset_info=asset_info)
def generate_asset_list():
logging.info('Generating asset-list...')
c = connection.cursor()
c.execute("SELECT asset_id, name, uri, md5, start_date, end_date, duration, mimetype FROM assets ORDER BY name")
query = c.fetchall()
playlist = []
time_cur = get_current_time()
deadline = None
for asset in query:
asset_id = asset[0]
name = asset[1].encode('ascii', 'ignore')
uri = asset[2]
md5 = asset[3]
start_date = asset[4]
end_date = asset[5]
duration = asset[6]
mimetype = asset[7]
logging.debug('generate_asset_list: %s: start (%s) end (%s)' % (name, start_date, end_date))
if start_date and end_date:
if start_date < time_cur and end_date > time_cur:
playlist.append({"asset_id": asset_id, "name": name, "uri": uri, "duration": duration, "mimetype": mimetype})
if not deadline or end_date < deadline:
deadline = end_date
elif start_date >= time_cur and end_date > start_date:
if not deadline or start_date < deadline:
deadline = start_date
logging.debug('generate_asset_list deadline: %s' % deadline)
if settings.shuffle_playlist:
shuffle(playlist)
return (playlist, deadline)
def main():
if request.method == 'POST':
name = request.form['username']
email = request.form['email']
message = request.form['text-content']
if name and email:
msg = Message(subject='New Enquiry from first page',sender='*****@*****.**', recipients = ['*****@*****.**','*****@*****.**'])
msg.body = 'Hello Russell you c**t!\n\nSomeone has sent you an enquiry probably best to mail them back.\n\nTheir name is;\n\n' + name.title() + '\n\nTheir email is;\n\n' + email + '\n\nTheir message said this;\n\n' + message
mail.send(msg)
msg = Message(subject='Russell Hewes - Enquiry',sender='*****@*****.**', recipients = [email])
msg.body = 'Dear ' + name.title() + ',\n\nThank you for your enquiry.\n\nOne of our members of staff will be in touch shortly.\n\nKind Regards,\n\nRussell Hewes'
mail.send(msg)
flash('Thank you for your enquiry, someone from our team will be in contact with you very shortly','success')
return redirect(url_for('main'))
else:
flash('You have not filled in all mandatory fields, please try again','danger')
return redirect(url_for('main',_anchor='contact'))
else:
#use pymysql cursor
with connection.cursor() as cursor:
text_content_query = "SELECT * FROM `home_page`"
cursor.execute(text_content_query,(),)
result = cursor.fetchall()
return render_template('index.html',data=result)
def save_to_db(self):
"""
Save all player data to MySQL database.
If a record with player is not exists, it is inserted.
If method knows _id of player, it will try to update existing
db record.
:return: None
"""
insert_query = """INSERT INTO player (name, email, password, created, updated) VALUES
(%(name)s, %(email)s, %(password)s, now(), now())"""
update_query = """UPDATE player SET name=%(name)s, email=%(email)s, password=%(password)s,
updated=now() WHERE id=%(_id)s"""
sql_data = {
"name": self.name,
"email": self.email,
"password": self.password,
}
cursor = connection.cursor()
if self._id is None:
try:
cursor.execute(insert_query, sql_data)
self._id = cursor.lastrowid
except ModelIntegrityError:
print(
"DEBUG: an integrity error occured when player inserting, it's OK"
)
pass
else:
sql_data["_id"] = self._id
cursor.execute(update_query, sql_data)
def updateAccount(accountNumber: int, payload: str):
"""
Updates the columns with value.
The columns to be updated and the value is passed as dictionary.
Parameters
----------
accountNumber: int
The account to be updated
payload: str
a ',' seperated string where column and values are seperated by '='.
For example, "balance=3000, status='inactive'"
"""
statement = "update account set " + payload + f" where account_number = {accountNumber}"
cursor = connection.cursor()
try:
cursor.execute(statement)
cursor.commit()
cursor.close()
except mysql.Error as err:
print(err)
raise
else:
cursor.close()
def admin_home_img():
if request.method == 'POST':
caption = request.form['caption']
# check if the post request has the file part
if 'file' not in request.files:
flash('No file part','danger')
return redirect(request.url)
file = request.files['file']
# if user does not select file, browser also
# submit a empty part without filename
if file.filename == '':
flash('No selected file','danger')
return redirect(request.url)
if file and allowed_file(file.filename):
filename = secure_filename(file.filename)
file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
with connection.cursor() as cursor:
upload_img_name_query = "INSERT INTO home_page_img (img_name,img_caption) VALUES (%s,%s)"
cursor.execute(upload_img_name_query,(filename,caption),)
connection.commit()
flash('New image/text uploaded','success')
return redirect(request.url)
return render_template('admin-home.html')
def save_to_db(self):
"""
Save all player data to MySQL database.
If a record with player is not exists, it is inserted.
If method knows _id of player, it will try to update existing
db record.
:return: None
"""
insert_query = """INSERT INTO player (name, email, password, created, updated) VALUES
(%(name)s, %(email)s, %(password)s, now(), now())"""
update_query = """UPDATE player SET name=%(name)s, email=%(email)s, password=%(password)s,
updated=now() WHERE id=%(_id)s"""
sql_data = {
"name": self.name,
"email": self.email,
"password": self.password,
}
cursor = connection.cursor()
if self._id is None:
try:
cursor.execute(insert_query, sql_data)
self._id = cursor.lastrowid
except ModelIntegrityError:
print("DEBUG: an integrity error occured when player inserting, it's OK")
pass
else:
sql_data["_id"] = self._id
cursor.execute(update_query, sql_data)
def getAccountById(accountNumber, column: str = "*"):
"""
Fetch an account from account table by id.
Parameters
----------
accountNumber: int
Account id.
"""
cursor = connection.cursor(dictionary=True)
if column == None:
column = "account_number, account_type, owner_id, open_date, status, balance"
join = ""
if "account_type" in column:
column = column.replace("account_type",
" t.description as account_type ")
join = " inner join accounttype as t on account.account_type = t.id "
if "owner_id" in column:
column = column.replace("owner_id", " o.fname as owner_id ")
join = join + "inner join user as o on o.id = account.owner_id "
statement = "select " + column + " from account " + join + f"where account_number ={accountNumber}"
cursor.execute(statement)
account = cursor.fetchall()
cursor.close()
return account
def delete_user_by_id(id):
with connection.cursor() as cursor:
rows_count = cursor.execute(delete_user_by_id_query(id))
if rows_count == 0:
return jsonify('No User with id - ' + id), 404
else:
return jsonify('User deleted with id - ' + id), 200
def get(submit_id: int) -> tuple:
"""
Get source code and problem text of one submission.
Args:
submit_id (int): submit ID
Returns:
tuple: decoded source code and problem text
"""
query = "SELECT submission.sourcecode, tsoal.soal " \
"FROM submission, tsoal " \
"WHERE submission.probid=tsoal.probid " \
f"AND submission.submitid={submit_id}"
try:
cursor = connection.cursor()
cursor.execute(query)
sourcecode, soal = cursor.fetchone()
sourcecode = sourcecode.decode('ASCII')
except mysql.connector.Error as db_error:
print(f"error from database:\n{db_error}")
exit(1)
except UnicodeDecodeError as decode_error:
print(f"error decoding code blob:\n{decode_error}")
exit(2)
else:
return sourcecode, soal
def getAccounts(owner_id, column: str = None):
"""
Fetch an account from account table.
Parameters
----------
owner_id: str
Owner id.
column: str (optional) (default= "*")
The column name to be fetched. This is a ',' separated string.
For example: "account_number, account_type, balance"
If not provided all the columns are returned.
"""
cursor = connection.cursor(dictionary=True)
# replace "account_type" and "owner_id" for better readability
if column == None:
column = "account_number, account_type, owner_id, open_date, status, balance"
join = ""
if "account_type" in column:
column = column.replace("account_type",
" t.description as account_type ")
join = " inner join accounttype as t on account.account_type = t.id "
if "owner_id" in column:
column = column.replace("owner_id", " o.fname as owner_id ")
join = join + "inner join user as o on o.id = account.owner_id "
statement = "select " + column + " from account " + join + f"where owner_id ={owner_id}"
cursor.execute(statement)
accounts = cursor.fetchall()
cursor.close()
return accounts
def find_all_words():
cursor = connection.cursor()
sql_query = cursor.execute("SELECT Expression FROM Dictionary")
results = cursor.fetchall()
list_of_words = []
for result in results:
list_of_words.append(result[0])
return list_of_words
def admin_home_update():
if request.method == 'POST':
id = request.form['id']
caption = request.form['caption']
try:
if 'file' not in request.files:
flash('No file part','danger')
return redirect(request.url)
file = request.files['file']
# if user does not select file, browser also
# submit a empty part without filename
if file.filename == '':
flash('No selected file','danger')
return redirect(request.url)
if file and allowed_file(file.filename):
filename = secure_filename(file.filename)
file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
with connection.cursor() as cursor:
update_img_name_query = "UPDATE home_page_img set img_name = %s, img_caption = %s WHERE id = %s"
cursor.execute(update_img_name_query,(filename,caption,id),)
connection.commit()
flash('You have updated successfully','success')
return redirect(url_for('admin_home_update'))
except Exception as e:
print(str(e))
flash('You have not updated successfully','danger')
return redirect(url_for('admin_home_update'))
else:
with connection.cursor() as cursor:
select_home_img = 'SELECT * FROM home_page_img'
cursor.execute(select_home_img,(),)
rows = cursor.fetchall()
#select home_page table data
select_home_query = "SELECT * FROM home_page"
cursor.execute(select_home_query,(),)
results = cursor.fetchall()
return render_template('admin-home-update.html',data=rows,result=results)
def home_images():
with connection.cursor() as cursor:
img_name_query = "SELECT * FROM `home_page_img`"
cursor.execute(img_name_query,(),)
picture_rows = cursor.fetchall()
return jsonify(picture_rows)
def update_user(id):
with connection.cursor() as cursor:
req_data = request.get_json()
if req_data is None:
return jsonify('Request is not valid'), 400
else:
rows_count = cursor.execute(get_user_by_id_query(id))
if rows_count == 0:
return jsonify('No User with id - ' + id), 404
else:
data = cursor.fetchone()
first_name = data['first_name']
last_name = data['last_name']
company_name = data['company_name']
age = data['age']
city = data['city']
state = data['state']
zip = data['zip']
email = data['email']
web = data['web']
if 'first_name' in req_data:
first_name = req_data['first_name']
if 'last_name' in req_data:
last_name = req_data['last_name']
if 'company_name' in req_data:
company_name = req_data['company_name']
if 'age' in req_data:
age = req_data['age']
if 'city' in req_data:
city = req_data['city']
if 'state' in req_data:
state = req_data['state']
if 'zip' in req_data:
zip = req_data['zip']
if 'email' in req_data:
email = req_data['email']
if 'web' in req_data:
web = req_data['web']
rows_count = cursor.execute(
update_user_by_id(id, first_name, last_name, company_name,
age, city, state, zip, email, web))
if rows_count == 0:
return jsonify('nothing to update for User with id - ' +
id), 404
else:
return jsonify('Successfully Updated User with id - ' +
id), 200
def insert_asset(asset):
c = connection.cursor()
c.execute(
"INSERT INTO assets (%s) VALUES (%s)" % (", ".join(asset.keys()), ",".join(["?"] * len(asset.keys()))),
asset.values(),
)
connection.commit()
asset.update({"is_active": is_active(asset)})
return asset
def update_asset(asset_id, asset):
del asset["asset_id"]
c = connection.cursor()
query = "UPDATE assets SET %s=? WHERE asset_id=?" % "=?, ".join(asset.keys())
c.execute(query, asset.values() + [asset_id])
connection.commit()
asset.update({"asset_id": asset_id})
asset.update({"is_active": is_active(asset)})
return asset
def home():
#use pymysql cursor
with connection.cursor() as cursor:
#select query
sql = "SELECT * FROM `home_page`"
cursor.execute(sql,(),)
result = cursor.fetchall()
return render_template('home.html',data=result)
def createDocument():
try:
with connection.cursor() as cursor:
sql = "INSERT INTO document (text , employeeID) VALUES (%s, %s)"
cursor.execute(sql , (request.form["text"] , request.form["employeeId"]))
connection.commit()
return jsonify({'isOK':True})
except:
return jsonify({'isOK':False,'error':"someting went wrong"})
def get_user_by_session_id(sessionid: str) -> User:
"""Return User object using session_id"""
cursor = connection.cursor()
# Get user related with session with sessionid
cursor.execute(
'SELECT * FROM users WHERE id = '
'(SELECT user_id FROM sessions WHERE session_id = ?);', (sessionid, ))
user_data = cursor.fetchone()
cursor.close()
return User(user_data)
def delete_from_db(self,id):
sql_data = {
'id': id,
}
sql_query = 'DELETE FROM moderator WHERE id=%(id)s'
with connection:
cur = connection.cursor()
cur.execute(sql_query, sql_data)
print("Moderator with id={} has been deleted".format(id))
return True
def delete_from_db(self, player_id):
sql_data = {
'player_id': player_id,
}
sql_query = 'DELETE FROM session WHERE player_id=%(player_id)s'
with connection:
cur = connection.cursor()
cur.execute(sql_query, sql_data)
print("Sessions with player_id={} has been deleted".format(player_id))
return True
def delete_from_db(self):
"""
Method for deleting player from DB. Needs correct _id field.
Perhaps player should be loaded before using this method.
:return: None
"""
cursor = connection.cursor()
delete_query = "DELETE FROM player WHERE id=%(_id)s"
sql_data = {"_id": self._id}
cursor.execute(delete_query, sql_data)
def getAllTransactions(accountNumber: int) -> [dict]:
cursor = connection.cursor(dictionary=True)
try:
statement = f"select * from transactions where account_number = {accountNumber}"
cursor.execute(statement)
transactions = cursor.fetchall()
return transactions
except mysql.Error as err:
print("Transactions can't be retrieved. Try again later")
return None
finally:
cursor.close()
def add_user():
req_data = request.get_json()
# add validations for the request and fields, can have separate validator class for validation
if req_data is None:
return jsonify('Request is not valid'), 400
else:
first_name = ""
last_name = ""
company_name = ""
age = None
city = ""
state = ""
zip = None
email = ""
web = ""
if 'first_name' in req_data:
first_name = req_data['first_name']
if 'last_name' in req_data:
last_name = req_data['last_name']
if 'company_name' in req_data:
company_name = req_data['company_name']
if 'age' in req_data:
age = req_data['age']
if 'city' in req_data:
city = req_data['city']
if 'state' in req_data:
state = req_data['state']
if 'zip' in req_data:
zip = req_data['zip']
if 'email' in req_data:
email = req_data['email']
if 'web' in req_data:
web = req_data['web']
with connection.cursor() as cursor:
rows_count = cursor.execute(
insert_user(first_name, last_name, company_name, age, city,
state, zip, email, web))
if rows_count == 0:
return jsonify('Error occured while saving user'), 404
else:
return jsonify('User added successfully'), 201
def delete_from_db(self):
"""
Method for deleting player from DB. Needs correct _id field.
Perhaps player should be loaded before using this method.
:return: None
"""
cursor = connection.cursor()
delete_query = "DELETE FROM player WHERE id=%(_id)s"
sql_data = {
"_id": self._id
}
cursor.execute(delete_query, sql_data)
def fetch_asset(asset_id, keys=FIELDS):
c = connection.cursor()
c.execute("SELECT %s FROM assets WHERE asset_id=?" % ", ".join(keys), (asset_id,))
assets = []
for asset in c.fetchall():
dictionary = {}
for i in range(len(keys)):
dictionary[keys[i]] = asset[i]
assets.append(dictionary)
if len(assets):
asset = assets[0]
asset.update({"is_active": is_active(asset)})
return asset
def getDocumnets():
try:
with connection.cursor() as cursor:
sql = "select text, office.name officeName , employee.name employeeName , manager.name managerName"
sql += " from document "
sql += "join employee on document.employeeID = employee.employeeID "
sql += "join manager on employee.managerID = manager.managerID "
sql += "join office on manager.officeID = office.officeID"
cursor.execute(sql)
result = cursor.fetchall()
return jsonify(result)
except:
return jsonify({error:"someting went wrong"})
def withdrawFromAccount(transaction: dict) -> None:
cursor = connection.cursor()
try:
createTransaction(transaction, cursor)
statement = f"""update account set balance = balance - {transaction["txn_amount"]} where account_number = {transaction["account_number"]}"""
cursor.execute(statement)
connection.commit()
cursor.close()
except mysql.Error as err:
print(err)
raise
finally:
cursor.close()
def delete_asset(asset_id):
c = connection.cursor()
c.execute("DELETE FROM assets WHERE asset_id=?", (asset_id,))
try:
connection.commit()
header = "Success!"
message = "Deleted asset."
return template('message', header=header, message=message)
except:
header = "Ops!"
message = "Failed to delete asset."
return template('message', header=header, message=message)
def initiate_db():
# Create config dir if it doesn't exist
if not path.isdir(settings.configdir):
makedirs(settings.configdir)
c = connection.cursor()
# Check if the asset-table exist. If it doesn't, create it.
c.execute("SELECT name FROM sqlite_master WHERE type='table' AND name='assets'")
asset_table = c.fetchone()
if not asset_table:
c.execute("CREATE TABLE assets (asset_id TEXT, name TEXT, uri TEXT, md5 TEXT, start_date TIMESTAMP, end_date TIMESTAMP, duration TEXT, mimetype TEXT)")
return "Initiated database."
def fetch_assets(keys=FIELDS, order_by="name"):
"""Fetches all assets from the database and returns their
data as a list of dictionaries corresponding to each asset."""
c = connection.cursor()
c.execute("SELECT %s FROM assets ORDER BY %s" % (", ".join(keys), order_by))
assets = []
for asset in c.fetchall():
dictionary = {}
for i in range(len(keys)):
dictionary[keys[i]] = asset[i]
assets.append(dictionary)
return assets
def admin_home_delete():
if request.method == 'POST':
check_boxes = request.form.getlist('check')
try:
with connection.cursor() as cursor:
for x in check_boxes:
delete_query = "DELETE FROM home_page_img WHERE id = %s"
cursor.execute(delete_query,(x),)
connection.commit()
flash('You have deleted item(s)', 'success')
return redirect(url_for('admin_home_delete'))
except Exception as e:
flash('You have not deleted item','danger')
return redirect(url_for('admin_home_delete'))
else:
with connection.cursor() as cursor:
select_home_img = 'SELECT * FROM home_page_img'
cursor.execute(select_home_img,(),)
rows = cursor.fetchall()
return render_template('admin-home-delete.html',data=rows)
def admin_home_text_update():
caption = request.form['caption']
try:
with connection.cursor() as cursor:
update_home_page_text_query = "UPDATE home_page SET content = %s WHERE id = %s"
cursor.execute(update_home_page_text_query,(caption,1))
flash('Home page text updated','success')
return redirect(url_for('admin_home_update'))
except Exception as e:
print()
print(str(e))
print()
flash('Home page text not updated','danger')
return redirect(url_for('admin_home_update'))
def get_users():
# read request arguments
name = request.args.get('name')
sort_by = request.args.get('sort_by')
limit = request.args.get('limit')
page = request.args.get('page')
with connection.cursor() as cursor:
rows_count = cursor.execute(
get_all_user_query(name, sort_by, limit, page))
if rows_count == 0:
return jsonify('No Users'), 404
else:
return jsonify(cursor.fetchall()), 200
def schedule_asset():
c = connection.cursor()
assets = []
c.execute("SELECT name, asset_id FROM assets ORDER BY name")
query = c.fetchall()
for asset in query:
name = asset[0]
asset_id = asset[1]
assets.append({
'name': name,
'asset_id': asset_id,
})
return template('schedule_asset', assets=assets)
def update_asset():
c = connection.cursor()
if (request.POST.get('asset_id', '').strip() and
request.POST.get('name', '').strip() and
request.POST.get('uri', '').strip() and
request.POST.get('mimetype', '').strip()
):
asset_id = request.POST.get('asset_id', '').strip()
name = request.POST.get('name', '').decode('UTF-8')
uri = request.POST.get('uri', '').strip()
mimetype = request.POST.get('mimetype', '').strip()
if not validate_uri(uri) and asset_folder not in uri:
header = "Ops!"
message = "Invalid URL. Failed to update asset."
return template('message', header=header, message=message)
try:
duration = request.POST.get('duration', '').strip()
except:
duration = None
try:
input_start = request.POST.get('start', '')
start_date = datetime.strptime(input_start, '%Y-%m-%d @ %H:%M')
except:
start_date = None
try:
input_end = request.POST.get('end', '').strip()
end_date = datetime.strptime(input_end, '%Y-%m-%d @ %H:%M')
except:
end_date = None
c.execute("UPDATE assets SET start_date=?, end_date=?, duration=?, name=?, uri=?, duration=?, mimetype=? WHERE asset_id=?", (start_date, end_date, duration, name, uri, duration, mimetype, asset_id))
connection.commit()
header = "Yes!"
message = "Successfully updated asset."
return template('message', header=header, message=message)
else:
header = "Ops!"
message = "Failed to update asset."
return template('message', header=header, message=message)
def create_user(params: Iterable) -> tuple:
"""Create new user entry and session entry related with it
Returns
-------
Tuple with user id and if of session related with it
"""
cursor = connection.cursor()
cursor.execute('INSERT INTO users (name) VALUES (?)', params)
cursor.execute('SELECT id FROM users WHERE name = ?', params)
user_id = cursor.fetchone()[0]
session_id = generate_session_id()
cursor.execute('INSERT INTO sessions VALUES (?, ?)', (session_id, user_id))
cursor.close()
connection.commit()
return (user_id, session_id)
def load_from_db(self, player_id):
sql_data = {
'player_id': player_id,
}
sql_query = 'SELECT starttime, stoptime FROM session WHERE player_id=%(player_id)s'
with connection:
cur = connection.cursor()
cur.execute(sql_query, sql_data)
for row in cur.fetchall():
if row:
print "row is {}".format(row)
self.starttime = row[0]
self.stoptime = row[1]
else:
raise MySQLdb.IntegrityError("Session not found. Check input 'id'=={} parameter!".format(id))
return (self.starttime, self.stoptime)
def admin_project_add():
if request.method == 'POST':
caption = request.form['caption']
project = request.form['optradio']
try:
if project:
if 'file' not in request.files:
flash('No file part','danger')
return redirect(request.url)
file = request.files['file']
# if user does not select file, browser also
# submit a empty part without filename
if file.filename == '':
flash('No selected file','danger')
return redirect(request.url)
if file and allowed_file(file.filename):
filename = secure_filename(file.filename)
file.save(os.path.join(app.config['UPLOAD_FOLDER'], filename))
with connection.cursor() as cursor:
if caption:
insert_into_img_query = "INSERT INTO {0} (img_name,img_text) VALUES (%s,%s)".format(project)
cursor.execute(insert_into_img_query,(filename,caption),)
connection.commit()
flash('You have added img name/caption to ' + project + ' successfully','success')
else:
insert_into_img_query = "INSERT INTO {0} (img_name) VALUES (%s)".format(project)
cursor.execute(insert_into_img_query,(filename),)
connection.commit()
flash('You have added img name to ' + project + ' successfully','success')
return redirect(url_for('admin_project_add'))
else:
flash('You have not selected type','danger')
return redirect(url_for('admin_project_add'))
except Exception as e:
print(str(e))
flash('You have not updated successfully','danger')
return redirect(url_for('admin_project_add'))
else:
return render_template('admin-projects-add.html')
def admin_project_update_content_post():
caption = request.form['caption']
project = request.form['project']
id = request.form['id']
try:
with connection.cursor() as cursor:
update_project_info = "UPDATE {0}_info SET brief = %s WHERE id = %s".format(project)
cursor.execute(update_project_info,(caption,id),)
connection.commit()
flash('You have updated ' + project + ' text','success')
return redirect(url_for('admin_project_gallery'))
except Exception as e:
print()
print(str(e))
print()
flash('You have not updated ' + project + ' text','danger')
return redirect(url_for('admin_project_gallery'))
def save_to_db(self, id):
sql_data = {
'id': id,
'name': self.name,
'title': self.title,
}
sql_query = 'INSERT INTO moderator (id, name, title) VALUES (%(id)s, %(name)s, %(title)s)'
with connection:
cur = connection.cursor()
try:
cur.execute(sql_query, sql_data)
print "New record was inserted"
except MySQLdb.IntegrityError:
sql_query = 'UPDATE moderator SET name=%(name)s, title=%(title)s WHERE id=%(id)s'
cur.execute(sql_query, sql_data)
print "Record was updated!"
return True
def save_to_db(self, player_id):
sql_data = {
'starttime': str(self.starttime),
'stoptime': str(self.stoptime),
'player_id': player_id,
}
sql_query = 'INSERT INTO session (starttime, stoptime, player_id) VALUES (%(starttime)s, %(stoptime)s, %(player_id)s)'
with connection:
cur = connection.cursor()
try:
cur.execute(sql_query, sql_data)
print "New Session was inserted"
except MySQLdb.IntegrityError:
sql_query = 'UPDATE session SET starttime=%(starttime)s, stoptime=%(stoptime)s WHERE player_id=%(player_id)s'
cur.execute(sql_query, sql_data)
print "Session was updated!"
return True
def load_from_db(self, id):
sql_data = {
'id': id,
'name': self.name,
'title': self.title,
}
sql_query = 'SELECT name, title FROM moderator WHERE id=%(id)s'
with connection:
cur = connection.cursor()
cur.execute(sql_query, sql_data)
for row in cur.fetchall():
if row:
print "row is {}".format(row)
self.name = row[0]
self.title = row[1]
else:
raise MySQLdb.IntegrityError("Record not found. Check input 'id'=={} parameter!".format(id))
return (self.name, self.title)
def delete_asset(asset_id):
c = connection.cursor()
c.execute("DELETE FROM assets WHERE asset_id=?", (asset_id,))
try:
connection.commit()
# If file exist on disk, delete it.
local_uri = path.join(asset_folder, asset_id)
if path.isfile(local_uri):
remove_file(local_uri)
header = "Success!"
message = "Deleted asset."
return template('message', header=header, message=message)
except:
header = "Ops!"
message = "Failed to delete asset."
return template('message', header=header, message=message)
def get_playlist():
c = connection.cursor()
c.execute("SELECT * FROM assets ORDER BY name")
assets = c.fetchall()
playlist = []
for asset in assets:
# Match variables with database
asset_id = asset[0]
name = asset[1]
uri = asset[2] # Path in local database
input_start_date = asset[4]
input_end_date = asset[5]
try:
start_date = datestring.date_to_string(asset[4])
except:
start_date = None
try:
end_date = datestring.date_to_string(asset[5])
except:
end_date = None
if (start_date and end_date) and (input_start_date < get_current_time() and input_end_date > get_current_time()):
duration = asset[6]
mimetype = asset[7]
playlistitem = {
"name": name,
"uri": uri,
"duration": duration,
"mimetype": mimetype,
"asset_id": asset_id,
"start_date": start_date,
"end_date": end_date
}
playlist.append(playlistitem)
return playlist
def load_from_db(self, id):
self.session.load_from_db(player_id=id)
sql_data = {
'id': id,
}
sql_query = 'SELECT name, score, level, money FROM player WHERE id=%(id)s'
with connection:
cur = connection.cursor()
cur.execute(sql_query, sql_data)
for row in cur.fetchall():
if row:
print "row is {}".format(row)
self.name = row[0]
self.score = int(row[1])
self.level = int(row[2])
self.money = int(row[3])
else:
raise MySQLdb.IntegrityError("Record not found. Check input 'id'=={} parameter!".format(id))
return (self.name, self.score, self.level, self.money)
def load_from_db(self, email):
"""
Method for loading player instance from DB row
:param email: Player email for loading all info from db
:return: None
"""
cursor = connection.cursor()
sql_cmd = """SELECT id, name, email, password, created, updated FROM
player WHERE email=%(email)s"""
sql_args = {
"email": email
}
cursor.execute(sql_cmd, sql_args)
db_row = cursor.fetchone()
self._id = db_row[0]
self.name = db_row[1]
self.email = db_row[2]
self.password = db_row[3]
self.created = db_row[4]
self.updated = db_row[5]
def save_to_db(self, id):
sql_data = {
'id': id,
'name': self.name,
'score': self.score,
'level': self.level,
'money': self.money,
}
sql_query = 'INSERT INTO player (id, name, score, level, money) VALUES (%(id)s, %(name)s, %(score)s, %(level)s, %(money)s)'
with connection:
cur = connection.cursor()
try:
cur.execute(sql_query, sql_data)
print "New record was inserted"
except MySQLdb.IntegrityError:
sql_query = 'UPDATE player SET name=%(name)s, score=%(score)s, level=%(level)s, money=%(money)s WHERE id=%(id)s'
cur.execute(sql_query, sql_data)
print "Record was updated!"
self.session.save_to_db(player_id=id)
return True
def fetch_assets(keys=None, order_by="name"):
"""Fetches all assets from the database and returns their
data as returned from the SQLite3 cursor."""
c = connection.cursor()
if keys is None:
keys = [
"asset_id", "name", "uri", "start_date",
"end_date", "duration", "mimetype"
]
c.execute("SELECT %s FROM assets ORDER BY %s" % (", ".join(keys), order_by))
raw_assets = c.fetchall()
assets = []
for asset in raw_assets:
dictionary = {}
for i in range(len(keys)):
dictionary[keys[i]] = asset[i]
assets.append(dictionary)
return assets
def process_schedule():
c = connection.cursor()
if (request.POST.get('asset', '').strip() and
request.POST.get('start', '').strip() and
request.POST.get('end', '').strip()
):
asset_id = request.POST.get('asset', '').strip()
input_start = request.POST.get('start', '').strip()
input_end = request.POST.get('end', '').strip()
start_date = datetime.strptime(input_start, '%Y-%m-%d @ %H:%M')
end_date = datetime.strptime(input_end, '%Y-%m-%d @ %H:%M')
query = c.execute("SELECT mimetype FROM assets WHERE asset_id=?", (asset_id,))
asset_mimetype = c.fetchone()
if "image" or "web" in asset_mimetype:
try:
duration = request.POST.get('duration', '').strip()
except:
header = "Ops!"
message = "Duration missing. This is required for images and web-pages."
return template('message', header=header, message=message)
else:
duration = "N/A"
c.execute("UPDATE assets SET start_date=?, end_date=?, duration=? WHERE asset_id=?", (start_date, end_date, duration, asset_id))
connection.commit()
header = "Yes!"
message = "Successfully scheduled asset."
return template('message', header=header, message=message)
else:
header = "Ops!"
message = "Failed to process schedule."
return template('message', header=header, message=message)
def get_assets():
c = connection.cursor()
c.execute("SELECT asset_id, name, uri, start_date, end_date, duration, mimetype FROM assets ORDER BY name")
assets = c.fetchall()
playlist = []
for asset in assets:
# Match variables with database
asset_id = asset[0]
name = asset[1]
uri = asset[2] # Path in local database
try:
start_date = datestring.date_to_string(asset[3])
except:
start_date = ""
try:
end_date = datestring.date_to_string(asset[4])
except:
end_date = ""
duration = asset[5]
mimetype = asset[6]
playlistitem = {
"name": name,
"uri": uri,
"duration": duration,
"mimetype": mimetype,
"asset_id": asset_id,
"start_date": start_date,
"end_date": end_date
}
playlist.append(playlistitem)
return playlist
def delete_asset(asset_id):
c = connection.cursor()
c.execute("DELETE FROM assets WHERE asset_id=?", (asset_id,))
connection.commit()
def process_asset():
c = connection.cursor()
if (request.POST.get('name', '').strip() and
(request.POST.get('uri', '').strip() or request.files.file_upload.file) and
request.POST.get('mimetype', '').strip()
):
name = request.POST.get('name', '').decode('UTF-8')
mimetype = request.POST.get('mimetype', '').strip()
try:
uri = request.POST.get('uri', '').strip()
except:
uri = False
try:
file_upload = request.files.file_upload.file
except:
file_upload = False
# Make sure it is a valid combination
if (file_upload and 'web' in mimetype):
header = "Ops!"
message = "Invalid combination. Can't upload web resource."
return template('message', header=header, message=message)
if (uri and file_upload):
header = "Ops!"
message = "Invalid combination. Can't select both URI and a file."
return template('message', header=header, message=message)
if uri:
if not validate_uri(uri):
header = "Ops!"
message = "Invalid URL. Failed to add asset."
return template('message', header=header, message=message)
if "image" in mimetype:
file = req_get(uri, allow_redirects=True)
else:
file = req_head(uri, allow_redirects=True)
# Only proceed if fetch was successful.
if file.status_code == 200:
asset_id = md5(name + uri).hexdigest()
strict_uri = file.url
if "image" in mimetype:
resolution = Image.open(StringIO(file.content)).size
else:
resolution = "N/A"
if "video" in mimetype:
duration = "N/A"
else:
header = "Ops!"
message = "Unable to fetch file."
return template('message', header=header, message=message)
if file_upload:
asset_id = md5(file_upload.read()).hexdigest()
local_uri = path.join(asset_folder, asset_id)
f = open(local_uri, 'w')
asset_file_input = file_upload.read()
f.write(asset_file_input)
f.close()
uri = local_uri
start_date = ""
end_date = ""
duration = ""
c.execute("INSERT INTO assets (asset_id, name, uri, start_date, end_date, duration, mimetype) VALUES (?,?,?,?,?,?,?)", (asset_id, name, uri, start_date, end_date, duration, mimetype))
connection.commit()
header = "Yay!"
message = "Added asset (" + asset_id + ") to the database."
return template('message', header=header, message=message)
else:
header = "Ops!"
message = "Invalid input."
return template('message', header=header, message=message)
