Namaste Coders👩‍💻

In this article, we will build a TODO project. It will handle all the CRUD (Create, Read, Update, Delete) functionalities.

We will learn how to connect OOPS programming language (Python) to the database.

What we will learn ?🤔

• Connecting to the Database
• Working with modules
• CRUD operationality

📑 Database used 👇

We will use Sqlite3 , which comes by default with Python.

📃File Structure📃

• my-todo-app.py
• todo-helper.py

my-todo-app.py : It is the main file which will consist of menu driven system, that will execute the project

todo-helper.py : It is the utility file that contains the database connectivity and all the database queries.

🙋‍♀️ Let's Connect the Database and write the queries in the utility file📰

todo-helper.py

import sqlite3

# connector to connect the database
con = sqlite3.connect("todo.db")

# cursor which will execute all the queries
cur = con.cursor()
table_name = 'todos'

We will import sqlite3 module. Then, making a connection with the todo.db database. cur is the cursor which helps in executing all the queries. todos is the table name.

We will create multiple functions handling individual query.

😎😎Creating the table😎😎

# create a table
# create table if not exists table_name (id integer primary key autoincrement, taskname text)
def create_table():
    sql = f'CREATE TABLE IF NOT EXISTS {table_name} (id integer primary key autoincrement, taskname text) '
    cur.execute(sql)

This function will be executed once, for the execution of the code for the first time.

🙌🙌Adding todo in the database 🙌🙌

# Adding todo in the table
def add_todo(todo_name):
    # insert into table_name (column_name) values (column_values)
    cur.execute("INSERT INTO " + table_name + " (taskname) VALUES (?)", [todo_name])
    print("TODO added in the database successfully!!")
    con.commit()

con.commit() command is required whenever there is a change in the database, to save the changes.

👩‍🏫👩‍🏫 Reading data from the database 👩‍🏫👩‍🏫

# Read data from the table
def read_todos():
    # select column_name1, column_name2 from table_name
    # select * from table_name
    cur.execute("SELECT * from " + table_name)

    for row in cur.fetchall():
        print(str(row[0]) + " --> ", row[1])

cur.execute() , executes all the sql queries. cur.fetchall() fetches all the data from the database, which is looped and data is printed.

👽👽 Updating the todo 👽👽

##  update the data 
def update_task(idx , updated_task ):
    # update table_name set column_name=new_value where ID=index
    cur.execute("UPDATE " +  table_name + " SET taskname = (?) WHERE id = (?)", [updated_task,idx] )
    print("Task updated successfully")
    con.commit()

updating the existing todo with the updated todo name, on the basis of unique id associated with the todo.

☠️☠️ Deleting the todo ☠️☠️

# delete the data
def delete_task(idx):
    # delete from table_name where id = index
    cur.execute("DELETE from " + table_name + " WHERE id = (?)" , [idx])
    print("Task deleted successfully!")
    con.commit()

🎗️🎗️ Closing the connection✒️✒️

#It is important to close all the connections at the end
def close_connection():
    cur.close()
    con.close()

my-todo-app.py

#importing todo helper utility file
import todo-helper

def main():
    run = 1 
    #creating table 
    todo-helper.create_table()

    #will excute infinite times until user does not exit
    while run :
        print("Press 1: To read data")
        print("Press 2: To add data")
        print("Press 3: To update data")
        print("Press 4: To delete data")
        print("Press 5: To exit")

        #choosing option from menu
        ch = input("Enter your choice: ")

        if ch == "1":
            todo-helper.read_todos()
        elif ch == "2":
            add_todo = input("Enter the new task: ")
            todo-helper.add_todo(add_todo)
        elif ch == "3":
            idx = int(input("Enter the id of todo"))
            update_task = input("Enter the updated task: ")
            todo-helper.update_task(idx, update_task)

        elif ch == "4":
            idx = int(input("Enter the id of todo"))
            todo-helper.delete_task(idx)
        elif ch == "5":
            run = 0

    #closing connection at end
    todo-helper.close_connection()

if __name__ == '__main__':
    main()

Code execution begins from main. When we execute my-todo-app.py, then it will execute as the main file and will provide a menu driven system, which allows user to perform different functionalities on the basis of choice.

The End

Do create the project and tag me👩‍🏫

I hope you enjoyed the article and had a good learning experience.

Follow for more articles and keep sharing👩

Keep coding

Python blogs

Linkedin

hashnode blogs