SQLite Deleting records

SQLite

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

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


plus2net.com



Post your comments , suggestion , error , requirements etc here




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