SQLite3-Python Todo app convert tuple to dictionary

Introduction

In previous tutorial todo app using Python-SQLite3 using SQL-query list of tuples to query read and find task inside database. This time we will level up by using a list of Dictionary.

Below is the script that will be modify for read-task and find_task functions.

# main-read.py

# from connect import db_connection
import sqlite3

# connect to the database and returns a connection object
conn = sqlite3.connect('todo.db')

with conn:
  c = conn.cursor()

def create_table():
    # initializes the database and creates 'todo' table if it doesn't exist
    c.execute("""
    CREATE TABLE IF NOT EXISTS todo (
        id INTEGER PRIMARY KEY,
        task TEXT NOT NULL);
        """)
    print('Table created!')

def create_task():
    new_task = input("What is your new task: ")
    c.execute("INSERT INTO todo (task) VALUES\
            (?)",(new_task,))
    # commit function will enable the change will take place
    conn.commit()

def read_task():
    c.execute("SELECT * FROM todo")

    rows = c.fetchall()

    for row in rows:
        # print(f"{row[0]}- {row[1]}")
        print(f"{row['id']}- {row['task']}")
    # conn.close()
    print("")

def find_task():
    # parameter query `like`
    task_input = input("Search by task: ")
    task_input = task_input.lower()

    # method 1 - completed
    # param_q = c.execute("SELECT * FROM todo WHERE task LIKE ?", ('%'+task_input+'%',))

    # method 2 - completed
    sql_query = "SELECT * FROM todo WHERE task LIKE ?"
    param_q = c.execute(sql_query, ('%'+task_input+'%',))

    # loop param_q
    for row in param_q:
        print(f"{row[0]}- {row[1]}")

def main():
    # create_table()

    # greeting
    print("*****Todo app*****")

    while True:

        print("Please choose one only either: ")
        print("(i) Insert task")
        print("(r) Read/Preview all task")
        print("(s) Search task")
        print("(q) Quit from the system")
        print("\n")

        prompt_user = (input("Choose only one: "))
        prompt_user = prompt_user.lower()
        if prompt_user.lower() == "q":
            print("Exit")
            break
        elif prompt_user == "i":
            create_task()
        elif prompt_user == "r":
            read_task()
        elif prompt_user == "s":
            find_task()
        else:
            print("Eror! Please put valid input. \n")

main()

If you look read_task function we loop rows for row[0] which is for id and row[1] for task.

        print(f"{row[0]}- {row[1]}")

while find_task function we loop rows for row[0] which is for id and row[1] for task.

        print(f"{row[0]}- {row[1]}")

According to Python official website it says that

By default, sqlite3 represents each row as a tuple. If a tuple does not suit your need, you can use the sqlite3.Row class or a custom row_factory

It is recommended that …

While row_factory exists as an attribute both on the Cursor and the Connection, and it is recommended to set Connection.row_factory, so all cursors created from the connection will use the same row factory.

Let’s see run Sqlite3 as CLI

$ sqlite3 todo.db

Next, let’s open todo.db. You will be prompt into

SQLite version 3.27.2 2019-02-25 16:06:06
Enter ".help" for usage hints.

Now let see the database connection.

sqlite> .databases
main: /home/ahmad/tutorial/sqlite3-tutorial/todo.db

Preview any table inside todo.db database

sqlite> .tables
todo

Command pragma table_info() for table todo.

sqlite> pragma table_info('todo');
0|id|INTEGER|1||1
1|taks|TEXT|1||0

Let’s explain this:

• First row it starts with 0 follows by id that separated by |
• Next or second row start with 1 follows by task while separated also like an above |
• Both numbers start for each row is a tuple that were a default value in SQLite
• It’s derived from select statement to query data from table todo

How-to-Convert-Tuple-to-Dictionary

If you are new to Sqlite3 and haven’t yet install Sqlite you can read how to install SQLITE3 CLI an acronym for Command-Line Interface. This tutorial will guide you on how to operate basic CRUD operation using SQLITE3 database.

To change for both read_task and find_task functions we can use sqlite.Row class.

# connect to the database and returns a connection object
conn = sqlite3.connect('todo.db')

conn.row_factory = sqlite3.Row
c = conn.cursor()

Next, we can replace from row[0] and row[1] into it’s field name to row['id'] and row['task'] for both functions which is read_task and find_task.

Once done, run it and see whether it’s working.

Here is the final source-code.

# main-read.py

# from connect import db_connection
import sqlite3

# connect to the database and returns a connection object
conn = sqlite3.connect('todo.db')

# with conn:
#   c = conn.cursor()
conn.row_factory = sqlite3.Row
c = conn.cursor()

def create_table():
    # initializes the database and creates 'todo' table if it doesn't exist
    c.execute("""
    CREATE TABLE IF NOT EXISTS todo (
        id INTEGER PRIMARY KEY,
        task TEXT NOT NULL);
        """)
    print('Table created!')

def create_task():
    new_task = input("What is your new task: ")
    c.execute("INSERT INTO todo (task) VALUES\
            (?)",(new_task,))
    # commit function will enable the change will take place
    conn.commit()

def read_task():
    c.execute("SELECT * FROM todo")

    rows = c.fetchall()

    for row in rows:
        # print(f"{row[0]}- {row[1]}")
        print(f"{row['id']}- {row['task']}")
    # conn.close()
    print("")

def find_task():
    # parameter query `like`
    task_input = input("Search by task: ")
    task_input = task_input.lower()

    # method 1 - completed
    # param_q = c.execute("SELECT * FROM todo WHERE task LIKE ?", ('%'+task_input+'%',))

    # method 2 - completed
    sql_query = "SELECT * FROM todo WHERE task LIKE ?"
    param_q = c.execute(sql_query, ('%'+task_input+'%',))

    # loop param_q
    for row in param_q:
        # print(f"{row[0]}- {row[1]}")
        print(f"{row['id']}- {row['task']}")

def main():
    # create_table()

    # greeting
    print("*****Todo app*****")

    while True:

        print("Please choose one only either: ")
        print("(i) Insert task")
        print("(r) Read/Preview all task")
        print("(s) Search task")
        print("(q) Quit from the system")
        print("\n")

        prompt_user = (input("Choose only one: "))
        prompt_user = prompt_user.lower()
        if prompt_user.lower() == "q":
            print("Exit")
            break
        elif prompt_user == "i":
            create_task()
        elif prompt_user == "r":
            read_task()
        elif prompt_user == "s":
            find_task()
        else:
            print("Eror! Please put valid input. \n")

main()