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

标签: RubypostgresDBIDBD
991人阅读 评论(1) 收藏 举报
分类:
在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'
classPg_DB_Test
attr_reader:conn
defcreateDBConnection
if!@conn
@conn=PGconn.connect(@dbhost,5432,'','',@dbname,@dbuser,@dbpassword)
end
end
definitialize(dbhost,dbname,dbuser,dbpassword,tableName)
@dbhost=dbhost
@dbname=dbname
@dbuser=dbuser
@dbpassword=dbpassword
@tableName=tableName
@conn= createDBConnection
end
defqueryTablesEmployeeExist?
res=@conn.exec('select tablename, tableowner from pg_tables')
res.eachdo|row|
#puts row['tablename'] + ' | '+ row['tableowner']
ifrow['tablename'].match(/employee/)
return true
else
next
end
end
return false
end

defcreateTable
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 )")
rescueDBI::DatabaseError=>e
puts"An error occurred"
puts"Error code:#{e.err}"
puts"Error message:#{e.errstr}"
end
end
definsertEmployee
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)"
putssqlStatement
@conn.exec(sqlStatement)
puts"Record has been created"
PG::Connection
puts@conn.class
end
defupdateEmployee
@conn.prepare'stm1',"UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = $1"
@conn.exec_prepared'stm1',['M']
end

defcloseDBConnection
@conn.closeif@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 执行完后,其数据库表的数据如下,


1
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:385771次
    • 积分:6344
    • 等级:
    • 排名:第4081名
    • 原创:228篇
    • 转载:0篇
    • 译文:5篇
    • 评论:157条
    博客专栏