SQLAlchemy

[mechanicalStore]

Hallo,

ich wühle mich gerade durch die SQLAlchemy Doku, soweit das möglich ist, die Themen sind ja doch sehr vielfältig. In einem früheren Thread viewtopic.php?t=57279&sid=cd5706e5874fa ... 6a8839a786 kam von __blackjack__ dieser Codeschnipsel:

Code: Alles auswählen

    @classmethod
    def exists(cls, session, name):
        return session.execute(
            select(cls).filter_by(name=name).exists().select()
        ).scalar()

Auf meinem jetzigen 'wissens'stand anhand der Doku, gelingt es mir immer noch nicht, diesen Schnipsel vollständig zu verstehen. Mir ist nicht klar, wo das .exists() beschrieben wird (zumindest nicht im Zusammenhang mit select() ) und warum exists().select(). das .select() nochmal angehängt wird. Wichtig ist mir, das nicht nur einfach zu benutzen, sondern zu verstehen, warum es so ist wie es ist.

Ein weiteres Thema ist hier https://docs.sqlalchemy.org/en/20/orm/m ... tyles.html. Dort ist dieser Codeschnipsel beschrieben:

Code: Alles auswählen

from sqlalchemy import Integer, String, ForeignKey
from sqlalchemy.orm import DeclarativeBase
from sqlalchemy.orm import Mapped
from sqlalchemy.orm import mapped_column


# declarative base class
class Base(DeclarativeBase):
    pass


# an example mapping using the base
class User(Base):
    __tablename__ = "user"

    id: Mapped[int] = mapped_column(primary_key=True)
    name: Mapped[str]
    fullname: Mapped[str] = mapped_column(String(30))
    nickname: Mapped[Optional[str]]

Während die rechte Seite mapped_column() Objekte der Tabellenspalten mit passenden Typen erzeugt, ist mir die linke Seite, bzw. generell die Mapped Klasse nicht klar. Verwirrend auch, dass bei name überhaupt nichts auf der rechten Seite zugewiesen wird.

Ganz sicher habe ich vieles überlesen bzw. nicht verstanden. Die Doku ist schon echt mächtig. Ein kleiner Anschwung, um weiter zu kommen, wäre sehr hilfreich.

Gruß und Danke vorab für Antworten.

[__blackjack__]

@mechanicalStore: Bei der Dokumentation von select() steht, dass da ein neues `Select`-Objekt erstellt wird. Und bei der Dokumentation von Select steht die Liste mit den Methoden und da ist `exists()` dabei.

Das zusätzliche `select()` braucht man weil das `Exists`-Objekt für eine EXISTS-Subquery steht, also keine eigenständige SQL-Abfrage ist. Das hat jetzt nicht direkt was mit SQLAlchemy zu tun, das ist SQL.

Hier mal das generierte SQL, da sieht man, dass das erste keine syntaktisch vollständige SQL-Abfrage ist:

Code: Alles auswählen

In [88]: print(select(Project).filter_by(name="Name").exists())
EXISTS (SELECT project.id, project.name, project.netplan, project.description 
FROM project 
WHERE project.name = :name_1)

In [89]: print(select(Project).filter_by(name="Name").exists().select())
SELECT EXISTS (SELECT project.id, project.name, project.netplan, project.description 
FROM project 
WHERE project.name = :name_1) AS anon_1

Zum Beispiel aus der Dokumentation: Bei `name` und `nickname` reicht die Typannotation aus, darum wird da nichts zugewiesen. Bei `id` reicht ``Mapped[int]`` nicht aus wenn das der Primärschlüssel sein soll, und bei `fullname` wird der Datentyp für die Datenbank präzisiert, also nicht einfach nur eine Zeichenkette, sondern eine die auf 30 Zeichen begrenzt ist.

[mechanicalStore]

Hallo __blackjack__

Danke für Deine Antwort.

@mechanicalStore: Bei der Dokumentation von select() steht, dass da ein neues `Select`-Objekt erstellt wird. Und bei der Dokumentation von Select steht die Liste mit den Methoden und da ist `exists()` dabei.

Ja und bei exists() wird ein EXISTS-Objekt(?) erzeugt, da steht dann Represent an EXISTS clause.. Was ist das für ein Datentyp? Auch wieder ein Object oder ein boolescher Wert?

Das zusätzliche `select()` braucht man weil das `Exists`-Objekt für eine EXISTS-Subquery steht, also keine eigenständige SQL-Abfrage ist. Das hat jetzt nicht direkt was mit SQLAlchemy zu tun, das ist SQL.

Ok, dann muss ich da nochmal nachlesen, das Thema Subquery erschien mir sehr komplex. Offenbar aber unumgänglich, wenn ich mehrere Tabellen hierarchisch hintereinander aufbaue? Also von Tabelle a zu Tabelle b one to many Beziehung, von Tabelle b zu c one to many Beziehung, etc... Geht das nur mit Subquery, oder lässt sich das mit mehreren Joins in eine Abfrage packen?

Zum Beispiel aus der Dokumentation: Bei `name` und `nickname` reicht die Typannotation aus, darum wird da nichts zugewiesen. Bei `id` reicht ``Mapped[int]`` nicht aus wenn das der Primärschlüssel sein soll, und bei `fullname` wird der Datentyp für die Datenbank präzisiert, also nicht einfach nur eine Zeichenkette, sondern eine die auf 30 Zeichen begrenzt ist.

Achso, ich hatte das so verstanden, dass die rechte Seite grundsätzlich die Tabellenstruktur beschreibt, während die linke Seite die Python-Struktur beschreibt?!

Gruß

[__blackjack__]

exists() erzeugt ein Exists-Objekt. Boole'sche Werte sind auch Objekte. Aber da man darauf `select()` aufrufen kann, ist das natürlich kein Wahrheitswert.

Subqueries haben per se nichts mit Beziehungen zwischen Tabellen zu tun, man kann auch sinnvolle Subqueries haben/brauchen wenn man Abfragen auf einer einzigen Tabelle macht. Umgekehrt braucht man keine Subquery um mehrere Tabellen miteinander zu verknüpfen.

Bei der ”Deklaration” beschreiben die Klassenattribute die Beziehung zwischen Python und der Datenbanktabelle. Eine klare Trennung in ”Seiten” gibt es da nicht wirklich. Selbst der Name beschreibt ja sowohl den Namen auf Python- als auch auf Datenbankseite, falls man da bei `mapped_column()` keinen anderen Namen angibt. Und auch die Typannotation ist erst einmal die Grundlage für Python und Datenbank, bis man das mit `mapped_column()` vielleicht verfeinert.

[mechanicalStore]

Ok, danke. Folgendes ist mir noch unklar (stark gekürzt):

Code: Alles auswählen

from sqlalchemy import DATETIME. Float
...
...
measuring_date: Mapped[DATETIME] = mapped_column(DATETIME)
measured_z: Mapped[Float] = mapped_column(Float)

Wenn ich die jeweils rechte Seite weg lasse, erhalte ich (jeweils entsprechend):

ArgumentError: The type provided inside the 'measuring_date' attribute Mapped annotation is the SQLAlchemy type <class 'sqlalchemy.sql.sqltypes.DATETIME'>. Expected a Python type instead

Funktioniert nur, wenn ich beide Seiten angebe, jedoch ist mir nicht klar, warum.

[__deets__]

Weil das direkt hinter dem Doppelpunkt keine Zuweisung, sondern eine Typ-Deklaration ist. DIE kannst du weglassen. NICHT die Sachen mit und rechts vom Gleichzeichen.

[mechanicalStore]

Vielleicht habe ich mit meinem "rechts" und "links" was durcheinander gebracht;

Im Folgenden funktionieren beide Zeilen:

Code: Alles auswählen

p_type: Mapped[str] = mapped_column(String(5))
p_type: Mapped[str]

Im Folgenden erzeugt die zweite Zeile die o.g. Fehlermeldung

Code: Alles auswählen

m_date: Mapped[DATETIME] = mapped_column(DATETIME)
m_date: Mapped[DATETIME]

Im Folgenden erzeugt die zweite Zeile ebenfalls die o.g. Fehlermeldung

Code: Alles auswählen

nominal_x: Mapped[Float] = mapped_column(Float)
nominal_x: Mapped[Float]

Werden die verschiedenen Typen (str, DATETIME, Float) also unterschiedlich gehandelt?

[__blackjack__]

@mechanicalStore: Offensichtlich prüft SQLAlchemy hier das man bei der Typannotation keine SQLALchemy-Typen angibt. Das macht ja auch keinen Sinn, weil die Attribute auf dem Objekt später ja niemals Werte von diesen Typen sind, denn diese Typen beschreiben ja die Datenbankdatentypen und nicht welche Typen dann im Programm verwendet werden.

[snafu]

@mechanicalStore:
Links muss ein Python-Datentyp stehen. Also ``float``, so wie du es bei ``str`` ja auch gemacht hast. Den Typ für Datumsangaben findest du im ``datetime``-Modul. Und rechts sollte dann überhaupt keine Typangabe stehen. Da gehören nur Einschränkungen (Contraints) hin wie die maximale Größe, ``primary_key``, ``nullable``, usw.

EDIT:
Wobei ein ``Optional`` auf der linken Seite bereits ein implizites ``nullable`` darstellt.

Letztlich ist man halt auf den Type-Annotation-Zug aufgesprungen, weil das ja im Moment "alle" so machen. Ich finde es nur mittelmäßig gelungen. Konsistenter wäre es, wenn auch die Constraints komplett links stehen dürften bzw. man idealerweise gar keine Zuweisung mehr bräuchte.

[__blackjack__]

@snafu: Man muss die Typannotationen ja nicht machen, man kann das auch alles beim `mapped_column()` angeben. Und da sollten schon Typangaben stehen wenn man den Datenbanktypen angeben will oder muss. Python `str` kann man ja auf verschiedene Datentypen auf SQL-Seite abbilden. String oder Text, oder TEXT, VARCHAR, CHAR.

[mechanicalStore]

Hallo Zusammen,

danke für die Hinweise, insbesondere, dass die rechte Seite nur Restriktionen darstellt und das Mapping links stattfindet. Damit hat es sich geklärt. Bei nochmaligem Nachlesen bin ich noch auf folgendes gestoßen (da ich nicht wusste, woher die Datentypen kommen):

https://docs.sqlalchemy.org/en/20/orm/d ... n-type-map

Angewendet auf meinen aktuellen Fall sieht das dann (stark gekürzt) so aus (und funktioniert auch):

Code: Alles auswählen

from datetime import datetime
...
from sqlalchemy import (
    create_engine,
    ForeignKey,
    Integer,
    String,
    TIMESTAMP,
    Float
)

class Base(DeclarativeBase):
    type_annotation_map = {
        datetime: TIMESTAMP(timezone=True),
        float: Float
    }

class Measuring(Base):
	measuring_date: Mapped[datetime]
	...

class MeasuringPiece(Base):
	point_upper_tolerance: Mapped[float]
	...
	

[mechanicalStore]

Hallo,

wenn ich hier https://docs.sqlalchemy.org/en/20/orm/q ... elect.html diesen CodeSchnipsel betrachte:

Code: Alles auswählen

address_alias_1 = aliased(Address)
address_alias_2 = aliased(Address)
stmt = (
    select(User)
    .join(address_alias_1, User.addresses)
    .where(address_alias_1.email_address == "patrick@aol.com")
    .join(address_alias_2, User.addresses)
    .where(address_alias_2.email_address == "patrick@gmail.com")
)
print(stmt)

dann ist die Frage, was wäre, wenn ich z.B. nicht nur nach 2, sondern z.B. nach 10 eMail-Adressen suchen wollte. Ich finde in der Doku (bisher) nichts, wie man das a) dynamisch gestalten könnte, und b) müsste man dann tatsächlich 10x .where(..) anhängen? Da gibts doch sicherlich einen optimaleren Weg (dictionary?), oder? Dazu kommt, dass ich mich auf dem, was schon in der Tabelle enthalten ist, dynamisch beziehen will?!

Dazu ein Beispiel; In (Unwort) Excel, wo eine Tabelle z.B. aus 3 Spalten besteht und man da einen Auto-Filter drauf setzt, dann kann man ja abhängig vom Inhalt der Spalten mehrere unterschiedliche Elemente zum Filtern heranziehen. In der zweiten und dritten Spalte käme dasselbe Verfahren additiv dazu und man hat dann eine sehr individuelle 'Abfrage', eben sehr dynamisch. Wie lässt sich das in SQLAlchemy verwirklichen, wenn jede Excel-Spalte aus dem Beispiel einer Tabelle entspricht, bzw. wo finde ich einen Hinweis dazu?

Gruß und Danke für Antworten

[__deets__]

Dann schreibst du eine Schleife, wie bei allen Dingen, die mehrfach, aber unbekannt oft vorkommen.

Code: Alles auswählen

statement = select(User)
for email in emails:
   alias = aliased(Address)
   statement = statement.join(alias, User.addresses).where(alias.email_address == email)

[__blackjack__]

Das ist unter anderem was ich an SQLAlchemy mag, das man Abfragen dynamisch mit Code erweitern kann, ohne da auf Zeichenketten herumoperieren zu müssen. Insbesondere weil das ja nicht immer nur erweitern am Ende ist, sondern durch zusätzliche Bedingungen auch neue JOINs dazu kommen können die ”in der Mitte” vom der SQL-Anweisung eingefügt werden. Man hat da bei SQLAlchemy mehr Freiheiten und kann sicherer sein, dass da am Ende gültiges SQL bei heraus kommt, im Gegensatz zum eigenen neu erfinden dieses Rads.

[mechanicalStore]

Ohje, Brett vorm Kopf…!
Danke für die Aufklärung.

Grüße

[mechanicalStore]

Hallo,
da es thematisch passt, nutze ich diesen Thread für eine weitere Frage.

Code: Alles auswählen

stmt = select(parent.name, children.name, grandchildren.name).join_from(parent, children).join(children, grandchildren)

# Variante 1
    for row in session.scalars(stmt).all():
        print(row)

# Variante 2
    for row in session.execute(stmt):
        print(row)

Laut https://docs.sqlalchemy.org/en/20/orm/q ... attributes sind bei Variante 2 die Instanzen in row-Objekten gekapselt, während sie Variante 1 als Instanzen in einer Liste gesammelt vorliegen. Das Ergebnis ansich müsste also das Gleiche sein. Dennoch habe ich bei Variante 1 (offenbar in Verbindung mit join_from?!) nur noch eine Liste mit Strings, die lediglich parent.name enthalten. Es müssten aber doch zumindest alle Felder enthalten sein?!

Andere Frage, ich möchte bei select nach order_by(element_name) aufsteigend sortieren. Das Attribut ist ein String, enthält z.B. folgende Elemente "G1", "G2", "G3", G20", "G21", "G30", usw.. Reihenfolge ist dann G1, G2, G20, G21, G3, G30, usw...
Es soll aber nach den Integerwerten sortiert werden. Mein Workaround ist, diesen zu extrahieren und in ein extra Feld zu speichern, nachdem dann sortiert wird. Oder kann man on the fly eine Funktion str2int einschieben?

[__blackjack__]

@mechanicalStore: Du hast da einen entscheidenden Unterschied zum Beispiel in der Dokumentation: Die fragen dort nicht mehrere Werte pro ”row” ab, sondern einen kompletten Benutzer. Und dieser *eine* Wert wird bei `execute()` noch mal in ein `Row`-Objekt verpackt, bei `scalars()` aber nicht. Da wird das ”ausgepackt” bevor man die Ergebnisse bekommt. Das macht bei Deinem Beispiel aber keinen Sinn weil Du ja mehrere Werte pro “row“ anfragst.

Das mit dem sortieren würde ich auf Python-Seite machen.