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.

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

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

Subscribe to our YouTube Channel here

SQLite Deleting records

delete rows

Deleting multiple rows with error printing

Number of records deleted

Using Parameters

Removing all records

Delete the table

Subscribe