Anzahl der Datenbankzugriffe minimieren

[Jora]

Guten Abend,

auf pythonanywhere.com läuft von mir eine kleine APP (flask).
Die APP verwaltete Aufgaben nach Datum und Schwerpunkten. (vereinfacht ausgedrückt)

Über die APP wird der Monat ausgewählt und die Daten werden mit Python für die HTML-Ausgaben aufbereitet.

Die Daten werden in Form einer Tabelle in HTML ausgegeben:
| ------ | Datum 1 | Datum 2 |
| Schwerpunkt 1 | Aufgabe 1 | Aufgabe 3 |
| Schwerpunkt 2 | Aufgabe 2 | Aufgabe 4 |

Problem
Leider dauert die HTML-Ausgabe, wegen den vielen Feldern in der Tabelle und daraus resultierenden Datenbankzugriffen, länger als mir lieb ist.
(Datenbank-Anfragebeschränkung seitens pythonanywhere.com)

Anbei der vereinfachte APP-Code

Code: Alles auswählen

class Schwerpunkte(db.Model):
	id = db.Column(db.Integer, primary_key = True)
	name = db.Column (db.String, nullable=False)
	
class Datum(db.Model):
	id = db.Column(db.Integer, primary_key = True)
	tag = db.Column (db.Integer, nullable=False)
	monat = db.Column(db.Integer, nullable=False)
	
class Aufgaben(db.Model):
	id = db.Column(db.Integer, primary_key=True)
	text = db.Column(db.String, nullable=False)
	schwerpunkt_id = db.Column(db.Integer, nullable=False)
	datum_id = db.Column(db.Integer, nullable=False)
	

In der APP sind weitere Tabellen mit Informationen zu den Aufgaben enthalten (hier in der Beschreibung nicht beschrieben)
Wegen der großen Datenmenge wurde die Datenbank bewusst ohne Relationships aufgebaut.

Code: Alles auswählen

dict_html_tabelle = defaultdict(
	lambda = defaultdict(list)
)
for tag in Datum.query.filter_by(monat = 12).all()

	list_aufgaben = []
	for schwerpunkt in Schwerpunkte.query.all()
	
		aufgabe = Aufgaben.query.filter_by(schwerpunkt_id = schwerpunkt.id, datum_id = tag.id
		list_aufgaben.append(aufgabe.text)
		
	dict_html_tabelle[tag.id][schwerpunkt.id] = list_aufgaben
	

Das flask-Template bröselt dict_html_tabelle entsprechend der oben skizzierten Tabelle aus.


Meine Frage

wie kann ich die zwei Schleifen (Datum und Schwerpunkte) verbinden um die Anzahl der Datenbankzugriffe auf die Tabelle Aufgaben zu minimieren?

Eine Lösung wäre die Verbindung der Tabellen (Schwerpunkte, Datum, Aufgaben) über Relationships.
Wie kann ich dann in der Query die Datenmenge begrenzen?

Problembeispie - Datenbank mit Relationships

Datenbankstruktur:
[ Land ]-1---n-[ Bundesland ]-1----n-[ Stadt ]-1----n-[ Straße ]-1----n-[ Hausnummer]-1---n-[ Stock ]-1----n-[ Zimmer ]-1---n-[ Lichtschalter ]

Wenn nur die Bundesländer eines Landes gefragt sind, dann erhalte ich diese mittels Relationship folgendermaßen:

land = Land.query.get(1)
bundeslaender = land.bundeslaender

Im Hinblick auf die umfangreiche Datenbank-Verknüpfung (von Land bis Lichtschalter) erscheint mir diese einfache Abfrage als nicht sehr sinnvoll.

Vielen Dank!

MfG
Jora

[__blackjack__]

@Jora: Warum die Models ohne Relationships erstellt wurden, ist mir jetzt erst einmal nicht wirklich ersichtlich.

Als nächstes finde ich die `Datum`-Tabelle ein bisschen merkwürdig, weil es ja einen DATE-Datentyp gibt.

Und dann machst Du da Sachen in Python die man eigentlich die Datenbank erledigen lassen sollte. Du fragst ja im Grunde alle Tabellen einzeln ab und bringst das dann in Schleifen in Python zusammen, statt das gleich von der DB abzufragen. Die Schwerpunkte rufst Du immer wieder ab, für jeden Tag. Und auch noch *alle*, statt das auf die Schwerpunkte zu beschränken die tatsächlich verwendet werden.

Ich würde da wahrscheinlich *eine* Abfrage machen, und zwar die Aufgaben, da die `Datum`-Tabelle ”joinen” und auf den Monat filtern, und SQLAlchemy sagen, dass die Schwerpunkte gleich ”eager” mitgeladen werden sollen. Dazu braucht's natürlich Relationships.

[Jora]

Gute Morgen __blackjack__,
danke dir für deine schnelle Antwort.

@Jora: Warum die Models ohne Relationships erstellt wurden, ist mir jetzt erst einmal nicht wirklich ersichtlich.

Das Problem mit der großen Datenmenge, die bei einer Abfrage aus der DB geladen werden könnte. (Im Problembeispiel näher ausgeführt)

Als nächstes finde ich die `Datum`-Tabelle ein bisschen merkwürdig, weil es ja einen DATE-Datentyp gibt.

Ja! Ich habe die db.Datum als Ausgangspunkt für alle Daten erstellt.

Und dann machst Du da Sachen in Python die man eigentlich die Datenbank erledigen lassen sollte. Du fragst ja im Grunde alle Tabellen einzeln ab und bringst das dann in Schleifen in Python zusammen, statt das gleich von der DB abzufragen. Die Schwerpunkte rufst Du immer wieder ab, für jeden Tag. Und auch noch *alle*, statt das auf die Schwerpunkte zu beschränken die tatsächlich verwendet werden.

In der ersten Schleife (for tag in Datum.filter(monat)) lese ich nur die ID''s der Tage im ausgewählten Monat.
In der zweiten Schleife (for schwerpunkt in Schwerpunkte) werden *alle* Schwerpunkte durchlaufen, da ich die jeweilige ID brauche um die Aufgaben aus der Datenbank abfragen zu können.

Ich würde da wahrscheinlich *eine* Abfrage machen, und zwar die Aufgaben, da die `Datum`-Tabelle ”joinen” und auf den Monat filtern, und SQLAlchemy sagen, dass die Schwerpunkte gleich ”eager” mitgeladen werden sollen. Dazu braucht's natürlich Relationships.

Aha, also mit der Abfrage von db.Aufgaben beginnen und über join + filter und "eager" die benötigten ID's herausfiltern. Ok, werde ich mir anschauen.

[sparrow]

Ich glaube, du machst da einige Denkfehler, was Beziehungen in Datenbanken angeht. Gerade jetzt machst du ja viele Anfragen und genau jetzt fallen dir die Datenmengen auf die Füße.
Es macht schlicht keinen Sinn was du tust. Im Gegenteil.

Wie detailliert du die Daten brauchst, weißt nur du. Ob man also eine Adresse tatsächlich so weit normalisieren muss. Falls du das für nötig hältst, ist das sogar zwingend notwendig, weil du nur so effektiv Tippfehler ausschließen kannst (Spamstr. <-> Spamstraße; Spamburg (OT Spamleben) <-> Spamleben).
Und warum du irgendwo selbst IDs als Fremdschlüssel führst, ohne sie als Fremdschlüssel zu deklarieren (ja, das wären dann Relationships so wie sie gedacht sind, statt irgenwie selbst zusammengefummelt), erschließt sich mir auch nicht.

Die Datum-Tabelle macht schlicht keinen Sinn. Da wollte dich __blackjack__ nicht unterstützen sondern dir sagen, dass das mit der Flinte durch den Fuß ist.
Überhaupt macht in meinen Augen ein nacktes Datum (zur Bezeichnung eines Tages) als einzige Information in einer Tabelle überhaupt keinen Sinn.

Was willst du mit der Datensammlung denn abbilden? Daraus ergibt sich ja in der Regel ein entsprechendes Design der Datenbank.

[Jora]

Hallo sparrow,

Ziel der APP:
*Alle* Aufgaben, die nach Schwerpunkten gegliedert sind, für einen Monat leserlich auszugeben.
z.B in einer HTML-Tabelle (wie oben angedeutet) Die Spalten bilden die Tage innerhalb des Monats ab und die Zeilen die Schwerpunkte.

Mein ganz erster Ansatz war mit Relationships, dann sind zusätzliche Tabellen hinzugekommen z.B Nachrichten, Kommentare, Straßen, Hausnummern usw..
Wenn ich nur die Aufgaben in der HTML-Tabelle ausgeben möcht, dann interessiert mich der weitere Anhang (Nachrichten, Straßen usw.) nicht.
Um die Datenmenge beim Query zu beschränken habe ich die Relationships herausgenommen.
Jetzt habe ich das Problem mit der Vielzahl der Datenbank-Anfragen. (Aufgrund der vielen Felder ( 30 Tage x 7 Schwerpunkte = 210 Anfragen) in der HTML-Tabelle)

[sparrow]

Du sattelst das Feld von hinten auf.
Du sprichst hier davon, dass die Aufgabe der Datenbank die Ausgabe ist - oder deren Design. Die Datenbank ist grundsätzlich nur dafür da um Daten zu speichern. Das ist ihr Job, das kann sie gut.
Die Frage, bevor du dich um die Ausgabe kümmerst, ist also: Was sind das für Daten, die da gespeichert werden und wie sieht dafür das passende Datenbankdesign aus?

[Jora]

Beispiel HTML-Ausgabe:
/ ---- / Tag 1 / Tag 2 /
/ Schwerpunkt (Mechanik) / Lichtschalter reparieren / Lichtschalter einbauen /
/ Schwerpunkt (Software) / Datenbankanfrage optimieren / APP testen /
/ Schwerpunkt (Elektronik) / Widerstand tauschen / keine Aufgabe /

Jeden Tag gibts es mehrere Aufgaben.
Aktuell speichert bei mir die Datenbank nur die Daten, alles Andere übernimmt Python.

Wenn ich auf "Lichtschalter reparieren" klicke, dann werden weitere Infos bezüglich der Aufgabe der Datenbank entnommen. (hier wird dann mit der ID der Aufgabe gearbeitet)

Mir ist klar, dass die Datenbank vieles bezüglich der Datenfilterung übernehmen kann.

Mit welchen Informationen und Schlagwörtern kann ich meine Wissenslücke und meinen Denkfehler beheben?
Die Doku zu SQLAlchemy ist riesig.

[sparrow]

Nochmal: es geht nicht um die Ausgabe. Die hat mit der Datenhaltung nichts zu tun.
So oft, wie du das immer wieder in Verbindung bringst, ahne ich ja fast, dass du gar keine Datenbank brauchst.

Also nochmal: Was tust du da? Was willst du abbilden (in der Datenstruktur, _nicht_ bei der Ausgabe)? Woher kommen die Daten?

[Jora]

Nochmal: es geht nicht um die Ausgabe. Die hat mit der Datenhaltung nichts zu tun.
So oft, wie du das immer wieder in Verbindung bringst, ahne ich ja fast, dass du gar keine Datenbank brauchst.

Also nochmal: Was tust du da? Was willst du abbilden (in der Datenstruktur, _nicht_ bei der Ausgabe)? Woher kommen die Daten?

Hallo,

die Daten (z.B. eine Aufgabe am Tag X mit dem Schwerpunkt X) gebe ich einzeln an einer anderen Stelle ein und erzeuge mir so einen Monatsplan.
Es kann vorkommen, dass eine Aufgabe wieder gelöscht oder verändert wird. Das ist alles kein Problem.

Die Problemstellung etwas anders ausgedrückt:
ich habe eine Liste mit Tag-IDs ( 1323, 1324, 1325 ,1326 , 1327, usw.)
und eine Liste mit Schwerpunkt-IDs ( 1, 2, 3, 4, 5, 6, 7)

Wie filtere ich die Daten aus der db.Aufgaben mit diesen zwei ID-Listen, ohne Relationship?

[__blackjack__]

@Jora: Was verstehst Du denn unter „Relationship“? Die sind ja *da*, also geht es gar nicht ohne. Die Frage ist also mehr als eigenartig. Und wo hast Du denn diese Listen her und wie hängen die zusammen? Ich habe die Befürchtung dass das wieder ausserhalb der Datenbank gedacht ist.

[Jora]

Ok. ich baue meine Datenbank um.

@__blackjack__ : Die Listen stehen beispielhaft für die zwei Schleifen!

Ich lese gerade ein Tutorial bezüglich "cross join" mit SQL-Befehlen.

Code: Alles auswählen

SELECT * FROM orders WHERE customer_id =
	(
		SELECT id FROM customers
		WHERE vorname = 'Peter'
	);

In meinem Fall habe ich jetzt nicht nur eine customer_id (Peter) sondern gleiche mehrere ID's (Peter , Hans, Udo)

Ein imp. inner join SQL-Befehl würde wie folgt aussehen:

Code: Alles auswählen

SELECT * FROM customers, orders
	WHERE customers.id = oders.customers_id;

mir fehlt hier die Möglichkeit, zwischen den vielen Customers nur eine Handvoll auswählen zu können.

[__deets__]

Wie kommst du denn ploetzlich auf customers? Das hilft doch ueberhaupt nicht. Denn man kann natuerlich auch eine explizite Liste der Customer angeben (SELECT name FROM customers WHERE id in (1, 2, 3, 4)) - doch das ist ein eher unueblicher Fall, und so wie das bisher klang ist dein Auswahlkriterium ein Datumsbereich. Also eher etwas wie

SELECT a.text FROM aufgaben AS a WHERE a.tag >= ? AND a.tag <= ?

wobei die ? dann per parametrisiertem execute angebene werden als zB Montag und Freitag einer Woche.

Wenn du jetzt noch eine n:m-Relation einfuegen willst, die Schwerpunkte zu Aufgaben assoziiert, dann wird das ganze natuerlich ein bisschen komplizierter.

SELECT a.text, s.name FROM aufgaben AS a, schwerpuntke AS s, aufgaben2schwerpunkte AS a2s WHERE a.tag >= ? AND a.tag <= ? AND a2s.aufgaben_id = a.id AND a2s.schwerpunkt_id = s.id

Dabei bekommst du jetzt allerdings mehrere Tupel zurueck - einen pro Schwerpunkt. Und potentiell keinen, solltest du keine Schwerpuntk-Assoziation haben. Wenn du das vermeiden willst, dann kommst du um zwei Abfragen nicht herum.

[Jora]

SELECT a.text, s.name FROM aufgaben AS a, schwerpuntke AS s, aufgaben2schwerpunkte AS a2s WHERE a.tag >= ? AND a.tag <= ? AND a2s.aufgaben_id = a.id AND a2s.schwerpunkt_id = s.id

Super, dass ist die Lösung!

Code: Alles auswählen

from sqlalchemy import and_
aufgaben = db.session.query(Schwerpunkte,Datum,Aufgaben).filter( and_(
	Schwerpunkte.id >=1 , Schwerpunkte.id <= 7,
	Datum.monat ==  2, Datum.tag >= 1, Datum.tag <=31,
	Schwerpunkte.id == Aufgabe.schwerpunkt_id,
	Datum.id == Aufgabe.datum_id
	))

[__deets__]

Schoen das es klappt. Aber bitte nicht full-quote, wenn der Text davor schon im Beitrag steht.

[__deets__]

Und dann ist natuerlich die Datum-Tabelle so falsch. Das macht man nicht. Es gibt einen Datetime oder Date Datentypen in der Datenbank, und den benutzt man. Statt da selbst eine Tabelle mit Spalten fuer Tage, Monate, Jahre etc. zu basteln.

[Jora]

ja, nur das Handling mit dem datetime ist mir nicht so geläufig..

[__deets__]

Dann uebst du das halt - ist keiner mit geboren worden. Davon das du es vermeidest wird's jedenfalls nicht besser.

[__blackjack__]

@Jora: Das Filterkriterium das der Tag >= 1 und <= 31 sein soll sieht komisch aus. Fast so als würdest Du erwarten, dass da tatsächlich Zahlen ausserhalb dieses Bereichs vorkommen könnten.

Die Namen `Schwerpunkte` und `Aufgaben` sind übrigens falsch. So eine Klasse und ein Objekt davon repräsentieren *einen* `Schwerpunkt` und *eine* `Aufgabe`.

`and_()` ist überflüssig. Statt auf Ober- und Untergrenze mit zwei Tests zu prüfen kennen sowohl SQL als auch SQLAlchemy BETWEEN/`between()`.
Ungetestet:

Code: Alles auswählen

aufgaben = db.session.query(Schwerpunkt, Datum, Aufgabe).filter(
    Schwerpunkt.id.between(1, 7),
    Schwerpunkt.id == Aufgabe.schwerpunkt_id,
    Datum.monat ==  2,
    Datum.id == Aufgabe.datum_id,
)

[Jora]

@__deets__: ich habe es bis jetzt nur vermieden, weil es viele andere Baustellen gibt. Es ist nicht so, dass ich dem generell aus dem Weg gehe möchte. Bei der aktuellen APP lag der Fokus auf flask, sqlalchemy usw. um eine lauffähige APP auf die Beine stellen zu können. In der nächsten APP wird die db.Datum entfallen!

@__blackjack__ : jop. war auch nur ein schneller Schuß. In der App wird mit *Datum.tag.in_(list_tage)* der Filter eingegrenzt.
In der aktuellen App habe ich alle Models in Mehrzahl benannt. Gerade bei for-Schleifen beware ich so einen Überblick:

Code: Alles auswählen

schwerpunkte = Schwerpunkte.query.all()
for schwerpunkt in schwerpunkte:
  print(schwerpunkt.name)

Problem gelöst!
Die Ladezeit der HTML-Tabelle wurde von 5 Sec. auf 1 Sec. verbessert!

Vielen Dank Euch!

[Sirius3]

Das es sich um Mehrzahl handelt, ist ja bei ›schwerpunkte‹ so. Die Klasse an sich, kann und sollte aber trotzdem ›Schwerpunkt‹ heißen.