介绍
在之前的指南中，我们解释了如何从 Excel 电子表格、制表符分隔文件和在线 API 导入数据。尽管这些资源很有用，但如果我们需要处理的数据量很大或变得过于复杂，数据库（关系型或其他）可能是更好的解决方案。无论您选择哪种风格，都有一些通用原则和基本工具可供我们用来查询数据库并使用 Python 操作结果。
先决条件
首先，我们需要为所使用的数据库系统安装适当的连接器（也称为驱动程序）。此实用程序以模块的形式提供，可以从标准库（例如sqlite3）或第三方软件包（例如分别用于 Mysql / MariaDB 和 PostgreSQL 的mysql-connector-python和psycopg2-binary ）中使用。无论如何，Python 软件包索引是我们搜索可用适配器的首选之地。
在本指南中，我们将使用 PostgreSQL，因为它提供了一个名为ROW_TO_JSON的现成函数。顾名思义，此函数将结果集中的每一行作为 JSON 对象返回。由于我们已经学习了如何使用 JSON 数据，因此我们将能够非常轻松地操作查询结果。
如果我们使用不同的数据库系统，我们可以迭代结果并创建一个字典列表，其中每个元素都是结果集中的一条记录。
话虽如此，我们需要安装psycopg2-binary，最好是在虚拟环境中进行安装：
pip install psycopg2-binary
现在让我们检查一下我们将要使用的 PostgreSQL 数据库，该数据库名为nba 。图 1 至图 3 显示了coaches、players和teams表的结构。
- coaches存储以下数据，其中coach_id充当主键。除了教练的名字和姓氏外，还有一个team_id ，它是引用teams表中同名字段的外键。
- players除了player_id（主键）和team_id（外键，表示当前效力的球队）之外，还保存了名字和姓氏、球衣号码、身高（米）、体重（公斤）和原籍国。
- 最后，球队描述包括球队名称、所属联盟、当前联盟排名、主场胜负场次、客场胜负场次。当然，它还有team_id主键，在其他两个表中引用。
下一步是编写一个 SQL 查询来检索按会议和排名排序的球队列表，以及每支球队的球员人数和教练姓名。在此过程中，我们还可以添加主场和客场获胜次数：
SELECT
t.name,
t.city,
t.conference,
t.conference_rank,
COUNT(p.player_id) AS number_of_players,
CONCAT(c.first_name, ' ', c.last_name) AS coach,
t.home_wins,
t.away_wins
FROM players p, teams t, coaches c
WHERE p.team_id = t.team_id
AND c.team_id = t.team_id
GROUP BY t.name, c.first_name, c.last_name, t.city, t.conference, t.conference_rank, t.home_wins, t.away_wins
ORDER BY t.conference, t.conference_rank
然后，为了方便起见，我们将查询包装在ROW_TO_JSON函数中，并将其保存到当前目录中名为query.sql的文件中：
SELECT ROW_TO_JSON(team_info) FROM (
SELECT
t.name,
t.city,
t.conference,
t.conference_rank,
COUNT(p.player_id) AS number_of_players,
CONCAT(c.first_name, ' ', c.last_name) AS coach,
t.home_wins,
t.away_wins
FROM players p, teams t, coaches c
WHERE p.team_id = t.team_id
AND c.team_id = t.team_id
GROUP BY t.name, c.first_name, c.last_name, t.city, t.conference, t.conference_rank, t.home_wins, t.away_wins
ORDER BY t.conference, t.conference_rank
) AS team_info
图 4 显示了上述查询的第一条记录。请注意，每一行都具有 Python 字典的结构，其中查询返回的字段名称是键。
最后，但并非最不重要的一点，需要提醒大家。要连接到数据库，我们需要用户名和密码。最佳做法是使用环境变量，而不是将它们作为连接字符串的一部分暴露在外。如果您将代码推送到其他人可以访问的版本控制系统，这一点尤其重要。在类 Unix 环境中，可以通过在 shell 的初始化文件末尾附加以下两行来完成此操作。要应用更改，您需要注销并重新登录或在当前会话中获取文件。
export DB_USER="your_PostgreSQL_username_here_inside_quotes"
export DB_PASS="your_password_inside_quotes"
在 Windows 中，转到控制面板/系统/高级系统设置。选择高级选项卡，然后单击环境变量以添加它们：
我们现在可以开始编写 Python 代码了！
查询数据库并操作结果
在程序的顶部，我们将导入必要的模块和一个处理错误的函数：
import os
import psycopg2 as p
from psycopg2 import Error
接下来，我们将query.sql的内容加载到query中并实例化连接。您也可以对host、port和database使用环境变量，就像我们对 user 和 password 所做的那样，尽管这样做并不是绝对必要的。
with open('query.sql') as sql:
query = sql.read()
conn = p.connect(
user = os.environ['DB_USER'],
password = os.environ['DB_PASS'],
host = 'localhost',
port = '5432',
database = 'nba'
)
一旦我们成功连接到数据库，就该执行查询了。为此，使用与连接关联的控制结构（称为游标） 。如果一切按预期进行，则名为result的变量包含一个单元素元组列表，其中每个元素都是一个字典。
cursor = conn.cursor()
cursor.execute(query)
result = cursor.fetchall()
此时，我们可以迭代结果并根据需要操作其内容。例如，我们可以将它们插入电子表格中（如图 5 所示），就像我们在使用 Python 从 Microsoft Excel 文件导入数据中学到的那样，或者使用它们通过 Web 应用程序来提供 HTML 表。
为了捕获错误（如果发生），必须将代码包装在try-except 块中。在执行此操作时，添加finally语句可以让我们在完成使用连接后清理连接：
try:
# Instantiate the connection
conn = p.connect(
user = os.environ['DB_USER'],
password = os.environ['DB_PASS'],
host = 'localhost',
port = '5432',
database = 'nba'
)
# Create cursor, execute the query, and fetch results
cursor = conn.cursor()
cursor.execute(query)
result = cursor.fetchall()
# Create workbook and select active sheet
wb = Workbook()
ws = wb.active
# Rename active sheet
ws.title = 'Teams'
# Column headings
column_headings = [
'Name',
'City',
'Conference',
'Rank',
'Players',
'Coach',
'Home wins',
'Away wins'
]
ws.append(column_headings)
# Add players
for team in result:
ws.append(list(team[0].values()))
# Get coordinates of last cell
last_cell = ws.cell(row = ws.max_row, column = ws.max_column).coordinate
# Create table
team_table = Table(displayName = 'TeamTable', ref = 'A1:{}'.format(last_cell))
# Add 'Table Style Medium 6' style
style = TableStyleInfo(name = 'TableStyleMedium6', showRowStripes = True)
# Apply style to table
team_table.tableStyleInfo = style
# Add table to spreadsheet
ws.add_table(team_table)
# Save spreadsheet
wb.save('teams.xlsx')
except p.Error as error:
print('There was an error with the database operation: {}'.format(error))
except:
print('There was an unexpected error of type {}'.format(sys.exc_info()[0]))
finally:
if conn:
cursor.close()
conn.close()
脚本和 SQL 文件均可在Github中找到。请根据需要随意使用和修改它们。
概括
在本指南中，我们学习了如何使用 Python 连接到 PostgreSQL 数据库、执行查询以及将结果导入 Excel 电子表格。但是，您可以调整代码来执行其他操作，例如创建和保存 JSON 文件或填充 HTML 表。
免责声明：本内容来源于第三方作者授权、网友推荐或互联网整理，旨在为广大用户提供学习与参考之用。所有文本和图片版权归原创网站或作者本人所有，其观点并不代表本站立场。如有任何版权侵犯或转载不当之情况，请与我们取得联系，我们将尽快进行相关处理与修改。感谢您的理解与支持！
