It's possible to write a substantial book on the SELECT statement alone. We'll skip all but the most fundamental features of SELECT. Our purpose is to cover just enough SQL to store and retrieve objects from a database.

In most of the previous examples, we've created a cursor to execute SQL statements. For DDL and other DML statements, the presence or absence of a cursor doesn't matter very much. A nonstandard shortcut that eliminates the explicit creation of the cursor and greatly simplifies SQL programming.

For a query, however, the cursor is essential for retrieving the rows from the database. A cursor object maintains the query state so that multiple rows can be fetched. When a cursor is closed, any internal buffers or locks are released. To locate a blog by title, we can start with something as simple as the following code:

SELECT * FROM blog WHERE title=?

After executing the SQL query, we need to fetch the resulting collection of row objects. Even when we're expecting one row as a response, in the SQL world, everything is a collection. Generally, every result set from a SELECT query looks like a table with rows and columns defined by the SELECT statement.

In this case, using SELECT * means we've avoided enumerating the expected result columns. This might lead to a large number of columns being retrieved. Using an explicit cursor, we'd execute this as follows:

query_blog_by_title = """
    SELECT * FROM blog WHERE title=?
"""
with closing(database.cursor()) as blog_cursor:
    blog_cursor.execute(query_blog_by_title, ("2013-2014 Travel",))
    for blog in blog_cursor.fetchall():
        print("Blog", blog)

This follows the previous pattern of creating a cursor and executing the SQL query statement with a bound value. The fetchall() method of a cursor will retrieve all of the result rows. This query will process all blogs with the same title.

Here's a common optimization for doing this using the SQLite shortcuts:

query_blog_by_title= """ 
    SELECT * FROM blog WHERE title=? 
""" 
cursor = database.execute(
    query_blog_by_title, ("2013-2014 Travel",))
for blog in cursor: 
    print(blog[0], blog[1]) 

We've bound the requested blog title to the "?" parameter in the SELECT statement. The result of the execute() function will be a cursor object. When used as an iterable, the cursor will yield all the rows in the result set, and close the cursor when the iteration is complete.

This shortened form can be handy for queries or simple transactions. For more complex transactions, like a cascading delete, then an explicit cursor with a commit() is required to assure the transaction is either executed completely or not at all. We'll look at the semantics of SQL transactions next.