A database hosts an application's data so software can store, retrieve, and modify records. SQLite provides a lightweight local option that uses the computer's disk directly and requires virtually no setup. section 14.3
A task-management application uses its database to:
- Insert one record with INSERT INTO, execute, and placeholders whose count matches the record fields. section 14.3.3
- Insert multiple records with executemany and a list of formatted records. section 14.3.3
- Update a selected record with UPDATE and a WHERE clause; check that its task ID still exists before changing it. section 14.3.4
Saving a task record
A database connection can execute an INSERT statement inside a connection context, passing the task's formatted fields separately from the SQL statement. section 14.3.3
Listing 14.7 Saving a record to the SQLite database
def save_to_db(self):
"""Save the record to the database
"""
if app_db == TaskierDBOption.DB_CSV.value:
# operations when the database is the CSV file
pass
else:
with self.con as con:
cursor = con.cursor()
sql_stmt = f"INSERT INTO task VALUES (?, ?, ?, ?, ?, ?);"
cursor.execute(sql_stmt, self._formatted_db_record())
Inspect the focused SQLite implementation for saving one formatted task record with placeholders. section 14.3.3
Listing 14.8 Saving multiple records to the SQLite database
task0 = Task.task_from_form_entry("Laundry", "Wash clothes", 3)
task1 = Task.task_from_form_entry("Homework", "Math and physics", 5)
task2 = Task.task_from_form_entry("Museum", "Egypt things", 4)
with Task.con as con:
cursor = con.cursor()
tasks = [task0, task1, task2]
formatted_records = [task._formatted_db_record() for task in tasks]
sql_stmt = f"INSERT INTO task VALUES (?, ?, ?, ?, ?, ?);"
cursor.executemany(sql_stmt, formatted_records)
Compare the batch version, which formats several tasks and saves them with executemany. section 14.3.3
def update_in_db(self):
"""Update the record in the database
"""
if app_db == TaskierDBOption.DB_CSV.value:
# operations when the database is the CSV file
pass
else:
with self.con as con:
cursor = con.cursor()
count_sql = f"SELECT COUNT(*) FROM task WHERE
task_id = {self.task_id!r}"
row_count = cursor.execute(count_sql).fetchone()[0] #1
if row_count > 0:
sql_stmt = f"UPDATE task SET task_id = ?, title = ?,
desc = ?, urgency = ?, status = ?, completion_note = ?
WHERE task_id = {self.task_id!r}"
cursor.execute(sql_stmt, self._formatted_db_record())
else:
raise TaskierError("The task appears to be
removed already!")
Inspect the guarded update pattern that counts matching records, applies UPDATE selectively, and raises an error when the task was removed. section 14.3.4
SQLite suits prototyping because it needs little configuration. Production deployments can move to larger databases, while SQLite's limited data types require dates and Boolean values to use strings or integers as workarounds. section 14.3.6
