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')]