Python Data Access 简明教程
Python PostgreSQL - Where Clause
在执行SELECT、UPDATE或DELETE操作时，可以使用WHERE子句指定条件以筛选记录。该操作将对满足给定条件的记录执行。
While performing SELECT, UPDATE or, DELETE operations, you can specify condition to filter the records using the WHERE clause. The operation will be performed on the records which satisfies the given condition.
Syntax
以下是PostgreSQL中WHERE子句的语法 −
Following is the syntax of the WHERE clause in PostgreSQL −
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 −
postgres=# CREATE TABLE CRICKETERS (
First_Name VARCHAR(255), Last_Name VARCHAR(255),
Age int, Place_Of_Birth VARCHAR(255), Country VARCHAR(255)
);
CREATE TABLE
postgres=#并且如果我们使用 INSERT 语句向其中插入 5 条记录，如下所示 -
And if we have inserted 5 records in to it using INSERT statements as −
postgres=# insert into CRICKETERS values('Shikhar', 'Dhawan', 33, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Jonathan', 'Trott', 38, 'CapeTown', 'SouthAfrica');
INSERT 0 1
postgres=# insert into CRICKETERS values('Kumara', 'Sangakkara', 41, 'Matale', 'Srilanka');
INSERT 0 1
postgres=# insert into CRICKETERS values('Virat', 'Kohli', 30, 'Delhi', 'India');
INSERT 0 1
postgres=# insert into CRICKETERS values('Rohit', 'Sharma', 32, 'Nagpur', 'India');
INSERT 0 1以下SELECT语句检索年龄大于35岁的记录 −
Following SELECT statement retrieves the records whose age is greater than 35 −
postgres=# SELECT * FROM CRICKETERS WHERE AGE > 35;
first_name | last_name | age | place_of_birth | country
------------+------------+-----+----------------+-------------
Jonathan | Trott | 38 | CapeTown | SouthAfrica
Kumara | Sangakkara | 41 | Matale | Srilanka
(2 rows)
postgres=#Where clause using python
要使用python程序从表中获取特定记录，请使用WHERE子句执行SELECT语句，通过将其作为参数传递给 execute() 方法。
To fetch specific records from a table using the python program execute the SELECT statement with WHERE clause, by passing it as a parameter to the execute() method.
Example
以下python示例演示了使用python的WHERE命令。
Following python example demonstrates the usage of WHERE command using python.
import psycopg2
#establishing the connection
conn = psycopg2.connect(
database="mydb", user='postgres', password='password', host='127.0.0.1', port= '5432'
)
#Setting auto commit false
conn.autocommit = True
#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
insert_stmt = "INSERT INTO EMPLOYEE (FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
VALUES (%s, %s, %s, %s, %s)"
data = [('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'M', 5000),
('Mac', 'Mohan', 26, 'M', 2000)]
cursor.executemany(insert_stmt, data)
#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()