SELECT Basics

The Idea

A query describes the shape of the answer, not the steps to get it. SELECT lists the columns, FROM names the table, and the engine works out the rest. SELECT * is handy at a prompt and wasteful in code: every extra column crosses the wire, and tomorrow's new column silently changes your result shape.

Real-World Example

A herdsman at the morning milking copies exactly two things onto the parlour sheet: the ear tag and the litres. Breed, birth date and vaccination history stay in the big book back at the office, because carrying the whole book down to the parlour helps nobody.

The Code

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
  CREATE TABLE cow (tag TEXT, breed TEXT, litres REAL);
  INSERT INTO cow VALUES ('A17', 'Jersey', 24.5),
                         ('B03', 'Holstein', 31.0),
                         ('C22', 'Jersey', 19.5);
""")
# Ask for the two columns the parlour sheet needs, not the whole row.
for row in db.execute("SELECT tag, litres FROM cow"):
    print(row)
# ('A17', 24.5)
# ('B03', 31.0)
# ('C22', 19.5)

import sqlite3
db = sqlite3.connect(":memory:")
db.executescript("""
CREATE TABLE cow (tag TEXT, litres REAL);
INSERT INTO cow VALUES ('A17', 24.5), ('B03', 31.0);
""")
print(db.execute("SELECT tag FROM cow").fetchall())