Query data

  1. Select all records from an author:

     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    print(cursor.fetchall())  # or use fetchone()

    For the print output, we use a formatted string literal or f-string by prefixing it with an f.

  2. Select all records sorted by author:

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

  3. Select titles containing Python:

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

  4. Finally, the data can be queried with:

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

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