Retrieving Specific Columns from a JOIN Query
In the previous exercise, we saw that we can use a JOIN to fetch the related rows from two tables. However, if we look at the results, we will see that it returned all the columns, thus combining both tables. This is not very concise. What about if we only want to see the emails and the related comments, and not all the data?
There is some nice shorthand code that lets us do this:
import sqlite3
with sqlite3.connect("../lesson.db") as conn:
cursor = conn.cursor()
cursor.execute("PRAGMA foreign_keys = 1")
sql = """
SELECT comments.* FROM comments \
JOIN user ON comments.user_id = user.email \
WHERE user.email='bob@example.com' \
"""
rows = cursor.execute(sql)
for...
