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

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

Video Tutorial on Delete Rows Query

SQLAlchemy connection to Sqlite database.

from sqlalchemy import create_engine
my_conn = create_engine("sqlite:////content/drive/MyDrive/db/my_db.db")

delete row

We will delete the row where student id=5

from sqlalchemy.exc import SQLAlchemyError
q="DELETE  FROM student WHERE id=5"
try:
  r_set=my_conn.execute(q)
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("No of Records deleted : ",r_set.rowcount)

Output

No of Records deleted :  1

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.

from sqlalchemy.exc import SQLAlchemyError
q="DELETE  from student  WHERE class='Four' "
try:
  r_set=my_conn.execute(q)
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("No of Records deleted : ",r_set.rowcount)

Output

No of Records deleted :  8

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

error:  no such table: student1

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.

from sqlalchemy.exc import SQLAlchemyError
my_data=("Five",)
q="DELETE  from student  WHERE class=? "
try:
  r_set=my_conn.execute(q,my_data)
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("No of Records deleted : ",r_set.rowcount)

Output

No of Records deleted :  3

Deleting records using more than 1 column

from sqlalchemy.exc import SQLAlchemyError
my_data=[("Seven"),("Six")]
q="DELETE  from student  WHERE class=? or class=?"
try:
  r_set=my_conn.execute(q,my_data)
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("No of Records deleted : ",r_set.rowcount)

Output

No of Records deleted :  17

Removing all records

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

from sqlalchemy.exc import SQLAlchemyError
q="DELETE  from student"
try:
  r_set=my_conn.execute(q)
except SQLAlchemyError as e:
  #print(e)
  error = str(e.__dict__['orig'])
  print(error)
else:
  print("No of Records deleted : ",r_set.rowcount)

Output

No of Records deleted :  3

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

Sqlite Sqlite Delete rows using sqlite3 insert update select Order By

Numpy Pandas Python & MySQL Python- Tutorials

Subscribe to our YouTube Channel here

SQLite Deleting records SQLAlchemy

Video Tutorial on Delete Rows Query

delete row

Deleting multiple rows with error printing

Using Parameters

Removing all records

Subscribe