Блог

Я использую следующий код, однако он создает несколько подключений при вызове функции map, и они не закрываются. В результате моя база данных rds заполняется подключениями. Есть ли какой-либо способ изменить этот код, чтобы предотвратить так много подключений?

    connect.to.database <- function (dbname, schema = "public", host, port, user, pass) {
      con <- dbConnect(RPostgres::Postgres(),
                       dbname = dbname,
                       user = user,
                       password = pass,
                       host = host,
                       port = port)
      
      
      # this puts the schema in the search path, which means that instead of
      # having to use <schema name>.<table name> you can just write <table name>
      res <- dbSendQuery(con, paste0("SET search_path TO ",
                                     dbQuoteIdentifier(con, schema),
                                     ", public"))
      
      # check for errors
      dbFetch(res)
      dbClearResult(res)
      
      con
    }

    schemas <- dbGetQuery(connect.to.database(dbname, "public", host, port, user, password), paste0("SELECT schema_name FROM information_schema.schemata"))
    
    schema_names <- schemas %>% pull()
    
    schemas_tables <- map(.x = schema_names,~dbGetQuery(connect.to.database(dbname, "public", host, port, user, password), paste0("SELECT table_name FROM information_schema.tables WHERE table_schema = ","'",.x,"'")) %>% mutate(schema_name = .x)) %>%
                      bind_rows()
 

Создайте единый глобальный объект подключения и используйте его внутри map . (Я удаляю ненужное paste0 из вашего первого запроса.)

 conn <- connect.to.database(dbname, "public", host, port, user, password)
schema <- dbGetQuery(conn, "SELECT schema_name FROM information_schema.schemata")

schemas_tables <- map(
  .x = schema$schema_name,
  ~ dbGetQuery(conn, paste0("SELECT table_name FROM information_schema.tables WHERE table_schema = ","'",.x,"'")) %>%
    mutate(schema_name = .x)
) %>%
  bind_rows()
 

Возможно, вы захотите рассмотреть параметризованные запросы вместо создания строк запроса вручную. Хотя существуют опасения по поводу вредоносной SQL-инъекции (например, использование XKCD для Mom, известного как «Таблицы Little Bobby»), также вызывает беспокойство искаженные строки или ошибки Unicode-vs-ANSI, даже если запрос выполняется одним аналитиком данных. Оба DBI (с odbc ) и RODBC поддерживают параметризованные запросы, либо изначально, либо через дополнения.

Это изменило бы это на:

 schemas_tables <- map(
  .x = schema$schema_name,
  ~ dbGetQuery(conn, "SELECT table_name FROM information_schema.tables WHERE table_schema = ?",
               params = list(.x)) %>%
    mutate(schema_name = .x)
) %>%
  bind_rows()
 

Но, честно говоря, я думаю, что это может быть намного проще использовать IN вместо = . Опять же, с использованием привязки параметров.

 schemas_tables <- dbGetQuery(conn, "SELECT table_name FROM information_schema.tables WHERE table_schema IN (?)",
                             params = list(schema$schema_name))
 

(Не map требуется.)

Или я считаю, что вы можете сделать это в одном запросе, а не в двух.

 dbGetQuery(conn, "
    select table_name
    from information_schema.tables
    where table_schema in (
      select schema_name from information_schema.schemata
    )")
 

Помните

… чтобы закрыть соединение, когда вы закончите.

 dbDisconnect(conn)