def __init__(self):
"""Adds arguements to the request. Verify a user's key, and connects to the database."""
password = Database.getPassword()
parser = reqparse.RequestParser()
parser.add_argument("con1", type=str)
parser.add_argument("key", type=str, location="headers")
parser.add_argument("user", type=str, location="headers")
self.parsed = parser.parse_args()
key, user = self.parsed["key"], self.parsed["user"]
if key == None or user == None:
abort(406, message="Either the key, or user field is left blank.")
Key().verifyKey(user, key)
"""A redirect table of some sort is needed. Refer to testing.redirect for a guide."""
try:
self.conn, self.cursor = Database.connect("localhost", "root",
password[0], "testing")
self.schema = "testing"
except Exception:
self.conn, self.cursor = Database.connect("localhost", "root",
password[1],
"tsc_office")
self.schema = "tsc_office"
def __init__(self):
"""Handles initialization, and connects to the database"""
password = Database.getPassword()
try:
self.conn, self.cursor = Database.connect("localhost", "root",
password[0], "testing")
self.schema = "testing"
self.table = "images"
except Exception:
self.conn, self.cursor = Database.connect("localhost", "root",
password[1],
"tsc_office")
self.schema = "tsc_office"
self.table = "timages"
parser = reqparse.RequestParser()
parser.add_argument("image", type=FileStorage, location="files")
self.parsed = parser.parse_args()
try:
path = "../src/images/"
os.chdir(path)
print("CHANGED PATH")
except Exception:
pass
def __init__(self):
"""Connects to the MySQL database"""
try:
self.tap, self.cursor = Database.connect("localhost", "root",
"8811967", "tsc_office")
except NotFound:
self.tap, self.cursor = Database.connect("localhost", "root",
"YJH030412yjh_g",
"tsc_office")
def __init__(self):
password = Database.getPassword()
self.schema = "tsc_office"
try:
self.conn, self.cursor = Database.connect("localhost", "root",
password[0], self.schema)
except Exception:
self.conn, self.cursor = Database.connect("localhost", "root",
password[1], self.schema)
parser = reqparse.RequestParser()
parser.add_argument("key", type=str, location='headers')
parser.add_argument("user", type=str, location='headers')
parsed = parser.parse_args()
key, user = parsed["key"], parsed["user"]
Key().verifyKey(user, key)
def get(self):
"""Processes the GET request"""
self.__info()
try:
self.conn, self.cursor = Database.connect("localhost", "root", "YJH030412yjh_g", "tsc_office")
except Exception:
self.conn, self.cursor = Database.connect("localhost", "root", "8811967", "tsc_office")
except Exception:
abort(404, "The table tlocations is not found. Please create it.")
sql_query = f"SELECT * FROM tsc_office.tlocations"
self.cursor.execute(sql_query)
results = self.cursor.fetchall()
if len(results) == 0:
abort(404, message=f"Location does not exist", code="404")
names = []
lat_one = []
lon_one = []
lat_two = []
lon_two = []
for result in results:
names.append(result[0])
lat_one.append(result[1])
lon_one.append(result[2])
lat_two.append(result[3])
lon_two.append(result[4])
response = {
"name": names,
"lat_one": lat_one,
"lon_one": lon_one,
"lat_two": lat_two,
"lon_two": lon_two
}
return response
def __init__(self):
self.schema = "testing"
self.table = "creds"
try:
self.keyTable, self.keyCursor = Database.connect(
"localhost", "root", "YJH030412yjh_g", self.schema)
except ProgrammingError as err:
abort(
503,
message="Unable to connect to database. Check your credentials."
)
def __init__(self):
"""Handles initialization and connects to a MySQL database."""
password = Database().getPassword()
try:
self.conn, self.cursor = Database.connect("localhost", "root", password[0], "testing")
self.schema = "testing"
except Exception:
self.conn, self.cursor = Database.connect("localhost", "root", password[1], "tsc_office")
self.schema = "tsc_office"
parser = reqparse.RequestParser()
parser.add_argument("key", type=str, location="headers")
parser.add_argument("user", type=str, location="headers")
parsed = parser.parse_args()
key, user = parsed["key"], parsed["user"]
Key().verifyKey(user, key)
self.BASE_QUERY = f"SELECT * FROM {self.schema}.spec"
"""
def __init__(self):
"""
Handles initilization, and connects to a MySQL database on the server, as well as, a local SQLite database.
"""
password = Database().getPassword()
try:
self.sqlConn, self.sqlCursor = Database.connect(
"localhost", "root", password[0], self.schema)
self.schema = "testing"
self.table = "trans"
except TYPE_INTERFACE_ERROR:
self.sqlConn, self.sqlCursor = Database().connect(
"localhost", "root", password[1], "tsc_office")
self.schema = "tsc_office"
self.table = ""
"""
These are the arguments the Sync endpoint will accept. If a request is sent through postman (Google it if you don't know what it is), it doesn't matter if
its from the 'Params' tab or a 'Body' tab. The only difference is the parameters will show up in the url if you choose 'Params', and it won't
shop up if you choose 'Body'
"""
parser = reqparse.RequestParser()
parser.add_argument("key", type=str, location='headers')
parser.add_argument("user", type=str, location='headers')
parsed = parser.parse_args()
key, user = parsed["key"], parsed["user"]
verified = Key().verifyKey(user, key) # add key, user
abort(406, message="Invalid credentials", code=406,
inside="sync.py") if verified == False else ""
try:
sqliteDb = "env-api\\endpoints\\databases\\syncDb.db" # Path to the local SQLite database stored in the device.
self.liteCon = sqlite3.connect(sqliteDb)
print("connecting to sqlite3")
os.chdir("..\\env-api\\endpoints\\databases")
PATH = os.getcwd() + "\\syncDb.db"
self.liteCon = sqlite3.connect(PATH)
self.liteCursor = self.liteCon.cursor()
except FileNotFoundError as e:
error = e.strerror
abort(400,
message=f"{error}. One should be created now. Try again.")
def put(self, table: str, invoiceno: str) -> dict:
"""Takes your role into account. If your role is not within the dictionary, a forbidden error is thrown.
---
# Parameters
### table
The name of the table you wish to make edits to
### Invoiceno (temporary)
The invoice number to reference whcih invoice you wish to edit.
---
# Exceptions
- ProgrammingError: Occurs when trying to connect to a database with invalid credentials
"""
wheres = {
"tinvoicehistory": "`finvoiceno`",
"tdebtordetail": "`debtorcode`"
}
if table not in wheres:
abort(404, message=f"table with the name of {table} not found.")
con1 = self.parsed["con1"]
conditionals = con1.split("|")
values = self.__validateDatabase(self.cursor, table)
columnNames = values.split("(")[1].split(",")
numberOfColumns = len(columnNames)
formattedColumnNames = [
columnName.strip(")").strip() for columnName in columnNames
]
joinedColumnNames = (", ").join(formattedColumnNames)
try:
redirConn, redirCursor = Database.connect(
"localhost", "root", "YJH030412yjh_g",
self.schema) # Change database
except Exception:
redirConn, redirCursor = Database.connect("localhost", "root",
"8811967", self.schema)
silentQuery = f"SELECT * FROM testing.{table} LIMIT 1"
redirCursor.execute(silentQuery)
res = redirCursor.fetchall()
if len(res) == 0:
abort(
404,
message=
f"The table {table} does not exist, check your spelling, and try again."
)
lengthOfGivenValues, quotedValues = self.__join(*conditionals)
if numberOfColumns > lengthOfGivenValues:
abort(
406,
message=
f"You are required to fill a total of {numberOfColumns} but you have only entered in {lengthOfGivenValues}. Please fill up all the fields and then try again."
)
if invoiceno != "n":
formattedColumnNamesForUpdate = Database.forUpdate(
joinedColumnNames, quotedValues)
sqlQuery = f"UPDATE {self.schema}.{table} SET {formattedColumnNamesForUpdate} WHERE {wheres[table]} = '{invoiceno}'"
else:
sqlQuery = f"INSERT INTO {self.schema}.{table} ({joinedColumnNames}) VALUES ({quotedValues})"
"""Uncomment (remove the hashtags from) the next two lines when the prerequisites have been setup."""
redirCursor.execute(sqlQuery)
redirConn.commit()
return {201: "successfully added a new entry.", "query": sqlQuery}
def get(self, table: str, o1: Union[str, int], o2: Union[str, int], o3: Union[str, int], o4: Union[str, int], o5: Union[str, int]) -> str:
"""Collects all the parameters into a list. Then filters out unwanted values.
---
# Parameters
### o1 - o5
The parameters you want to use in the SQL statement.
"""
initialParams = [o1, o2, o3, o4, o5]
filteredParams = list(filter(self.removeNone, initialParams)) # removes all empty values ("n")
noneIndices = [i for i, v in enumerate(initialParams) if v == "n"]
noneIndices.sort(reverse=True) # Reverse it so that when deleting values from lists, via indices the program won't throw an IndexError exception
"""Makes a request to the SQL database which returns None intentionally, as this is required in order to get the column names"""
silent = f"{self.BASE_QUERY} WHERE table_name = 'silent'"
self.cursor.execute(silent)
_ = self.cursor.fetchall()
"""The actual query to be made, to get the associated SQL query to get data for that specific table."""
sqlQuery = f"{self.BASE_QUERY} WHERE table_name = '{table}'"
self.cursor.execute(sqlQuery)
res = self.cursor.fetchall()
desc = self.cursor.description
"""Starts from the 2nd index, because the first two column names are: table_name and sql. Which is not needed."""
names = [des[0] for des in desc][2:]
parameters = {k: v for k, v in zip(names, filteredParams)}
if len(res) == 0:
abort(404, message = f"Table with the name {table} does not exist. Check your spelling, and try again.")
formattedRes = Database().toSerialisable(res)
conditionals = list(filter(self.removeNone, formattedRes[0]))[2:]
"""Deletes unneeded parameters from the list so it won't be added into a SQL query."""
for i in noneIndices:
del conditionals[i]
keyValuePairs = {k: v for k, v in zip(filteredParams, conditionals)}
whereQuerySnippets = []
for value, k in zip(filteredParams, parameters):
con, v = keyValuePairs[value], parameters[k]
whereQuerySnippets.append(f"{con} = '{v}'")
stitchedWhereQuery= (" AND ").join(whereQuerySnippets)
stitchedQuery = f"SELECT * FROM {self.schema}.{table} WHERE {stitchedWhereQuery}"
"""The part where the api makes another SQL query on your behalf"""
_ , redirCursor = Database.connect("localhost", "root", "YJH030412yjh_g", "{self.schema}")
redirCursor.execute(stitchedQuery)
redirRes = redirCursor.fetchall()
lengthOfRedirRes = len(redirRes)
"""Handles the part with more than one result"""
if lengthOfRedirRes > 1:
tempConn, tempCursor = Database.connect("localhost", "root", "YJH030412yjh_g", "{self.schema}")
silent = tempCursor.execute(f"SELECT * FROM {self.schema}.trans LIMIT 1")
res = tempCursor.fetchall()
if len(res) == 0:
abort(404, message = "Table not found.")
serialisedRedirRes = Database().formatEntries(redirRes)
return serialisedRedirRes
elif lengthOfRedirRes == 0:
abort(404, message = "No entries with the specified information found.")
serialisedRedirRes = Database().formatEntries(redirRes)
"""Makes a key value pair to the appriroiate value. For it to be clearer."""
keyValuePairOfSerialisedRedirRes = Database().keyValuePairing(redirCursor, serialisedRedirRes)
return keyValuePairOfSerialisedRedirRes