Need to keep a task app’s data between runs without setting up a database server? SQLite stores the data on the computer’s disk and requires virtually no setup. Python’s built-in `sqlite3` module provides the connection and SQL operations needed to use it. section 14.3 section 14.3.1
Start by connecting to a database path. If no database exists there, `sqlite3.connect()` creates it. Create a table through a cursor, then use the connection for later queries and changes. In a task app, a class attribute can hold the connection shared by `Task` instances. section 14.3.1
@classmethod
def create_sqlite_database(cls):
"""Create the SQLite database
"""
with sqlite3.connect(TaskierDBOption.DB_SQLITE.value) as con:
cls.con = con #1
cursor = con.cursor()
cursor.execute("CREATE TABLE task (task_id text, title text,
desc text, urgency integer, status integer, completion_note text);")
The database-creation method connects to `tasks.sqlite`, creates a cursor, and executes `CREATE TABLE task` with text and integer fields. The `with` statement manages the operation’s commit. section 14.3.1
Once the table exists, the same connection supports the app’s everyday operations:
- Query records with `SELECT`; `fetchall()` returns rows as tuples that can be converted into `Task` objects. Optional status, urgency, and text filters narrow the results. section 14.3.2
- Update an edited record with `UPDATE ... SET ... WHERE ...`. Check that its task ID still exists, and keep the `WHERE` clause: without it, the update would affect every record. section 14.3.4
- Remove a record with `DELETE FROM task WHERE task_id = ...`. The condition identifies the task to delete. section 14.3.5
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!")
The update method checks for a matching task, uses placeholders for the new field values, and raises an application error if the record has already been removed. section 14.3.4
SQLite is a practical choice for prototyping and a more formal database choice than a CSV file. The task example uses text and integer fields; it represents values such as dates or Boolean states with strings or integers. A larger database may be appropriate when moving the application to production. section 14.3.6 section 14.5
