你有没有想过从 RStudio 查询像 PostgreSQL 这样的关系数据库的可能性?在 2021 年,我尝试这样做,但我发现没有太多资源可以详细解释如何做到这一点。我应该早就写这篇文章了。然而,在那段时间里,我在 Coursera 上教授了一门名为“在 RStudio 中使用 SQL 操作 R 数据帧”的基于项目的课程,以了解如何使用和编写 SQL 代码来查询 RStudio 中的 R 数据帧。你可以在这里查看课程。

在本文中,我将向您展示如何在本地连接到 PostgreSQL 数据库。在连接到其他关系数据库(包括 MySQL 和 SQL Server)时,您将从本文中收集到的想法将很有帮助,即使该数据库位于云存储中。

在探索这篇文章之前,应该想到的一个问题是——这篇文章的基本原理是什么?为什么需要将 RStudio 连接到 SQL 数据库?

通常,关系数据库中的数据以标准化格式存储。因此,对于数据专业人员进行统计计算和数据可视化等分析,需要编写高级和复杂的 SQL 查询。但是,R 可以轻松连接到许多关系数据库,如 PostgreSQL、MySQL、Oracle、SQL Server 等,并从它们中检索数据作为数据框。当数据成功进入 R 环境时,它就变成了典型的 R 数据集,使用所有强大的包和功能可以轻松操作或分析。

我相信这为这篇文章的重要性提供了一个充分的理由。

在我们继续之前,这里有一点警告。这篇文章是一个简单的指南,这意味着它并没有包罗万象地解释所涉及的所有技术细节。请放心,按照本指南中概述的步骤,您将能够将您的 RStudio 连接到本地 PostgreSQL 数据库。此外,这些步骤只需稍加调整即可连接到基于云的数据库。

另外,在我们开始之前,我想假设你已经安装了 PostgreSQL,你知道如何创建一个数据库,并且你在 PostgreSQL 中至少有一个数据库。假设您之前没有使用 SQL 数据库的经验。在这种情况下,您可以在此处查看我教授的名为“在 SQL 中执行数据定义和操作”的基于项目的课程。

此外,各种函数和包可以帮助实现本文的相同目的——将 RStudio 连接到 PostgreSQL 数据库。但是,我将使用 odbc R 包来展示这个插图。ODBC 表示开放式数据库连接是一种开放式标准应用程序编程接口 (API),允许应用程序程序员访问任何数据库。

最后,在我们开始之前,我想提一下,确保你通读到最后;我在这篇文章中添加了一个奖励部分和一份礼物。现在,让我们开始吧!

开始使用

第一步是安装和导入所需的 R 包。如果您以前使用过 R,那么您应该知道 R 具有几乎任何用途的包。 R 综合存档网络 (CRAN) 存储库中有超过 13,000 个软件包。要安装和导入我们将在本文中使用的包,请使用以下代码。

注意: 要在 R 中运行一行代码,对于 Windows 用户,请使用 CTRL ENTER 或使用 RStudio 界面上的“运行”按钮。

我建议使用谷歌搜索来检查每个导入的包的作用。我已经跳过解释导入的每个包的功能,以使本文尽可能简短紧凑。

有了这个,我们现在可以看到如何将 RStudio 连接到 PostgreSQL 数据库。

将 RStudio 连接到 PostgreSQL 数据库

在本节中,我们将查询 Employees 表中的 employees 表。因此,首先,我们将连接到 PostgreSQL 中名为 Employees 的本地数据库。请注意,我的 PostgreSQL 中已经有这个数据库。让我展示一下数据库的样子,包括员工表中的一些记录或行。

Employees 数据库包含七个表和两个视图,如上图所示,包括employees 表。此外,在右侧,返回查询employees 表中所有数据的输出。但是,由于空间限制,我们只能看到前十行。但是,employees 表包含大约 11,822 行和 6 列,如下图所示。

伟大的!我们可以开始学习如何将 RStudio 连接到 Employees PostgreSQL 数据库。我将概述从 RStudio 成功连接到该数据库的步骤。如果您准备好并很高兴看到如何做到这一点,那就加入我吧!

第一步:定义数据库连接

创建从 RStudio 到 PostgreSQL 的数据库连接相对简单。在这一步中,我们将使用我们之前安装的 DBI 包中的函数。

在我们编写将 RStudio 连接到 PostgreSQL 的代码之前,我们希望确保我们拥有有关数据库的所有信息,包括端口号和用户名。要获取该信息,请在打开 pgAdmin(PostgreSQL 的管理界面)后,执行以下操作,如下图所示:

• 右键单击PostgreSQL14并选择Properties(会打开一个对话框)

• 选择Connection 选项卡,您将看到创建连接所需的所有信息。

注意: PostgreSQL 的默认端口号是 5432。

惊人!现在,我们有了创建连接所需的信息。

通常,最好先检查是否存在连接,即检查我们是否可以从 RStudio 连接到 PostgreSQL 数据库。本文将使用 DBI 包中的dbCanConnect()函数。该函数只检查有效性而不返回连接对象。默认实现打开连接并在成功时断开连接。简单地说,它可以帮助我们检查 RStudio 是否可以与数据库建立连接。

上面的代码看起来很容易理解。我们填写了我们获得的有关数据库的信息。回想一下,我们想要连接到 Employees 数据库,因此参数 dbname u003d “Employees”。此外,输入您在安装 PostgreSQL 时使用的相同密码,这与您打开 PgAdmin 界面时输入的密码相同。

美丽的!打印连接对象con的结果是**TRUE,**表示存在有效连接。确定后,我们可以使用 dbConnect() 函数通过适当的身份验证过程连接到数据库。它采用与 dbCanConnect() 函数类似的参数。

正如我所提到的,它们采用了相似的论点,但产生了不同的结果。让我们看看我们创建的连接对象 con_1 的结果。

我相信我们都同意他们返回不同的结果。这个结果告诉我们已经成功连接到 Employees 数据库的 5432 端口。

太棒了!从这里开始,这将是一个平稳的旅程。连接到数据库是第一步也是最关键的一步。这不就是你阅读这篇文章的原因吗?

现在,让我们来玩一些其他功能和有趣的数据任务。接下来,使用 dbListTables() 函数,我们可以返回数据库中所有视图和临时对象(表)的列表。

你从结果中注意到什么?

返回了九个对象,对应于七个表和两个视图,正如我们在本节开头的图像中看到的那样。这是有道理的,对吧?

现在的问题是——在创建连接之后,然后呢?在下一步中找出这个问题的答案。

第二步:查询数据库

现在,让我们使用 DBI 包中的 dbGetQuery() 函数直接从 RStudio 查询数据库。此函数将查询结果作为数据框返回。它需要两个必需的参数:连接和 SQL 语句。

运行以下代码后,将查询员工表以返回所有数据。按照我编写的方式运行代码的缺点是它会在 RStudio 控制台上打印结果,从而使您的 RStudio 控制台非常混乱。

一种更好的方法是将结果存储为 R 对象,例如 emp_df。有了这个 R 对象,我们可以做很多其他的事情,包括对雇员表进行数据操作,而不会影响 Employees 数据库中的原始雇员表,或者即使我们关闭与数据库的连接也会丢失数据。

伟大的!你做得很好。该代码将成功查询employees表中的所有数据。

让我们继续第三步!

第三步:探索数据集

现在,我们将 employees 表中的数据保存为 R 数据框。首先,我们可以使用 dim() 函数检查数据的维度(行数和列数)。

这不是很有趣吗?结果告诉我们数据有 11,822 行和 6 列。

这些数字会引起共鸣吗?是的!

这些是 Employees PostgreSQL 数据库中员工表中的行数和列数的确切结果。显然,毫无疑问,我们已经成功连接到员工表。

现在,我们可以使用 glimpse() 函数查看数据。glimpse()就像是 print() 的转置版本:列在页面下方,数据在页面中运行。此功能可以查看数据框中的每一列。这有点像 str() 应用于数据框,但它试图向您显示尽可能多的数据。此外,它始终显示基础数据,即使应用于远程数据源也是如此。 glimpse() 是 dplyr 包中的一个函数。

注意: dplyr 包属于 tidyverse 包家族。 dplyr 包非常强大,事实上,它是 R 中用于数据操作的最有用的包。这是我有史以来最好的数据操作包。您可以在 Coursera 上查看我的基于项目的课程,标题为 Data Manipulation with dplyr in R此处。 我在该课程中介绍了 dplyr 包的使用。

惊人!在一个目标上,我们可以看到一个数据集的所有信息,包括行数、列数、列名、列数据类型,甚至是几行数据。

随着我们的前进,我们可以使用 head 和 tail base R 函数来获取数据集的第一行和最后六行。

这不是很漂亮吗?这些结果进一步强调了我们在本文中考虑的内容的重要性。只需连接到数据库,我们就可以查询数据库中的任何表。在这种情况下,我们查询了 employees 表并将其保存为 R 数据框。有了这个,我们绝对可以对雇员表进行任何数据操作、清理、转换或可视化,如果我们要编写 SQL 查询,这可能会很困难或很昂贵。此外,我们可以在不篡改数据库的情况下完成所有这些数据任务——感谢 RStudio。

在我们开始结束本文时,至关重要的是,一旦您将查询的数据保存为 R 对象(如数据框),您应该断开或关闭您创建的连接。这样做有助于丢弃所有待处理的工作并释放资源(例如内存、套接字)。

我们将使用 DBI 包中的dbDisconnect()函数。它的工作方式与我们用来创建连接的 dbConnect() 函数相反。 dbConnect() 函数需要一个必需的参数:连接。

! zoz100077](https://devpress-image.s3.cn-north-1.jdcloud-oss.com/a/8fd1e0a747_1*__kLwHrz9nRxo0mTJjei1A.jpg)

此代码关闭连接,这意味着我们不再有权访问 Employees 数据库中的表。但是,我们仍然可以对 employees 表执行数据操作任务,因为我们已将该数据保存为名为 emp_df. 的 R 数据框

例如,可以提出一个数据问题,例如 1990 年 1 月 1 日或之前雇用了多少员工?下面的代码帮助我们回答了这个问题。

上面的代码使用了 dplyr 包中的函数。 %>% 代表管道函数。在 dplyr 中将一系列操作链接在一起是一个非常有用的功能。 filter() 函数过滤hire_date 在1990 年1 月1 日或之前的位置(因此使用<u003d)。此外,您会注意到日期采用 yyyy-mm-dd 格式(类似于典型的 SQL 日期格式)。您可以决定使用强大的 lubridate() 包中的函数来更改日期格式_(这样做超出了本文的范围)_。最后,我们使用 count() 函数来获取所需的数字。

让我们看看上面的 R 代码的结果。

惊人!在 11,822 名员工中,公司在 1990 年 1 月 1 日或之前雇用了 6,453 名。

我们已经结束了关于将 RStudio 连接到 PostgreSQL 数据库的讨论。

哦......等等!让我在这里快速补充这一点。您也可以使用下面的代码来创建连接。此代码与我们上面使用的代码之间的区别只是使用 RStudio API 来请求用户名和密码。

实际上,没有区别,但是这段代码看起来比第一个更安全。如果您与他人共享您的代码并且不想透露您的用户名和密码,使用此功能可能会有所帮助。

运行上面的代码后,会弹出一个对话框,询问您的用户名和密码,如下所示。输入用户名和密码后,将创建连接。

注意: 您只在上面的代码中请求了用户名和密码。您还可以使用 RStudio API 来询问数据库名称。

我相信这是完全可以理解的。我决定添加最后一部分以表明您可以使用 RStudio 做类似的事情。你可以进一步探索。

您是否有兴趣了解如何将 RStudio 连接到 MySQL 数据库?连接到 MySQL 数据库并不像连接到 PostgreSQL 数据库那么简单。然而,在我的文章中,我已经解释了你需要知道和做的一切将 RStudio 连接到 MySQL 数据库。请务必阅读它。

结论

惊人!你已经走了很长一段路,我很高兴你能走到这一步。我向你的顽强和决心致以敬意,以完成这幅插图。我相信值得您花时间学习这些概念,您认为这些概念很有价值。

我有一个礼物给你——我已经将用于这个插图的完整 R 脚本上传到 GitHub 存储库此处。该脚本为您节省了编写代码的压力,您可以快速运行代码。当然,要练习这个脚本,您需要调整脚本,例如更改数据库名称、表名称等。

此外,在创建与数据库的连接后,您还可以做许多其他事情。您可以从 RStudio 更新表或将新记录添加到数据库中的表。本文为您提供了最大化 RStudio 和 SQL 功能的第一步,也是至关重要的一步。从这里,您可以阅读包和函数的文档,以使用您从 RStudio 连接到的数据库执行不同的操作。

如果您喜欢这篇文章,请在评论区留下您的评论,并与其他人分享这篇文章。如果您可以单击“拍手”图标(最多允许拍手 50 次),这对我来说意义重大。

作为 Coursera Guided Project Network 的基于项目的课程讲师,我教授了一些关于使用 R 的课程,尤其是 R tidyverse 系列包,包括 dplyr 包。您可以在此处查看以参加任何这些课程。谢谢你!下次见!