Python Data Access 简明教程
Python PostgreSQL - Select Data
你可以使用 SELECT 语句来检索 PostgreSQL 中现有表的内容。在此语句中，你需要指定表的名称，它以表格形式返回其内容，这就是所谓的结果集。
You can retrieve the contents of an existing table in PostgreSQL using the SELECT statement. At this statement, you need to specify the name of the table and, it returns its contents in tabular format which is known as result set.
Syntax
以下是 PostgreSQL 中 SELECT 语句的语法：
Following is the syntax of the SELECT statement in PostgreSQL −
SELECT column1, column2, columnN FROM table_name;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 查询会从 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.
postgres=# SELECT FIRST_NAME, LAST_NAME, COUNTRY FROM CRICKETERS;
first_name | last_name | country
------------+------------+-------------
Shikhar | Dhawan | India
Jonathan | Trott | SouthAfrica
Kumara | Sangakkara | Srilanka
Virat | Kohli | India
Rohit | Sharma | India
(5 rows)如果你想要检索每条记录的所有列，你需要用 "*" 替换列的名称，如下所示：
If you want to retrieve all the columns of each record you need to replace the names of the columns with "*" as shown below −
postgres=# SELECT * FROM CRICKETERS;
first_name | last_name | age | place_of_birth | 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
(5 rows)
postgres=#Retrieving data using python
对任何数据库的读取操作意味着从数据库中获取一些有用的信息。你可以使用 psycopg2 提供的 fetch() 方法从 PostgreSQL 中获取数据。
READ Operation on any database means to fetch some useful information from the database. You can fetch data from PostgreSQL using the fetch() method provided by the psycopg2.
游标类提供了三种方法，即 fetchall()、fetchmany() 和 fetchone()，其中：
The 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
以下 Python 程序连接到 PostgreSQL 的名为 mydb 的数据库并检索名为 EMPLOYEE 的表中的所有记录。
The following Python program connects to a database named mydb of PostgreSQL and retrieves all the records from a table named EMPLOYEE.
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()
#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()