In this article, we will discuss how we can delete data in the table in the SQLite database from the Python program using the sqlite3 module. In SQLite database we use the following syntax to delete data from a table:
DELETE FROM table_name [WHERE Clause]
To create the database, we will execute the following code:
Python3
import sqlite3# Connecting to sqlite# connection objectconnection_obj = sqlite3.connect('geek.db')# cursor objectcursor_obj = connection_obj.cursor()# Drop the GEEK table if already exists.cursor_obj.execute("DROP TABLE IF EXISTS GEEK") # Creating tabletable = """ CREATE TABLE GEEK ( Email VARCHAR(255) NOT NULL, Name CHAR(25) NOT NULL, Score INT ); """ cursor_obj.execute(table)#inserting data into geek tableconnection_obj.execute("""INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk1@gmail.com","Geek1",25)""")connection_obj.execute("""INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk2@gmail.com","Geek2",15)""")connection_obj.execute("""INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk3@gmail.com","Geek3",36)""")connection_obj.execute("""INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk4@gmail.com","Geek4",27)""")connection_obj.execute("""INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk5@gmail.com","Geek5",40)""")connection_obj.execute("""INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk6@gmail.com","Geek6",14)""")connection_obj.execute("""INSERT INTO GEEK (Email,Name,Score) VALUES ("geekk7@gmail.com","Geek7",10)""")connection_obj.commit()# Close the connectionconnection_obj.close() |
Output:
Now we will create a python program to delete the row in a table:
Approach:
- Import the required module.
- Establish the connection or create a connection object with the database using to connect() function of the sqlite3 module.
- Create a Cursor object by calling the cursor() method of the Connection object.
- Finally, trigger to execute() method on the cursor object, bypassing a DELETE statement as a parameter to it.
Example 1: (Delete some data)
Python3
import sqlite3# Connecting to sqlite# connection objectconnection_obj = sqlite3.connect('geek.db')# cursor objectcursor_obj = connection_obj.cursor()#delete datacursor_obj.execute("DELETE FROM GEEK WHERE Score < 15")connection_obj.commit()# Close the connectionconnection_obj.close() |
Output:
Example 2: (Delete all data)
Python3
import sqlite3# Connecting to sqlite# connection objectconnection_obj = sqlite3.connect('geek.db')# cursor objectcursor_obj = connection_obj.cursor()cursor_obj.execute("SELECT * FROM GEEK")print(cursor_obj.fetchall())#delete data '''It will delete all rows from the table'''cursor_obj.execute("DELETE FROM GEEK")print()print("After deleting all rows")cursor_obj.execute("SELECT * FROM GEEK")print(cursor_obj.fetchall())connection_obj.commit()# Close the connectionconnection_obj.close() |
Output:
