Python Academy · Lesson

Insert, Select, Update, Delete

Run CRUD operations.

Insert, Select, Update, Delete is a free Python Academy lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Python Academy learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

CRUD Operations

The four basic database actions are Create, Read, Update, Delete (CRUD). In SQL these map to INSERT, SELECT, UPDATE, and DELETE.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
print('Ready for CRUD')
conn.close()

Inserting a Row

Use INSERT INTO to add a row. List the columns, then the matching values.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
print('Inserted Alice')
conn.close()

Reading All Rows

SELECT * reads every column. Use fetchall() to get a list of all rows as tuples.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
cur.execute("INSERT INTO users (name, age) VALUES ('Bob', 25)")
cur.execute('SELECT * FROM users')
print(cur.fetchall())
conn.close()

Fetching One Row

fetchone() returns a single row, or None when there are no more rows. It is handy when you expect exactly one result.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cur.execute("INSERT INTO users (name) VALUES ('Alice')")
cur.execute('SELECT * FROM users')
print(cur.fetchone())
conn.close()

Selecting Specific Columns

Instead of *, name the columns you want. This is faster and clearer.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
cur.execute('SELECT name FROM users')
print(cur.fetchall())
conn.close()

Filtering with WHERE

The WHERE clause limits which rows are affected or returned.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
cur.execute("INSERT INTO users (name, age) VALUES ('Bob', 25)")
cur.execute('SELECT name FROM users WHERE age > 28')
print(cur.fetchall())
conn.close()

Updating Rows

UPDATE changes existing data. Always add a WHERE clause, or every row gets updated.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
cur.execute("UPDATE users SET age = 31 WHERE name = 'Alice'")
cur.execute('SELECT age FROM users')
print(cur.fetchone())
conn.close()

Deleting Rows

DELETE FROM removes rows. Like UPDATE, it needs a WHERE clause to target specific rows.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cur.execute("INSERT INTO users (name) VALUES ('Alice')")
cur.execute("INSERT INTO users (name) VALUES ('Bob')")
cur.execute("DELETE FROM users WHERE name = 'Bob'")
cur.execute('SELECT * FROM users')
print(cur.fetchall())
conn.close()

Counting Affected Rows

After UPDATE or DELETE, cursor.rowcount tells you how many rows changed.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cur.execute("INSERT INTO users (name) VALUES ('Alice')")
cur.execute("INSERT INTO users (name) VALUES ('Bob')")
cur.execute('DELETE FROM users')
print('Deleted rows:', cur.rowcount)
conn.close()

The lastrowid

After an INSERT, cursor.lastrowid gives the auto-generated primary key of the new row.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT)')
cur.execute("INSERT INTO users (name) VALUES ('Alice')")
print('New id:', cur.lastrowid)
conn.close()

Ordering Results

Add ORDER BY to sort. Use ASC for ascending (default) or DESC for descending order.

import sqlite3
conn = sqlite3.connect(':memory:')
cur = conn.cursor()
cur.execute('CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT, age INTEGER)')
cur.execute("INSERT INTO users (name, age) VALUES ('Alice', 30)")
cur.execute("INSERT INTO users (name, age) VALUES ('Bob', 25)")
cur.execute('SELECT name FROM users ORDER BY age DESC')
print(cur.fetchall())
conn.close()

Quick Check

Test your CRUD knowledge.

Recap

You practiced full CRUD with SQLite.

INSERT INTO adds rows; lastrowid gives the new id
SELECT reads with fetchone() or fetchall()
UPDATE and DELETE change rows, always with WHERE
ORDER BY sorts the results

Frequently asked questions

Is the “Insert, Select, Update, Delete” lesson free?

Yes — the full text of “Insert, Select, Update, Delete” is free to read here on the web, and the Python Academy course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Python Academy course, upgrade to CoddyKit PRO.

What will I learn in “Insert, Select, Update, Delete”?

Run CRUD operations. You practise Python Academy with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Python Academy?

No prior experience is required. Python Academy on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Insert, Select, Update, Delete” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Python Academy lesson?

Yes. Every Python Academy lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to Python Academy