SQLITE3 CRUD OPERATION – todo app

Table of Contents

1. Introduction
2. How-to-create-new-database?
3. How-to-CRUD-SQLite3
   • CRUD-Create-or-insert-data
   • CRUD-Review-data
   • CRUD-Update-data
   • CRUD-Delete-data

Introduction

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.

before we jump into CRUD operation we need to go a few process from the beginning which I will show to you as below

• Create DATABASE
• Create Table for DATABASE
• Operate CRUD with data to the Table

You can see the flow as this

DATABASE -> Table -> data

How-to-create-new-database?

Currently I am in my current directory:

$ /home/ahmad/tutorial/sqlite-tutorial/

Inside root folder got a database .db extension file name lang.db

Now I am going to show how to create a new database.

There are 2 ways to create new database which is:
1st – In terminal type

$ sqlite3 <database-name>

2nd – In SQLite3 CLI

> .open <database-name>

Both will do the same thing. If its exist it will open and connect to the current database while if it isn’t available the commands above will create a new database.

$ sqlite3 todo-list

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> .database
main: /home/ahmad/tutorial/sqlite3-tutorial/todo-list.db

Let’s create a table into todo-list.db database named as todo

sqlite> CREATE TABLE todo (
…> id INTEGER NOT NULL PRIMARY KEY,
…> tasks TEXT NOT NULL
…> );

Preview any table inside todo-list.db database

sqlite> .table
todo

See what schema for todo table that was created.

sqlite> .schema todo
CREATE TABLE todo (
id INTEGER NOT NULL PRIMARY KEY,
taks TEXT NOT NULL
);

Command pragma table_info() for table todo.

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

How-to-CRUD-SQLite3

CRUD stands for Create, Read, Update and Delete. Usually when you start coding dealing programming language and database you will start with CRUD as basics.

This is a step by step for CRUD SQLITE3 tutorial will guide you a simple database to get your hands dirty learning code with SQLITE3 as database.

CRUD-Create-or-insert-data

Insert single line of data

INSERT INTO todo (task)
VALUES ('Cook pancake for breakfast');

Insert multiple line of data

INSERT INTO todo (task)
VALUES ('Cook pancake for breakfast'),
('Cook bryani for lunch and dinner'),
('Do Calisthenics workout!'),
('Watch worldcup Morocco VS Spain'),
('Watch UFC- Diaz VS Connor'),
('Programming Flask-Python');

CRUD-Review-data

Display data

SELECT * FROM todo;

To make a neat well structured presentation

.header on

and

.mode column

Now let’s see the again the table of data. Here is the difference before and after

CRUD-Update-data

Update data

UPDATE todo SET task = 'Programming Flask-Python with   SQLite3' WHERE id=4;

CRUD-Delete-data

Delete data from the table in database.

DELETE FROM table_name
WHERE search_condition;

so to delete programming Flask-Python

DELETE FROM todo
WHERE id=1

To delete according to specific condition such as ‘cook’ and related

DELETE FROM todo WHERE tasks like '%cook%'