Python Delete from SQLite Table

This article demonstrates how to execute a DELETE Query from Python application to delete data from the SQLite table. you’ll learn how to use Python built-in module sqlite3 to delete data from the SQLite table.

Goals of this lesson

• Delete a single row, multiple rows, all rows, a single column, and multiple columns from the SQLite table using Python
• Use a Python parameterized query to provide value at runtime to the SQLite delete query
• Commit and rollback the delete operation
• Perform bulk delete using a single query

Prerequisite

Before executing the following Python SQLite delete operations, please make sure you know the SQLite table name, and it’s column details which you want to delete.

For this demo, I am using “SqliteDb_developers” table present in my SQLite database.

If no table present in your SQLite database, then please refer to how to create a SQLite table from Python.

Python example to delete a single row from the SQLite table

As of now, the SqliteDb_developers table contains six rows, so let’s remove the developer whose id is 6. To perform SQLite DELETE query from Python, you need to follow these simple steps:

• First, Connect to SQLite from Python.
• Second, create a cursor object using the SQLite connection object.
• Then, Define the SQLite DELETE Query. Here you need to know the table, and it’s column name on which you want to perform delete operation.
• Execute the DELETE query using the cursor.execute()
• After the successful execution of an SQLite delete query, commit your changes to the database.
• Close the SQLite database connection.
• Also, don’t forget to catch SQLite exceptions if any.
• At last, verify the result by selecting data from SQLite table from Python.

Let’ s see the example now.

import sqlite3

def deleteRecord():
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        # Deleting single record now
        sql_delete_query = """DELETE from SqliteDb_developers where id = 6"""
        cursor.execute(sql_delete_query)
        sqliteConnection.commit()
        print("Record deleted successfully ")
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to delete record from sqlite table", error)
    finally:
        if (sqliteConnection):
            sqliteConnection.close()
            print("the sqlite connection is closed")

deleteRecord()

Output: sqlitedb_developers table after deleting a row from Python.

Connected to SQLite
Record deleted successfully 
the sqlite connection is closed

Understand Python SQLite delete example

import sqlite3

• This line imports the sqlite3 module in our program.
• Using the classes and method defined in the sqlite3 module we can communicate with the SQLite database.

sqlite3.connect() and connection.cursor()

• Using a sqlite3.connect() method we established a connection to SQLite Database from Python.
• Next, we used a sqliteConnection.cursor() method to get a cursor object from the connection object.

Next, we prepared the SQLite DELETE query to delete a table row 6 i.e., a developer whose id is 6. In the delete query, We mentioned developer id column value we want to delete.

cursor.execute()

• We executed the operation stored in the SQLite UPDATE query using a execute() method of a Cursor object.
• Once the record deleted successfully, we committed our changes to the database using a connection.commit().

At last, we are closing cursor, and SQLite connection object inside the finally block once we complete deleting a record.

Use Python Variable in a parameterized query to Delete Row from SQLite table

Most of the time, we need to delete a row from an SQLite a table in which row primary key passed at runtime. For example, when a user cancels his/her subscription through a user interface in such cases, we need to delete his/her entry from a table.

In such circumstances, It is always best practice to use a parameterized query. The parameterized query uses placeholders (?) inside SQL statements that contain input from users. It helps us to delete runtime values and prevent SQL injection concerns. Read more on Python Parameterized Query and its performance benefits. Let see the example to remove a developer whose id is 5.

import sqlite3

def deleteSqliteRecord(id):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")

        sql_update_query = """DELETE from SqliteDb_developers where id = ?"""
        cursor.execute(sql_update_query, (id, ))
        sqliteConnection.commit()
        print("Record deleted successfully")

        cursor.close()

    except sqlite3.Error as error:
        print("Failed to delete reocord from a sqlite table", error)
    finally:
        if (sqliteConnection):
            sqliteConnection.close()
            print("sqlite connection is closed")

deleteSqliteRecord(5)

Output: sqlitedb_developers table after removing row using a parameterized query.

You can verify the result by selecting data from a SQLite table from Python.

Connected to SQLite
Record deleted successfully
sqlite connection is closed

Let’s understand the above example

• We used the parameterized query to accept developer id at runtime using a placeholder(?). We used single placeholders in delete query i.e., for the” id” column.
• We then prepared data tuple by using python variables.
• Next, passed the SQL delete query and data tuple to cursor.execute() method.
• In the end, we made our changes permanent into the database using a commit() method of a connection class.

Python Delete multiple rows from SQLite table

In the above example, we have used execute() method of cursor object to update a single record, but sometimes in Python application, we need to delete an N-number of rows. For example, You want to delete employee data from the developer’s table who left the organization.

So instead of executing a delete query every time to delete each record, you can perform the bulk delete operation in a single query from Python. We can delete multiple records of the SQLite table in a single query using the cursor.executemany() method.

The cursor.executemany(query, seq_param) method accepts two arguments SQL query and List of record to delete.

Let see the example now. In this example, we are removing three developers.

import sqlite3

def deleteMultipleRecords(idList):
    try:
        sqliteConnection = sqlite3.connect('SQLite_Python.db')
        cursor = sqliteConnection.cursor()
        print("Connected to SQLite")
        sqlite_update_query = """DELETE from SqliteDb_developers where id = ?"""

        cursor.executemany(sqlite_update_query, idList)
        sqliteConnection.commit()
        print("Total", cursor.rowcount, "Records deleted successfully")
        sqliteConnection.commit()
        cursor.close()

    except sqlite3.Error as error:
        print("Failed to delete multiple records from sqlite table", error)
    finally:
        if (sqliteConnection):
            sqliteConnection.close()
            print("sqlite connection is closed")

idsToDelete = [(4,),(3,)]
deleteMultipleRecords(idsToDelete)

Output: You can verify the result by selecting data from a SQLite table from Python.

Connected to SQLite
Total 2 Records deleted successfully
sqlite connection is closed

Let’s understand the above example

• After connecting to SQLite, We prepared SQLite parameterized delete query with a single placeholder for developers id to delete and then created a list of Ids to remove in tuple format.
• Each element of a list is nothing but a tuple for each row. Each tuple contains the id of a developer. Here we created three tuples, so we are deleting three rows.
• Next, we called cursor.executemany(sqlite_delete_query, idList) method to delete multiple rows from the SQLite table. Remember we passed SQLite delete query and Ids list to cursor.executemany() as arguments.
• To get to know the number of records updated, we used a cursor.rowcount method. At last, we committed our changes into the SQLite database using the commit method of a connection class.

Next Steps:

To practice what you learned in this article, Please solve a Python Database Exercise project to Practice and master the Python Database operations.