simple CLI todo manager with sqlite3 database storing problem

Question

I am currently trying to make a command line todo manager that will allow the user to input a task(s), remove it and list the task(s) out. From what I tried visualizing it didn't do as I thought it would, it's my first time using sqlite3.

What I am trying to achieve:

• Storing the task(s) in the database which will automatically add an incrementing ID to it.

Example:

python todo.py -add do the laundry on Sunday

[in the database]
Id   Task
1    do the laundry on Sunday

My code.

import sqlite3
import argparse


def parse_args():
    desc = 'Todo manager for storing and removing tasks'
    parser = argparse.ArgumentParser(description=desc)
    parser.add_argument("-a", "--add", "-add", help="To add a new item to the list",
                        type=str, nargs="+")
    parser.add_argument("-r", "-remove", "--remove", help="To remove an item from the list",
                        type=int)
    parser.add_argument("-l", "-list", "--list", help="displays the tasks or task in the list",
                        nargs="*")
    args = parser.parse_args()
    return args


@staticmethod
def dict_factory(cursor, row):
    d = {}
    for idx, col in enumerate(cursor.description):
        d[col[0]] = row[idx]
    return d


def get_todo_list():
    database_connection.row_factory = dict_factory
    cursor = database_connection.cursor()
    cursor.execute("select rowid, * FROM todo_list")
    return cursor.fetchall()


def add_to_todo_list(num,task):
    cursor = database_connection.cursor()
    cursor.execute("INSERT INTO todo_list VALUES (?)", (str(task),))
    database_connection.commit()


def remove_from_todo_list(rowid):
    cursor = database_connection.cursor()
    cursor.execute("DELETE FROM todo_list WHERE rowid = ?", (rowid,))
    database_connection.commit()


if __name__ == '__main__':
    commands = parse_args()
    # Creating table for database using sqlite
    database_connection = sqlite3.connect('todo_list.db')
    cursor = database_connection.cursor()
    cursor.execute('''CREATE TABLE if not exists todo_list(
                  description TEXT);''')
    database_connection.commit()

    if commands.add:
        # Stops accepting tasks when there is a blank task as input. 
        if not commands.add == ' ':
           add_to_todo_list(commands.add)
    elif commands.remove:
        remove_from_todo_list(commands.remove)
    elif commands.list:
        get_todo_list()

However, my database is not accepting any values when I am trying to store data. By putting Id as Id INTEGER PRIMARY KEY when creating the table i.e.

cursor.execute('''CREATE TABLE if not exists todo_list(
                      Id INTEGER PRIMARY KEY
                      description TEXT);''')

Will the Id increment as I add data to the database?

Answer 1

sqlite3.InterfaceError: Error binding parameter 1 - probably unsupported type.

Your inputs from argparse are coming in as str, yet you defined your column ID as an INTEGER in your db. Fix is:

cursor.execute("INSERT INTO todo_list VALUES (?,?)", (int(num), task,))

---

Storing the task(s) in the database which will automatically add an incrementing ID to it.

According to the sqlite docs here, defining a INTEGER PRIMARYKEY will auto-increment. Simply pass a null value to it, and sqlite takes care of the rest for you.

---

You have a few issues on your code when it comes to displaying and adding the tasks. First, initializing the DB:

cursor.execute(
        """CREATE TABLE if not exists todo_list(
                      id INTEGER PRIMARY KEY,
                      description TEXT);"""
    )

How you had it before your post edit was fine. Then, the add_to_todo_list:

def add_to_todo_list(task):
    cursor = database_connection.cursor()
    cursor.execute("INSERT INTO todo_list VALUES (?,?)", (None, str(task)))
    database_connection.commit()

Notice the removal of num from the functions input, and the passing of None for the column ID. Within the get_todo_list() you can fetch it more easily as so:

def get_todo_list():
    cursor = database_connection.cursor()
    cursor.execute("select * FROM todo_list")
    return cursor.fetchall()

A fix is also needed in the way you parse your args; for commands.list you need to do the following:

elif commands.list is not None:
    print(get_todo_list())

This is since commands.list will be a [] when you do app.py -list, which Python evaluates to False (empty lists are falsey). You also ought to print the contents of the function to terminal -- so don't forget that. With the edits above I can do on my terminal:

python test.py -add Random Task!
python test.py -add Hello World!

python test.py -list

[(1, "['Random', 'Task!']"), (2, "['Hello', 'World!']")]