SELECT Funktion

Installation und Anwendung von Datenbankschnittstellen wie SQLite, PostgreSQL, MariaDB/MySQL, der DB-API 2.0 und sonstigen Datenbanksystemen.
Antworten
Bindl
User
Beiträge: 70
Registriert: Donnerstag 27. Oktober 2016, 11:48

Hi zusammen,

ich möchte eine allgemein gültige Fkt für den Selectbefehl schreiben mit max 2 Bedinungen.

Code: Alles auswählen

# SELECT Funktion
def selection(a,b,x,d,var1,var2,all):
    c = g.con.cursor()
    if var2 is None:
        c.execute("SELECT %s FROM %s WHERE %s = %s", (a, b, x, var1,))
        if all is None:
            row = c.fetchone()
        else:
            row = c.fetchall()
    else:
        c.execute("SELECT %s FROM %s WHERE %s = %s AND %s = %s",(a, b, x, var1, d, var2,))
        if all is None:
            row = c.fetchone()
        else:
            row = c.fetchall()
    c.close()
    return row
In meiner Login Fkt mache ich dann folgenden excute Befehl:

Code: Alles auswählen

row = selection("user_hashed_password", "user", "user_name", None, request.form['logname'], None, None)
Jetzt bekomme ich folgende Fehlermeldung:
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''user' WHERE 'user_name' = 'bakn'' at line 1

Wie muss ich meine Fkt oder den execute Befehl ändern?
Mir fällt dazu nichts ein.
An der Datenbank kann ich persönlich nichts ändern, die ist vorgegeben.

Vielen Dank für die Hilfe im voraus
BlackJack

@Bindl: Über die Platzhalter kann man nur Werte übermitteln. Spalten und Tabellennamen sind keine Werte.

Spätestens hier sehe ich den Zeitpunkt kein selbst geschriebenes SQL als Zeichenketten mehr zu verwenden, sondern eine Abstraktion wie SQLAlchemy zu verwenden. Wenn schon nicht das ORM, dann doch wenigstens die Möglichkeit programmatisch Abfragen zu erstellen.

Das könnte dann beispielsweise so aussehen:

Code: Alles auswählen

    cursor = g.connection.execute(
        sa.select([user_table.c.user_hashed_password])
            .where(user_table.c.user_name == 'someone')
    )
    
    row = cursor.fetchone()
    if row:
        print(row.user_hashed_password)
    cursor.close()

    # oder in diesem Fall auch:

    hashed_password = cursor.scalar()
    print(hashed_password)

    # oder wenn man alle Ergebnisse will:

    hashed_passwords = cursor.fetchall()

    # beziehungsweise

    for row in cursor:
        print(row.user_hashed_password)
Und wenn man SQLAlchemy verwendet, könnte man auch gleich das ORM verwenden:

Code: Alles auswählen

    hashed_password = (
        g.session.query(User.user_hashed_password)
            .filter(User.user_name == 'someone')
            .scalar()
    )
    print(hashed_password)
Und wenn man das schon verwendet, kann man auch gleich die Namen wenigstens auf den Objekten besser wählen:

Code: Alles auswählen

    hashed_password = (
        g.session.query(User.hashed_password)
            .filter(User.name == 'someone')
            .scalar()
    )
    print(hashed_password)
Wobei ich persönlich ja immer erst einmal einfachen Code schreibe und erst bei Bedarf optimiere. Also hier zum Beispiel ruhig den gesamten Benutzer abfrage.

Code: Alles auswählen

    user = g.session.query(User).filter(User.name == 'someone').scalar()
    print(user.hashed_password)
Alle bekommt man mit `all()` statt `scalar()`, und speziell in diesem Falle würde sich auch `one()` anbieten das sicherstellt, das es *genau* *einen* Datensatz gibt.
Bindl
User
Beiträge: 70
Registriert: Donnerstag 27. Oktober 2016, 11:48

Hi,

schade das es nicht so machbar ist. Fand die Idee recht gut.
Leider kenn ich mich mit SQLAlchemy null aus und werde das bestimmt nicht so gut anwenden können, da ich nur noch wenige Tage Zeit habe.
Die Zeit mir das anzugucken wird dann nicht reichen.

Echt schade, hätte das gerne benutzt.
Aber danke für die Hilfe
Bindl
User
Beiträge: 70
Registriert: Donnerstag 27. Oktober 2016, 11:48

Hi,

das lässt mir jetzt keine Ruhe.

Kann man das wirklich nicht so machen wie ich es versuchen?

Vielleicht kann man für die "Nichtwerte" wie den Spaltenname einen anderen Platzhalter als %s angeben.
Gibt es da keine Möglichkeit?

Merci
__deets__
User
Beiträge: 14493
Registriert: Mittwoch 14. Oktober 2015, 14:29

Du kannst die Spalten und Tabellennamen vorher String.format angeben. Aber SQL ändert sich nicht dynamisch. Warum denkst du du brauchst so etwas? Und falls es sich bei den Werten um werte dreht die aus einem webformular kommen, dann ist dieses Vorgehen grob fahrlässig! Denn damit kann ein Benutzer deine Datenbank beliebig angreifen. Und hidden input fields sind immer noch Eingabe Felder....

Und wieso übergibst du nicht einfach das fertige SQL Statement? So viele kann es davon ja nicht geben, die dann auch noch mit deinen restlichen Parameter Vorstelluneegen zusammenspielen.
Bindl
User
Beiträge: 70
Registriert: Donnerstag 27. Oktober 2016, 11:48

Hi,

danke für die Hinweise, ich würde es aber wirklich gerne veruschen.

Wie sollte der string.format Code dann aussehen?

Ich habe

Code: Alles auswählen

c.execute("SELECT %s FROM %s WHERE %s = %s", (a, b, x, var1,))
Wie und wo muss ich nun string.format anwenden?

Ich kenne .format bisher nur aus print Befehlen. z.B.:

Code: Alles auswählen

a = 12
print("Peter ist {0} Jahre alt.".format(a))
__deets__
User
Beiträge: 14493
Registriert: Mittwoch 14. Oktober 2015, 14:29

Schau noch mal genau auf dein Beispiel - was hat denn das format nun genau mit dem print zu tun?
Bindl
User
Beiträge: 70
Registriert: Donnerstag 27. Oktober 2016, 11:48

Hi,

muss es dann so aussehen?

Code: Alles auswählen

c.execute("SELECT {0} FROM {1} WHERE {2} = %s".format(a,b,x), (var1,))
__deets__
User
Beiträge: 14493
Registriert: Mittwoch 14. Oktober 2015, 14:29

Hast du das einfach mal ausprobiert, statt hier zu fragen? Dein Python-Interpreter kann dir solche Fragen deutlich schneller beantworten, als du sie hier stellen & beantwortet bekommst.
Bindl
User
Beiträge: 70
Registriert: Donnerstag 27. Oktober 2016, 11:48

Hi,
habe es probiert und es klappt nicht.

Ich habe es auch mal so eingegeben

Code: Alles auswählen

c.execute("SELECT %s FROM %s WHERE %s = %s", .format(a,b,c,var1,))
Aber zeigt mir Pycharm schon direkt an das was nicht stimmt
.format soll ja das mitgegebene so formatieren wie es benötigt wird
Ich komme aber nicht weiter.
Kann mir jmd zeigen wie man das hier anwnedet?
Vllt auch sagen wieso man, wie gezeigt, anwendet um für die Zukunft es selbst zu können.

Wäre ich echt froh
__deets__
User
Beiträge: 14493
Registriert: Mittwoch 14. Oktober 2015, 14:29

Programmieren ist nicht rate! Das sind absolute Grundlagen.

Um ein solches Problem anzugehen, muss man es in seine Teilprobleme zerlegen: du hast ein c.execute, von dem du weisst, dass es funktioniert, wenn du das richtige SQL statement eingibst. Jetzt willst du das SQL statement aendern. Es gibt absolut keinen Grund, das mit dem *ausfuehren* des Statements in c.execute zusammenzumanschen.

Zieh also die Erzeugung des SQL statements *vor* diesen Code, und pruefe zB durch ein print, dass du das erwartete SQL erzeugt hast.
Bindl
User
Beiträge: 70
Registriert: Donnerstag 27. Oktober 2016, 11:48

Hi,

ich bin "nur" angehender Wirtschaftsinformatiker und all das hier müssen wir uns in diesem Semester selbst erarbeiten.
Dies was ich nun mache ist ein Zusatz was meinen ganzen Quellecode, wie ich finde, hübscher machen.
Wir bekommen das leider in diesem Semester in keiner Vorlesung beigebracht, aber ich habe trotzdem ein Interesse dran.
Ich bitte dies bei der Hilfe zu bedenken.

Wenn ich deinen Hinweis richtig verstanden habe muss ich erst die der Funktion mitgegebenen Variablen SQL fähig formatieren. Die Zumnindest die Variablen die die Tabelle und Spalten angeben.
Dann kann ich den execute Befehl ausführen.
Nur weiß ich echt nicht wie ich dieses formatieren machen soll und auch das viele googeln bringt mich leider nicht weiter.

Code: Alles auswählen

# SELECT Funktion
def selection(a, b, x, d, var1, var2, all):
    c = g.con.cursor()
    if var2 is None:
        # Hier muss die Formatierung für a, b und c stehen
        c.execute("SELECT %s FROM %s WHERE %s = %s", (a, b, c, var1,))
        if all is None:
            row = c.fetchone()
        else:
            row = c.fetchall()
    else:
        # Hier muss die Formatierung für a, b, x, d stehen
        c.execute("SELECT %s FROM %s WHERE %s = %s AND %s = %s",(a, b, x, var1, d, var2,))
        if all is None:
            row = c.fetchone()
        else:
            row = c.fetchall()
    c.close()
    return row
Kannst du mir vllt für a zeigen wie ich das machen muss?
Sirius3
User
Beiträge: 17711
Registriert: Sonntag 21. Oktober 2012, 17:20

Bindl hat geschrieben:muss es dann so aussehen?

Code: Alles auswählen

c.execute("SELECT {0} FROM {1} WHERE {2} = %s".format(a,b,x), (var1,))
Ja, so kann es aussehen, wenn die Werte a,b und x wohldefiniert und sicher sind.
Bindl hat geschrieben:Hi,
habe es probiert und es klappt nicht.
Was bedeutet "es klappt nicht"? Hast Du Dir den Select-String schonmal ausgeben lassen?
Bindl
User
Beiträge: 70
Registriert: Donnerstag 27. Oktober 2016, 11:48

Hi,

also ich habe nun folgende Fkt

Code: Alles auswählen

# SELECT Funktion
def selection1(a, b, x, d, var1, var2, all):
    c = g.con.cursor()
    if var2 is None:
        print(a)
        print(type(a))
        # Hier muss die Formatierung für a, b und c stehen
        c.execute("SELECT %{0} FROM {1} WHERE {2} = %s".format(a, b, x), (var1,))
        if all is None:
            row = c.fetchone()
        else:
            row = c.fetchall()
    else:
        # Hier muss die Formatierung für a, b, x, d stehen
        c.execute("SELECT {0} FROM {1} WHERE {x} = %s AND {3} = %s".format(a, b, x, d), (var1, var2,))
        if all is None:
            row = c.fetchone()
        else:
            row = c.fetchall()
    c.close()
    return row
Das ist der execute Befehl

Code: Alles auswählen

row = selection1('user_hashed_password', 'user', 'user_name', None, request.form['logname'], None, None)
a ist logischerweise user.hashed_password als string

Was a dann nach .format hat kann ich nicht prüfen da es ab dem execute Befehl eine Fehlermeldung gibt.
mysql.connector.errors.ProgrammingError: 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%user_hashed_password FROM user WHERE user_name = 'bakn'' at line 1

Nun bin ich wieder ahnungslos

Wie muss a,b,x denn vordefiniert sein und wie macht man das vordefinieren?
Sirius3
User
Beiträge: 17711
Registriert: Sonntag 21. Oktober 2012, 17:20

@Bindl: `%user_hashed_password` ist ja auch kein gültiger Feldname. Du solltest exakt lesen, was Du schreibst. Zeichen für Zeichen. Computer sind in der Hinsicht sehr kleinlich.
BlackJack

@Bindl: Ich würde ja gerne noch was zu der Funktion im ersten Beitrag sagen. Als erstes: Die Namen sind so alle schlecht gewählt, bis auf `row`, wobei das an zwei Stellen der passende Name ist, und an zwei anderen Stellen der falsche Name ist. Denn der Name wird manchmal an *eine* Datenbankzeile gebunden, und manchmal an *mehrere* Datenbankzeilen.

`selection()` beschreibt keine Tätigkeit und damit auch nicht was die Funktion macht.

`all` ist der Name einer (nützlichen) eingebauten Funktion. Den sollte man nicht an etwas anderes binden. Das führt zu Irritationen beim Leser. Das Argument ist ein Flag, da sollte man also nicht `None` und *irgendwas anderes* übergeben, sondern `True` und `False`.

Schliessen von Ressourcen würde ich mit ``with`` und `contextlib.closing()` erledigen (sofern das Objekt selbst kein Kontextmanager ist). Wenn man das mit dem Cursor macht, dann wird man den `row`-Namen los, weil man einfach direkt ``return`` verwenden kann.

Die beiden Zweige von dem ``if``/``else`` enden mit genau dem gleichen Code. Der sollte da also nicht zweimal stehen, sondern einmal hinter dem ``if``/``else``.

Mit vernünftigeren Namen und ``with`` könnte das dann so aussehen:

Code: Alles auswählen

def select(
    result_column_name,
    table_name,
    column_name_1,
    column_name_2,
    value_1,
    value_2,
    fetch_all,
):
    with closing(g.connection.cursor()) as cursor:
        if value_2 is None:
            cursor.execute(
                'SELECT %s FROM %s WHERE %s = %s',
                (result_column_name, table_name, column_name_1, value_1,)
            )
        else:
            cursor.execute(
                'SELECT %s FROM %s WHERE %s = %s AND %s = %s',
                (
                    result_column_name,
                    table_name,
                    column_name_1,
                    value_1,
                    column_name_2,
                    value_2,
                )
            )
        return cursor.fetchall() if fetch_all else cursor.fetchone()
So wirklich wesentlich unterscheiden sich die beiden Zweige in der Funktion ja nicht und nummerierte Namen sind in der Regel auch keine gute Wahl. Sie deuten oft darauf hin, dass man keine einzelnen Namen und Werte, sondern eigentlich eine Datenstruktur verwenden sollte. Oft eine Liste. Und auch das wäre hier sinnvoll: Eine Liste mit Paaren von Spaltennamen und Werten aus denen dann die Anfrage aufgebaut wird (ungetestet):

Code: Alles auswählen

def select(result_column_name, table_name, names_and_values, fetch_all):
    sql = 'SELECT %s FROM %s'

    query_values = list()
    for item in names_and_values:
        query_values.extend(item)

    if query_values:
        sql += ' WHERE ' + ' AND '.join(['%s = %s'] * len(names_and_values))

    with closing(g.connection.cursor()) as cursor:
        cursor.execute(sql, query_values)
        return cursor.fetchall() if fetch_all else cursor.fetchone()
Ein Aufruf würde dann so aussehen:

Code: Alles auswählen

    row = select(
        'user_hashed_password',
        'user',
        [('user_name', request.form['logname'])],
        False
    )
Und man kann dann nicht nur ein oder zwei Kriterien übegerben, sondern 0 bis beliebig viele.

Achtung: Die Funktion hat natürlich immer noch das Problem mit den SQL-Namen und -Werten von der Ausgangsfunktion!

Was immer noch unpraktisch ist (neben der Tatsache das die Funktion nicht funktioniert ;-)) ist die unterschiedliche Struktur des Rückgabewerts in Abhängigkeit des letzten Arguments. Für solche APIs werden Dich andere Programmierer irgendwann anfangen zu hassen. Und da man selbst der ”andere Programmierer” ist, wenn man den Code eine Weile nicht angefasst hat, kann man das auch nicht mit „ist ja nur für mich“ abtun. Man könnte leicht zwei Funktionen schreiben und den gemeinsamen Teil in eine nicht-öffentliche Funktion auslagern:

Code: Alles auswählen

def _select(result_column_name, table_name, names_and_values, method_name):
    sql = 'SELECT %s FROM %s'

    query_values = list()
    for item in names_and_values:
        query_values.extend(item)

    if query_values:
        sql += ' WHERE ' + ' AND '.join(['%s = %s'] * len(names_and_values))

    with closing(g.connection.cursor()) as cursor:
        cursor.execute(sql, query_values)
        return getattr(cursor, method_name)()


def select_one(result_column_name, table_name, names_and_values):
    return _select(result_column_name, table_name, names_and_values, 'fetchone')


def select_all(result_column_name, table_name, names_and_values):
    return _select(result_column_name, table_name, names_and_values, 'fetchall')
Um noch mal auf SQLAlchemy zurück zu kommen: Das Datei hochladen in einem anderen Thema von Dir könnte man statt so:

Code: Alles auswählen

            cursor = g.connection.cursor()
            cursor.execute(
                'INSERT INTO file (file_file, file_name, file_author,'
                ' file_upload, file_type, data_size)'
                ' VALUES (%s, %s, %s, NOW(), %s, %s)',
                (
                    data,
                    filename,
                    session['username'],
                    os.path.splitext(filename)[1],
                    len(data),
                )
            )
            g.connection.commit()
            cursor.close()
dann so schreiben:

Code: Alles auswählen

            g.session.add(
                File(
                    content=data,
                    name=filename,
                    author=session['username'],
                    type=os.path.splitext(filename)[1],
                    size=len(data),
                )
            )
            g.session.commit()
Alle PDF-Dateien eines Benutzer abzufragen, ginge beispielsweise so:

Code: Alles auswählen

    pdf_files = (
        g.session.query(File)
            .filter(File.user_name == User.name, File.type == 'pdf')
            .order_by(File.name)
            .all()
    )
An alle Dateien eines Benutzers kommt man, wenn man die Fremdschlüsselbeziehungen definiert hat, über das Benutzerobjekt ganz einfach so:

Code: Alles auswählen

    files = user.files
Und man kann den Datenmodell-Objekten natürlich auch Methoden verpassen, damit man die Abfragen nicht überall im Code verteilt. Beispielsweise eine Methode die von einem Benutzer alle Dateien eines bestimmten Typs abfragen kann:

Code: Alles auswählen

class User(Base):
    # ...

    def get_files(self, file_type=None):
        query = self.files_query  # <- Bei Fremdschlüsselbeziehung definiert.
        if file_type is not None:
            query = query.filter(File.type == file_type)
        return query.all()
Damit kann man dann woanders im Code einfach das hier schreiben wenn man ein Benutzer-Objekt hat:

Code: Alles auswählen

    pdf_files = user.get_files('pdf')
Antworten