Summary: in this tutorial, you will learn how to query data from the PostgreSQL tables in Python.
简介: 在本教程中,您将学习如何使用 Python 从 PostgreSQL 表中查询数据。
This tutorial picks up from where the Handling BLOB Data Tutorial left off.
本教程从处理 BLOB 数据教程结束的地方开始。
The steps for querying data from a PostgreSQL table in Python
在 Python 中查询 PostgreSQL 表中的数据的步骤
To query data from one or more PostgreSQL tables in Python, you use the following steps.
要在 Python 中查询一个或多个 PostgreSQL 表的数据,请使用以下步骤。
First, establish a connection to the PostgreSQL server by calling the connect()
function of the psycopg2
module.
首先,通过调用 psycopg2
模块的 connect()
函数建立与 PostgreSQL 服务器的连接。
conn = psycopg2.connect(config)
Code language: Python (python)
If the connection is created successfully, the connect()
function returns a new Connection
object; Otherwise, it throws a DatabaseError
exception.
如果成功创建连接,则 connect()
函数将返回一个新的 Connection
对象;否则,它将引发 DatabaseError
异常。
Next, create a new cursor by calling the cursor()
method of the Connection
object. The cursor
object is used to execute a SELECT statement.
接下来,通过调用 Connection
对象的 cursor()
方法来创建新游标。游标
对象用于执行 SELECT 语句。
cur = conn.cursor()
Code language: Python (python)
Then, execute a SELECT
statement by calling the execute()
method. If you want to pass values to the SELECT
statement, you use the placeholder ( %s
) in the SELECT
statement and bind the input values when calling the execute()
method:
然后,通过调用 execute()
方法执行 SELECT
语句。如果要将值传递给 SELECT
语句,请在 SELECT
语句中使用占位符 ( %s
),并在调用 execute()
方法时绑定输入值:
cur.execute(sql, (value1,value2))
Code language: Python (python)
After that, process the result set returned by the SELECT statement using the fetchone()
, fetchall()
, or fetchmany()
method.
之后,使用 fetchone()、
fetchall()
或 fetchmany()
方法处理 SELECT 语句返回的结果集。
- The
fetchone()
fetches the next row in the result set. It returns a single tuple orNone
when no more row is available.fetchone()
获取结果集中的下一行。它返回单个 Tuples,或者当没有更多行可用时返回None
。 - The
fetchmany(size=cursor.arraysize)
fetches the next set of rows specified by thesize
parameter. If you omit this parameter, thearraysize
will determine the number of rows to be fetched. Thefetchmany()
method returns a list of tuples or an empty list if no more rows are available.
将fetchmany(size=cursor.arraysize)
获取size
参数指定的下一组行。如果省略此参数,则 arraysize
将确定要获取的行数。fetchmany()
方法返回一个 Tuples 列表,如果没有更多行可用,则返回一个空列表。 - The
fetchall()
fetches all rows in the result set and returns a list of tuples. If there are no rows to fetch, thefetchall()
method returns an empty list.fetchall()
获取结果集中的所有行并返回元组列表。如果没有要获取的行,则 fetchall()
方法返回一个空列表。
Finally, close the database connection by calling the close()
method of the Cursor
and Connection
objects
最后,通过调用 Cursor
和 Connection
对象的 close()
方法来关闭数据库连接
cur.close()
conn.close()
Code language: Python (python)
If you use context managers, you don’t need to explicitly call the close()
methods of the Cursor
and Connection
objects.
如果使用上下文管理器,则不需要显式调用 Cursor
和 Connection
对象的 close()
方法。
Querying data using the fetchone() method
使用 fetchone() 方法查询数据
For the demonstration purposes, we will use the parts
, vendors
, and vendor_parts
tables in the suppliers
database:
出于演示目的,我们将使用 suppliers
数据库中的 parts
、vendors
和 vendor_parts
表:
The following get_vendor()
function selects data from the vendors
table and fetches the rows using the fetchone()
method.
以下 get_vendor()
函数从 vendors
表中选择数据,并使用 fetchone()
方法获取行。
import psycopg2
from config import load_config
def get_vendors():
""" Retrieve data from the vendors table """
config = load_config()
try:
with psycopg2.connect(**config) as conn:
with conn.cursor() as cur:
cur.execute("SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_name")
print("The number of parts: ", cur.rowcount)
row = cur.fetchone()
while row is not None:
print(row)
row = cur.fetchone()
except (Exception, psycopg2.DatabaseError) as error:
print(error)
if __name__ == '__main__':
get_vendors()
Code language: Python (python)
Output: 输出:
The number of parts: 8
(1, '3M Corp')
(2, 'AKM Semiconductor Inc.')
(3, 'Asahi Glass Co Ltd.')
(4, 'Daikin Industries Ltd.')
(5, 'Dynacast International Inc.')
(6, 'Foster Electric Co. Ltd.')
(8, 'LG')
(7, 'Murata Manufacturing Co. Ltd.')
Code language: JavaScript (javascript)
Querying data using the fetchall() method
使用 fetchall() 方法查询数据
The following get_parts()
function uses the fetchall()
method of the cursor object to fetch rows from the result set and display all the parts in the parts
table.
以下 get_parts()
函数使用 cursor 对象的 fetchall()
方法从结果集中获取行并显示 parts
表中的所有部分。
import psycopg2
from config import load_config
def get_vendors():
""" Retrieve data from the vendors table """
config = load_config()
try:
with psycopg2.connect(**config) as conn:
with conn.cursor() as cur:
cur.execute("SELECT vendor_id, vendor_name FROM vendors ORDER BY vendor_name")
rows = cur.fetchall()
print("The number of parts: ", cur.rowcount)
for row in rows:
print(row)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
if __name__ == '__main__':
get_vendors()
Code language: Python (python)
The number of parts: 8
(1, '3M Corp')
(2, 'AKM Semiconductor Inc.')
(3, 'Asahi Glass Co Ltd.')
(4, 'Daikin Industries Ltd.')
(5, 'Dynacast International Inc.')
(6, 'Foster Electric Co. Ltd.')
(8, 'LG')
(7, 'Murata Manufacturing Co. Ltd.')
Code language: Python (python)
Querying data using the fetchmany() method
使用 fetchmany() 方法查询数据
The following get_suppliers()
function selects parts and vendor data using the fetchmany()
method.
以下 get_suppliers()
函数使用 fetchmany()
方法选择零件和供应商数据。
import psycopg2
from config import load_config
def iter_row(cursor, size=10):
while True:
rows = cursor.fetchmany(size)
if not rows:
break
for row in rows:
yield row
def get_part_vendors():
""" Retrieve data from the vendors table """
config = load_config()
try:
with psycopg2.connect(**config) as conn:
with conn.cursor() as cur:
cur.execute("""
SELECT part_name, vendor_name
FROM parts
INNER JOIN vendor_parts ON vendor_parts.part_id = parts.part_id
INNER JOIN vendors ON vendors.vendor_id = vendor_parts.vendor_id
ORDER BY part_name;
""")
for row in iter_row(cur, 10):
print(row)
except (Exception, psycopg2.DatabaseError) as error:
print(error)
if __name__ == '__main__':
get_part_vendors()
Code language: Python (python)
Output: 输出:
('Antenna', 'Foster Electric Co. Ltd.')
('Antenna', 'Murata Manufacturing Co. Ltd.')
('Home Button', 'Dynacast International Inc.')
('Home Button', '3M Corp')
('LTE Modem', 'Dynacast International Inc.')
('LTE Modem', '3M Corp')
('SIM Tray', 'AKM Semiconductor Inc.')
('SIM Tray', '3M Corp')
('Speaker', 'Daikin Industries Ltd.')
('Speaker', 'Asahi Glass Co Ltd.')
('Vibrator', 'Dynacast International Inc.')
('Vibrator', 'Foster Electric Co. Ltd.')
Code language: JavaScript (javascript)
Download the project source code
下载项目源代码
In this tutorial, we have learned how to select data from the PostgreSQL tables in Python using the fetchone()
, fetchall()
, and fetchmany()
methods.
在本教程中,我们学习了如何使用 fetchone()、
fetchall()
和 fetchmany()
方法从 Python 中的 PostgreSQL 表中选择数据。
本教程有帮助吗 ?