# 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 November 19, 2026

By Gregor Dzierzon

## 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:

```python
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())
```

```text
[(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:

```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:

```python
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)
```

```text
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:

```python
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)
```

```text
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:

```text
[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

- [CREATE TABLE](https://www.ask-codey.com/glossary/create-table)
- [INSERT](https://www.ask-codey.com/glossary/insert)
- [NOT NULL Constraint](https://www.ask-codey.com/glossary/not-null-constraint)
- [DEFAULT Clause](https://www.ask-codey.com/glossary/default-clause)
- [CHECK Constraint](https://www.ask-codey.com/glossary/check-constraint)
- [NULL](https://www.ask-codey.com/glossary/null-database)

## Related

- [What a Database Is, and Why SQLite](https://www.ask-codey.com/lessons/fastapi-sqlite-what-a-database-is)
- [Reading Rows: SELECT, WHERE and ORDER BY](https://www.ask-codey.com/lessons/fastapi-sql-select)
- [Placeholders, and Why SQL Is Never Built from Strings](https://www.ask-codey.com/lessons/fastapi-sql-placeholders-and-injection)

Source: https://www.ask-codey.com/lessons/fastapi-sql-create-and-insert
