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)