Python Data Access 简明教程
Python SQLite - Limit
在获取记录时，如果你想通过某个特定的数字来限制它们，你可以使用 SQLite 的 LIMIT 子句来做到。
While fetching records if you want to limit them by a particular number, you can do so, using the LIMIT clause of SQLite.
Syntax
以下是 SQLite 中的 LIMIT 子句的语法：
Following is the syntax of the LIMIT clause in SQLite −
SELECT column1, column2, columnN
FROM table_name
LIMIT [no of rows]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>以下语句使用 LIMIT 子句检索 Cricketers 表的前 3 条记录：
Following statement retrieves the first 3 records of the Cricketers table using the LIMIT clause −
sqlite> SELECT * FROM CRICKETERS LIMIT 3;
First_Name Last_Name Age Place_Of_B Country
---------- ---------- ---- ---------- -------------
Shikhar Dhawan 33 Delhi India
Jonathan Trott 38 CapeTown SouthAfrica
Kumara Sangakkara 41 Matale Srilanka
sqlite>如果你需要从第 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.
sqlite> SELECT * FROM CRICKETERS LIMIT 3 OFFSET 2;
First_Name Last_Name Age Place_Of_B Country
---------- ---------- ---- ---------- -------------
Kumara Sangakkara 41 Matale Srilanka
Virat Kohli 30 Delhi India
Rohit Sharma 32 Nagpur India
sqlite>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.
Example
以下 Python 示例使用 LIMIT 子句检索 EMPLOYEE 表的前两条记录。
Following python example retrieves the first two records of the EMPLOYEE table using the LIMIT clause.
import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving single row
sql = '''SELECT * from EMPLOYEE LIMIT 3'''
#Executing the query
cursor.execute(sql)
#Fetching the data
result = cursor.fetchall();
print(result)
#Commit your changes in the database
conn.commit()
#Closing the connection
conn.close()