Python sqlite delete rows using where query and getting number of records deleted of a table

We are using our student table. Check how to create database and student table here.

Interactive Companion Jupyter Notebook

Explore interactive code cells, execute single and multi-row DELETE queries, test rowcount outputs, and run SQLAlchemy 2.0+ delete workflows directly in Google Colab.

View on GitHub

Query to delete records from SQLite database & printing number of records deleted by using rowcount

delete rows

We will delete the row where student id=5

my_query = "DELETE FROM student WHERE id = 5"
curs = my_conn.execute(my_query)
print("Record Deleted")
my_conn.commit()

Output

Record Deleted

Deleting multiple rows with error printing

We can display error message if there is any error by using try except . We will also delete all class of Four.

q = "DELETE FROM student WHERE class = 'Four'"
try:
  r_set = my_conn.execute(q)
  print("Records deleted")
  my_conn.commit()
except sqlite3.Error as my_error:
  print("error: ", my_error)

Output

Records deleted

We can change the the query to q="DELETE FROM student1 WHERE class='Four'" and get the error message

error:  no such table: student1

Number of records deleted

We can display number of rows deleted by using rowcount, output is 9

q = "DELETE FROM student WHERE class = 'Four'"
try:
  r_set = my_conn.execute(q)
  print("No of Records deleted : ", r_set.rowcount)
  my_conn.commit()
except sqlite3.Error as my_error:
  print("error: ", my_error)

Output

No of Records deleted :  9

We can also use changes() to get number of records deleted. ( this code is used after executing above code )

x = my_conn.execute('''select changes()''')
id = x.fetchone()
print(id[0])

Using Parameters

Always use parameterized query when the data is coming from unknown sources. This is required to prevent injection attack.

We have used rowcount to display number of rows affected or deleted.

my_data = ('Three',)
q = "DELETE FROM student WHERE class = ?"
try:
  r_set = my_conn.execute(q, my_data)
  print("Records deleted : ", r_set.rowcount)
  my_conn.commit()
except sqlite3.Error as my_error:
  print("error: ", my_error)

Output

Records deleted :  3

Deleting records using more than 1 column

my_data = ('Seven', 'Six')
q = "DELETE FROM student WHERE class = ? OR class = ?"
try:
  r_set = my_conn.execute(q, my_data)
  print("Number of Records deleted : ", r_set.rowcount)
  my_conn.commit()
except sqlite3.Error as my_error:
  print("error: ", my_error)

Output

Number of Records deleted :  17

Using SQLAlchemy (2.0+ Context Manager)

from sqlalchemy import create_engine, text
from sqlalchemy.exc import SQLAlchemyError

my_conn = create_engine("sqlite:///my_db.db")
q = text("DELETE FROM student WHERE class = :cls")

try:
  with my_conn.begin() as connection:
    r_set = connection.execute(q, {"cls": "Three"})
    print("No of records deleted : ", r_set.rowcount)
except SQLAlchemyError as e:
  print(e)

Removing all records

SQLite does not support TRUNCATE command. We can use DELETE to remove all records.

q = "DELETE FROM student"
try:
  r_set = my_conn.execute(q)
  print("No of Records deleted : ", r_set.rowcount)
  my_conn.commit()
except sqlite3.Error as my_error:
  print("error: ", my_error)

Output

No of Records deleted :  35

All records are only deleted. The structure of the table is not removed.

Delete the table

#query="DELETE FROM student"
query = 'DROP TABLE student'
try:
  r_set = my_conn.execute(query)
  print("No of Records deleted : ", r_set.rowcount)
  my_conn.commit()
except sqlite3.Error as my_error:
  print("error: ", my_error)

Sqlite insert update select Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author Watch on YouTube

Connect: