Python Data Access 简明教程
Python MySQL - Where Clause
如果您想获取 MySQL 表中的特定行，删除或更新这些行，您需要使用 where 从句指定条件以筛选表的行进行操作。
If you want to fetch, delete or, update particular rows of a table in MySQL, 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
以下是 WHERE 从句的语法 -
Following is the syntax of the WHERE clause −
SELECT column1, column2, columnN
FROM table_name
WHERE [condition]Example
假设我们在 MySQL 中使用 EMPLOYEES 作为名称创建了一个表格，如下所示：
Assume we have created a table in MySQL with name EMPLOYEES as −
mysql> CREATE TABLE EMPLOYEE(
FIRST_NAME CHAR(20) NOT NULL,
LAST_NAME CHAR(20),
AGE INT,
SEX CHAR(1),
INCOME FLOAT
);
Query OK, 0 rows affected (0.36 sec)如果使用 INSERT 语句向其中插入了 4 条记录，如下所示：
And if we have inserted 4 records in to it using INSERT statements as −
mysql> INSERT INTO EMPLOYEE VALUES
('Krishna', 'Sharma', 19, 'M', 2000),
('Raj', 'Kandukuri', 20, 'M', 7000),
('Ramya', 'Ramapriya', 25, 'F', 5000),
('Mac', 'Mohan', 26, 'M', 2000);以下 MySQL 语句检索收入大于 4000 的员工记录。
Following MySQL statement retrieves the records of the employees whose income is greater than 4000.
mysql> SELECT * FROM EMPLOYEE WHERE INCOME > 4000;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
+------------+-----------+------+------+--------+
| Raj | Kandukuri | 20 | M | 7000 |
| Ramya | Ramapriya | 25 | F | 5000 |
+------------+-----------+------+------+--------+
2 rows in set (0.00 sec)WHERE clause using python
从表中使用 python 程序获取特定记录 -
To fetch specific records from a table using the python program −
-
import mysql.connector package.
-
Create a connection object using the mysql.connector.connect() method, by passing the user name, password, host (optional default: localhost) and, database (optional) as parameters to it.
-
Create a cursor object by invoking the cursor() method on the connection object created above.
-
Then, execute the SELECT statement with WHERE clause, by passing it as a parameter to the execute() method.
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 mysql.connector
#establishing the connection
conn = mysql.connector.connect(
user='root', password='password', host='127.0.0.1', database='mydb')
#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, 'F', 5000),('Mac', 'Mohan', 26, 'M', 2000)]
cursor.executemany(insert_stmt, data)
conn.commit()
#Retrieving specific records using the where clause
cursor.execute("SELECT * from EMPLOYEE WHERE AGE <23")
print(cursor.fetchall())
#Closing the connection
conn.close()