SQLite Deleting records

SQLite SQLite Delete using SQLAlchemy

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

  • Video Tutorial on Delete Rows Query



Read more on delete query here.

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])
9

Using Parameters

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.

Sqlite insert update selectOrder By

Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    Post your comments , suggestion , error , requirements etc here





    Python Video Tutorials
    Python SQLite Video Tutorials
    Python MySQL Video Tutorials
    Python Tkinter Video Tutorials
    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2021 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer