Skip to content
Related Articles

Related Articles

Improve Article

Python SQLite – CRUD Operations

  • Last Updated : 21 Apr, 2021

In this article, we will go through the CRUD Operation using the SQLite module in Python.

CRUD Operations

The abbreviation CRUD expands to Create, Read, Update and Delete. These four are fundamental operations in a database. In the sample database, we will create it, and do some operations. Let’s discuss these operations one by one with the help of examples.

CREATE

The create command is used to create the table in database. First we will go through its syntax then understand with an example.

Syntax: CREATE TABLE table_name ( Attr1 Type1, Attr2 Type2, … , Attrn Typen ) ;

In this example, we will create a table named “gfg” with three attributes:



  1. name
  2. points
  3. accuracy

Python




# Python code to create a relation 
# using SQLite3
  
# import the sqlite3 package
import sqlite3  
  
# create a database named backup
cnt = sqlite3.connect("backup.dp")  
  
# create a table named gfg
cnt.execute('''CREATE TABLE gfg(
NAME TEXT,
POINTS INTEGER,
ACCURACY REAL);''')

Output:

INSERT

This refers to the insertion of new data into the table. Data is inserted in the form of a tuple. The number of attributes in the tuple must be equal to that defined in the relation schema while creating the table.

1. To insert attributes in the order specified in the relation schema:
Syntax: INSERT INTO tableName VALUES ( value1, value2, … valuen )
 
2.To insert attributes in the order specified in the relation schema or in a different order:
INSERT INTO tableName ( Attribute1, Attribute3, Attribute2 . . . ) VALUES ( value1, value3, value2 . . . )

The program below demonstrates the addition of three tuples to the gfg relation that was created earlier.

Python3




# Python3 Code to insert data into
# the database
  
# Insert three tuples into the gfg table
# insert in default order
cnt.execute('''INSERT INTO gfg VALUES(
'Count Inversion',20,80.5);''')
  
# insert in different order
cnt.execute('''INSERT INTO gfg(ACCURACY, POINTS, NAME) VALUES(
90.5, 15, 'Kadanes Algo');''')
  
cnt.execute('''INSERT INTO gfg(NAME, ACCURACY, POINTS) VALUES(
'REVERSE STR', 100, 5);''')
  
# commit changes to the database
cnt.commit()

Output:



READ

This refers to reading data from a database. A read statement has three clauses:

  1. SELECT: Takes as the predicate the attributes to be queried, use * for all attributes.
  2. FROM: Takes as the predicate a relation.
  3. WHERE: Takes as the predicate a condition, this is not compulsory.

After executing a read statement in python SQLite3, an iterable cursor object is returned. This can be used to print data.

Example: SELECT NAME, POINTS, ACCURACY FROM gfg WHERE ACCURACY>85;

The program below demonstrates the usage of the read statement.

Python3




# Python3 code to read data from a table
  
print('Name, Points and Accuracy from '
      'records with accuracy greater than 85')
  
cursor = cnt.execute('''SELECT * FROM gfg WHERE ACCURACY>85;''')
  
# print data using the cursor object
for i in cursor:
    print(i[0]+"    "+str(i[1])+"   "+str(i[2]))
  
print('')  # Print new line
  
print('Name, Accuracy from '
      'records with accuracy greater than 85')
  
cursor = cnt.execute('''SELECT NAME, ACCURACY FROM
gfg WHERE ACCURACY>85;''')
  
# print data using the cursor object
for i in cursor:
    print(i[0]+"    "+str(i[1]))

Output:

UPDATE

This refers to the updating of tuple values already present in the table.

Syntax: UPDATE tableName SET Attribute1 = Value1 , Attribute2 = Value2 , . . . WHERE condition;
The WHERE clause must be included, else all records in the table will be updated.



EXAMPLE: UPDATE gfg SET POINTS=POINTS+5 WHERE POINTS<20;

The program below demonstrates the usage of the update statement.

Python3




# Python3 code to update records in a database
  
# Print records before updation
cursor = cnt.execute('''SELECT * FROM gfg''')
print('Before Updation')
for i in cursor:
    print(i[0]+"    "+str(i[1])+"    "+str(i[2]))
  
print('')  # print a newline
  
# Execute an Update statement
cnt.execute('''UPDATE gfg SET POINTS=POINTS+5 WHERE
POINTS<20;''')
  
cursor = cnt.execute('''SELECT * FROM gfg''')
print('After Updation')
for i in cursor:
    print(i[0]+"    "+str(i[1])+"    "+str(i[2]))

Output:

DELETE

This refers to the deletion of the tuple present in the table.

SYNTAX: DELETE FROM tableName WHERE condition

If WHERE clause is not used then all the records will be deleted.

EXAMPLE: DELETE FROM gfg WHERE ACCURACY>91

The program below demonstrates the usage of the delete statement.

Python3




# Python3 code to delete records from database
  
# Print records before deletion
cursor = cnt.execute('''SELECT * FROM gfg''')
print('Before Deletion')
for i in cursor:
    print(i[0]+"    "+str(i[1])+"    "+str(i[2]))
  
print('')  # print a newline
  
# Execute a delete statement
cnt.execute('''DELETE FROM gfg WHERE ACCURACY>91;''')
  
cursor = cnt.execute('''SELECT * FROM gfg''')
print('After Deletion')
for i in cursor:
    print(i[0]+"    "+str(i[1])+"    "+str(i[2]))

Output:

 Attention geek! Strengthen your foundations with the Python Programming Foundation Course and learn the basics.  

To begin with, your interview preparations Enhance your Data Structures concepts with the Python DS Course. And to begin with your Machine Learning Journey, join the Machine Learning – Basic Level Course




My Personal Notes arrow_drop_up
Recommended Articles
Page :