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

2016-11-17 08:15:22来源:CSDN作者:chancein007人点击

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

1.The database interface (DBI): 数据库的接口层,类似于JDBC2.The database driver (DBD) layer:实际的数据库的驱动,类似于java中实际的MySQL,Oracle的JDBC的具体实现包。
下面以postgresql数据库为例子1. 下载dbd-pg2. 下载dbi3. 代码require'pg'require'SecureRandom'classPg_DB_Testattr_reader:conndefcreateDBConnectionif!@conn@conn=PGconn.connect(@dbhost,5432,'','',@dbname,@dbuser,@dbpassword)endenddefinitialize(dbhost,dbname,dbuser,dbpassword,tableName)@dbhost=dbhost@dbname=dbname@dbuser=dbuser@dbpassword=dbpassword@tableName=tableName@conn= createDBConnectionenddefqueryTablesEmployeeExist?res=@conn.exec('select tablename, tableowner from pg_tables')res.eachdo|row|#puts row['tablename'] + ' | '+ row['tableowner']ifrow['tablename'].match(/employee/)return trueelsenextendendreturn falseend
defcreateTablebegin@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=>eputs"An error occurred"puts"Error code:#{e.err}"puts"Error message:#{e.errstr}"endenddefinsertEmployeeif!self.queryTablesEmployeeExist?puts"----------Created new table:employee-------"createTableelseputs"-----The employee table already existed!!!-----"endsqlStatement="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::Connectionputs@conn.classenddefupdateEmployee@conn.prepare'stm1',"UPDATE EMPLOYEE SET AGE = AGE + 1 WHERE SEX = $1"@conn.exec_prepared'stm1',['M']end
defcloseDBConnection@conn.closeif@conn#rescue nilendendputsdbhost="127.0.0.1"dbname="test"dbuser="postgres"dbpassword="password"tableName="student"pgdb=Pg_DB_Test.new(dbhost,dbname,dbuser,dbpassword,tableName)pgdb.insertEmployeepgdb.updateEmployeepgdb.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 执行完后,其数据库表的数据如下,
第七城市

最新文章

123

最新摄影

微信扫一扫

第七城市微信公众平台