def find_by_current_semester(self, current_semester):
"""Suchen eines Semester anhand vom Current Semester."""
result = []
cursor = self._cnx.cursor()
command = "SELECT * FROM semester WHERE current_semester like '{}'".format(
current_semester)
cursor.execute(command)
tuples = cursor.fetchall()
if len(tuples) != 0:
for (id, name, current_semester, create_time) in tuples:
semester = Semester()
semester.set_id(id)
semester.set_name(name)
semester.set_current_semester(current_semester)
semester.set_create_time(create_time)
result.append(semester)
else:
result = None
self._cnx.commit()
cursor.close()
return result
def find_by_key(self, id):
"""Suchen eines Semesters mit vorgegebener ID-Nummer."""
result = []
cursor = self._cnx.cursor()
command = "SELECT * FROM semester WHERE id like '{}'".format(id)
cursor.execute(command)
tuples = cursor.fetchall()
if len(tuples) != 0:
for (id, name, current_semester, create_time) in tuples:
semester = Semester()
semester.set_id(id)
semester.set_name(name)
semester.set_current_semester(current_semester)
semester.set_create_time(create_time)
result.append(semester)
result = semester
else:
result = None
self._cnx.commit()
cursor.close()
return result
def find_semester_of_student(self, id):
"""
finde alle Semester eines Studenten, in welcher er eine Teilnahme hat in der Datenbank
:param id
"""
result = []
cursor = self._connection.cursor()
command = "SELECT semester.id, semester.name FROM teilnahmen INNER JOIN projekte on teilnahmen.lehrangebot = projekte.id \
INNER JOIN semester on projekte.halbjahr = semester.id WHERE teilnahmen.teilnehmer ='{}'".format(
id)
cursor.execute(command)
tuples = cursor.fetchall()
filtered_tuples = list(set(tuples)) #entfernt doppelte Semester
for (id, name) in filtered_tuples:
semester = Semester()
semester.set_id(id)
semester.set_name(name)
result.append(semester)
self._connection.commit()
cursor.close()
return result
def find_by_id(self, id):
"""Eindeutiges Auslesen eines Semesters durch ID
:param id
:return Semester-Objekt, das der übergebenen ID entspricht oder None wenn DB-Tupel nicht vorhanden ist
"""
result = None
cursor = self._connection.cursor()
command = "SELECT id, name FROM semester WHERE id ='{}'".format(id)
cursor.execute(command)
tuples = cursor.fetchall()
try:
(id, name) = tuples[0]
semester = Semester()
semester.set_id(id)
semester.set_name(name)
result = semester
except IndexError:
"""Der IndexError wird oben beim Zugriff auf tuples[0] auftreten, wenn der vorherige SELECT-Aufruf
keine Tupel liefert, sondern tuples = cursor.fetchall() eine leere Sequenz zurück gibt."""
result = None
self._connection.commit()
cursor.close()
return result
def create_semester(self, name, current_semester):
"""Ein Semester anlegen"""
semester = Semester()
semester.set_name(name)
semester.set_current_semester(current_semester)
semester.set_id(1)
with SemesterMapper() as mapper:
return mapper.insert(semester)
def create_semester(self, creation_time, name, start, end):
"""Ein Semester anlegen"""
semester = Semester()
semester.set_creation_time(creation_time)
semester.set_name(name)
semester.set_start(start)
semester.set_end(end)
semester.set_id(1)
with SemesterMapper() as mapper:
return mapper.insert(semester)
def find_by_name(self, name):
result = []
cursor = self._cnx.cursor()
command = "SELECT * FROM semester WHERE name like '{}'".format(name)
cursor.execute(command)
tuples = cursor.fetchall()
for (id, name, current_semester, create_time) in tuples:
semester = Semester()
semester.set_id(id)
semester.set_name(name)
semester.set_current_semester(current_semester)
semester.set_create_time(create_time)
result.append(semester)
self._cnx.commit()
cursor.close()
return result
def find_all(self):
"""Auslesen aller Semesters."""
result = []
cursor = self._cnx.cursor()
command = "SELECT * FROM semester"
cursor.execute(command)
tuples = cursor.fetchall()
for (id, name, current_semester, create_time) in tuples:
semester = Semester()
semester.set_id(id)
semester.set_name(name)
semester.set_current_semester(current_semester)
semester.set_create_time(create_time)
result.append(semester)
self._cnx.commit()
cursor.close()
return result
def find_all(self):
result = []
cursor = self._cnx.cursor()
cursor.execute("SELECT id, creation_time, start, end, name FROM semester")
tuples = cursor.fetchall()
for (id, creation_time, start, end, name) in tuples:
semester = Semester()
semester.set_id(id)
semester.set_creation_time(creation_time)
semester.set_name(name)
semester.set_start(start)
semester.set_end(end)
result.append(semester)
self._cnx.commit()
cursor.close()
return result
def find_all(self):
"""Auslesen aller vorhandenen Semester
:return Eine Sammlung aller Semester-Objekten.
"""
result = []
cursor = self._connection.cursor()
command = "SELECT id, name FROM semester"
cursor.execute(command)
tuples = cursor.fetchall()
for (id, name) in tuples:
semester = Semester()
semester.set_id(id)
semester.set_name(name)
result.append(semester)
self._connection.commit()
cursor.close()
return result
def find_by_id(self, id): #suche semester nach id
result = None
cursor = self._cnx.cursor()
command = "SELECT id, creation_time, name, start, end FROM semester WHERE id={}".format(id)
cursor.execute(command)
tuples = cursor.fetchall()
if tuples[0] is not None:
(id, creation_time, name, start, end) = tuples[0]
semester = Semester()
semester.set_id(id)
semester.set_creation_time(creation_time)
semester.set_name(name)
semester.set_start(start)
semester.set_end(end)
result = semester
self._cnx.commit()
cursor.close()
return result
self._cnx.commit()
cursor.close()
"""Zu Testzwecken können wir diese Datei bei Bedarf auch ausführen,
um die grundsätzliche Funktion zu überprüfen.
Anmerkung: Nicht professionell aber hilfreich..."""
if (__name__ == "__main__"):
with SemesterMapper() as mapper:
result = mapper.delete(2020)
for p in result:
print(p)
if (__name__ == "__main__"):
s = Semester()
s.set_id(2023)
s.set_creation_time(2023-12-10)
s.set_name("SSSasfsfaf")
s.set_start(2023-10-10)
s.set_end(2023-10-10)
with SemesterMapper() as mapper:
result = mapper.update(s)