def execute(self, statement, args=None): """Execute an SQL Statement :param statement: The SQL statement to be executed against the database. :type statement: str :param args: The list of arguments for the SQL statement. :type args: list :raises BadRequest: Raises a database error if something goes wrong. :return: If the SQL statement is a SELECT then it returns a list of results; If the SQL statement is an INSERT then the id of the new entry is returned. :rtype: list or int """ try: name = (statement[0:statement.index(" ")]).lower() except Exception as e: raise BadRequest(path=request.path,message=str(e), error="Please revise the SQL Query", operation="Query Processing", api="database") try: if ("select" in name): return self.__select(statement, args) if ("insert" in name): return self.__insert(statement, args) if ("update" in name): return self.__update(statement, args) if ("delete" in name): return self.__delete(statement, args) if ("create" in name or "alter" in name or "drop" in name or "truncate" in name): return self.__table_structure(statement) except Exception as e: raise BadRequest(path=request.path,message=str(e), operation="Query Processing", api="database")
def __select(self, statement, args=None): """Data Manipulation - SELECT :param statement: The SELECT statement to be executed against the database. :type statement: str :param args: SQL statement arguments, defaults to None. :type args: list, optional :raises BadRequest: Raises an exception if a database related error is catched. :return: For n>1 results, returns a list of dics containing the results of the provided SQL statement. Otherwise, returns a dic containing the result of the provided SQL statement. :rtype: list of dics or dic """ try: # Returns a list of dics as the result of a sql statement cursor = self.connection.cursor() # Execute the SQL Select Statement. if (args is not None): cursor.execute(statement, args) else: cursor.execute(statement) res = cursor.fetchall() cursor.close() froggy.gadgets.fprint("SQL statement executed :" + str(statement)) froggy.gadgets.fprint("SQL statement arguments :" + str(args)) # If there is only one result, lets just return an dic instead of a list of dics if (len(res) == 1): res = res[0] # 'You complete me.' return(res) except Exception as e: raise BadRequest(path=request.path,message=str(e), error="Database error", operation="SELECT", api="database")
def __insert(self, statement, args): """Data Manipulation - INSERT :param statement: The INSERT statement to be executed against the database. :type statement: str :param args: SQL statement arguments. :type args: list :raises BadRequest: Raises an exception if a database related error is catched. :return: Returns the id of the new entry. """ try: cursor = self.connection.cursor() # Execute the SQL statement with 'args' cursor.execute(statement, args) # Get the last created id after executing the SQL statement. # This is useful to get primary key values that were set as auto-increment. created_id = -1 if (self.type == Type.Mysql): created_id = self.connection.insert_id() if (self.type == Type.Sqlite3): created_id = cursor.lastrowid # Save (commit) the changes and close the cursor self.connection.commit() cursor.close() froggy.gadgets.fprint("SQL statement executed :" + statement) froggy.gadgets.fprint("SQL statement arguments :" + str(args)) froggy.gadgets.fprint("Last inserted ID :" + str(created_id)) # 'You make me want to be a better man.' return({"id:": created_id}) except Exception as e: raise BadRequest(path=request.path,message=str(e), error="Database error", operation="INSERT", api="database")
def get(upload_directory, filename, as_attachment=False): """Static method to get a file from a static folder available on the server. :param upload_directory: The upload directory that must exist on the root of the project. :type upload_directory: str :param filename: The name of the file to be removed. :type filename: str :param as_attachment: Set to true if you want to send this file with a Content-Disposition: attachment header, defaults to False. :type as_attachment: bool, optional :raises BadRequest: Raises when the file is not found on the upload directory or there are permission-related errors. :return: Returns the file content. :rtype: Any """ try: # 'E.T. phone home.' return send_from_directory(upload_directory, filename, as_attachment=True) except Exception as e: raise BadRequest(path=request.path, message="File not found or permission denied.", error="File Handling Related Error.", operation="Get file", api="files", status=404)
def __table_structure(self, statement): """Table Structures - Table Structures related queries :param statement: The SQL statement to be executed against the database. :type statement: str :raises BadRequest: Raises an exception if a database related error is catched. """ try: cursor = self.connection.cursor() cursor.execute(statement) self.connection.commit() cursor.close() except Exception as e: raise BadRequest(path=request.path,message=str(e), error="Database error", operation="SELECT", api="database")
def upload(self): """Uploads a file to a static folder available on the server. :raises BadRequest: Raises if the filename defines a subdirectory. :raises BadRequest: Raises if the type of the valid is not valid. :raises BadRequest: Raises if a invalid file is parsed. """ # Subdirectories are not allowed, mate! if "/" in self.filename: raise BadRequest(path=request.path, message="No subdirectories directories allowed", error="File Handling Related Error.", operation="Post file", api="files", status=500) if self.file: if self.__allowed_file(self.filename): self.filename = secure_filename(self.filename) self.file.save(self.upload_directory + "/" + self.filename) else: raise BadRequest(path=request.path, message="Invalid file extension", error="File Handling Related Error.", operation="Post file", api="files", status=500) else: raise BadRequest(path=request.path, message="Invalid file", error="File Handling Related Error.", operation="Post file", api="files", status=500) # 'Show me the money!' return (True)
def get_last_id(self, table, id_column): """Gets the last id (primary key value) of a table. This id is usually sequencial. :param table: Name of the table you wish to get the last entry id (primary key value). :type table: str :param id_column: Name of the primary key column. :type id_column: str :raises BadRequest: Raises a database error if something goes wrong. :return: An integer that represents the unique value that identifies a row of a table. :rtype: int """ try: row = (self.__select("SELECT MAX(" + id_column + ") as id FROM "+ table)) return(int(row["id"])) # Return the last id created on [table] except Exception as e: raise BadRequest(path=request.path,message=str(e), error="Database error", operation="Get last id", api="database", status=500)
def do_login(): """ @api {post} /cookbook/auth/login User Login @apiName authenticate @apiDescription Example of an authentication service using sqlite3 and the froggy framework. @apiGroup Authentication @apiParam {String} [email protected] Email of the user. @apiParam {String} psw=123 User's password. @apiExample {curl} Example usage: curl -d "[email protected]&psw=123" -X POST http://localhost:5000/cookbook/auth/login """ # On a production env do NOT POST your password without SSL enabled. email = request.form['email'] psw = request.form['psw'] # Let's create the database schema conn = create_database() query = froggy.database.Query(conn, Type.Sqlite3) user = query.execute( "SELECT id, email, psw, createdon, updatedon FROM User WHERE email=?", [email]) # Let's get the groups the user belongs 2. The groups of the user should be placed in a list of dics or froggy's group authorization API will not work. user['groups'] = [] user['groups'].append( query.execute( "SELECT group_id as id, group_name, createdon, updatedon FROM Users_View WHERE user_id=?", [user['id']])) conn.close() if (not bool(user)): raise BadRequest(path=request.path, message="Frog not Found", error="Unknown User", status=403) # Perfom authentication using the predefined framework method db_psw = user['psw'] del user['psw'] framework.auth.authenticate(user, user['email'], db_psw, psw) # Authentication was a success, the 'frog' 'can follow the white rabbit!". return (framework.response(user))
def __delete(self, statement, args): """Data Manipulation - DELETE. :param statement: The DELETE statement to be executed against the database. :type statement: str :param args: SQL statement arguments. :type args: list :raises BadRequest: Raises an exception if a database related error is catched. """ try: cursor = self.connection.cursor() froggy.gadgets.fprint("SQL statement executed :" + statement) froggy.gadgets.fprint("SQL values parsed :" + str(args)) # Execute the SQL statement with 'args' cursor.execute(statement, args) # Save (commit) the changes and close the cursor self.connection.commit() cursor.close() except Exception as e: raise BadRequest(path=request.path,message=str(e), error="Database error", operation="DELETE", api="database")
def remove(upload_directory, filename): """Static method to remove a file from a static folder available on the server. :param upload_directory: Target upload directory that must exist on the root of the project. :type upload_directory: str :param filename: The name of the file to be removed. :type filename: str :raises BadRequest: Raises when the file is not found on the upload directory or there are permission-related errors. """ try: os.remove(os.path.join(upload_directory, filename)) # 'Just keep swimming' return (True) except: raise BadRequest(path=request.path, message="File not found or permission denied.", error="File Handling Related Error.", operation="Remove file", api="files", status=500)