Sqlalchemy Abfrage von Beziehungen

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MariaDB/MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Antworten
inco
User
Beiträge: 16
Registriert: Sonntag 30. Dezember 2018, 16:32

Servus Zusammen,

ganz kurze Vorgeschichte:
.accdb File --> import der Tabellen mit pyodbc --> snapshot und Konvertierung zu sqlite3 Datenbank --> sqlalchemy automap() --> Definition der Beziehungen --> Problem mit der verknüpften Abfrage von Werten aus anderen Tabellen!

Zur Veranschaulichung hab ich mir mal ein kleines Codebeispiel ausgedacht. Es sind mehrere Indextabellen vorhanden, welche nur zum Nachschalgen der entsprechenden Strings verwendet werden.
zB. Doors (ID-1, name-Eichentür)

Code: Alles auswählen

Base = automap_base()

engine = create_engine('sqlite///sql.db', echo = False)

class Doors(Base):
    __tablename__ 'tblDoors'
    ID = Column(Integer, primaray_key = True)
    name = Column(String(10))
    
class Windows(Base):
    __tablename__ 'tblWindows'
    ID = Column(Integer, primaray_key = True)
    name = Column(String(10))

class House(Base):
    __tablename__ 'tblHouse'
    ID = Column(Integer, primaray_key = True)
    Doors = Column(Integer, ForeignKey('tblDoors.ID'), nullable=False)
    Doors_rel = relationship(Doors, backref=backref('idk', uselist=True, cascade='delete,all'))
    Windows = Column(Integer, ForeignKey('tblDoors.ID'), nullable=False)
    Win_rel = relationship(Doors, backref=backref('idk', uselist=True, cascade='delete,all'))
Base.prepare(engine, reflect=True)

#additional classes
#plates = Base.classes['00_plates']

s = Session(engine)

for part in s.query(House.Doors, House.Windows).distinct():
print(part)

Ich möchte mir nun zum Beispiel alle Kombinationen von Fenstern und Türen anzeigen lassen.
Also zum Beispiel:
Eichentüre-Birkenfenster
EIchentüre-Kunststofffenster
...
Die oben gezeigte Abfrage der Kombinationen als Zahlenreihe funktioniert soweit. Nur schaffe ich es bisher nicht die Strings auf einfache Weise nachzuschlagen.
Ich hätte beispielsweise print(part.Doors_rel.name ) versucht.
Die Ausgabe funktioniert leider nicht, da part keine Informationen über die Beziehung mehr enthält....

Ich habe bisher einige andere Möglichkeiten für Abfragen ganz ohne vorher definierte Beziehung gefunden:
https://docs.sqlalchemy.org/en/latest/orm/tutorial.html
Nur wenn ich mir schon die Arbeit mache das ganze vorher in Objekten zu definieren, würde ich es gerne auch entsprechend "einfach" abfragen können... ;)

Eine weitere für mich sehr interessante Abfrage wären auch die ID-Blöcke aller gleichartiger Kombinationen in Blöcken.
Zum Beispiel in Verbindung mit allen Rechnungsnummern der Kombination Eichentüre-Birkenfenster.
Als Ausgabeformat eignet sich zum Beispiel ein Wörterbuch mit
{"Eichentüre-Birkenfenster" :[1,105,3008]
"EIchentüre-Kunststofffenster":[1005,300]}

Kann mir jemand bei diesem Problem helfen?
Benutzeravatar
__blackjack__
User
Beiträge: 13003
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

@inco: Ich würde ja als erstes mal ganz dringend die Namen, mindestens mal auf Python-Seite anpassen. Das etwas `Doors` heisst, also Mehrzahl, aber nur für *eine* Tür steht, ist vorsichtig gesagt *ungünstig*. Das sollte als Klasse `Door` und als Attribut `House.door` heissen. Ausserdem ist die Bennenung mit `_rel` und `idk` blöd/verwirrend. Statt `House.Doors` was ja eigenltich *eine* ID ist, sollte man `House.door_id` verwenden und für die Beziehung dann `House.door` statt `House.Doors_rel`. Der Rückverweis könnte dann `houses` statt `idk` heissen. Analog mit `Windows`/`Windows`/`Windows_rel`. Damit würde der Code ein ganzes Stück verständlicher.

Du fragst ja nur Zahlen ab, eben die IDs, was bei besser benannten Attributen auch schon beim lesen des Codes klar wäre. Stattdessen müsstest Du die Beziehungen abfragen. Da sollten dann komplette Objekte bei heraus kommen.

Oder Du fragst in der Schleife noch mal die einzelnen Objekte über die ID ab.
“Most people find the concept of programming obvious, but the doing impossible.” — Alan J. Perlis
inco
User
Beiträge: 16
Registriert: Sonntag 30. Dezember 2018, 16:32

Scheinbar kann ich meinen initialen Post nicht überarbeiten... bezgl. der Nomenklatur hast du natürlich Recht...hab mir das Beispiel etwas an den Haaren herbeigezogen... und vielleicht auch etwas zu schnell hingeklatscht ;)

Ja genau ich frage da erstmal nur Zahlen ab. Sollte aufgrund der Indizierung auch der bessere Weg sein.
Nur hätte ich das Ergebnis dann ganz gern als lesbare Strings ... Ich dachte, dass ich dazu die vorhandenen Beziehungen nutzen könnte.
Anstatt einen neue Abfrage "Integer welchen String versteckst du" starten zu müssen.
Vielleicht drücke ich mich etwas umständlich aus ... :S

Vielleicht kannst du in einem kurzen Codesample zeigen, wie du die gewünschte Abfrage umsetzen würdest?

Vielen Dank und viele Grüße
Benutzeravatar
__blackjack__
User
Beiträge: 13003
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

@inco: Welche Beziehungen? Du fragst explizit nur die Zahlen ab. Die Zahlen sind eben genau das, einfach nur Zahlen, ohne eine Beziehung zu *irgendwas*.

Ich sehe gerade das da so einige Fehler drin sind. Ganz grundlegende syntaktische, und dann so Sachen wie das in `Windows` das Klassenattribut `Doors` definiert wird, und sich das `Doors` in der nächsten Zeile dann natürlich *darauf* bezieht, und nicht auf die Klasse `Doors`. Gleiches gilt für das `Doors` in der `Win_rel` was sich ziemlich sicher auf `Windows` beziehen sollte. Also auf die Klasse, nicht auf das gleichnamige `House.Windows` was kurz vorher definiert wird. Argh!

Also wirklich erst einmal alles ordentlich benennen. Dann ist das was Du abfragst ziemlich deutlich ein Paar von IDs:

Code: Alles auswählen

#!/usr/bin/env python3
from sqlalchemy import and_, create_engine, Column, Integer, String, ForeignKey
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.orm import backref, relationship, Session

Base = declarative_base()


class Door(Base):
    __tablename__ = 'door'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(10))

    # `houses` as backref from `House`.


class Window(Base):
    __tablename__ = 'window'
    
    id = Column(Integer, primary_key=True)
    name = Column(String(10))

    # `houses` as backref from `House`.


class House(Base):
    __tablename__ = 'house'
    
    id = Column(Integer, primary_key=True)
    door_id = Column(Integer, ForeignKey(Door.id), nullable=False)
    window_id = Column(Integer, ForeignKey(Window.id), nullable=False)
    
    door = relationship(
        Door, backref=backref('houses', uselist=True, cascade='delete,all')
    )
    window = relationship(
        Window, backref=backref('houses', uselist=True, cascade='delete,all')
    )


def main():
    engine = create_engine('sqlite:///:memory:', echo=True)
    session = Session(engine)
    
    pair_ids_query = session.query(House.door_id, House.window_id).distinct()
    for door_id, window_id in pair_ids_query:
        door = session.query(Door).get(door_id)
        window = session.query(Window).get(window_id)
        print(f'{door.name}-{window.name}')


if __name__ == '__main__':
    main()
Das führt für die Schleife zu dieser SQL-Abfrage:

Code: Alles auswählen

SELECT DISTINCT house.door_id AS house_door_id,
  house.window_id AS house_window_id 
FROM house
In der Schleife werden dann `Door`- und `Window`-Exemplare zu den IDs aus der Datenbank abgefragt. Wobei die Objekte gecached werden, das heisst nur der erste Aufruf pro ID und Datentyp führt tatsächlich zu einer zusätzlichen Datenbankabfrage.

Alternativ könnte man die vorhandene Abfrage zu einer Unterabfrage machen und die zu einer Abfrage von `Door` und `Window`-Paaren „joinen“:

Code: Alles auswählen

    pair_ids_query = (
        session.query(House.door_id, House.window_id).distinct().subquery()
    )
    pairs_query = (
        session.query(Door, Window)
            .join(
                pair_ids_query,
                and_(
                    Door.id == pair_ids_query.c.door_id,
                    Window.id == pair_ids_query.c.window_id
                )
            )
    )
    for door, window in pairs_query:
        print(f"{door.name}-{window.name}")
Was zu einer Abfrage führt bei der die Zuordnung der IDs übernimmt:

Code: Alles auswählen

SELECT door.id AS door_id, door.name AS door_name,
  window.id AS window_id, window.name AS window_name 
FROM window, door
JOIN (
  SELECT DISTINCT house.door_id AS door_id, house.window_id AS window_id 
  FROM house
) AS anon_1
  ON door.id = anon_1.door_id AND window.id = anon_1.window_id
Alles ungetestet. :-)
“Most people find the concept of programming obvious, but the doing impossible.” — Alan J. Perlis
inco
User
Beiträge: 16
Registriert: Sonntag 30. Dezember 2018, 16:32

Ich sehe gerade das da so einige Fehler drin sind. Ganz grundlegende syntaktische, und dann so Sachen wie das in `Windows` das Klassenattribut `Doors` definiert wird, und sich das `Doors` in der nächsten Zeile dann natürlich *darauf* bezieht, und nicht auf die Klasse `Doors`. Gleiches gilt für das `Doors` in der `Win_rel` was sich ziemlich sicher auf `Windows` beziehen sollte. Also auf die Klasse, nicht auf das gleichnamige `House.Windows` was kurz vorher definiert wird. Argh!

Also wirklich erst einmal alles ordentlich benennen. Dann ist das was Du abfragst ziemlich deutlich ein Paar von IDs:
Sry, da war ich gestern wohl nicht mehr ganz auf der Höhe.
Vielen Dank für die Korrektur. @Admin es wäre schön, wenn ich selber die Möglichkeit hätte das richtig zu stellen.

Code: Alles auswählen

    for door_id, window_id in pair_ids_query:
        door = session.query(Door).get(door_id)
        window = session.query(Window).get(window_id)
Ich dachte der Schritt wäre unnötig. Habe es hier irgendwie mal anders gemacht... kann es nur leider nicht mehr reproduzieren:
https://stackoverflow.com/questions/528 ... rom-access
Aber was solls... solange es funktioniert. :mrgreen:

Die Abfrage der ID-Blöcke würdest du dann vermutlich etwa so umsetzen?

Code: Alles auswählen

    for door_id, window_id in pair_ids_query:
        door = session.query(Door).get(door_id)
        window = session.query(Window).get(window_id)
        id_list = [house.id for house in session.query(House).filter(
        and_(House.Door==door_id, House.Window==window_id))]
        dict[f'{door.name}-{window.name}']=id_list
PS. Deinen zweiten Lösungsweg versuche ich gerade noch zu verstehen. Aber mir kommt die Lösung aufwendiger vor... lohnt sich dass im Bezug auf die Performance?
Sirius3
User
Beiträge: 17710
Registriert: Sonntag 21. Oktober 2012, 17:20

Das ist doch jetzt total verquer, erst über house zu gehen, um alle Türen und Fenster zu bekommen und dann nochmal das selbe um wieder an die Häuser zu gelangen.

Code: Alles auswählen

doors_and_windows = defaultdict(list)
for house in session.query(House):
    doors_and_windows[f"{house.door.name}-{house.window.name}"].append(house)
Dann muß man sie nie um IDs kümmern.
inco
User
Beiträge: 16
Registriert: Sonntag 30. Dezember 2018, 16:32

Ahhhh, stimmt ... manchmal sieht man den Wald vor lauter Bäumen nicht :mrgreen:
Benutzeravatar
__blackjack__
User
Beiträge: 13003
Registriert: Samstag 2. Juni 2018, 10:21
Wohnort: 127.0.0.1
Kontaktdaten:

@inco: Schon wieder die Namen… :roll: :-)

Grunddatentypen haben nix in Namen verloren, also `ids` statt `id_list`, oder besser `house_ids`, damit man weiss was das für IDs sind.

Die Attribute bei `House` haben schon wieder die alten, schlechten Namen.

Das `and_()` bei `Query.filter()` kann man sich sparen, weil die Methode beliebig viele Argumente mit Filterkriterien entgegen nimmt, die dann mit AND verknüpft werden. Da nur einfache Vergleiche mit Feldern der abgefragten Klasse gemacht werden, kann man des etwas einfacher mit `Query.filter_by()` haben.

`dict` ist der Name eines eingebauten Typs, den sollte man nicht an etwas anderes binden.

Zwischenstand:

Code: Alles auswählen

        door_window_to_house_ids[f'{door.name}-{window.name}'] = [
            house.id
            for house in session.query(House).filter_by(
                door_id=door_id, window_id=window_id
            )
        ]
Falls dieses Wörterbuch das Ziel sein sollte, könnte man auch eine Abfrage mit etwas redundanten Daten machen, die nach Tür- und Fenster-ID sortieren, und dann das Ergebnis im Programm mit `itertools.groupby()` gruppieren und in ein Wörterbuch schreiben.

Was sich da letztendlich in Bezug auf die Performance am besten macht, hängt von vielen Faktoren ab. Da würde ich ohne reale Zahlen nix vermuten wollen. Also am besten erst einmal eine Lösung nehmen die keine offensichtlichen Probleme mit Laufzeit oder Speicher hat und verständlich ist.
“Most people find the concept of programming obvious, but the doing impossible.” — Alan J. Perlis
inco
User
Beiträge: 16
Registriert: Sonntag 30. Dezember 2018, 16:32

@inco: Schon wieder die Namen… :roll: :-)

Grunddatentypen haben nix in Namen verloren, also `ids` statt `id_list`, oder besser `house_ids`, damit man weiss was das für IDs sind.
Schade, dass mache ich gerne (mit den Grunddatentypen im Namen) :mrgreen:
Falls dieses Wörterbuch das Ziel sein sollte, könnte man auch eine Abfrage mit etwas redundanten Daten machen, die nach Tür- und Fenster-ID sortieren, und dann das Ergebnis im Programm mit `itertools.groupby()` gruppieren und in ein Wörterbuch schreiben.

Was sich da letztendlich in Bezug auf die Performance am besten macht, hängt von vielen Faktoren ab. Da würde ich ohne reale Zahlen nix vermuten wollen. Also am besten erst einmal eine Lösung nehmen die keine offensichtlichen Probleme mit Laufzeit oder Speicher hat und verständlich ist.
Alles klar, das probiere ich einfach aus.
Antworten