SQLite Deleting records SQLAlchemy

SQLite SQLite Delete query using sqlite3

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

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 selectOrder By


plus2net.com



Post your comments , suggestion , error , requirements etc here





Python Video Tutorials
Python SQLite Video Tutorials
Python MySQL 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