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)