4). Single Column – Deleting Records from Database Table in SQLite

SQL query for Deleting or to DROP column:

“ALTER TABLE table_name DROP COLUMN column_name“

But this query will not work with SQLite3, as SQLite3 does not supports this.

But we can DROP column manually:

We have to open the DB in a DB Browser:

In DB Browser for SQLite:

1. Go to the, “Database Structure”
2. Select your table & Select Modify table
3. Select the column you want to delete
4. Click on Remove field & click OK
5. Column will be deleted

5). Multiple Columns – Deleting Records from Database Table in SQLite

SQL QUERY for deleting or to DROP multiple columns:

“ALTER TABLE table_name DROP COLUMN column_name1, column_name2“

But this query will also not work with SQLite3, as SQLite3 does not supports this.

But we can DROP columns manually:

We have to open the DB in a DB Browser:

In DB Browser for SQLite:

1. Go to the, “Database Structure”
2. Select your table & Select Modify table
3. Select the columns you want to delete
4. Click on Remove field & click OK
5. Column will be deleted

6). Delete in Bulk – Deleting Records from Database Table in SQLite in Python

#Importing Database Library
import sqlite3

# Database Connectivity

try:
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    print("Connected to Database Successfully")
    while(True):
        ids = int(input("Enter ID to Delete: "))
        query = "DELETE from USERS where ID = ?"
        cursor.execute(query,(ids,))
        con.commit()
        ch = input("Do you want to Delete More Data(Y/N): ")
        if ch == "N" or ch == "n":
            break
        else:
            pass
except:
    print("Database Error")


#END

Output:

# Connected to Database Successfully
# Enter ID to Delete: 6
# Do you want to Delete More Data(Y/N): y
# Enter ID to Delete: 7
# Do you want to Delete More Data(Y/N): n

#END

Output: USERS table after deletion of rows:-

Explanation: Program explanation is same as I have discussed above. Only difference is that here we have used parameterized function and some basic concepts of Core Python.

7). Deleting all records from table without Deleting Table

This is our present table:

Program for deleting all records from table without deleting table

#Importing Database Library
import sqlite3

# Database Connectivity

try:
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    print("Connected to Database Successfully")
    #Deleting all records available from your table
    #But it will not delete your table
    query = "DELETE FROM test"
    cursor.execute(query)
    con.commit()
except:
    print("Database Error")

#END

Output: TEST table after deletion Operation:-

Explanation: Program explanation is same as I have discussed above. Only difference is the query used here.

8). Delete Table – Deleting Table from Database in SQLite in Python

#Importing Database Library
import sqlite3

# Database Connectivity

try:
    con = sqlite3.connect("data.db")
    cursor = con.cursor()
    print("Connected to Database Successfully")
    #Deleting Table
    query = "DROP TABLE USERS"
    cursor.execute(query)
    con.commit()
except:
    print("Database Error")

Output: This program will delete your table completely

Here we can see that table “users” is not there in our Database.