Hour One With SQLite Database

What does SQLite database contain?

SQLite Database has data stored in it in the form of tables.

What does a table contain?

It contains rows and columns.

A row is also called a record or tuple.

A column is also called an attribute.

Let's say we have a table called 'marks'.

What all columns can table of marks contain?

1. Year

2. Class

3. Subject

4. Marks

Four Basic Types of Operations We Do in Databases

Assuming you have a database with you:

Insert: To add or create a new record or tuple in your database.

Select: To read a records or tuples present in the database.

Update: To change an existing record or tuple in your database.

Delete: To remove or delete a record or tuple from the database.

Can be remembered using the acronym: CRUD for Create, Read, Update, Delete

Single File For Storing a Database

Note: SQLite uses a single file for storing data related a database. For example: we have two databases with us and there names are 'marks.db' and 'sharks.db'.

(base) ashish@ashish:~/Desktop/ws/sqlite_db$ ls

marks.csv marks.db sharks.db

(base) ashish@ashish:~/Desktop/ws/sqlite_db$

Creating a Database file and Creating a Table

(base) ashish@ashish:~$ sqlite3 sharks.db

# Here, sqlit3 is your application and sharks.db is the filename for that application to open.

SQLite version 3.39.3 2022-09-05 11:02:23
Enter '.help' for usage hints.

sqlite> CREATE TABLE sharks(id integer NOT NULL, name text NOT NULL, sharktype text NOT NULL, length integer NOT NULL);

INSERT INTO sharks VALUES (1, 'Sammy', 'Greenland Shark', 427);
INSERT INTO sharks VALUES (2, 'Alyoshka', 'Great White Shark', 600);
INSERT INTO sharks VALUES (3, 'Himari', 'Megaladon', 1800);

sqlite> select * from sharks;


1|Sammy|Greenland Shark|427
2|Alyoshka|Great White Shark|600
3|Himari|Megaladon|1800    

sqlite>

(base) ashish@ashish:~/Desktop/ws/sqlite_db$ sqlite3 sharks.db

SQLite version 3.39.3 2022-09-05 11:02:23

Enter '.help' for usage hints.

sqlite> .schema sharks

CREATE TABLE sharks(id integer NOT NULL, name text NOT NULL, sharktype text NOT NULL, length integer NOT NULL);

sqlite>

Viewing the Structure of a Table

Exploring a Database file

(base) ashish@ashish:~/Desktop/ws/sqlite_db$ sqlite3 sharks.db

SQLite version 3.39.3 2022-09-05 11:02:23

Enter '.help' for usage hints.

sqlite> .tables

sharks

sqlite> select * from sharks;

1|Sammy|Greenland Shark|427

2|Alyoshka|Great White Shark|600

3|Himari|Megaladon|1800

sqlite> ^Z

[3]+ Stopped sqlite3 sharks.db

(base) ashish@ashish:~/Desktop/ws/sqlite_db$ sqlite3 marks.db

SQLite version 3.39.3 2022-09-05 11:02:23

Enter '.help' for usage hints.

sqlite> .tables

marks

sqlite> select * from marks;

2023|8|english|75

2023|8|math|85

2023|8|computer|90

2022|7|english|90

2022|7|math|85

2022|7|computer|95

2023|8|physics|99

2023|8|chemistry|97

2023|8|biology|95

sqlite>

Import a CSV file

(base) ashish@ashish:~/Desktop/ws/sqlite_db$ sqlite3 marks.db
SQLite version 3.39.3 2022-09-05 11:02:23
Enter '.help' for usage hints.
sqlite>
sqlite> .tables
sqlite> .mode csv
sqlite> .import ./marks.csv marks

First line in marks.csv should contain the header with column names.

sqlite> .tables

marks

sqlite>

sqlite> select * from marks;

2023,8,english,75

2023,8,math,80

2023,8,computer,90

2022,7,english,90

2022,7,math,85

2022,7,computer,95

sqlite>

Inserting a single row

sqlite> .schema marks

CREATE TABLE IF NOT EXISTS 'marks' ('year' TEXT, 'class' TEXT, 'subject' TEXT, 'marks' TEXT);

sqlite>

sqlite> insert into marks (year, class, subject, marks) values (2023, 8, 'sanskrit', 90);

sqlite> select * from marks;

2023|8|english|75

2023|8|math|80

2023|8|computer|90

2022|7|english|90

2022|7|math|85

2022|7|computer|95

2023|8|sanskrit|90

sqlite>

Inserting multiple rows

sqlite> insert into marks (year, class, subject, marks) values (2023, 8, 'physics', 99), (2023, 8, 'chemistry', '97'), (2023, 8, 'biology', 95);

sqlite> select * from marks;

2023|8|english|75

2023|8|math|80

2023|8|computer|90

2022|7|english|90

2022|7|math|85

2022|7|computer|95

2023|8|sanskrit|90

2023|8|physics|99

2023|8|chemistry|97

2023|8|biology|95

Selection and Filtering

sqlite> select * from marks where marks > 90;

2022|7|computer|95

2023|8|physics|99

2023|8|chemistry|97

2023|8|biology|95

sqlite> select * from marks where class = 8;

2023|8|english|75

2023|8|math|80

2023|8|computer|90

2023|8|sanskrit|90

2023|8|physics|99

2023|8|chemistry|97

2023|8|biology|95

Update

sqlite> select * from marks where class = 8 and subject = 'math';

2023|8|math|80

sqlite>

sqlite> update marks set marks = marks + 5 where class = 8 and subject = 'math';

sqlite> select * from marks where class = 8 and subject = 'math';

2023|8|math|85

Deleting a record

sqlite> select count(*) from marks where subject = 'sanskrit';

1

sqlite> select * from marks where subject = 'sanskrit';

2023|8|sanskrit|90

sqlite> delete from marks where subject = 'sanskrit';

sqlite> select * from marks where subject = 'sanskrit';

sqlite> select count(*) from marks where subject = 'sanskrit';

0

sqlite>

Cursor

Row Num	0:ID	1:Name	2:SharkType	3:Length
0	1	Sammy	Greenland Shark	427
1	2	Alyoshka	Great White Shark	600
2	3	Himari	Megaladon	1800

[0][0]	[0][1]	[0][2]	[0][3]
[1][0]	[1][1]	[1][2]	[1][3]
[2][0]	[2][1]	[2][2]	[2][3]

A cursor allows us to iterate over a table.

And, a table can be viewed as a list of rows.

And, a row can be viewed as a tuple of cells.

In Code (Part 1)

import sqlite3

conn = sqlite3.connect('sharks.db')

print('Opened database successfully')

cursor = conn.execute('SELECT id, name, sharktype, length FROM sharks')

for row in cursor:

print('ID = ', row[0])

print('NAME = ', row[1])

print('SHARKTYPE = ', row[2])

print('LENGTH = ', row[3])

print('\n')

print('Operation done successfully')

conn.close()

Output

Opened database successfully

ID = 1

NAME = Sammy

SHARKTYPE = Greenland Shark

LENGTH = 427

ID = 2

NAME = Alyoshka

SHARKTYPE = Great White Shark

LENGTH = 600

ID = 3

NAME = Himari

SHARKTYPE = Megaladon

LENGTH = 1800

Operation done successfully

Tags: Technology,Database,