Python SQLite - CRUD Operations

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 ( Attr₁ Type_1, Attr₂ Type_2, … , Attr_n Type_n ) ;

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

1. name
2. points
3. accuracy

Output:

👁 Image

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.

Output:

👁 Image

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.

Output:

👁 Image

UPDATE

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

Syntax: UPDATE tableName SET Attribute₁ = Value₁ , 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.

Output:

👁 Image

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.

Output:

👁 Image