{"id":792,"date":"2016-01-03T23:06:42","date_gmt":"2016-01-03T16:06:42","guid":{"rendered":"http:\/\/www.sqlitetutorial.net\/?page_id=792"},"modified":"2024-10-26T20:54:31","modified_gmt":"2024-10-26T13:54:31","slug":"delete","status":"publish","type":"page","link":"https:\/\/www.sqlitetutorial.net\/sqlite-python\/delete\/","title":{"rendered":"SQLite Python: Deleting Data"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn how to delete data in an SQLite database from a Python program using the sqlite3<\/code> module.<\/p>\n\n\n\n

How to delete data from a table using Python<\/h2>\n\n\n\n

To delete data in the SQLite database from a Python program, you follow these steps:<\/p>\n\n\n\n

First, create a database connection<\/a> to an SQLite database file by calling the connect()<\/code> function of the sqlite3 module:<\/p>\n\n\n

conn = sqlite3.connect(database)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
The connect()<\/code> function returns a Connection<\/code> object representing a connection to a specified SQLite database.<\/p>\n\n\n\n
Next, create a cursor object from Connection<\/code> object by calling the cursor()<\/code> method:<\/p>\n\n\n
cur = conn.cursor()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Then, execute a DELETE<\/code> statement by calling the execute()<\/code> method of the Cursor<\/code> object:<\/p>\n\n\n
cur.execute(delete_statement)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
DELETE FROM table_name\nWHERE id = ?<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
In this syntax, the question mark (?<\/code>) acts as a placeholder that will be replaced by an id. In this case, you need to pass the second argument as a tuple that includes the id to the execute()<\/code> method:<\/p>\n\n\n
cur.execute(delete_statement, (id,))<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
After that, call the commit()<\/code> method of the Connection<\/code> object to apply the change to the database permanently:<\/p>\n\n\n
conn.commit()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Finally, close the database connection by calling the close()<\/code> method of the Connection<\/code> object:<\/p>\n\n\n
conn.close()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Here are the complete steps:<\/p>\n\n\n
sql = 'DELETE FROM sample_table WHERE id = ?'<\/span>\nconn = sqlite3.connect(database)\ncur = conn.cursor()\ncur.execute(sql, (id,))\nconn.commit()\nconn.close()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
import<\/span> sqlite3\n\nsql = 'DELETE FROM sample_table WHERE id = ?'<\/span>\nwith<\/span> sqlite3.connect(database) as<\/span> conn:\n    cur = conn.cursor()\n    cur.execute(sql, (id,))\n    conn.commit()<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
import<\/span> sqlite3\n\nsql = 'DELETE FROM sample_table WHERE id = ?'<\/span>\n\ntry<\/span>:\n    with<\/span> sqlite3.connect(database) as<\/span> conn:\n        cur = conn.cursor()\n        cur.execute(delete_stmt, (id,))\n        conn.commit()\nexcept<\/span> sqlite3.OperationalError as<\/span> e:\n    print(e)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Deleting data from a table example<\/h2>\n\n\n\n
The following program illustrates how to delete a row from the tasks<\/code> table:<\/p>\n\n\n
import<\/span> sqlite3\n\nsql = 'DELETE FROM tasks WHERE id = ?'<\/span>\n\ntry<\/span>:\n    with<\/span> sqlite3.connect('my.db'<\/span>) as<\/span> conn:\n        cur = conn.cursor()\n        cur.execute(sql, (1<\/span>,))\n        conn.commit()\nexcept<\/span> sqlite3.OperationalError as<\/span> e:\n    print(e)<\/code><\/span>Code language:<\/span> Python<\/span> (<\/span>python<\/span>)<\/span><\/small><\/pre>\n\n\n
Summary<\/h2>\n\n\n\n
    \n
  • Call the execute()<\/code> method of a Cursor object to run a DELETE<\/code> statement that deletes a row from a table.<\/li>\n\n\n\n
  • Always call the commit()<\/code> method of the Connection object to permanently delete data from a table.<\/li>\n<\/ul>\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t\n\t\t\t
An error may occur when deleting the data. To handle it, you can use the try-except<\/a> statement:<\/p>\n\n\n
If you use a context manager<\/a>, you don’t need to explicitly close the database connection. The code will be like this:<\/p>\n\n\n
If you want to bind the arguments to the DELETE<\/a> statement, use a question mark (?<\/code>) for each argument. For example:<\/p>\n\n\n