Python Data Access 简明教程
Python SQLite - Delete Data
要从 SQLite 表格中删除记录，需要使用 DELETE FROM 语句。要移除特定记录，需要同时使用 WHERE 子句。
To delete records from a SQLite table, you need to use the DELETE FROM statement. To remove specific records, you need to use WHERE clause along with it.
要更新特定行，需要同时使用 WHERE 子句。
To update specific rows, you need to use the WHERE clause along with it.
Syntax
以下是 SQLite 中 DELETE 查询的语法：
Following is the syntax of the DELETE query in SQLite −
DELETE FROM table_name [WHERE Clause]Example
假设我们使用以下查询创建了一个名为 CRICKETERS 的表 -
Assume we have created a table with name CRICKETERS using the following query −
sqlite> CREATE TABLE CRICKETERS (
First_Name VARCHAR(255),
Last_Name VARCHAR(255),
Age int,
Place_Of_Birth VARCHAR(255),
Country VARCHAR(255)
);
sqlite>并且如果我们使用 INSERT 语句向其中插入 5 条记录，如下所示 -
And if we have inserted 5 records in to it using INSERT statements as −
sqlite> insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
sqlite> insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
sqlite> insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
sqlite> insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
sqlite>以下语句删除了姓氏为“Sangakkara”的板球运动员记录。
Following statement deletes the record of the cricketer whose last name is 'Sangakkara'.
sqlite> DELETE FROM CRICKETERS WHERE LAST_NAME = 'Sangakkara';
sqlite>如果您使用 SELECT 语句检索表中的内容，您只能看到 4 条记录，因为我们删除了其中一条。
If you retrieve the contents of the table using the SELECT statement, you can see only 4 records since we have deleted one.
sqlite> SELECT * FROM CRICKETERS;
First_Name Last_Name Age Place_Of_B Country
---------- ---------- ---- ---------- -------------
Shikhar Dhawan 46 Delhi India
Jonathan Trott 39 CapeTown SouthAfrica
Virat Kohli 31 Delhi India
Rohit Sharma 33 Nagpur India
sqlite>如果您在没有 WHERE 子句的情况下执行 DELETE FROM 语句，那么指定表中的所有记录都将被删除。
If you execute the DELETE FROM statement without the WHERE clause, all the records from the specified table will be deleted.
sqlite> DELETE FROM CRICKETERS;
sqlite>由于您删除了所有记录，如果您尝试使用 SELECT 语句检索 CRICKETERS 表中的内容，您将获得空的结果集，如下所示：
Since you have deleted all the records, if you try to retrieve the contents of the CRICKETERS table, using SELECT statement you will get an empty result set as shown below −
sqlite> SELECT * FROM CRICKETERS;
sqlite>Deleting data using python
要在 SQLite 数据库的现有表中添加记录：
To add records to an existing table in SQLite database −
-
Import sqlite3 package.
-
Create a connection object using the connect() method by passing the name of the database as a parameter to it.
-
The cursor() method returns a cursor object using which you can communicate with SQLite3 . Create a cursor object by invoking the cursor() object on the (above created) Connection object.
-
Then, invoke the execute() method on the cursor object, by passing an DELETE statement as a parameter to it.
Example
以下 Python 示例从 EMPLOYEE 表中删除 age 值大于 25 的记录。
Following python example deletes the records from EMPLOYEE table with age value greater than 25.
import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving contents of the table
print("Contents of the table: ")
cursor.execute('''SELECT * from EMPLOYEE''')
print(cursor.fetchall())
#Deleting records
cursor.execute('''DELETE FROM EMPLOYEE WHERE AGE > 25''')
#Retrieving data after delete
print("Contents of the table after delete operation ")
cursor.execute("SELECT * from EMPLOYEE")
print(cursor.fetchall())
#Commit your changes in the database
conn.commit()
#Closing the connection
conn.close()Output
Contents of the table:
[('Ramya', 'Rama priya', 27, 'F', 9000.0),
('Vinay', 'Battacharya', 21, 'M', 6000.0),
('Sharukh', 'Sheik', 26, 'M', 8300.0),
('Sarmista', 'Sharma', 26, 'F', 10000.0),
('Tripthi', 'Mishra', 24, 'F', 6000.0)]
Contents of the table after delete operation
[('Vinay', 'Battacharya', 21, 'M', 6000.0),
('Tripthi', 'Mishra', 24, 'F', 6000.0)]