The best Ruby database access - DBI Tutorial Tutorial In 2024, In this tutorial you can learn DBI application architecture,installation,Database Connectivity,INSERT operation,Use do statement,Usingprepareandexecute,READ operation,Update Operation,DELETE operation,Executive Affairs,Method I,Method II,COMMIT operation,ROLLBACK operation,Disconnect the database,Processing error,Block method,Driver-specific functions and properties,Examples,
This chapter will show you how to use Ruby to access the database.Ruby DBImodule provides a database-independent interface is similar to Perl DBI module for Ruby scripts.
DBI namely Database independent interface, on behalf of the Ruby database-independent interface. DBI between Ruby code with the underlying database provides an abstraction layer that allows you to easily implement a database switchover. It defines a set of methods, variables and norms, provides a database-independent consistent database interface.
DBI can interact with the following:
DBI independent of any database available in the background. Whether you're using Oracle, MySQL, Informix, you can use the DBI. The following chart clearly illustrates this point.
Ruby DBI general architecture uses two layers:
If you want to write Ruby scripts to access the MySQL database, you need to install Ruby MySQL module.
# Ubuntu sudo apt-get install mysql-client sudo apt-get install libmysqlclient15-dev # Centos yum install mysql-devel
Mac OS, you need to modify ~ / .bash_profile or ~ / .profile file, add the following code:
MYSQL=/usr/local/mysql/bin export PATH=$PATH:$MYSQL export DYLD_LIBRARY_PATH=/usr/local/mysql/lib:$DYLD_LIBRARY_PATH
Or use the soft connection:
sudo ln -s /usr/local/mysql/lib/libmysqlclient.18.dylib /usr/lib/libmysqlclient.18.dylib
RubyGems about founded in November 2003, became part of the Ruby standard library from Ruby 1.9 version. More details can be viewed: the Ruby RubyGems
Use gem install dbi and dbd-mysql:
sudo gem install dbi sudo gem install mysql sudo gem install dbd-mysql
The module is a DBD, from http://tmtm.org/downloads/mysql/ruby/ download.
After downloading the latest package, unzip into the directory, execute the following command to install:
% ruby extconf.rb 或者 % ruby extconf.rb --with-mysql-dir=/usr/local/mysql 或者 % ruby extconf.rb --with-mysql-config
Then compile:
% make
Obtain and install the Ruby / DBI
You can download and install the Ruby DBI module from the following link:
https://github.com/erikh/ruby-dbi |
Before starting the installation, make sure you have root privileges. Now, install the following steps to install:
step 1
git clone https://github.com/erikh/ruby-dbi.git
Or directly under another zip and unzip.
Step 2
Enter the directoryruby-dbi-master,usesetup.rbconfiguration script in the directory. The most common configuration commands are not followed by any parameter config parameters. The default configuration command to install all drivers.
$ ruby setup.rb config
More specifically, you can use the --with option to list the specific part you want to use. For example, if you want to configure the main DBI module and the MySQL DBD layer drive, enter the following command:
$ ruby setup.rb config --with=dbi,dbd_mysql
Step 3
The last step is to create the drive, use the following command to install:
$ ruby setup.rb setup $ ruby setup.rb install
Suppose we are using the MySQL database before connecting to the database, make sure that:
The following are examples of MySQL database connection "TESTDB" of:
#!/usr/bin/ruby -w require "dbi" begin # 连接到 MySQL 服务器 dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") # 获取服务器版本字符串,并显示 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 # 断开与服务器的连接 dbh.disconnect if dbh end
When you run this script, will produce the following results on a Linux machine.
Server version: 5.0.45
If you establish a connection with a data source, the returned database handle (Database Handle), and saved to thedbh for subsequent use, or dbhwill be set to nilvalue,e.err ande :: errstrreturn the error code and error string.
Finally, before exiting this program, be sure to close the database connection, the release of resources.
When you want to create a record in a database table, you need to use INSERT operation.
Once a database connection, we're ready to create a table or insert data in the table to create a recording method using ado or prepareandexecutemethods.
The statement does not return rows by callingdo database processing methods.This method takes a statement string parameter and returns the number of rows affected by the statement.
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 )" );
Similarly, you can executeaSQLINSERTstatement to create records into the EMPLOYEE table.
#!/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 "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 # 断开与服务器的连接 dbh.disconnect if dbh end
You can use DBI'sprepareandexecutemethod to execute Ruby code SQL statements.
To create a record as follows:
Here is the syntax of these two methods:
sth = dbh.prepare(statement) sth.execute /en. zero or more SQL operations /en. sth.finish
Both methods can be used tobind a value to pass SQL statements.Sometimes the value is entered may not be given in advance, in this case, is used to bind values. Use a question mark(?) Instead of the actual value, the actual value to pass through execute () API.
The following example creates two records in the EMPLOYEE table:
#!/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 "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 # 断开与服务器的连接 dbh.disconnect if dbh end
If you use multiple INSERT, then first prepare a statement, and then execute multiple times in a loop through each cycle than it calls do much more efficiently.
READ operation on any database refers to obtain useful information from the database.
Once a database connection, we're ready to query the database. We can use a method ordo prepareandexecutemethod to get the value from a database table.
Get step recorded as follows:
The following example query all wage (salary) recorded more than 1,000 from the EMPLOYEE table.
#!/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 "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 # 断开与服务器的连接 dbh.disconnect if dbh end
This produces the following results:
First Name: Mac, Last Name : Mohan Age: 20, Sex : M Salary :2000 First Name: John, Last Name : Poul Age: 25, Sex : M Salary :2300
Any database UPDATE operation refers to one or more database update existing records. The following example updates SEX is 'M' for all records. Here, we will add a year all males AGE. It will be divided into three steps:
#!/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 "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" dbh.rollback ensure # 断开与服务器的连接 dbh.disconnect if dbh end
When you want to delete records from the database, you need to use the DELETE operation. The following examples delete AGE 20 over all records from the EMPLOYEE. The operation steps are as follows:
#!/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 "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" dbh.rollback ensure # 断开与服务器的连接 dbh.disconnect if dbh end
A transaction is a mechanism to ensure transaction consistency. Services should have the following four attributes:
DBI provides two ways to perform the transaction. One is thecommitorrollbackmethods to commit or roll back the transaction. There is also atransactionmethod can be used to implement the transaction. Next we introduce two simple method of implementing transactions:
The first method uses DBI'scommitandrollbackmethods to explicitly commit or cancel the transaction:
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 end dbh['AutoCommit'] = true
The second method uses thetransactionmethod. This method is relatively simple, because it requires a transaction constitutes a code block containing statement.transactionmethod executes the block, and then the block is executed successfully, automatically invokecommitorrollback:
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'") end dbh['AutoCommit'] = true
Commit is an operation has been completed to identify the database changes, and after this operation, all the changes are not recoverable.
Here is a simple examplecommit method invocation.
dbh.commit
If you are not satisfied with one or a few changes, you want to completely restore these changes, use therollback method.
Here is a simple example ofrollback method call.
dbh.rollback
To disconnect from the database, use the disconnect API.
dbh.disconnect
If the user closes the database connection disconnect method, DBI rolls back all incomplete transactions. However, it does not rely on the implementation details of any DBI, and your application can be a good explicit call to commit or rollback.
There are many different sources of error. For example, syntax errors in the implementation of the SQL statement or the connection fails, or is a complete statement or a canceled handle calls fetch method.
If a DBI method fails, DBI will throw an exception. DBI method can throw any type of exception, but the two most important exception class isDBI :: InterfaceErrorandDBI :: DatabaseError.
Exception objects of these classes areerr,errstr three attributes andstate,sub-table represents the error number, a descriptive error string and a standard error code. Attribute specified as follows:
In the example above, you've seen the following code:
rescue DBI::DatabaseError => e puts "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" dbh.rollback ensure # 断开与服务器的连接 dbh.disconnect if dbh end
To obtain debug information about the contents of the script is executed when the script is executed, you can enable tracing. To do this, you must first download the dbi / trace module, and then call tracking control mode and output destinationtracemethod:
require "dbi/trace" /en/en/en/en/en/en/en trace(mode, destination)
Value mode can be 0 (off), 1,2, or 3, the value of the destination should be an IO object. The default values are 2 and STDERR.
There are ways to create a handle. These methods are invoked by code block. Advantages of using code blocks with the method is that they provide a handle for the block as an argument when the block is terminated automatically cleared handle. Here are some examples to help understand this concept.
DBI.connect may have a block of code, passing it the database handle, and the handle will automatically disconnect at the end of the block.
dbh = DBI.connect("DBI:Mysql:TESTDB:localhost", "testuser", "test123") do |dbh|
dbh.prepare may have a code block, passing the statement handle, and automatically calls finish at the end of the block.
dbh.prepare("SHOW DATABASES") do |sth| sth.execute puts "Databases: " + sth.fetch_all.join(", ") end
dbh.execute may have a code block, passing the statement handle, and automatically calls finish at the end of the block.
dbh.execute("SHOW DATABASES") do |sth| puts "Databases: " + sth.fetch_all.join(", ") end
DBItransaction method may also be provided with a block of code, which in the above sections have explained before.
Let DBI database driver provides additional functions specific database, these functions can be called by any userfuncmethod Handle object.
Use[] = or []method to set or get attributes specific drivers.
DBD :: Mysql implements the function following a specific driver:
No. | Function & Description |
---|---|
1 | dbh.func (: createdb, db_name) Create a new database. |
2 | dbh.func (: dropdb, db_name) To delete a database. |
3 | dbh.func (: reload) Reload operation. |
4 | dbh.func (: shutdown) Shut down the server. |
5 | dbh.func (: insert_id) => Fixnum Returns recent AUTO_INCREMENT value of the connection. |
6 | dbh.func (: client_info) => String Returns the MySQL client version information as required. |
7 | dbh.func (: client_version) => Fixnum Depending on the version information back to the client. This is: Similar to client_info, but it will return a fixnum, rather than returning a string. |
8 | dbh.func (: host_info) => String Returns the host information. |
9 | dbh.func (: proto_info) => Fixnum Returns the protocols used for communication. |
10 | dbh.func (: server_info) => String Returns the MySQL server version information based. |
11 | dbh.func (: stat) => Stringb > Returns the current state of the database. |
12 | dbh.func (: thread_id) => Fixnum Returns the current thread ID. |
#!/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 "An error occurred" puts "Error code: #{e.err}" puts "Error message: #{e.errstr}" ensure dbh.disconnect if dbh end
This produces the following results:
5.0.45 50045 Localhost via UNIX socket 10 5.0.45 150621 Uptime: 384981 Threads: 1 Questions: 1101078 Slow queries: 4 \ Opens: 324 Flush tables: 1 Open tables: 64 \ Queries per second avg: 2.860