使用 Python 从数据库导入数据

2019-02-05 08:00:00 · 飞浪

介绍在之前的指南中，我们解释了如何从Excel电子表格、制表符分隔文件和在线API导入数据。尽管这些资源很有用，但如果我们需要处理的数据量很大或变得过于复杂，数据库（关系型或其他）可能

介绍

在之前的指南中，我们解释了如何从 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 表。

_{免责声明：本内容来源于第三方作者授权、网友推荐或互联网整理，旨在为广大用户提供学习与参考之用。所有文本和图片版权归原创网站或作者本人所有，其观点并不代表本站立场。如有任何版权侵犯或转载不当之情况，请与我们取得联系，我们将尽快进行相关处理与修改。感谢您的理解与支持！}

_查看原文

python

技术指南

阅读全文

使用 Python 从数据库导入数据

杭州电子商务研究院

5年前 · 面向社会、服务行业、政产学研结合、整合资源、和谐发展

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

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

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' )

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()