Skip to content
Related Articles

Related Articles

Improve Article

Python SQLite – Deleting Data in Table

  • Last Updated : 20 Jul, 2021

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]

 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

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:

 

 




My Personal Notes arrow_drop_up
Recommended Articles
Page :