Daten abfragen

  1. Alle Datensätze eines Autors auswählen:

     7def select_all_records_from_author(cursor, author):
 8    print(f"All books from {author}:")
 9    sql = "SELECT * FROM books WHERE author=?"
10    cursor.execute(sql, [author])
11    for row in cursor.execute("SELECT * FROM books ORDER BY author"):
12        print(row)

    Für die print-Ausgabe verwenden wir durch ein vorangestelltes f ein formatiertes String-Literal oder f-string.

  2. Alle Daten auswählen und nach Autor sortieren:

    15def select_all_records_sorted_by_author(cursor):
16    print("Listing of all books sorted by author:")
17    for row in cursor.execute("SELECT * FROM books ORDER BY author"):
18        print(row)

  3. Alle Titel auswählen, die Python enthalten:

    21def select_using_like(cursor, text):
22    print(f"All books with {text} in the title:")
23    sql = f"""
24    SELECT * FROM books
25    WHERE title LIKE '%{text}%'"""
26    cursor.execute(sql)
27    print(cursor.fetchall())

  4. Schließlich können die Daten abgefragt werden mit:

    30select_all_records_from_author(cursor, author="Veit Schiele")
31select_all_records_sorted_by_author(cursor)
32select_using_like(cursor, text="Python")

    >>> import create_db
>>> import create_data_from_csv
>>> import query_data
All books from Veit Schiele:
('Python basics', 'en', 'Veit Schiele', 'BSD-3-Clause', '2021-10-28')
('Jupyter Tutorial', 'en', 'Veit Schiele', 'BSD-3-Clause', '2019-06-27')
('Jupyter Tutorial', 'de', 'Veit Schiele', 'BSD-3-Clause', '2020-10-26')
('PyViz Tutorial', 'en', 'Veit Schiele', 'BSD-3-Clause', '2020-04-13')
Listing of all books sorted by author:
('Python basics', 'en', 'Veit Schiele', 'BSD-3-Clause', '2021-10-28')
('Jupyter Tutorial', 'en', 'Veit Schiele', 'BSD-3-Clause', '2019-06-27')
('Jupyter Tutorial', 'de', 'Veit Schiele', 'BSD-3-Clause', '2020-10-26')
('PyViz Tutorial', 'en', 'Veit Schiele', 'BSD-3-Clause', '2020-04-13')
All books with Python in the title:
[('Python basics', 'en', 'Veit Schiele', 'BSD-3-Clause', '2021-10-28')]