Tables and Rows: CREATE TABLE and INSERT

CREATE TABLE IF NOT EXISTS gives each column a type and rules such as NOT NULL, DEFAULT and CHECK that refuse bad rows, and INSERT adds rows with ? placeholders, one at a time with execute or many with executemany, with lastrowid giving the new row's id.

Apprentice · SQLite · SQL · Database · Updated

By

After this lesson you can

  • Create a table with CREATE TABLE IF NOT EXISTS, choosing column types and the rules NOT NULL, DEFAULT and CHECK
  • Add rows with INSERT and ? placeholders, add many with executemany, and read the new row's id from lastrowid

Key takeaways

  • CREATE TABLE IF NOT EXISTS makes a table only when it isn't there yet, so an app can run it every time it starts.
  • Each column gets a type, INTEGER, REAL or TEXT, and SQLite keeps true and false as 1 and 0.
  • NOT NULL refuses a missing value, DEFAULT fills in a value the INSERT left out, and CHECK refuses a row that breaks its rule.
  • INSERT names the columns it fills and passes the values separately, one ? placeholder for each, in the same order.
  • A cursor's lastrowid is the id SQLite gave the new row, and executemany adds a whole list of rows with one statement.

Every time an app saves something new, a sign-up, an order, a review, it adds a row to a table. Before the first row goes in, someone decides what the table holds and what it refuses. This lesson does both for Fern & Fig's catalog: it makes the plants table properly and fills it.

A table that takes anything

The table from What a Database Is, and Why SQLite had three columns and no rules. It takes whatever it's given:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE plants (id INTEGER PRIMARY KEY, name TEXT, price REAL)")
conn.execute("INSERT INTO plants (name, price) VALUES (?, ?)", (None, -5))
conn.execute("INSERT INTO plants (name, price) VALUES (?, ?)", ("Peperomia", "a lot"))
print(conn.execute("SELECT * FROM plants").fetchall())
[(1, None, -5.0), (2, 'Peperomia', 'a lot')]

A plant with no name at minus five dollars, and a price that's a sentence. None went in as NULL, SQL's word for "no value here", the way None is Python's. A table can refuse rows like these, if it's told what to refuse.

CREATE TABLE, column by column

Here is the catalog's real table, written as SQL:

CREATE TABLE IF NOT EXISTS plants (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    light TEXT NOT NULL,
    water TEXT NOT NULL,
    price REAL NOT NULL CHECK (price > 0),
    pet_safe INTEGER NOT NULL DEFAULT 0,
    care_tip TEXT
);

CREATE TABLE names the table, then lists its columns in brackets, separated by commas. Each column is a name, a type and any rules:

Part What it means
IF NOT EXISTS Make the table only if it isn't there yet, so an app can run this every time it starts
INTEGER, REAL, TEXT A whole number, a number with a decimal point, or text
INTEGER PRIMARY KEY The id: SQLite numbers new rows 1, 2, 3 by itself
NOT NULL The column must have a value
DEFAULT 0 The value to use when an insert leaves the column out
CHECK (price > 0) A rule every row must pass

SQLite has no true-or-false type, so pet_safe keeps 1 for yes and 0 for no. care_tip has no rules, so a plant can have no tip, and it holds NULL.

One thing about SQLite is unusual: a column's type is a preference, not a rule. Hand a REAL column the text "12.50" and it stores the number 12.5, but hand it "a lot" and it keeps the text. Your Python code checks the types before data gets this far (Module 2's models do exactly that), and the table's rules catch what's left.

INSERT with ? placeholders

An INSERT names the columns it fills, then gives one ? for each value. The values travel separately, in a tuple, in the same order:

import sqlite3

TABLE = """
CREATE TABLE IF NOT EXISTS plants (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    light TEXT NOT NULL,
    water TEXT NOT NULL,
    price REAL NOT NULL CHECK (price > 0),
    pet_safe INTEGER NOT NULL DEFAULT 0,
    care_tip TEXT
)
"""

conn = sqlite3.connect(":memory:")
conn.execute(TABLE)
cursor = conn.execute(
    "INSERT INTO plants (name, light, water, price, pet_safe) VALUES (?, ?, ?, ?, ?)",
    ("Spider Plant", "bright", "weekly", 12.5, 1),
)
print("New plant's id:", cursor.lastrowid)

new_plants = [
    ("Snake Plant", "low", "monthly", 24.0),
    ("Golden Pothos", "low", "weekly", 15.0),
    ("Grandma's Fern", "medium", "weekly", 18.0),
]
conn.executemany("INSERT INTO plants (name, light, water, price) VALUES (?, ?, ?, ?)", new_plants)
conn.commit()

for row in conn.execute("SELECT id, name, price, pet_safe, care_tip FROM plants"):
    print(row)
New plant's id: 1
(1, 'Spider Plant', 12.5, 1, None)
(2, 'Snake Plant', 24.0, 0, None)
(3, 'Golden Pothos', 15.0, 0, None)
(4, "Grandma's Fern", 18.0, 0, None)
  • cursor.lastrowid is the id SQLite gave the row that was added. An API sends it back, so the app knows where the new plant lives.
  • executemany runs one INSERT for every tuple in a list.
  • The last three plants left out pet_safe and care_tip, so they got the DEFAULT, 0, and NULL.
  • "Grandma's Fern" went in whole, apostrophe and all, because the value never became part of the SQL. Placeholders, and Why SQL Is Never Built from Strings shows why that matters so much.
  • A for loop over a cursor gets one row at a time.

When a rule says no

When a row breaks a rule, execute raises sqlite3.IntegrityError, and the row isn't added. Here a smaller table refuses two tries before it takes the third:

import sqlite3

conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE plants (id INTEGER PRIMARY KEY, name TEXT NOT NULL, price REAL NOT NULL CHECK (price > 0))")

attempts = [(None, 14.0), ("Peperomia", -14.0), ("Peperomia", 14.0)]
for name, price in attempts:
    try:
        conn.execute("INSERT INTO plants (name, price) VALUES (?, ?)", (name, price))
        print("Added", name)
    except sqlite3.IntegrityError as error:
        print("Refused:", error)
Refused: NOT NULL constraint failed: plants.name
Refused: CHECK constraint failed: price > 0
Added Peperomia

The message names the rule and the column. In an API, a refused row means either a bug in your code or bad data in the request, and either way the bad data never reaches the table.

Do it: make the catalog's table and fill it

Fern & Fig is moving its plants into the database. The connection is open and its database is empty.

  • conn: an open connection to an empty database.
  • plants: a list of plants to add, each a tuple of a name, a light level and a price, such as ("Boston Fern", "medium", 18.0).

Your job: write the function add_plants(conn, plants).

  • Make the table plants if it isn't there yet, with four columns: id INTEGER PRIMARY KEY, name TEXT NOT NULL, light TEXT NOT NULL and price REAL NOT NULL.
  • Add each plant, in order, with an INSERT that uses ? placeholders.
  • Return a list of the new rows' ids, in the same order.
  • A plant whose name is None is refused by the table: let the sqlite3.IntegrityError go up to the caller. Don't catch it.

Worked through: on an empty database, add_plants(conn, [("Boston Fern", "medium", 18.0), ("Parlor Palm", "low", 29.0), ("Spider Plant", "bright", 12.5)]) makes the table, adds the three plants as rows 1, 2 and 3, and returns:

[1, 2, 3]

Call it again on the same connection with one more plant, and the table is already there, so the new plant becomes row 4 and the function returns [4].

Practiced live on the lesson page: write the Python right there and check it.

Check yourself

2 questions to try on the lesson page.

So what

A table with the right types and rules refuses bad data on its own, and an INSERT with placeholders puts every value in safely, so what's saved is what you meant to save.

Key terms

Related