Open In App

Python SQLite – Deleting Data in Table

Improve
Improve
Like Article
Like
Save
Share
Report

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 object
connection_obj = sqlite3.connect('geek.db')
 
# cursor object
cursor_obj = connection_obj.cursor()
 
# Drop the GEEK table if already exists.
cursor_obj.execute("DROP TABLE IF EXISTS GEEK")
   
# Creating table
table = """ CREATE TABLE GEEK (
            Email VARCHAR(255) NOT NULL,
            Name CHAR(25) NOT NULL,
            Score INT
        ); """
   
cursor_obj.execute(table)
 
#inserting data into geek table
connection_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 connection
connection_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 object
connection_obj = sqlite3.connect('geek.db')
 
# cursor object
cursor_obj = connection_obj.cursor()
 
 
#delete data
cursor_obj.execute("DELETE FROM GEEK WHERE Score < 15")
 
connection_obj.commit()
# Close the connection
connection_obj.close()


 
 

Output:

 

 

Example 2: (Delete all data) 

Python3




import sqlite3
 
# Connecting to sqlite
# connection object
connection_obj = sqlite3.connect('geek.db')
 
# cursor object
cursor_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 connection
connection_obj.close()


 
 

Output:

 

 



Last Updated : 20 Jul, 2021
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads