Python Data Access 简明教程
Python SQLite - Where Clause
如果要获取、删除或更新 SQLite 中表的特定行，您需要使用 where 子句来指定条件，以便筛选表的行以进行操作。
If you want to fetch, delete or, update particular rows of a table in SQLite, you need to use the where clause to specify condition to filter the rows of the table for the operation.
例如，如果您有带 where 从句的 SELECT 语句，则只有满足指定条件的行才会被检索。
For example, if you have a SELECT statement with where clause, only the rows which satisfies the specified condition will be retrieved.
Syntax
以下是 SQLite 中 WHERE 子句的语法：
Following is the syntax of the WHERE clause in SQLite −
SELECT column1, column2, columnN
FROM table_name
WHERE [search_condition]您可以使用比较或逻辑运算符（如>、<、=、LIKE、NOT等）指定search_condition。以下示例将阐明此概念。
You can specify a search_condition using comparison or logical operators. like >, <, =, LIKE, NOT, etc. The following examples would make this concept clear.
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>以下SELECT语句检索年龄大于35岁的记录 −
Following SELECT statement retrieves the records whose age is greater than 35 −
sqlite> SELECT * FROM CRICKETERS WHERE AGE > 35;
First_Name Last_Name Age Place_Of_B Country
---------- ---------- ---- ---------- -------------
Jonathan Trott 38 CapeTown SouthAfrica
Kumara Sangakkara 41 Matale Srilanka
sqlite>Where clause using python
Cursor 对象/类包含执行查询和获取数据等的所有方法。连接类的 cursor 方法返回一个 cursor 对象。
The Cursor object/class contains all the methods to execute queries and fetch data, etc. The cursor method of the connection class returns a cursor object.
因此，要在 Python 中创建 SQLite 数据库中的表：
Therefore, to create a table in SQLite database using python −
-
Establish connection with a database using the connect() method.
-
Create a cursor object by invoking the cursor() method on the above created connection object.
-
Now execute the CREATE TABLE statement using the execute() method of the Cursor class.
Example
以下示例创建一个名为 Employee 的表并填充它。然后使用 where 从句检索年龄值小于 23 的记录。
Following example creates a table named Employee and populates it. Then using the where clause it retrieves the records with age value less than 23.
import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Doping EMPLOYEE table if already exists.
cursor.execute("DROP TABLE IF EXISTS EMPLOYEE")
sql = '''CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
)'''
cursor.execute(sql)
#Populating the table
cursor.execute('''INSERT INTO EMPLOYEE(
FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES
('Ramya', 'Rama priya', 27, 'F', 9000)''')
cursor.execute('''INSERT INTO EMPLOYEE
(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES
('Vinay', 'Battacharya', 20, 'M', 6000)''')
cursor.execute('''INSERT INTO EMPLOYEE(
FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES
('Sharukh', 'Sheik', 25, 'M', 8300)''')
cursor.execute('''INSERT INTO EMPLOYEE(
FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES
('Sarmista', 'Sharma', 26, 'F', 10000)''')
cursor.execute('''INSERT INTO EMPLOYEE(
FIRST_NAME, LAST_NAME, AGE, SEX, INCOME) VALUES
('Tripthi', 'Mishra', 24, 'F', 6000)''')
#Retrieving specific records using the where clause
cursor.execute("SELECT * from EMPLOYEE WHERE AGE <23")
print(cursor.fetchall())
#Commit your changes in the database
conn.commit()
#Closing the connection
conn.close()