# How to utilize the built-in database(db-sqlite) of python for data storage and manipulation.

pip install db-sqlite3 -- run this command after opening CMD as admin to install sqlite3.

pip freeze | findstr sqlite3 -- use this command to check the version, mine shows 0.0.1 when I wrote this blog.It also ensures that you have successfully installed sqlite3.

After a successful installation let's move to Python IDLE to code our program to perform some simple tasks.

• Creation of the database.
• Creation of table.
• Check if any other table of the same name exists or not. 
• Insert some data into the table.
• Read data from the table.
• Update the data of the table. 
• Delete a particular row.

it seems too much? don't worry python makes it really simple .

Creation of database:-

Here we go, we will create a random database of any desired name just make sure that the name remains unique else it will update things in the database defined earlier.

Open Python IDLE and write this code, strictly DO NOT COPY.

import sqlite3 
conn = sqlite3.connect('movies.db') 
c = conn.cursor()

here, sqlite3.connect makes a database connection with a database named students.db NOTE: If the database already exists it will connect to it else it will make a new database.

also, we have created a cursor named c which will be used to execute SQL commands.

Creation of table:-

let's create a function to check if the table name already exists or not,

def table_exists(table_name): 
    c.execute('''SELECT count(name) FROM sqlite_master WHERE TYPE = 'table' AND name = '{}' '''.format(table_name)) 
    if c.fetchone()[0] == 1: 
        return True 
    return False

Check if any other table of the same name exists or not:- 

The code below checks if the table already exists or not, if not it creates a table with desired fields.

if not table_exists('marksheet'): 
    c.execute('''CREATE TABLE marksheet( stu_id INTEGER, stu_name TEXT, class INTEGER, subject TEXT, marks INTEGER )''')

We have done with this section now the fun starts.

Insert, read, update, delete:- 

# Function for Insertion --

def insert_marks(stu_id, stu_name, class, subject, marks): 
    c.execute(''' INSERT INTO marksheet (stu_id, stu_name, class, subject, marks) VALUES(?, ?, ?, ?, ?) ''', (stu_id, stu_name, class, subject, marks)) 
    conn.commit()

# Function to get all the rows in the table -- 

def get_marks(): 
    c.execute('''SELECT * FROM marksheet''') 
    data = [] 
    for row in c.fetchall(): 
        data.append(row) 
    return data

# Function to get rows as the stu_id --

def get_student_marks(stu_id): 
    c.execute('''SELECT * FROM marksheet WHERE stu_id = {}'''.format(stu_id)) 
    data = [] 
    for row in c.fetchall():  
        data.append(row) 
    return data

# Function to update data -- 

def update_marksheet(stu_id, update_dict): 
    valid_keys = ['stu_name', 'class', 'subject', 'marks'] 
    for key in update_dict.keys():  
        if key not in valid_keys: 
            raise Exception('Invalid field name!') 
    for key in update_dict.keys(): 
        if type(update_dict[key]) == str: 
            stmt = '''UPDATE marksheet SET {} = '{}' WHERE stu_id = {}'''.format(key, update_dict[key], stu_id) 
        else: 
            stmt = '''UPDATE marksheet SET {} = '{}' WHERE stu_id = {}'''.format(key, update_dict[key], stu_id) 
        c.execute(stmt) 
    conn.commit()

# Finally, the last function to delete a row --

def delete_stu_marks(stu_id): 
    c.execute('''DELETE FROM marksheet WHERE stu_id = {}'''.format(stu_id)) 
    conn.commit()

guess what time it is ?? it's testing time guys..!

insert_marks(1, 'Aman', 10, 'Math', 90) 
insert_marks(2, 'Rahul', 10, 'Science', 79) 
insert_marks(3, 'James', 10, 'Hindi', 58) 
insert_marks(4, 'Riya', 10, 'Physics', 73) 
insert_marks(5, 'Jack', 10, 'Math', 100)
print(get_marks())
print(get_student_marks(2))
update_marksheet(5, {'stu_name': 'Harry', 'marks': 70})
print(get_marks())
delete_stu_marks(3)
print(get_marks())

 

 

Before you go,

I hope it was easy to follow along, we have just covered the basics and didn't touch many things although this will be enough to get started.

Thanks for reading.