Deleting rows from an SQLite table in Python is a straightforward process that involves creating a connection to the database, constructing a DELETE query, executing the query, and committing the changes. Here’s a step-by-step guide to deleting rows from an SQLite table using Python:
Import SQLite Library
First, ensure you have SQLite’s library available in Python. If you’re using Python’s standard library, sqlite3 is what you’ll need. You can import it like this:
import sqlite3
Connect to the SQLite Database
Establish a connection to the SQLite database. If the database does not exist, SQLite will create it.
connection = sqlite3.connect('my_database.sqlite')
Replace ‘my_database.sqlite’ with the path to your SQLite database file.
Create a Cursor Object
A cursor is used to interact with the database. You can create it using the connection object.
cursor = connection.cursor()
Write the DELETE Query
The DELETE query will specify which rows to delete. You can delete rows based on conditions. For example, to delete rows where the id is 10:
delete_query = "DELETE FROM table_name WHERE id = 10"
Replace table_name with the name of your table and adjust the condition as per your requirement.
Execute the Query
Use the cursor to execute the query.
cursor.execute(delete_query)
Commit the Changes
After executing the DELETE query, you need to commit the changes to the database.
connection.commit()
Close the Connection
Finally, close the cursor and the connection to the database.
cursor.close() connection.close()
Example
Here’s how it looks when put together:
import sqlite3
# Connect to SQLite database
connection = sqlite3.connect('my_database.sqlite')
cursor = connection.cursor()
# DELETE query
delete_query = "DELETE FROM table_name WHERE id = 10"
# Execute and commit changes
cursor.execute(delete_query)
connection.commit()
# Close cursor and connection
cursor.close()
connection.close()
Make sure to replace ‘my_database.sqlite’, table_name, and the condition in the DELETE query with your actual database name, table name, and condition for deleting rows.
Error Handling
Consider adding error handling to manage any exceptions that might occur during database operations. This ensures that your program can gracefully handle unexpected situations.
Note
Always be cautious with DELETE queries. Once a row is deleted, it cannot be recovered unless you have a backup of your database.