def delete(self):
"""Deletes self from db."""
execute_sql(
f'''
DELETE FROM "siteTable"
where majorZone = '{self.majorZone}' AND
minorZone = '{self.minorZone}' AND
abbr = '{self.abbr}' '''
)
def _create_table():
execute_sql('''
CREATE TABLE IF NOT EXISTS "rescTable" (
"researcher" TEXT NOT NULL,
"rescID" TEXT NOT NULL
"lab" TEXT
"email" TEXT
PRIMARY KEY("rescID"),
UNIQUE("rescID")
)
''')
def insert(self):
""" Inserts self into db."""
execute_sql(
"INSERT INTO siteTable VALUES (?,?,?,?,?,?,?,?)",
(self.majorZone,
self.minorZone,
self.latitude,
self.longitude,
self.abbr,
self.name,
self.description,
self.oldcode))
def _create_table():
execute_sql('''
CREATE TABLE IF NOT EXISTS "siteTable" (
"majorZone" TEXT NOT NULL,
"minorZone" TEXT NOT NULL,
"latitude" NUMBER NOT NULL,
"longitude" NUMBER NOT NULL,
"abbr" TEXT NOT NULL,
"name" TEXT NOT NULL,
"description" TEXT,
"oldcode" TEXT,
PRIMARY KEY("majorZone", "minorZone", "abbr"),
UNIQUE("majorZone", "minorZone", "abbr")
)
''')
def fetch_by_sql(clause, param):
"""Fetch researcher details by query
clause: String
Contains the clause for the query. SELECT will be added automatically.
eg: "WHERE name='?'"
params: Tuple = ()
Arguments to add to clause
"""
query = '''SELECT * from rescTable ''' + clause
return [Researcher(row) for row in execute_sql(query, param)]
def fetch_by_id(rescID):
"""Fetch the researcher by id
Parameters
------
major: rescID
The unique researcher ID for every researcher
"""
return Researcher(
execute_sql(
f"SELECT * FROM rescTable WHERE rescID = '{rescID}'").fetchone())
def fetch_by_id(major, minor, abbr):
"""Fetch the site by id
Params
------
major: String
The major zone of the site
minor: String
The minor zone of the site
abbr: String
The abbr code of the site
"""
return Site(execute_sql(
f"SELECT * FROM siteTable WHERE majorZone = '{major}' AND "
f"minorZone = '{minor}' AND abbr = '{abbr}'").fetchone())
def fetch_all():
"""Return a list of all researchers in the database"""
return [Researcher(row) for row in execute_sql("SELECT * FROM rescTable")]
def delete(self):
"""Deletes self from database."""
execute_sql(f'''
DELETE FROM "rescTable"
where rescID='{self.rescID}' ''')
def insert(self):
""" Inserts self into database"""
execute_sql("INSERT INTO rescTable VALUES (?,?,?,?)",
(self.researcher, self.rescID, self.lab, self.email))
def fetch_all():
"""Return a list of all sites"""
return [Site(row) for row in execute_sql("SELECT * FROM siteTable")]