World's Best AI Learning Platform with profoundly Demanding Certification Programs
Designed by IITians, only for AI Learners.
Download our e-book of Introduction To Python
Related Blog
Top Discussion
Exception Type: JSONDecodeError at /update/ Exception Value: Expecting value: line 1 column 1 (char 0) How can I write Python code to change a date string from "mm/dd/yy hh: mm" format to "YYYY-MM-DD HH: mm" format? How to leave/exit/deactivate a Python virtualenvironment What is the process of executing a Python script within Power Automate? Change the hyperparameter means ? Why is my f1_scores different when i calculate them manually vs output by sklearn.metrics How can a sentence be matched to a cluster of keywords? How Python interpreter Works? Join Discussion
Top Courses
Master's In Artificial Intelligence Job Guarantee Program
4.5 (1,292 Ratings)
559 Learners
Webinars
Why You Should Learn Data Science in 2023?
Jun 8th (7:00 PM) 289 Registered
Python SQLite - Delete Data
Neha Kumawat
a year ago
Table of Content
- Syntax
- Example
- Deleting Data Using Python
- Output
To delete records from a SQLite table, you need to use the DELETE FROM statement. To remove specific records, you need to use WHERE clause along with it.
Syntax
Following is the syntax of the DELETE query in SQLite −
DELETE FROM table_name [WHERE Clause]
Example
Assume we have created a table with name CRICKETERS using the following query −
sqlite> CREATE TABLE CRICKETERS (
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Age int,
Place_Of_Birth VARCHAR(255),
Country VARCHAR(255)
);
sqlite>
And if we have inserted 5 records in to it using INSERT statements as −
sqlite> insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
sqlite> insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
sqlite> insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
sqlite>
Following statement deletes the record of the cricketer whose last name is 'Sangakkara'.
sqlite> DELETE FROM CRICKETERS WHERE LAST_NAME = 'Sangakkara';
sqlite>
If you retrieve the contents of the table using the SELECT statement, you can see only 4 records since we have deleted one.
sqlite> SELECT * FROM CRICKETERS;
First_Name Last_Name Age Place_Of_B Country
---------- ---------- ---- ---------- --------
Shikhar Dhawan 46 Delhi India
Jonathan Trott 39 CapeTown SouthAfrica
Virat Kohli 31 Delhi India
Rohit Sharma 33 Nagpur India
sqlite>
If you execute the DELETE FROM statement without the WHERE clause, all the records from the specified table will be deleted.
sqlite> DELETE FROM CRICKETERS;
sqlite>
Since you have deleted all the records, if you try to retrieve the contents of the CRICKETERS table, using SELECT statement you will get an empty result set as shown below −
sqlite> SELECT * FROM CRICKETERS;
sqlite>
Deleting Data Using Python
To add records to an existing table in SQLite database −
- Import sqlite3 package.
Import sqlite3 package.
- Create a connection object using the connect() method by passing the name of the database as a parameter to it.
Create a connection object using the connect() method by passing the name of the database as a parameter to it.
- The cursor() method returns a cursor object using which you can communicate with SQLite3. Create a cursor object by invoking the cursor() object on the (above created) Connection object.
The cursor() method returns a cursor object using which you can communicate with SQLite3. Create a cursor object by invoking the cursor() object on the (above created) Connection object.
- Then, invoke the execute() method on the cursor object, by passing an DELETE statement as a parameter to it.
Then, invoke the execute() method on the cursor object, by passing an DELETE statement as a parameter to it.
Example
Following python example deletes the records from EMPLOYEE table with age value greater than 25.
import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving contents of the table
print("Contents of the table: ")
cursor.execute('''SELECT * from EMPLOYEE''')
print(cursor.fetchall())
#Deleting records
cursor.execute('''DELETE FROM EMPLOYEE WHERE AGE > 25''')
#Retrieving data after delete
print("Contents of the table after delete operation ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())
#Commit your changes in the database
conn.commit()
#Closing the connection
conn.close()
Output
Contents of the table:
[
('Ramya', 'Rama priya', 27, 'F', 9000.0),
('Vinay', 'Battacharya', 21, 'M', 6000.0),
('Sharukh', 'Sheik', 26, 'M', 8300.0),
('Sarmista', 'Sharma', 26, 'F', 10000.0),
('Tripthi', 'Mishra', 24, 'F', 6000.0)
]
Contents of the table after delete operation
[
('Vinay', 'Battacharya', 21, 'M', 6000.0),
('Tripthi', 'Mishra', 24, 'F', 6000.0)
]
Like the Blog, then Share it with your friends and colleagues to make this AI community stronger.
To learn more about nuances of Artificial Intelligence, Python Programming, Deep Learning, Data Science and Machine Learning, visit our insideAIML blog page.
Keep Learning. Keep Growing.