Python Data Access 简明教程
Python MySQL - Limit
在获取记录时，如果您想按特定数量对它们进行限制，可以使用 MYSQL 的 LIMIT 子句来实现。
While fetching records if you want to limit them by a particular number, you can do so, using the LIMIT clause of MYSQL.
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);以下 SQL 语句使用 LIMIT 子句检索 Employee 表的前两条记录。
Following SQL statement retrieves first two records of the Employee table using the LIMIT clause.
SELECT * FROM EMPLOYEE LIMIT 2;
+------------+-----------+------+------+--------+
| FIRST_NAME | LAST_NAME | AGE | SEX | INCOME |
+------------+-----------+------+------+--------+
| Krishna | Sharma | 19 | M | 2000 |
| Raj | Kandukuri | 20 | M | 7000 |
+------------+-----------+------+------+--------+
2 rows in set (0.00 sec)Limit clause using python
如果您通过将 SELECT 查询与 LIMIT 子句一起传递给游标对象，来调用 execute() 方法，您可以检索所需数量的记录。
If you invoke the execute() method on the cursor object by passing the SELECT query along with the LIMIT clause, you can retrieve required number of records.
要使用 python 从 MYSQL 数据库中删除表，请在游标对象上调用 execute() 方法，并将 drop 语句作为参数传递给它。
To drop a table from a MYSQL database using python invoke the execute() method on the cursor object and pass the drop statement as a parameter to it.
Example
下面的 Python 示例创建并填充了一个名为 EMPLOYEE 的表，然后使用 LIMIT 子句来提取其中的前两条记录。
Following python example creates and populates a table with name EMPLOYEE and, using the LIMIT clause it fetches the first two records of it.
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()
#Retrieving single row
sql = '''SELECT * from EMPLOYEE LIMIT 2'''
#Executing the query
cursor.execute(sql)
#Fetching the data
result = cursor.fetchall();
print(result)
#Closing the connection
conn.close()LIMIT with OFFSET
如果你需要从第 n 个记录开始限制记录（不是第一个），你可以使用 OFFSET 和 LIMIT 来做到。
If you need to limit the records starting from nth record (not 1st), you can do so, using OFFSET along with LIMIT.
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()
#Retrieving single row
sql = '''SELECT * from EMPLOYEE LIMIT 2 OFFSET 2'''
#Executing the query
cursor.execute(sql)
#Fetching the data
result = cursor.fetchall();
print(result)
#Closing the connection
conn.close()