This type of application is referred to using the CRUD acronym, which stands for Create, Read, Update, and Delete, and is easily mapped into the SQL statements INSERT, SELECT, UPDATE, and DELETE. We will now take a look at how to implement each operation using the sqlite3.Connection class.

INSERT statements add new records to a table, specifying the column names with the corresponding values. If you omit the column names, the column order will be used.

When you create a table in SQLite, it adds, by default, a column called rowid and automatically assigns a unique value to identify each row. Since we usually need it for subsequent operations, we retrieve it with the lastrowid attribute available in the Cursor class:

sql = "INSERT INTO my_table (col1, col2, col3) VALUES (?, ?, ?)"
with connection:
    cursor = connection.cursor()
    cursor.execute(sql, (value1, value2, value3))
    rowid = cursor.lastrowid

SELECT statements retrieve the values of one or more columns from the records of a table. Optionally, we can add a WHERE clause to filter the records to be retrieved. This is useful to efficiently implement searches and pagination, but we will omit this functionality in our sample application:

sql = "SELECT rowid, col1, col2, col3 FROM my_table"
for row in connection.execute(sql):
    # do something with row

UPDATE statements modify the value of one or more columns from the records in a table. Typically, we add a WHERE clause to update only the rows that match the given criteria—here, we could use rowid if we want to update a specific record:

sql = "UPDATE my_table SET col1 = ?, col2 = ?, col3 = ? 
WHERE rowid = ?"
with connection:
    connection.execute(sql, (value1, value2, value3, rowid))

Finally, DELETE statements remove one or more records from a table. It is even more important to add the WHERE clause in these statements, because if we omit it, the statement will delete all the rows in the table:

sql = "DELETE FROM my_table WHERE rowid = ?"
with connection:
    connection.execute(sql, (rowid,))