Доступ к базе данных DBI с помощью Ruby

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

DBI независим от любой базы данных, доступной в фоновом режиме. Независимо от того, используете ли вы Oracle, MySQL, Informix, вы можете использовать DBI. Диаграмма архитектуры清楚地 иллюстрирует это.

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

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

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

Установите

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

Установите разработчикский пакет Mysql

# Ubuntu
sudo apt-get install mysql-client
sudo apt-get install libmysqlclient15-dev
　
# Centos
yum install mysql-devel

Для системы Mac OS необходимо изменить файл ~/.bash_profile или ~/.profile, добавив следующий код:

MYSQL=/usr/local/mysql/bin
export PATH=$PATH:$MYSQL
export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH

или создайте символическую ссылку:

sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib

Установите DBI с помощью RubyGems (рекомендуется)

RubyGems был создан около 2003 года и с версии Ruby 1.9 стал частью стандартной библиотеки Ruby. Дополнительные подробности можно найти здесь:RubyGems Ruby

Установите dbi и dbd-mysql с помощью gem:

sudo gem install dbi
sudo gem install mysql
sudo gem install dbd-mysql

Установите из исходного кода (Для версий Ruby менее 1.9 используйте этот метод)

Этот модуль является DBD и может быть получен из http://tmtm.org/downloads/mysql/ruby/ Загрузка.

Загрузите последнюю версию пакета, распакуйте его в папку и выполните следующие команды для установки:

ruby extconf.rb
　
или
　
ruby extconf.rb --with-mysql-dir=/usr/local/mysql
　
или
　
ruby extconf.rb --with-mysql-config

Затем скомпилируйте:

make

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

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

https://github.com/erikh/ruby-dbi

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

Шаг 1

git clone https://github.com/erikh/ruby-dbi.git

или напрямую распакуйте zip-архив.

Шаг 2

Перейдите в каталог ruby-dbi-master, в каталоге используйте 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.

• Вы уже создали таблицу EMPLOYEE в TESTDB.

• Эта таблица содержит поля FIRST_NAME, LAST_NAME, AGE, SEX и INCOME.

• Установите ID пользователя "testuser" и пароль "test123", чтобы получить доступ к TESTDB

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

• Вы уже видели учебник MySQL, понимаете основные операции MySQL.

Вот пример подключения к базе данных MySQL "TESTDB":

Онлайн пример

#!/usr/bin/ruby -w
　
require "dbi"
　
begin
　　　　　#　连接到 MySQL 服务器
　　　　　dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",　
　　　　　　　　　　　　　　　　　　　　　　　"testuser", "test123")
　　　　　# Получение строки версии сервера и отображение
　　　　　row = dbh.select_one("SELECT VERSION()")
　　　　　puts "Версия сервера: " + row[0]
rescue DBI::DatabaseError => e
　　　　　puts "Произошла ошибка"
　　　　　puts "Код ошибки:　　　　#{e.err}"
　　　　　puts "Ошибка сообщения: #{e.errstr}"
ensure
　　　　　# Разорвать соединение с сервером
　　　　　dbh.disconnect if dbh
конец

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

Версия сервера: 5.0.45

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

最后，在退出这段程序之前，请确保关闭数据库连接，释放资源。

INSERT 操作

当您想要在数据库表中创建记录时，需要用到 INSERT 操作。

一旦建立了数据库连接，我们就可以准备使用 do Метод или prepare и execute 方法创建表或创建插入数据表中的记录。

使用 do 语句

不返回行的语句可通过调用 do 数据库处理方法。该方法带有一个语句字符串参数，并返回该语句所影响的行数。

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
　　　　　#　连接到 MySQL 服务器
　　　　　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 "Запись была создана"
　　　　　dbh.commit
rescue DBI::DatabaseError => e
　　　　　puts "Произошла ошибка"
　　　　　puts "Код ошибки:　　　　#{e.err}"
　　　　　puts "Ошибка сообщения: #{e.errstr}"
　　　　　dbh.rollback
ensure
　　　　　# Разорвать соединение с сервером
　　　　　dbh.disconnect if dbh
конец

Использование prepare и execute

您可以使用 DBI 的 prepare и execute 方法来执行 Ruby 代码中的 SQL 语句。

创建记录的步骤如下：

• 准备带有 INSERT 语句的 SQL 语句。这将通过使用 prepare метод для выполнения.

• Исп�行 SQL-запроса, чтобы выбрать все результаты из базы данных. Это можно сделать, используя execute метод для выполнения.

• Освободить обработчик запроса. Это будет сделано с помощью finish API 来完成。

• если все идет хорошо, то commit эту операцию,否则 вы можете rollback завершить сделку.

下面是使用这两种方法的语法：

Онлайн пример

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

这两种方法可用于传 bind 值给 SQL 语句。有时候被输入的值可能未事先给出，在这种情况下，则会用到绑定值。使用问号（?）代替实际值，实际值通过 execute() API 来传递。

下面的示例在 EMPLOYEE 表中创建了两个记录：

Онлайн пример

#!/usr/bin/ruby -w
　
require "dbi"
　
begin
　　　　　#　连接到 MySQL 服务器
　　　　　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 "Запись была создана"
rescue DBI::DatabaseError => e
　　　　　puts "Произошла ошибка"
　　　　　puts "Код ошибки:　　　　#{e.err}"
　　　　　puts "Ошибка сообщения: #{e.errstr}"
　　　　　dbh.rollback
ensure
　　　　　# Разорвать соединение с сервером
　　　　　dbh.disconnect if dbh
конец

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

Операция чтения

Чтение данных из базы данных指的是 получение полезной информации из базы данных.

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

Шаги для получения записей такие:

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

• Исп�行 SQL-запроса, чтобы выбрать все результаты из базы данных. Это можно сделать, используя execute метод для выполнения.

• Постепенно получать результаты и выводить их. Это можно сделать, используя fetch метод для выполнения.

• Освободить обработчик запроса. Это будет сделано с помощью finish метод для выполнения.

В следующем примере все записи из таблицы EMPLOYEE с зарплатой (salary) более 1000 будут выбраны.

Онлайн пример

#!/usr/bin/ruby -w
　
require "dbi"
　
begin
　　　　　#　连接到 MySQL 服务器
　　　　　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 "Имя: %s, Фамилия: %s\n", row[0], row[1]
　　　　　　　　printf "Возраст: %d, Пол: %s\n", row[2], row[3]
　　　　　　　　printf "Зарплата: %d \n\n", row[4]
　　　　　конец
　　　　　sth.finish
rescue DBI::DatabaseError => e
　　　　　puts "Произошла ошибка"
　　　　　puts "Код ошибки:　　　　#{e.err}"
　　　　　puts "Ошибка сообщения: #{e.errstr}"
ensure
　　　　　# Разорвать соединение с сервером
　　　　　dbh.disconnect if dbh
конец

Это вызовет следующие результаты:

Имя: Mac, Фамилия: Mohan
Возраст: 20, Пол: M
Зарплата: 2000
Имя: John, Фамилия: Poul
Возраст: 25, Пол: M
Зарплата: 2300

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

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

Обновление данных в базе данных指的是 обновление одной или нескольких уже существующих записей в базе данных. В следующем примере все записи с SEX = 'M' будут обновлены. Здесь мы увеличим возраст всех мужчин на один год. Это будет разделено на три шага:

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

• Исп�行 SQL-запроса, чтобы выбрать все результаты из базы данных. Это можно сделать, используя execute метод для выполнения.

• Освободить обработчик запроса. Это будет сделано с помощью finish метод для выполнения.

• если все идет хорошо, то commit эту операцию,否则 вы можете rollback завершить сделку.

Онлайн пример

#!/usr/bin/ruby -w
　
require "dbi"
　
begin
　　　　　#　连接到 MySQL 服务器
　　　　　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 "Произошла ошибка"
　　　　　puts "Код ошибки:　　　　#{e.err}"
　　　　　puts "Ошибка сообщения: #{e.errstr}"
　　　　　dbh.rollback
ensure
　　　　　# Разорвать соединение с сервером
　　　　　dbh.disconnect if dbh
конец

Операция DELETE

Когда вы хотите удалить запись из базы данных, вам нужно использовать операцию DELETE. В следующем примере все записи из таблицы EMPLOYEE с AGE, превышающим 20, будут удалены. Шаги операции такие:

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

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

• Освободить обработчик запроса. Это будет сделано с помощью finish метод для выполнения.

• если все идет хорошо, то commit эту операцию,否则 вы можете rollback завершить сделку.

Онлайн пример

#!/usr/bin/ruby -w
　
require "dbi"
　
begin
　　　　　#　连接到 MySQL 服务器
　　　　　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 "Произошла ошибка"
　　　　　puts "Код ошибки:　　　　#{e.err}"
　　　　　puts "Ошибка сообщения: #{e.errstr}"
　　　　　dbh.rollback
ensure
　　　　　# Разорвать соединение с сервером
　　　　　dbh.disconnect if dbh
конец

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

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

• Атомарность (Atomicity):Атомарность транзакции означает, что программы, содержащиеся в транзакции, являются логическими единицами работы базы данных, и все их операции по изменению данных должны выполняться полностью или не выполняться вообще.

• Консистентность (Consistency):Консистентность транзакции означает, что база данных должна быть в состоянии согласованности до и после выполнения транзакции. Если состояние базы данных satisfies все ограничения целостности, говорят, что база данных является一致的.

• Изоляция (Isolation):Изоляция транзакции означает, что транзакции выполняются изолированно, то есть операции внутри транзакции и данные, над которыми они работают, должны быть заблокированы, чтобы другие транзакции, которые хотят изменить данные, не могли их видеть.

• Постоянность (Durability):Постоянность транзакции означает, что при сбое системы или носителя данных обновления, уже отправленные транзакции не должны丢失. То есть, как только транзакция отправлена, изменения в данных базы данных должны быть постоянными и устойчивыми к любым сбоям системы базы данных. Постоянность обеспечивается резервным копированием и восстановлением базы данных.

DBI предоставляет два метода для выполнения транзакций. Один из них commit или rollback метод, используемый для завершения или отмены транзакции. Есть также transaction метод, который можно использовать для реализации транзакций. Давайте рассмотрим два простых метода реализации транзакций:

метод I

Первый метод использует commit и rollback метод для явного завершения или отмены транзакции:

Онлайн пример

dbh['AutoCommit'] = false # Установить автоматическую отправку в 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
　　　конец
　　　dbh['AutoCommit'] = true

метод II

Вторая метод использует transaction метод. Этот метод несколько проще, так как он требует блока кода, содержащего строки транзакции.transaction Блок выполнения метода, после чего автоматически вызывается commit или rollback:

Онлайн пример

dbh['AutoCommit'] = false # Установить автоматическую отправку в 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'
　　　конец
　　　dbh['AutoCommit'] = true

Операция COMMIT

Commit - это операция, которая указывает на то, что изменения в базе данных завершены, после которой все изменения становятся недоступными для отмены.

Ниже приведен пример вызова commit Пример метода.

dbh.commit

операцию ROLLBACK

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

Ниже приведен пример вызова rollback Пример метода.

dbh.rollback

Разорвать базу данных

Чтобы разорвать соединение с базой данных, используйте API disconnect.

dbh.disconnect

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

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

Есть много разных источников ошибок. Например, это может быть грамматическая ошибка в выполнении SQL-запроса, или сбой подключения, или вызов метода fetch на уже отмененном или завершенном обработчике запроса.

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

Эти объекты класса Exception имеют err、errstr и state Три атрибута, которые представляют ошибку, описывающую ошибку и стандартный код ошибки. Подробное описание атрибутов см. ниже:

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

• errstr:Вернуть строковое представление ошибки, которая произошла.

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

В примере, который вы видели выше, вы уже видели следующий код:

rescue DBI::DatabaseError => e
　　　　　puts "Произошла ошибка"
　　　　　puts "Код ошибки:　　　　#{e.err}"
　　　　　puts "Ошибка сообщения: #{e.errstr}"
　　　　　dbh.rollback
ensure
　　　　　# Разорвать соединение с сервером
　　　　　dbh.disconnect if dbh
конец

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

require "dbi/trace"
..............
　
trace(mode, destination)

значение mode может быть 0(off), 1, 2 или 3, значение destination должно быть объектом IO. По умолчанию значения分别是 2 и STDERR.

кодовый блок метода

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

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

• dbh.prepare :Этот метод генерирует обработчик запроса, рекомендуется вызывать его в конце блока. finish. В блоке вам необходимо вызвать execute метод для выполнения запроса.

• dbh.execute :Этот метод ähnelt dbh.prepare, но dbh.execute не требует вызова метода execute в блоке. Обработчик запроса автоматически выполняется.

Пример 1

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

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

Пример 2

dbh.prepare Метод может иметь кодовый блок, передающий в него обработчик запроса, и автоматически вызывать finish в конце блока.

dbh.prepare("SHOW DATABASES") do |sth|
　　　　　　　sth.execute
　　　　　　　puts "Databases: " + sth.fetch_all.join(", ")
конец

Пример 3

dbh.execute Метод может иметь кодовый блок, передающий в него обработчик запроса, и автоматически вызывать finish в конце блока.

dbh.execute("SHOW DATABASES") do |sth|
　　　puts "Databases: " + sth.fetch_all.join(", ")
конец

DBI transaction Метод также может иметь кодовый блок, который был объяснен в предыдущей главе.

функции и свойства специфические для драйвера

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

Использование []= или [] Метод может устанавливать или получать свойства специфические для драйвера.

DBD::Mysql реализует функции специфических для драйверов:

Онлайн пример

#!/usr/bin/ruby
　
require "dbi"
begin
　　　#　连接到 MySQL 服务器
　　　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 "Произошла ошибка"
　　　puts "Код ошибки:　　　　#{e.err}"
　　　puts "Ошибка сообщения: #{e.errstr}"
ensure
　　　dbh.disconnect if dbh
конец

Это вызовет следующие результаты:

5.0.45
50045
Localhost через UNIX socket
10
5.0.45
150621
Время работы: 384981　　Треды: 1　　Запросы: 1101078　　Медленные запросы: 4　\
Открывается: 324　　Очищены таблицы: 1　　Открытые таблицы: 64　　\
Среднее количество запросов в секунду: 2.860