Ruby / DBI Tutorial

В этой главе рассказывается, как получить доступ к базе данных с помощью Ruby. Модуль Ruby DBI предоставляет независимый от базы данных интерфейс для сценариев Ruby, аналогичный интерфейсу модуля Perl DBI.

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

DBI может взаимодействовать со следующим —

• ADO (объекты данных ActiveX)
• DB2
• Frontbase
• Msql
• MySQL
• ODBC
• оракул
• OCI8 (Oracle)
• PostgreSQL
• Прокси-сервер
• SQLite
• SQLRelay

Архитектура приложения DBI

DBI не зависит от любой базы данных, доступной в бэкэнде. Вы можете использовать DBI независимо от того, работаете ли вы с Oracle, MySQL или Informix и т. Д. Это ясно из следующей диаграммы архитектуры.

Общая архитектура для Ruby DBI использует два уровня:

• Уровень интерфейса базы данных (DBI). Этот уровень не зависит от базы данных и предоставляет набор общих методов доступа, которые используются одинаково, независимо от типа сервера базы данных, с которым вы общаетесь.

• Уровень драйвера базы данных (DBD). Этот слой зависит от базы данных; разные драйверы обеспечивают доступ к различным ядрам базы данных. Есть один драйвер для MySQL, другой для PostgreSQL, другой для InterBase, другой для Oracle и так далее. Каждый драйвер интерпретирует запросы от уровня DBI и отображает их на запросы, подходящие для данного типа сервера базы данных.

Уровень интерфейса базы данных (DBI). Этот уровень не зависит от базы данных и предоставляет набор общих методов доступа, которые используются одинаково, независимо от типа сервера базы данных, с которым вы общаетесь.

Уровень драйвера базы данных (DBD). Этот слой зависит от базы данных; разные драйверы обеспечивают доступ к различным ядрам базы данных. Есть один драйвер для MySQL, другой для PostgreSQL, другой для InterBase, другой для Oracle и так далее. Каждый драйвер интерпретирует запросы от уровня DBI и отображает их на запросы, подходящие для данного типа сервера базы данных.

Предпосылки

Если вы хотите писать сценарии Ruby для доступа к базам данных MySQL, вам необходимо установить модуль Ruby MySQL.

Этот модуль действует как DBD, как описано выше, и может быть загружен с https://www.tmtm.org/en/mysql/ruby/.

Получение и установка Ruby / DBI

Вы можете скачать и установить модуль Ruby DBI из следующего местоположения —

https://imgur.com/NFEuWe4/embed

Перед началом этой установки убедитесь, что у вас есть права root. Теперь выполните следующие шаги:

Шаг 1

$ tar zxf dbi-0.2.0.tar.gz

Шаг 2

Перейдите в каталог дистрибутива dbi-0.2.0 и настройте его, используя скрипт setup.rb в этом каталоге. Самая общая команда конфигурации выглядит следующим образом, без аргументов после аргумента config. Эта команда настраивает дистрибутив для установки всех драйверов по умолчанию.

$ ruby setup.rb config

Чтобы быть более конкретным, укажите параметр —with, в котором перечислены конкретные части дистрибутива, которые вы хотите использовать. Например, чтобы настроить только основной модуль DBI и драйвер уровня DBD MySQL, введите следующую команду:

$ ruby setup.rb config --with = dbi,dbd_mysql

Шаг 3

Последний шаг — собрать драйвер и установить его с помощью следующих команд:

$ ruby setup.rb setup
$ ruby setup.rb install

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

Предполагая, что мы собираемся работать с базой данных MySQL, перед подключением к базе данных убедитесь в следующем:

• Вы создали базу данных TESTDB.

• Вы создали РАБОТНИКА в TESTDB.

• В этой таблице есть поля FIRST_NAME, LAST_NAME, AGE, SEX и INCOME.

• Идентификатор пользователя «testuser» и пароль «test123» установлены для доступа к TESTDB.

• Ruby Module DBI правильно установлен на вашем компьютере.

• Вы прошли учебник по MySQL, чтобы понять основы MySQL.

Вы создали базу данных TESTDB.

Вы создали РАБОТНИКА в TESTDB.

В этой таблице есть поля FIRST_NAME, LAST_NAME, AGE, SEX и INCOME.

Идентификатор пользователя «testuser» и пароль «test123» установлены для доступа к TESTDB.

Ruby Module DBI правильно установлен на вашем компьютере.

Вы прошли учебник по MySQL, чтобы понять основы MySQL.

Ниже приведен пример соединения с базой данных MySQL «TESTDB».

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   # get server version string and display it
   row = dbh.select_one("SELECT VERSION()")
   puts "Server version: " + row[0]
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

При запуске этого скрипта он выдает следующий результат на нашей машине с Linux.

Server version: 5.0.45

Если соединение с источником данных установлено, то дескриптор базы данных возвращается и сохраняется в dbh для дальнейшего использования, в противном случае для dbh устанавливается значение nil, а e.err и e :: errstr возвращают код ошибки и строку ошибки соответственно.

Наконец, перед выходом убедитесь, что соединение с базой данных закрыто и ресурсы освобождены.

ВСТАВИТЬ Операция

Операция INSERT требуется, когда вы хотите создать свои записи в таблице базы данных.

Как только соединение с базой данных установлено, мы готовы создавать таблицы или записи в таблицах базы данных, используя метод do или метод подготовки и выполнения .

Использование do Statement

Утверждения, которые не возвращают строки, могут быть выполнены с помощью вызова метода do database handle. Этот метод принимает строковый аргумент оператора и возвращает количество строк, затронутых оператором.

dbh.do("DROP TABLE IF EXISTS EMPLOYEE")
dbh.do("CREATE TABLE EMPLOYEE (
   FIRST_NAME  CHAR(20) NOT NULL,
   LAST_NAME  CHAR(20),
   AGE INT,  
   SEX CHAR(1),
   INCOME FLOAT )" );

Аналогично, вы можете выполнить инструкцию SQL INSERT, чтобы создать запись в таблице EMPLOYEE.

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   dbh.do( "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
      VALUES ('Mac', 'Mohan', 20, 'M', 2000)" )
   puts "Record has been created"
   dbh.commit
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
   dbh.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Использование подготовить и выполнить

Вы можете использовать методы prepare и execute класса DBI для выполнения оператора SQL через код Ruby.

Создание записи включает следующие шаги:

• Подготовка оператора SQL с оператором INSERT. Это будет сделано с использованием метода prepare .

• Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью метода execute .

• Выпускать заявление о ручке. Это будет сделано с использованием готового API

• Если все в порядке, передайте эту операцию, иначе вы сможете откатить всю транзакцию.

Подготовка оператора SQL с оператором INSERT. Это будет сделано с использованием метода prepare .

Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью метода execute .

Выпускать заявление о ручке. Это будет сделано с использованием готового API

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

Ниже приведен синтаксис для использования этих двух методов:

sth = dbh.prepare(statement)
sth.execute
   ... zero or more SQL operations ...
sth.finish

Эти два метода могут использоваться для передачи значений связывания в операторы SQL. Возможен случай, когда вводимые значения не указаны заранее. В таком случае используются значения привязки. Вместо фактических значений используется знак вопроса ( ? ), А затем фактические значения передаются через API execute ().

Ниже приведен пример создания двух записей в таблице EMPLOYEE:

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   sth = dbh.prepare( "INSERT INTO EMPLOYEE(FIRST_NAME, LAST_NAME, AGE, SEX, INCOME)
      VALUES (?, ?, ?, ?, ?)" )
   sth.execute('John', 'Poul', 25, 'M', 2300)
   sth.execute('Zara', 'Ali', 17, 'F', 1000)
   sth.finish
   dbh.commit
   puts "Record has been created"
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
   dbh.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Если одновременно существует несколько INSERT, то подготовка инструкции, а затем ее выполнение несколько раз в цикле более эффективна, чем вызов do каждый раз в цикле.

ЧИТАЙТЕ Операцию

Чтение Операция с любой базой данных означает получение некоторой полезной информации из базы данных.

Как только наше соединение с базой данных установлено, мы готовы сделать запрос в эту базу данных. Мы можем использовать либо метод do, либо подготовить и выполнить методы для извлечения значений из таблицы базы данных.

Выборка записи включает следующие шаги:

• Подготовка SQL-запроса на основе обязательных условий. Это будет сделано с использованием метода prepare .

• Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью метода execute .

• Получение всех результатов по одному и печать этих результатов. Это будет сделано методом извлечения .

• Выпускать заявление о ручке. Это будет сделано с использованием метода финиша .

Подготовка SQL-запроса на основе обязательных условий. Это будет сделано с использованием метода prepare .

Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью метода execute .

Получение всех результатов по одному и печать этих результатов. Это будет сделано методом извлечения .

Выпускать заявление о ручке. Это будет сделано с использованием метода финиша .

Ниже приведена процедура запроса всех записей из таблицы EMPLOYEE с зарплатой более 1000.

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   sth = dbh.prepare("SELECT * FROM EMPLOYEE WHERE INCOME > ?")
   sth.execute(1000)

   sth.fetch do |row|
   printf "First Name: %s, Last Name : %s\n", row[0], row[1]
   printf "Age: %d, Sex : %s\n", row[2], row[3]
   printf "Salary :%d \n\n", row[4]
end
   sth.finish
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Это даст следующий результат —

First Name: Mac, Last Name : Mohan
Age: 20, Sex : M
Salary :2000

First Name: John, Last Name : Poul
Age: 25, Sex : M
Salary :2300

Есть более короткие методы для извлечения записей из базы данных. Если вам интересно, тогда пройдите « Извлечение результата», в противном случае перейдите к следующему разделу.

Операция обновления

ОБНОВЛЕНИЕ Операция в любой базе данных означает обновление одной или нескольких записей, которые уже доступны в базе данных. Ниже приведена процедура обновления всех записей, имеющих SEX как «M». Здесь мы увеличим ВОЗРАСТ всех мужчин на один год. Это займет три шага —

• Подготовка SQL-запроса на основе обязательных условий. Это будет сделано с использованием метода prepare .

• Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью метода execute .

• Выпускать заявление о ручке. Это будет сделано с использованием метода финиша .

• Если все в порядке, передайте эту операцию, в противном случае вы можете откатить всю транзакцию.

Подготовка SQL-запроса на основе обязательных условий. Это будет сделано с использованием метода prepare .

Выполнение запроса SQL для выбора всех результатов из базы данных. Это будет сделано с помощью метода execute .

Выпускать заявление о ручке. Это будет сделано с использованием метода финиша .

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

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   sth = dbh.prepare("UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = ?")
   sth.execute('M')
   sth.finish
   dbh.commit
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
   dbh.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

УДАЛЕНИЕ Операция

Операция DELETE требуется, когда вы хотите удалить некоторые записи из вашей базы данных. Ниже описана процедура удаления всех записей из EMPLOYEE, где AGE больше 20. Эта операция будет включать следующие шаги.

• Подготовка SQL-запроса на основе обязательных условий. Это будет сделано с использованием метода prepare .

• Выполнение SQL-запроса для удаления необходимых записей из базы данных. Это будет сделано с помощью метода execute .

• Выпускать заявление о ручке. Это будет сделано с использованием метода финиша .

• Если все в порядке, передайте эту операцию, в противном случае вы можете откатить всю транзакцию.

Подготовка SQL-запроса на основе обязательных условий. Это будет сделано с использованием метода prepare .

Выполнение SQL-запроса для удаления необходимых записей из базы данных. Это будет сделано с помощью метода execute .

Выпускать заявление о ручке. Это будет сделано с использованием метода финиша .

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

#!/usr/bin/ruby -w

require "dbi"

begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123")
   sth = dbh.prepare("DELETE FROM EMPLOYEE WHERE AGE > ?")
   sth.execute(20)
   sth.finish
   dbh.commit
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
   dbh.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Выполнение транзакций

Транзакции — это механизм, который обеспечивает согласованность данных. Транзакции должны иметь следующие четыре свойства —

• Атомарность — либо транзакция завершена, либо ничего не происходит вообще.

• Согласованность — транзакция должна начинаться в согласованном состоянии и оставлять систему в согласованном состоянии.

• Изоляция — промежуточные результаты транзакции не видны за пределами текущей транзакции.

• Долговечность — после совершения транзакции последствия сохраняются даже после сбоя системы.

Атомарность — либо транзакция завершена, либо ничего не происходит вообще.

Согласованность — транзакция должна начинаться в согласованном состоянии и оставлять систему в согласованном состоянии.

Изоляция — промежуточные результаты транзакции не видны за пределами текущей транзакции.

Долговечность — после совершения транзакции последствия сохраняются даже после сбоя системы.

DBI предоставляет два метода для фиксации или отката транзакции. Существует еще один метод под названием транзакция, который можно использовать для реализации транзакций. Есть два простых подхода для реализации транзакций —

Подход I

Первый подход использует методы фиксации и отката DBI для явного подтверждения или отмены транзакции —

dbh['AutoCommit'] = false # Set auto commit to false.
begin
   dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'John'")
   dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'Zara'")
   dbh.commit
rescue
   puts "transaction failed"
   dbh.rollback
end
dbh['AutoCommit'] = true

Подход II

Второй подход использует метод транзакции . Это проще, потому что требуется блок кода, содержащий операторы, составляющие транзакцию. Метод транзакции выполняет блок, а затем автоматически выполняет фиксацию или откат в зависимости от того, успешно ли выполняется блок —

dbh['AutoCommit'] = false # Set auto commit to false.
dbh.transaction do |dbh|
   dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'John'")
   dbh.do("UPDATE EMPLOYEE SET AGE = AGE+1 WHERE FIRST_NAME = 'Zara'")
end
dbh['AutoCommit'] = true

Операция COMMIT

Фиксация — это операция, которая дает зеленый сигнал базе данных для завершения изменений, и после этой операции никакие изменения не могут быть возвращены обратно.

Вот простой пример вызова метода commit .

dbh.commit

ROLLBACK Операция

Если вас не устраивает одно или несколько изменений, и вы хотите полностью отменить эти изменения, используйте метод отката .

Вот простой пример вызова метода отката .

dbh.rollback

Отключение базы данных

Чтобы отключить соединение с базой данных, используйте отключить API.

dbh.disconnect

Если соединение с базой данных закрыто пользователем с помощью метода разъединения, все незавершенные транзакции откатываются DBI. Однако вместо того, чтобы зависеть от каких-либо подробностей реализации DBI, вашему приложению было бы лучше явно вызвать commit или rollback.

Обработка ошибок

Есть много источников ошибок. Вот несколько примеров: синтаксическая ошибка в выполненном операторе SQL, сбой соединения или вызов метода выборки для уже отмененного или законченного дескриптора оператора.

В случае сбоя метода DBI DBI вызывает исключение. Методы DBI могут вызывать любое из нескольких типов исключений, но два наиболее важных класса исключений — это DBI :: InterfaceError и DBI :: DatabaseError .

Объекты исключений этих классов имеют три атрибута с именами err , errstr и state , которые представляют номер ошибки, описательную строку ошибки и стандартный код ошибки. Атрибуты объяснены ниже —

• err — возвращает целочисленное представление произошедшей ошибки или nil, если это не поддерживается DBD. Например, DBD Oracle возвращает числовую часть сообщения об ошибке ORA-XXXX .

• errstr — возвращает строковое представление произошедшей ошибки.

• состояние — возвращает код SQLSTATE возникшей ошибки. SQLSTATE представляет собой строку длиной пять символов. Большинство DBD не поддерживают это и возвращают nil.

err — возвращает целочисленное представление произошедшей ошибки или nil, если это не поддерживается DBD. Например, DBD Oracle возвращает числовую часть сообщения об ошибке ORA-XXXX .

errstr — возвращает строковое представление произошедшей ошибки.

состояние — возвращает код SQLSTATE возникшей ошибки. SQLSTATE представляет собой строку длиной пять символов. Большинство DBD не поддерживают это и возвращают nil.

Вы видели следующий код выше в большинстве примеров —

rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
   dbh.rollback
ensure
   # disconnect from server
   dbh.disconnect if dbh
end

Чтобы получить отладочную информацию о том, что делает ваш скрипт во время его выполнения, вы можете включить трассировку. Для этого вы должны сначала загрузить модуль dbi / trace, а затем вызвать метод trace, который управляет режимом трассировки и назначением вывода —

require "dbi/trace"
..............

trace(mode, destination)

Значение режима может быть 0 (выключено), 1, 2 или 3, и назначение должно быть объектом ввода-вывода. Значения по умолчанию: 2 и STDERR соответственно.

Блоки кода с методами

Есть несколько методов, которые создают дескрипторы. Эти методы могут быть вызваны с помощью блока кода. Преимущество использования блока кода вместе с методами состоит в том, что они предоставляют дескриптор для блока кода в качестве его параметра и автоматически очищают дескриптор, когда блок завершается. Есть несколько примеров, чтобы понять концепцию.

• DBI.connect — этот метод генерирует дескриптор базы данных, и рекомендуется отключить разъединение в конце блока, чтобы отключить базу данных.

• dbh.prepare — этот метод генерирует дескриптор оператора, и его рекомендуется завершать в конце блока. Внутри блока вы должны вызвать метод execute для выполнения инструкции.

• dbh.execute — этот метод похож, за исключением того, что нам не нужно вызывать execute внутри блока. Дескриптор оператора выполняется автоматически.

DBI.connect — этот метод генерирует дескриптор базы данных, и рекомендуется отключить разъединение в конце блока, чтобы отключить базу данных.

dbh.prepare — этот метод генерирует дескриптор оператора, и его рекомендуется завершать в конце блока. Внутри блока вы должны вызвать метод execute для выполнения инструкции.

dbh.execute — этот метод похож, за исключением того, что нам не нужно вызывать execute внутри блока. Дескриптор оператора выполняется автоматически.

Пример 1

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

dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") do |dbh|

Пример 2

dbh.prepare может взять блок кода, передать ему дескриптор оператора и автоматически вызвать финиш в конце блока следующим образом.

dbh.prepare("SHOW DATABASES") do |sth|
   sth.execute
   puts "Databases: " + sth.fetch_all.join(", ")
end

Пример 3

dbh.execute может взять блок кода, передать ему дескриптор оператора и автоматически вызвать финиш в конце блока следующим образом:

dbh.execute("SHOW DATABASES") do |sth|
   puts "Databases: " + sth.fetch_all.join(", ")
end

Метод транзакции DBI также принимает блок кода, который был описан выше.

Специфичные для водителя функции и атрибуты

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

Специфичные для драйвера атрибуты поддерживаются и могут быть установлены или получены с использованием методов [] = или [] .

#!/usr/bin/ruby

require "dbi"
begin
   # connect to the MySQL server
   dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") 
   puts dbh.func(:client_info)
   puts dbh.func(:client_version)
   puts dbh.func(:host_info)
   puts dbh.func(:proto_info)
   puts dbh.func(:server_info)
   puts dbh.func(:thread_id)
   puts dbh.func(:stat)
rescue DBI::DatabaseError => e
   puts "An error occurred"
   puts "Error code:    #{e.err}"
   puts "Error message: #{e.errstr}"
ensure
   dbh.disconnect if dbh
end