Query data¶
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 anf
.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)
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())
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')]