Python SQLite – CRUD Operations
In this article, we will go through the CRUD Operation using the SQLite module in Python.
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.
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:
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.
This refers to reading data from a database. A read statement has three clauses:
- SELECT: Takes as the predicate the attributes to be queried, use * for all attributes.
- FROM: Takes as the predicate a relation.
- 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.
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.
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.