How to Delete a specific Row from a Given SQLite Table using Python?

How to Delete a specific Row from a Given SQLite Table using Python?

SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL database engine. It is the most used database engine on the world wide web. Python has a library to access SQLite databases, called sqlite3.

Let’s see how we can delete a specific row from a given Sqlite table.

First, Let’s create a database and table then insert some records into the table.

Code 1: Create a table with some records in it.

# Import sqlite3 library as sq 
# in this program
import sqlite3 as sq
  
# Create a connection object,
# Make a new db if not exist already and 
# connect it, if exist then connect.
connection = sq.connect('information.db')

# Create a cursor object
curs = connection.cursor()

# Run create table sql query
curs.execute("create table if not exists student" + 
             " (id integer, name text, gender text, age integer,course text, branch text)")

# insert 5 records into studentInfo table
curs.execute("insert into student (id, name, gender, age, course, branch) values(1, 'shivangi','f','20','btech','cse')")
curs.execute("insert into student (id, name, gender, age, course, branch) values(2, 'ankit','m','21','btech','cse')")
curs.execute("insert into student (id, name, gender, age, course, branch) values(3, 'ankthon','m','22','btech','it')")
curs.execute("insert into student (id, name, gender, age, course, branch) values(4, 'priya','f','20','btech','it')")
curs.execute("insert into student (id, name, gender, age, course, branch) values(5, 'shaurya','m','22','btech','cse')")

             
# Run select sql query
curs.execute('select * from student')

# Fetch all records
# as list of tuples
records = curs.fetchall()

print("Showing Records of Student table:")

# Display results
for row in records:
    # show row
    print(row)
    
# saved in the database
connection.commit()

Output:
Records

Now, we will delete a row from the student table. For deleting the row we are using the delete clause with where clause in SQL query statement then run this SQL query using execute() method of cursor object.
Code 2: We will delete a record from the student table where id is 3.

# Delete a record from student table where id is 3
curs.execute("delete from student where id = 3")

# Saved in the database
connection.commit()

# Run select all sql query
curs.execute('select * from student')

# Fetch all records
# as list of tuples
records = curs.fetchall()

print("Showing Records of Student table after removing those " +
      "\nrecords/rows where student id is 3 :")

# Display result 
for row in records:
    # show record
    print(row)

Output:
delete one record from table

We can delete multiple rows/records from the table by passing filter condition at run time as well. for passing values at run time, we are using ? symbol and then pass tuple value in this execute() method of cursor object.

Code 3: We will delete records from the student table where gender is ‘m’.

value = 'm'

# Delete a records from student table where gender is male
curs.execute("delete from student where gender = ?",
 (value,))

# saved in the database
connection.commit()

# Run select all sql query
curs.execute('select * from student')

# Fetch all records
# as list of tuples
records = curs.fetchall()

print("Showing Records of Student table after removing those" +
      "\nrecords rows where student gender is male")

# Display result 
for row in records:
    # show record
    print(row)

Output:
delete multiple records from table

4 Comments

  1. Your style is unique in comρarіson to other people I’ve read stuff
    from. I аppreciate you for posting when you have the opportunity, Guess Ι’ll just boοk mark this site.

Leave a Reply

Your email address will not be published.