使用RStudio连接窗格连接PostgreSQL数据库 [英] Using RStudio Connection Pane to connect with a PostgreSQL database

问题描述

我们正在使用PostgreSQL数据库。每次我想连接到它时，我都会手动执行以下代码来建立连接，并且可以正常工作。

We have a PostgreSQL database at work. Every time I want to connect to it, I'm manually executing the following code to build my connection and it works just fine.

    library(RPostgreSQL)
    con <- dbConnect(dbDriver("PostgreSQL"),
                     dbname   = "company_xy",
                     host     = "db.company_xy.de",
                     port     = 5432,
                     user     = rstudioapi::askForPassword("User"),
                     password = rstudioapi::askForPassword("Password!"))

我现在想开始使用连接窗格，但是尽管尝试了多种设置方法，但我始终无法正常工作。这是我按照 RStudio文档中所述的过程：

I now want to start using the Connections pane but despite trying in multiple ways to set it up, I never got it working. This is the process I followed as described in the RStudio documentation:

1。单击New Connection

2.选择PostgreSQL Unicode（x64）

3.在参数窗口中粘贴以下代码：

1.Click on New Connection
2.Select PostgreSQL Unicode(x64)
3.In the parameters window I paste in the following code:

    Driver = "PostgreSQL",
    Server = "db.company_xy.de",
    Database = "company_xy",
    UID = "my_username",
    PWD = "my_password",
    Port = 5432

我总是收到以下错误消息：

I always receive the following error message:

失败。 ：2.99：意外符号1：库（DBI）con<-
dbConnect（odbc :: odbc（），.connection_string = Driver = {PostgreSQL
Unicode（x64）}; Driver = PostgreSQL

Failure. :2.99: unexpected symbol 1: library(DBI) con <- dbConnect(odbc::odbc(), .connection_string = "Driver={PostgreSQL Unicode(x64)};Driver = "PostgreSQL

我尝试删除括号并在参数（{}，;）之间使用不同类型的分隔符，但一直没有结束。

I tried removing the parenthesis and using different kind of separators between the parameters ({}, ;) but to no end. The drivers for odbc and DBI are installed.

有人可以发现编码错误还是我做错了？

Can anyone spot a coding mistake or did I do it wrong?

推荐答案

只要删除双引号和空格，并用分号替换逗号，它应该可以工作。

It should work provided that you remove the double quotes and spaces, and replace the commas by semicolons.

因此，参数窗口应看起来像这样：

So your parameters window should look like that:

Server=db.company_xy.de;
Database=company_xy;
UID=my_username;
PWD=my_password;
Port=5432;

请注意，您不需要指定驱动程序，因为在其中选择 PostgreSQL Unicode（x64）时已经选择了驱动程序

Note that you don't need to specify the driver since you already chose it when you selected PostgreSQL Unicode(x64) in the previous window.

实际上，似乎此参数窗口的目的只是为了建立连接字符串，您可以在下面看到它。因此，从R脚本（或控制台）设置IMO更为直接：

Actually it seems that the point of this parameters windows is just to build the connection string that you can see below it. So IMO it's more direct to set the connection from your R script (or console):

library(DBI)
con <- dbConnect(
  odbc::odbc(),
  driver = "PostgreSQL Unicode(x64)",
  Server = "db.company_xy.de",
  Database = "company_xy",
  UID = rstudioapi::askForPassword("User"),
  PWD = rstudioapi::askForPassword("Password!"),
  Port = 5432
)

或者，如果您喜欢连接字符串：

or, if you like connection strings:

library(DBI)
con <- dbConnect(
  odbc::odbc(), 
  .connection_string = "Driver={PostgreSQL Unicode(x64)};Server=db.company_xy.de;Database=company_xy;UID=my_username;PWD=my_password;Port=5432;"
)

，但是后一种方法不允许您使用 rstudioapi :: askForPassword 。

but this latter method does not allow you to use rstudioapi::askForPassword.

在两种情况下，连接都将显示在连接窗格中。

In both cases the connection will appear in your Connections pane.

也许在未来您是否不需要为此使用 odbc ：
https://community.rstudio.com/t/postgresql-in-connection-tab/1817/4

Maybe in the future you won't need to use odbc for that: https://community.rstudio.com/t/postgresql-in-connection-tab/1817/4

这篇关于使用RStudio连接窗格连接PostgreSQL数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！