SQLite CRUD operations

1. SQLite CRUD operations

The SQLite CRUD operations include four important operations:

• Create
• Retrieve
• Update
• Delete

1. Create

This operation involves inserting a new row in a SQlite database table.  To create a new row, follow the following simple guidelines:

import sqlite3

db=sqlite3.connect(‘studentdb’)

qry=”insert into Studentsmarks (name, age, marks) values(‘Ranjha’, 20, 50);”

try:

    cur=db.cursor()

    cur.execute(qry)

    db.commit()

    print (“Marks added successfully“)

except:

    print (“error in operation”)

    db.rollback()

db.close()

1. Retrieve

This operation involves retrieving a particular row or the entire database table information using the fetchone () and fetchall () methods respectively.

To fetch a single query:

import sqlite3

db=sqlite3.connect(‘studentdb’)

sql=”SELECT * from Studentsmarks;”

cur=db.cursor()

cur.execute(sql)

while True:

    record=cur.fetchone()

    if record==None:

        break    print (record)

db.close()

To fetch all database queries:

1. Update

This operation involves altering the information in the database table.  Using this operation you can be able to update any row or the entire table information. This operation works with conditions. For example, in the following code, we are going to update the query where 

import sqlite3

db=sqlite3.connect(‘studentdb’)

qry=”update Studentsmarks set age=? where name=?;”

try:

    cur=db.cursor()

    cur.execute(qry, (19,’Shamir’))

    db.commit()

    print(“record updated successfully”)

except:

    print(“error in operation”)    db.rollback()

db.close()

1. Delete

This operation allows you to drop a particular entry in the database table. You can drop a particular row or drop the entire table. For instance, in the following code, we are going to drop the query where

import sqlite3

db=sqlite3.connect(‘test.db’)

qry=”DELETE from student where name=?;”

try:

    cur=db.cursor()

    cur.execute(qry, (‘Bill’,))

    db.commit()

    print(“student deleted successfully”)

except:

    print(“error in operation”)

    db.rollback()

db.close()