[3]ruby&ruby on rails入门笔记---用Ruby访问postgresql

在Ruby中，访问数据库可以通过DBI（database API）来访问。 Ruby DBI模块为ruby程序访问数据库提供了一个与数据库无关的接口，就像perl的DBI模块一样。其具体的结构如下图，

1.The database interface (DBI): 数据库的接口层，类似于JDBC

2.The database driver (DBD) layer：实际的数据库的驱动，类似于java中实际的MySQL，Oracle的

JDBC的具体实现包。

下面以postgresql数据库为例子

1. 下载dbd-pg

2. 下载dbi

3. 代码

require 'pg'

require 'SecureRandom'

class Pg_DB_Test

attr_reader :conn

def createDBConnection

if ! @conn

@conn = PGconn . connect ( @dbhost , 5432 , '' , '' , @dbname , @dbuser , @dbpassword )

end

end

def initialize ( dbhost , dbname , dbuser , dbpassword , tableName )

@dbhost = dbhost

@dbname = dbname

@dbuser = dbuser

@dbpassword = dbpassword

@tableName = tableName

@conn = createDBConnection

end

def queryTablesEmployeeExist?

res = @conn .exec( 'select tablename, tableowner from pg_tables' )

res .each do | row |

#puts row[ 'tablename' ] + ' | ' + row[ 'tableowner' ]

if row [ 'tablename' ]. match ( / employee / )

return true

else

next

end

end

return false

end

def createTable

begin

@conn .exec( "DROP TABLE IF EXISTS EMPLOYEE" )

@conn .exec( "CREATE TABLE EMPLOYEE (ID serial primary key,FIRST_NAME CHAR(20) NOT NULL,LAST_NAME VARCHAR,AGE INT,SEX CHAR(1),INCOME FLOAT )" )

rescue DBI :: DatabaseError => e

puts "An error occurred"

puts "Error code: #{ e .err } "

puts "Error message: #{ e .errstr } "

end

end

def insertEmployee

if ! self .queryTablesEmployeeExist?

puts "----------Created new table:employee-------"

createTable

else

puts "-----The employee table already existed!!!-----"

end

sqlStatement = "INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)VALUES ('Mac','" + SecureRandom .uuid + "', 20, 'M', 2000)"

puts sqlStatement

@conn .exec( sqlStatement )

puts "Record has been created"

PG :: Connection

puts @conn .class

end

def updateEmployee

@conn .prepare 'stm1' , "UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = $1"

@conn .exec_prepared 'stm1' ,[ 'M' ]

end

def closeDBConnection

@conn .close if @conn #rescue nil

end

end

puts

dbhost = "127.0.0.1"

dbname = "test"

dbuser = "postgres"

dbpassword = "password"

tableName = "student"

pgdb = Pg_DB_Test .new( dbhost , dbname , dbuser , dbpassword , tableName )

pgdb .insertEmployee

pgdb . updateEmployee

pgdb .closeDBConnection

#1 执行完后，控制台输出如下

C:\RailsInstaller\Ruby2.2.0\bin\ruby.exe -e $stdout.sync=true;$stderr.sync=true;load($0=ARGV.shift) D:/ruby/learnruby/pg_db_test.rb
Testing started at 6:44 PM ...
NOTICE:  table "employee" does not exist, skipping
----------Created new table:employee-------
INSERT INTO EMPLOYEE(FIRST_NAME,LAST_NAME,AGE,SEX,INCOME)VALUES ('Mac','d8886789-fcbc-407b-b2bc-8e65fcdcb1d8', 20, 'M', 2000)
Record has been created
PG::Connection
Process finished with exit code 0

#2 执行完后，其数据库表的数据如下，