Pull to refresh

Взаимодействие R с базами данных на примере Microsoft SQL Server и других СУБД

Reading time16 min
Views18K

Поскольку львиная доля бизнес информации храниться в базах данных. На каком бы языке программирования вы не писали, вам придётся производить различные действия с ними.


В этой статье я расскажу о двух интерфейса для работы с базами данных в R. Большая часть примеров демонстрируют работу с Microsoft SQL Server, тем не менее все примеры кода будут работать и с другими базами данных, такими как: MySQL, PostgreSQL, SQLite, ClickHouse, Google BigQuery и др.


image


Содержание


Если вы интересуетесь анализом данных, и в частности языком R, возможно вам будут интересны мои telegram и youtube каналы. Большая часть контента которых посвящена языку R.



Требуемое программное обеспечение


Для того, что бы повторить все описанные в статье примеры работы с СУБД вам потребуется перечисленное ниже, бесплатное программное обеспечение:


  1. Язык R;
  2. Среда разработки RStudio;
  3. Система Управления Базами Данных, на выбор:
    3.1. Microsoft SQL Server
    3.2. MySQL
    3.3. PostgreSQL

Пакет DBI


Пакет DBI является наиболее популярным и удобным способом взаимодействия с базами данных в R.


DBI предоставляет вам набор функций, с помощью которых вы можете управлять базами данных. Но для подключения к базам данных требуется установка дополнительных пакетов, которые являются драйверами к различным системам управления базами данных (СУБД).


Список основных функций DBI


  • dbConnect — подключение к базе данных;
  • dbWriteTable — запись таблицы в базу данных;
  • dbReadTable — загрузка таблицы из базы данных;
  • dbGetQuery — загрузка результата выполнения запроса;
  • dbSendQuery — отправка запроса к базе данных;
  • dbFetch — извлечение элементов из набора результатов;
  • dbExecute — выполнение запросов на обновление / удаление / вставку данных в таблицы;
  • dbGetInfo — запрос информацию о результате запроса или подключении;
  • dbListFields — запрос списка полей таблицы;
  • dbListTables — запрос списка таблиц базы данных;
  • dbExistsTable — проверка наличия таблицы в базе данных;
  • dbRemoveTable — удаление таблицы из базы данных;
  • dbDisconnect — разрыв отсоединения с базы данных.

Подключение к базам данных


Для взаимодействия с базами данных предварительно к ним необходимо подключиться. В зависимости от СУБД с которой вы планируете работать вам потребуется дополнительный пакет, ниже перечень наиболее часто используемых.


  • odbc — Драйвер для подключения через ODBC интерфейс;
  • RSQLite — Драйвер к SQLite;
  • RMySQL / RMariaDB — Драйвер к СУБД MySQL и MariaDB;
  • RPostgreSQL — Драйвер к PosrtgreSQL;
  • bigrquery — Драйвер к Google BigQuery;
  • RClickhouse / clickhouse — Драйвер к ClickHouse;
  • RMSSQL — Драйвер к Microsoft SQL Server (MS SQL), на момент написания статьи присутствует только на GitHub.

Пакет DBI поставляется с базовой комплектацией R, но пакеты, которые являются драйверами к базам данных необходимо устанавливать с помощью команды install.packages("название драйвера").


Для установки пакетов с GitHub вам также понадобится дополнительный пакет — devtools. Например пакет RMSSQL на данный момент не опубликован в основном репозитории R пакетов, для его установки воспользуйтесь следующим кодом:


install.packages("devtools")
devtools::install_github("bescoto/RMSSQL")

Пример подключения к Microsoft SQL Server с помощью пакета odbc


Перед использованием любого пакета в R сессии его предварительно необходимо подключить с помощью функции library("название пакета").


Я неспроста выбрал Microsoft SQL Server в качестве основной СУБД на которой будет приведена большая часть примеров этой статьи. Дело в том, что это достаточно популярная база данных, но при этом она до сих пор не имеет драйвера для подключения из R опубликованного на CRAN.


Но к счастью SQL Server, как и практически любая другая база имеет ODBC (англ. Open Database Connectivity) интерфейс для подключения. Для подключения к СУБД через ODBC интерфейс в R есть ряд пакетов. Первым мы рассмотрим подключение через пакет odbc.


Простое подключение к БД через odbc интерфейс
# установка пакета odbc
install.packages("odbc")

# подключение пакета
library(odbc)

# подключение к MS SQL
con <- dbConnect(drv = odbc(),
                 Driver   = "SQL Server",
                 Server   = "localhost",
                 Database = "mybase",
                 UID      = "my_username",
                 PWD      = "my_password",
                 Port     = 1433)

В функцию dbConnect() вам необходимо первым аргументом drv передать функцию, которая является драйвером для подключения к СУБД (odbc()). Такие функции обычно называются также, как и СУБД, и поставляются с пакетами которые являются драйверами для DBI.


Далее необходимо перечислить параметры подключения. Для подключения к MS SQL через ODBC необходимо задать следующие параметры:


  • Driver — Название ODBC драйвера;
  • Server — IP адрес SQL сервера;
  • Database — Название базы данных к которой необходимо подключиться;
  • UID — Имя пользователя базы данных;
  • PWD — Пароль;
  • Port — Порт для подключения, у SQL Server по умолчанию порт 1433.

ODBC драйвер для подключения к Microsoft SQL Server включен в комплектацию Windows, но он может иметь и другое название. Посмотреть список установленных драйверов можно в Администраторе источника данных ODBC. Запустить администратор источника данных в Windows 10 можно по следующему пути:


  • 32-разрядной версии: %systemdrive%\Windows\SysWoW64\Odbcad32.exe
  • 64-разрядной версии: %systemdrive%\Windows\System32\Odbcad32.exe


Получить список всех установленных на вашем ПК драйверов также можно с помощью функции odbcListDrivers().


   name                                  attribute        value                                   
   <chr>                                 <chr>            <chr>                                   
 1 SQL Server                            APILevel         2                                       
 2 SQL Server                            ConnectFunctions YYY                                     
 3 SQL Server                            CPTimeout        60                                      
 4 SQL Server                            DriverODBCVer    03.50                                   
 5 SQL Server                            FileUsage        0                                       
 6 SQL Server                            SQLLevel         1                                       
 7 SQL Server                            UsageCount       1                                       
 8 MySQL ODBC 5.3 ANSI Driver            UsageCount       1                                       
 9 MySQL ODBC 5.3 Unicode Driver         UsageCount       1                                       
10 Simba ODBC Driver for Google BigQuery Description      Simba ODBC Driver for Google BigQuery2.0
# ... with 50 more rows

Скачать ODBC драйвера для других СУБД можно по следующим ссылкам:



Для различных СУБД название параметров для подключения могут быть другими, например:


  • PostgreSQL / MySQL / MariaDB — user, password, host, port, dbname;
  • GoogleBigQuery — project, dataset;
  • ClickHouse — user, password, db, port, host;

С помощью администратора источника данных ODBC вы можете запустить мастер для создания ODBC источника данных. Для этого достаточно открыть администратор, перейти на вкладку "Пользовательский DSN" и нажать кнопку "Добавить...".



При создании источника данных используя администратор вы присваиваете ему имя, DSN (Data Source Name).



В примере выше мы создали источник данных с DSN "my_test_source". Теперь мы можем использовать этот источник для подключения к Microsoft SQL Server, и не указывать в коде остальные параметры подключения.


Подключение к БД через odbc интерфейс с использованием DSN
# подключение через DSN
con <- dbConnect(odbc(),
                DSN = "my_test_source",
                UID = "my_username",
                PWD = "my_password")

Посмотреть имена всех созданных на вашем ПК источников данных ODBC можно с помощью функции odbcListDataSources().


   name            description                          
   <chr>           <chr>                                
 1 BQ              Simba ODBC Driver for Google BigQuery
 2 BQ_main         Simba ODBC Driver for Google BigQuery
 3 BQ ODBC         Simba ODBC Driver for Google BigQuery
 4 OLX             Simba ODBC Driver for Google BigQuery
 5 Multicharts     Simba ODBC Driver for Google BigQuery
 6 PostgreSQL35W   PostgreSQL Unicode(x64)              
 7 hillel_bq       Simba ODBC Driver for Google BigQuery
 8 blog_bq         Simba ODBC Driver for Google BigQuery
 9 MyClientMSSQL   SQL Server                           
10 local_mssql     SQL Server                           
11 MSSQL_localhost SQL Server                           
12 my_test_source  SQL Server                           
13 Google BigQuery Simba ODBC Driver for Google BigQuery

Пример подключения к Microsoft SQL Server с помощью пакета RMSSQL


RMSSQL не опубликован на CRAN, поэтому установить его можно с GitHub с помощью пакета devtools.


install.packages("devtools")
devtools::install_github("bescoto/RMSSQL")

Пример подключения с помощью DBI драйвера RMSSQL
# подключение требуемых пакетов
library(RJDBC)
library(RMSSQL)
library(DBI)

# через RMSSQL
con <- dbConnect(MSSQLServer(), 
                 host     = 'localhost', 
                 user     = 'my_username', 
                 password = 'my_password', 
                 dbname   = "mybase")

В большинстве случаев, используя для работы с базами данных пакет DBI, вы будете подключаться именно таким способом. Т.е. устанавливать один из требуемых пакетов — драйверов, передавая в качестве значения аргумента drv функции dbConnect, функцию — драйвер для подключения к нужной вам СУБД.


Пример подключения к MySQL, PostgreSQL, SQLite и BigQuery
# подключение к MySQL
library(RMySQL)

con <- dbConnect(MySQL(), 
                 host     = 'localhost', 
                 user     = 'my_username', 
                 password = 'my_password', 
                 dbname   = "mybase",
                 host     = "localhost")

# подключение к PostrgeSQL
library(RPostgreSQL)

con <- dbConnect(PostgreSQL(), 
                 host     = 'localhost', 
                 user     = 'my_username', 
                 password = 'my_password', 
                 dbname   = "mybase",
                 host     = "localhost")

# подключение к PostrgeSQL
library(RPostgreSQL)

con <- dbConnect(PostgreSQL(), 
                 host     = 'localhost', 
                 user     = 'my_username', 
                 password = 'my_password', 
                 dbname   = "mybase",
                 host     = "localhost")

# Подключение к SQLite
library(RSQLite)

# connection or create base
con <- dbConnect(drv = SQLite(),
                 "localhost.db")

# Подключение к Google BigQuery
library(bigrquery)

con <- dbConnect(drv     = bigquery(),
                 project = "my_proj_id",
                 dataset = "dataset_name")

Как скрыть пароли от базы данных в R скриптах


Выше я привёл несколько примеров которые можно использовать для подключения к любой базе данных но в них есть один минус, в таком виде все доступы к базам данных, включая пароли, хранятся в виде текста в самих скриптах.


Если все ваши скрипты развёрнуты и запускаются исключительно локально на вашем ПК, и он при этом защищён паролем, то скорее всего никакой проблемы в этом не будет. Но если вы совместно с кем то работаете на одном сервере то хранение паролей от баз данных в тексте ваших скриптов не лучшее решение.


В любой операционной системе есть утилита для управления учётными данными. Например, в Windows это диспетчер учетных данных (Credential Manager). Добавить в это хранилище пароль который вы используете для подключения к базе данных можно через пакет keyring. Пакет кроссплатформенный и приведённый пример будет работать в любой операционной системе, как минимум на Windows, MacOS и Linux.


# install.packages("keyring")

# подключаем пакет
library(keyring)
library(RMSSQL)

# создаём ключ
key_set_with_value(service = "mssql", 
                   username = "my_username",
                   password = "my_password")

# подключение через RMSSQL
con <- dbConnect(MSSQLServer(), 
                 host     = 'localhost', 
                 user     = 'my_username', 
                 password = key_get("mssql", "my_username"), 
                 dbname   = "mybase")

Т.е. с помощью функции key_set_with_value() вы добавляете пароль в хранилище учётных данных, а с помощью key_get() запрашиваете его, при этом запросить пароль может только тот пользователь который добавил его в хранилище. С помощью keyring можно хранить пароли не только от баз данных, но и от любых сервисов, а так же авторизационные токены при работе с API.


Создание таблиц и запись в базу данных


Запись в базу данных осуществляется функцией dbWriteTable().


Аргументы функции dbWriteTable():


Жирным шрифтом выделены обязательные аргументы, курсивом — не обязательные


  • conn — объект подключения к СУБД, созданный с помощью функции dbConnect;
  • name — название таблицы в СУБД, в которую будут записаны данные;
  • value — таблица (объект класса data.frame / data.table / tibble_frame) в R, данные из которого будут записаны в СУБД;
  • row.names — Добавляет в таблицу столбец row_names, с номерами строк, по умолчанию имеет значение FALSE.
  • overwrite — Перезаписывать таблицу, если таблица с именем указанным в аргументе name уже присутвует в СУБД, по умолчанию имеет значение FALSE;
  • append — Дописывать данные, если таблица с именем указанным в аргументе name уже присутвует в СУБД, по умолчанию имеет значение FALSE;
  • field.types — Принимает на вход именованный вектор, и задаёт тип данных в каждом поле при записи в СУБД, по умолчанию имеет значение NULL;
  • temporary — Позволяет создавать временные таблицы в СУБД, которые будут доступны до момента разрыва соединения с базой, по умолчанию имеет значение FALSE.

Пример записи данных в СУБД через DBI
# подключаем пакет
library(odbc)
# соединяемся с базой через DSN
con <- dbConnect(odbc(),
                DSN = "my_test_source",
                UID = "my_username",
                PWD = "my_password")
# создаём в базе таблицу iris, и записываем в неё данные из встроенного в R набора iris
dbWriteTable(conn  = con,
             name  = "iris", 
             value = iris)

# разрыв соединения с БД
dbDisconnect(con)

Для просмотра таблиц в базе данных служит функция dbListTables(), для удаления таблиц dbRemoveTable()


Пример запроса списка таблиц и удаления таблицы из СУБД
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# просмотр списка таблиц
dbListTables(con)
# удаление таблицы iris
dbRemoveTable(con, "iris")

# разрыв соединения с БД
dbDisconnect(con)

Чтение данных из СУБД


С помощью DBI вы можете запрашивать либо таблицы целиком, либо результат выполнения вашего SQL запроса. Для выполнения этих операций используются функции dbReadTable() и dbGetQuery().


Пример запроса таблицы iris из СУБД
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# загружзка табоицы iris в объект iris
dbiris <- dbReadTable(con, "iris")

# разрыв соединения с БД
dbDisconnect(con)

Пример загрузки результата выполнения SQL из СУБД
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# Запрашиваем результат выполнения запроса
setosa <- dbGetQuery(con,
                     "SELECT * FROM iris WHERE Species = 'setosa'")

# разрыв соединения с БД
dbDisconnect(con)

Манипулирование данными в СУБД (DML)


Рассмотренная выше функция dbGetQuery() используется исключительно для запросов на выборку данных (SELECT).


Для операций манипуляций с данными, таких как UPDATE, INSERT, DELETE, в DBI существует функция dbExecute().


Пример кода для манипуляции данными в СУБД
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# Вставка строк (INSERT)
dbExecute(con, 
          "INSERT INTO iris (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species])
           VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')")

# Обновление данных (UPDATE)
dbExecute(con, 
          "UPDATE iris
           SET [Species] = 'old_value'
           WHERE row_names = 51")

# Удаление строк из таблицы (DELETE)
dbExecute(con, "DELETE FROM iris WHERE row_names = 51")

# разрыв соединения с БД
dbDisconnect(con)

Транзакции в СУБД


Транзакция это последовательное выполнение операций чтения и записи. Окончанием транзакции может быть либо сохранение изменений (фиксация, commit) либо отмена изменений (откат, rollback). Применительно к БД транзакция это нескольких запросов, которые трактуются как единый запрос.

Цитата из статьи "Транзакции и механизмы их контроля"


Транзакция инкапсулирует несколько операторов SQL в элементарную единицу. В DBI начало транзакции инициируется с помощью dbBegin() и далее либо подтверждается с помощью dbCommit(), либо отменяется с помощью dbRollback(). В любом случае СУБД гарантирует, что: либо все, либо ни одно из утверждений не будут применены к данным.


Для примера, давайте в ходе транзакции добавим в таблицу iris 51 строку, далее изменим значение Sepal.Width в 5 строке, и удалим 43 строку из таблицы.


Пример кода проведения транзакции
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# запрашиваем значения до внесения изменений
dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")

#   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
# 1         5          5.0         3.6          1.4         0.2     setosa
# 2        43          4.4         3.2          1.3         0.2     setosa
# 3        51          7.0         3.2          4.7         1.4 versicolor

# инициируем начало транзакции
dbBegin(con)

# добавляе строку
dbExecute(con, 
          "INSERT INTO iris 
          (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species])
           VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')")

# меняем строку 
dbExecute(con, 
          "UPDATE iris
           SET [Sepal.Width] = 8
           WHERE row_names = 5")

# удаляем строку 43
dbExecute(con, "DELETE FROM iris WHERE row_names = 43")

# подтверждаем транзакцию
dbCommit(con)

# запрашиваем значения после внесения изменений
dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")

#   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
# 1         5            5         8.0          1.4         0.2     setosa
# 2        51            7         3.2          4.7         1.4 versicolor
# 3        51            5         3.3          1.7         0.3 new_values

Пример кода отмены транзакции
# подключение пакета
library(odbc)

# подключение к БД
con <- dbConnect(odbc(),
                 DSN = "my_test_source",
                 UID = "my_username",
                 PWD = "my_password")

# запрашиваем значения до внесения изменений
dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")

#   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
# 1         5          5.0         3.6          1.4         0.2     setosa
# 2        43          4.4         3.2          1.3         0.2     setosa
# 3        51          7.0         3.2          4.7         1.4 versicolor

# инициируем начало транзакции
dbBegin(con)

# добавляе строку
dbExecute(con, 
          "INSERT INTO iris 
          (row_names, [Sepal.Length], [Sepal.Width], [Petal.Length], [Petal.Width], [Species])
           VALUES (51, 5.0, 3.3, 1.7, 0.3, 'new_values')")

# меняем строку 
dbExecute(con, 
          "UPDATE iris
           SET [Sepal.Width] = 8
           WHERE row_names = 5")

# удаляем строку 43
dbExecute(con, "DELETE FROM iris WHERE row_names = 43")

# отменяем транзакцию
dbRollback(con)

# запрашиваем значения после внесения изменений
dbGetQuery(con, "SELECT * FROM iris WHERE row_names IN (5, 43, 51)")

#   row_names Sepal.Length Sepal.Width Petal.Length Petal.Width    Species
# 1         5          5.0         3.6          1.4         0.2     setosa
# 2        43          4.4         3.2          1.3         0.2     setosa
# 3        51          7.0         3.2          4.7         1.4 versicolor

Объектно ориентированный интерфейс взаимодействия с базами данных


В базовом R объектно ориентированное программирование реализовано на S3 классах, в основе которых лежат обобщённые функции. Эта идея имеет мало общего с классическим объектно ориентированным программированием. Тем не менее пакет R6 реализует классическую реализацию объектно ориентированного программирования в языке R.


Узнать больше про S3 классы можно в статье “ООП в языке R (часть 1): S3 классы”, про R6 классы в статье “ООП в языке R (часть 2): R6 классы”.

Использовать классическое ООП при работе с базами данных довольно удобно. Вы создаёте объект подключения и обращаетесь к его методам. Такую возможность вам даёт пакет rocker, который является обёрткой над DBI.


В rocker реализованы все необходимые функции из DBI. Для начала необходимо создать объект базы данных, и настроить драйвер.


Начало работы с пакетом rocker
# устанавливаем rocker
install.packages('rocker')

# подключаем пакеты
library(rocker)
library(odbc)

# создаём объект подключения
db <- newDB(id = 'main db')

# настраиваем драйвер
db$setupDriver(
  drv = odbc(),
  Driver   = "SQL Server",
  Server   = "localhost",
  Database = "mybase",
  UID      = "my_username",
  PWD      = "my_password",
  Port     = 1433
)

# инициализируем подключение
db$connect()

При создании объекта базы данных вы можете использовать аргумент id, тем самым настроить в одной сессии сразу несколько подключений, пометив их идентификаторы.


Под некоторый популярные базы данных в rocker есть встроенные методы настройки драйверов:


db$setupMariaDB()
db$setupPostgreSQL()
db$setupSQLite()

Созданный объект базы данных имеет весь функционал DBI в виде своих методов, но т.к. сам объект подключение хранится внутри экземпляра класса, вам не потребуется использовать его в каждой функции. Ниже пример работы с базой данных с помощью ООП и пакета rocker:


Взаимодействие с базой данных с помощью rocker
# создаём таблицу
db$writeTable(
    name  = "iris", 
    value = iris
)

# посмотреть списка таблиц
db$listTables()
# чтение таблицы
dbiris <- db$readTable("iris")
# чтение результата запроса
db$getQuery("SELECT * FROM iris WHERE Species = 'setosa'")
# удаление таблицы iris
db$removeTable("iris")

# разрываем соединение
db$disconnect()

По такому же принципу в rocker реализованы и все остальные функции DBI, в том числе функции для работы с транзакциями.


Помимо объекта подключения вы можете хранить в экземпляре класса имя пользователя и пароль используемые при подключении. Реализовать это можно с помощью аргумента protect:


Хранение учётных данных в экземпляре класса rocker
db <- rocker::newDB()

# настраиваем драйвер
db$setupDriver(
  drv = odbc(),
  Driver   = "SQL Server",
  Server   = "localhost",
  Database = "mybase",
  UID      = "my_username",
  PWD      = "my_password",
  Port     = 1433,
  protect = c("PWD", "UID")
)

# инициализируем подключение
db$connect()

В таком случае при повторном подключение к базе вам не понадобится передавать учётные данные:


Повторное подключение при хранении учётных данных в экземпляре класса rocker
# закрываем соединение
db$disconnect()

# инициируем повторное подключение
# при этом не указываем повторно учётные данные
db$connect()

Пакет RODBC


Пакет RODBC предоставляет автономный интерфейс для подключения и работы с СУБД через ODBC интерфейс.


RODBC не совместим с DBI, т.е. вы не можете использовать объект подключения созданный с помощью RODBC в функциях предоставляемых пакетом DBI.


Основные функции пакета RODBC


  • odbcConnect — Подключение к СУБД через DSN;
  • odbcDriverConnect — Подключение к базе через строку подключения;
  • sqlQuery — Отправка запроса в СУБД, и получение результата его выполнения. Поддерживает запросы любого типа: SELECT, UPDATE, INSERT, DELETE.
  • sqlFetch — Получить целиком таблицу из СУБД;
  • sqlTables — Получить список таблиц в базе.
  • sqlSave — Создание новой таблицы в базе данных, или добавление новых данных в уже существующую таблицу;
  • sqlUpdate — Обновление данных в таблице которая уже существует в СУБД;
  • sqlDrop — Удаление таблицы в СУБД;
  • odbcClose — Завершение соединения с СУБД.

Пример работы с RODBC


С моей точки зрения RODBC менее функционален чем DBI, но в нём есть все необходимые функции для работы с СУБД.


Пример взаимодействия с СУБД через RODBC
# подключение пакета
library(RODBC)
# строка подключения
con_string <- odbcDriverConnect(connection = "Driver=SQL Server;Server=localhost;Database=mybase;UID=my_username;PWD=my_password;Port=1433")
# подключение через DSN
con_dsn    <- odbcConnect(dsn = "my_test_source",
                          uid = "my_username",
                          pwd = "my_password")

# создание таблицы в базе
sqlSave(con_dsn,
        dat       = iris,
        tablename = "iris")

# дописать строки в табдицу iris
sqlSave(con_dsn,
        dat       = iris,
        tablename = "iris",
        append    = TRUE)

# запрашиваем первые 4 строки
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

# вносим изменение в данные в R
iris[1, 5] <- "virginica"

# обновляем табицу в СУБД
sqlUpdate(con_dsn,
          dat       = iris,
          tablename = "iris")

# запрашиваем первые 4 строки после изменения данных
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

# удаление таблицы
sqlDrop(con_dsn, sqtable = "iris")

# разрыв соеденения с базой
odbcCloseAll()

Транзакционность


По умолчанию транзакционность в RODBC выключена. Управление транзакциями осуществляется двумя функциями.


  • odbcSetAutoCommit — Переключение между обычным и транзакционным режимом работы с СУБД;
  • odbcEndTran — Подтверждение или отмена транзакции.

Включение и отключение транзакционного режима осуществляется функцией odbcSetAutoCommit с помощью аргумента autoCommit.


Примре работы в транзакционном режиме в RODBC
# подключение пакета
library(RODBC)
# подключение через DSN
con_dsn    <- odbcConnect(dsn = "my_test_source",
                          uid = "my_username",
                          pwd = "my_password")

# создание таблицы в базе
sqlSave(con_dsn,
        dat       = iris,
        tablename = "iris")

# включение транзакционного режима
odbcSetAutoCommit(con_dsn, autoCommit = FALSE)

# запрашиваем первые 4 строки
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

#   rownames SepalLength SepalWidth PetalLength PetalWidth Species
# 1        1         5.1        3.5         1.4        0.2  setosa
# 2        2         4.9        3.0         1.4        0.2  setosa
# 3        3         4.7        3.2         1.3        0.2  setosa
# 4        4         4.6        3.1         1.5        0.2  setosa

# вносим изменение в данные в R
iris[1, 5] <- "virginica"

# обновляем табицу в СУБД
sqlUpdate(con_dsn,
          dat       = iris,
          tablename = "iris")

# запрашиваем первые 4 строки
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

#   rownames SepalLength SepalWidth PetalLength PetalWidth   Species
# 1        1         5.1        3.5         1.4        0.2 virginica
# 2        2         4.9        3.0         1.4        0.2    setosa
# 3        3         4.7        3.2         1.3        0.2    setosa
# 4        4         4.6        3.1         1.5        0.2    setosa

# отменяем изменения
odbcEndTran(con_dsn, commit = FALSE)

# запрашиваем первые 4 строки
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

#   rownames SepalLength SepalWidth PetalLength PetalWidth Species
# 1        1         5.1        3.5         1.4        0.2  setosa
# 2        2         4.9        3.0         1.4        0.2  setosa
# 3        3         4.7        3.2         1.3        0.2  setosa
# 4        4         4.6        3.1         1.5        0.2  setosa

# обновляем табицу в СУБД
sqlUpdate(con_dsn,
          dat       = iris,
          tablename = "iris")

# применяем изменения
odbcEndTran(con_dsn, commit = TRUE)

# запрашиваем первые 4 строки после изменения данных
sqlFetch(con_dsn, "iris", rownames = FALSE, max = 4)

#   rownames SepalLength SepalWidth PetalLength PetalWidth   Species
# 1        1         5.1        3.5         1.4        0.2 virginica
# 2        2         4.9        3.0         1.4        0.2    setosa
# 3        3         4.7        3.2         1.3        0.2    setosa
# 4        4         4.6        3.1         1.5        0.2    setosa

# разрыв соеденения с базой
odbcClose(con_dsn)

Заключение


Два описанных в статье метода работы с базами данных на языке R, DBI и RODBC, достаточно универсальны, и будут работать практически с любой СУБД.


Единственная разница в работе между различными СУБД заключается в процессе подключения. Для большинства популярных СУБД существуют отдельные пакеты которые являются драйверами. Для остальных СУБД необходимо настраивать подключение через ODBC интерфейс используя пакеты odbc или RODBC. Все остальные манипуляции, вне зависимости от выбранной вами СУБД, будут неизменны. Исключением является отправка SQL запросов, в зависимости от SQL диалекта который поддерживается СУБД с которой вы работаете.


P.S.
Если вы дочитали стататью, то наверняка интересуетесь языком R, в таком случае думаю вам будет интересен мой телеграм и youtube каналы, большая часть контента которых посвящена языку R. Подписывайтесь.

Tags:
Hubs:
If this publication inspired you and you want to support the author, do not hesitate to click on the button
Total votes 13: ↑12 and ↓1+11
Comments7

Articles