Python Data Access 简明教程
Python SQLite - Select Data
您可以使用 SELCT 查询从 SQLite 表中检索数据。此查询/语句以表格形式返回指定关系（表）的内容，并称为结果集。
You can retrieve data from an SQLite table using the SELCT query. This query/statement returns contents of the specified relation (table) in tabular form and it is called as result-set.
Syntax
以下是 SQLite 中 SELECT 语句的语法 -
Following is the syntax of the SELECT statement in SQLite −
SELECT column1, column2, columnN FROM table_name;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 查询会从 CRICKETERS 表检索 FIRST_NAME、LAST_NAME、COUNTRY。
Following SELECT query retrieves the values of the columns FIRST_NAME, LAST_NAME and, COUNTRY from the CRICKETERS table.
sqlite> SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
Shikhar |Dhawan |India
Jonathan |Trott |SouthAfrica
Kumara |Sangakkara |Srilanka
Virat |Kohli |India
Rohit |Sharma |India
sqlite>正如你所看到的，SQLite 数据库的 SELECT 语句只返回指定表的记录。要获得格式化输出，需要在 SELECT 语句之前使用 header 和 mode 设置各自的命令，如下所示：
As you observe, the SELECT statement of the SQLite database just returns the records of the specified tables. To get a formatted output you need to set the header, and mode using the respective commands before the SELECT statement as shown below −
sqlite> .header on
sqlite> .mode column
sqlite> SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
First_Name Last_Name Country
---------- -------------------- ----------
Shikhar Dhawan India
Jonathan Trott SouthAfric
Kumara Sangakkara Srilanka
Virat Kohli India
Rohit Sharma India
sqlite>如果你想要检索每条记录的所有列，就需要用 "*" 替换列名，如下所示：
If you want to retrieve all the columns of each record, you need to replace the names of the columns with "*" as shown below −
sqlite> .header on
sqlite> .mode column
sqlite> SELECT * FROM CRICKETERS;
First_Name Last_Name Age Place_Of_Birth Country
---------- ---------- ---------- -------------- ----------
Shikhar Dhawan 33 Delhi India
Jonathan Trott 38 CapeTown SouthAfric
Kumara Sangakkara 41 Matale Srilanka
Virat Kohli 30 Delhi India
Rohit Sharma 32 Nagpur India
sqlite>SQLite 默认情况下各列的宽度为 10 个值，超过这个宽度就会被截断（观察上表中第 2 行 country 列）。可以在检索表格内容之前，使用 .width 命令设置所需的各列宽度，如下所示：
In SQLite by default the width of the columns is 10 values beyond this width are chopped (observe the country column of 2nd row in above table). You can set the width of each column to required value using the .width command, before retrieving the contents of a table as shown below −
sqlite> .width 10, 10, 4, 10, 13
sqlite> SELECT * FROM CRICKETERS;
First_Name Last_Name Age Place_Of_B Country
---------- ---------- ---- ---------- -------------
Shikhar Dhawan 33 Delhi India
Jonathan Trott 38 CapeTown SouthAfrica
Kumara Sangakkara 41 Matale Srilanka
Virat Kohli 30 Delhi India
Rohit Sharma 32 Nagpur India
sqlite>Retrieving data using python
对任何数据库执行 READ 操作意味着从中获取一些有用的信息。可以使用 sqlite python 模块提供的 fetch() 方法从 MYSQL 中获取数据。
READ Operation on any database means to fetch some useful information from the database. You can fetch data from MYSQL using the fetch() method provided by the sqlite python module.
sqlite3.Cursor 类提供 fetchall()、fetchmany() 和 fetchone() 三个方法，其中：
The sqlite3.Cursor class provides three methods namely fetchall(), fetchmany() and, fetchone() where,
-
The fetchall() method retrieves all the rows in the result set of a query and returns them as list of tuples. (If we execute this after retrieving few rows it returns the remaining ones).
-
The fetchone() method fetches the next row in the result of a query and returns it as a tuple.
-
The fetchmany() method is similar to the fetchone() but, it retrieves the next set of rows in the result set of a query, instead of a single row.
Note - 结果集是在使用光标对象查询表格时返回的对象。
Note − A result set is an object that is returned when a cursor object is used to query a table.
Example
下面的示例使用 SELECT 查询来获取 EMPLOYEE 表中的所有行，并且从最初获得的结果集中，我们使用 fetchone() 方法检索第一行，然后使用 fetchall() 方法获取剩余的行。
Following example fetches all the rows of the EMPLOYEE table using the SELECT query and from the obtained result set initially, we are retrieving the first row using the fetchone() method and then fetching the remaining rows using the fetchall() method.
以下 Python 程序演示如何从上述示例中创建的 COMPANY 表中获取和显示记录。
Following Python program shows how to fetch and display records from the COMPANY table created in the above example.
import sqlite3
#Connecting to sqlite
conn = sqlite3.connect('example.db')
#Creating a cursor object using the cursor() method
cursor = conn.cursor()
#Retrieving data
cursor.execute('''SELECT * from EMPLOYEE''')
#Fetching 1st row from the table
result = cursor.fetchone();
print(result)
#Fetching 1st row from the table
result = cursor.fetchall();
print(result)
#Commit your changes in the database
conn.commit()
#Closing the connection
conn.close()