PostgreSQL Ruby 教程 · ZetCode 中文系列教程

# PostgreSQL Ruby 教程 > 原文： [http://zetcode.com/db/postgresqlruby/](http://zetcode.com/db/postgresqlruby/) 這是針對 PostgreSQL 數據庫的 Ruby 編程教程。它涵蓋了使用 Ruby 語言進行 PostgreSQL 編程的基礎。 ## PostgreSQL PostgreSQL 是一個功能強大的開源對象關系數據庫系統。它是一個多用戶數據庫管理系統。它可以在多個平臺上運行，包括 Linux，FreeBSD，Solaris，Microsoft Windows 和 Mac OSX。PostgreSQL 由 PostgreSQL 全球開發小組開發。 PostgreSQL 具有復雜的功能，例如多版本并發控制（MVCC），時間點恢復，表空間，異步復制，嵌套事務（保存點），聯機/熱備份，復雜的查詢計劃程序/優化程序以及用于容錯的預寫日志記錄。它支持國際字符集，多字節字符編碼，Unicode，并且對于排序，區分大小寫和格式具有區域設置意識。 ## Ruby Ruby 是一種動態的，反射性的，通用的面向對象的編程語言。原始作者是日本程序員松本行弘。 Ruby 于 1995 年首次出現。Ruby 支持各種編程范例。這包括面向對象，反射，命令式和反射式編程。 ## Ruby `pg` Ruby `pg`是一個模塊，允許 Ruby 程序與 PostgreSQL 數據庫引擎進行交互。它支持`libpq` C 庫中定義的函數。 ## 安裝我們將安裝 PostgreSQL 數據庫和其他必要的庫。 ```ruby $ sudo apt-get install postgresql ``` 在基于 Debian 的系統上，我們可以使用上述命令從包中安裝 PostgreSQL 數據庫。 ```ruby $ sudo update-rc.d -f postgresql remove Removing any system startup links for /etc/init.d/postgresql ... /etc/rc0.d/K21postgresql /etc/rc1.d/K21postgresql /etc/rc2.d/S19postgresql /etc/rc3.d/S19postgresql /etc/rc4.d/S19postgresql /etc/rc5.d/S19postgresql /etc/rc6.d/K21postgresql ``` 如果我們從包中安裝 PostgreSQL 數據庫，它將自動添加到操作系統的啟動腳本中。如果我們僅學習使用數據庫，則不必在每次引導系統時都啟動數據庫。上面的命令刪除 PostgreSQL 數據庫的所有系統啟動鏈接。 ```ruby $ sudo apt-get install libpq-dev ``` 要編譯 Ruby `pg`模塊，我們還需要 C `libpq`庫的開發文件。 ```ruby $ sudo -u postgres psql postgres psql (9.3.9) Type "help" for help. postgres=# \password postgres ``` 我們為`postgres`用戶設置了密碼。 ```ruby $ sudo apt-get install ruby-dev ``` 我們安裝了 Ruby 開發庫，這是編譯 Ruby 擴展模塊所必需的。 ```ruby $ sudo gem install pg ``` 我們安裝了 Ruby `pg`模塊，它是 PostgreSQL 數據庫的 Ruby 接口。 ## 啟動和停止 PostgreSQL 在下一節中，我們將展示如何啟動 PostgreSQL 數據庫，停止它以及查詢它的狀態。 ```ruby $ sudo service postgresql start * Starting PostgreSQL 9.3 database server [ OK ] ``` 在基于 Debian 的 Linux 上，我們可以使用`service postgresql start`命令啟動服務器。 ```ruby $ sudo service postgresql status 9.3/main (port 5432): online ``` 我們使用`service postgresql status`命令檢查 PostgreSQL 是否正在運行。 ```ruby $ sudo service postgresql stop * Stopping PostgreSQL 9.3 database server [ OK ] ``` 我們使用`service postgresql stop`命令停止 PostgreSQL。 ```ruby $ service postgresql status 9.3/main (port 5432): down ``` 此時，`service postgresql status`命令報告 PostgreSQL 數據庫已關閉。 ## 創建用戶和數據庫在以下步驟中，我們將創建一個新的數據庫用戶和數據庫。 ```ruby $ sudo -u postgres createuser janbodnar ``` 我們在 PostgreSQL 系統中創建了一個新角色。我們允許它具有創建新數據庫的能力。角色是數據庫世界中的用戶。角色與操作系統用戶是分開的。 ```ruby $ sudo -u postgres psql postgres psql (9.3.9) Type "help" for help. postgres=# ALTER USER janbodnar WITH password 'pswd37'; ALTER ROLE postgres=# \q ``` 使用`psql`命令，為新用戶添加密碼。 PostgreSQL 通常在本地連接上使用信任或對等認證策略。在信任??認證策略的情況下，PostgreSQL 假定可以連接到服務器的任何人都可以使用他們指定的任何數據庫用戶名（甚至超級用戶名）訪問數據庫。與數據庫建立連接時，不需要密碼。（在數據庫和用戶列中所做的限制仍然適用。）信任認證對于單用戶工作站上的本地連接是適當的，并且非常方便。它通常不適用于多用戶計算機。如果使用對等認證策略，則數據庫用戶名必須與操作系統用戶名匹配。 ```ruby $ sudo -u postgres createdb testdb --owner janbodnar ``` 使用`createdb`命令，我們創建了一個名為`testdb`的新數據庫。它的所有者是新的數據庫用戶。 ## libpq 庫 `libpq`庫是 PostgreSQL 的 C 接口。它是一組庫函數，允許客戶端程序與 PostgreSQL 交互。它也是其他 PostgreSQL 應用接口的基礎引擎，包括為 C++ ，Perl，PHP，Ruby，Python 和 Tcl 編寫的接口。 Ruby `pg`模塊是`libpg`庫的包裝。 lib_version.rb ```ruby #!/usr/bin/ruby require 'pg' puts 'Version of libpg: ' + PG.library_version.to_s ``` 該程序將打印`libpq`庫的版本。 ```ruby require 'pg' ``` 我們包括`pg`模塊。 ```ruby puts 'Version of libpg: ' + PG.library_version.to_s ``` `library_version`方法返回正在使用的`libpq`的版本。 ```ruby $ ./lib_version.rb Version of libpg: 90309 ``` 該庫的版本是 9.3.9。 ## 服務器版本在下面的示例中，我們找到了 PostgreSQL 數據庫的版本。 server_version.rb ```ruby #!/usr/bin/ruby require 'pg' begin con = PG.connect :dbname => 'testdb', :user => 'janbodnar' puts con.server_version rescue PG::Error => e puts e.message ensure con.close if con end ``` 該示例連接到 PostgreSQL 數據庫，執行`server_version`方法，打印版本，關閉連接，然后清除。 ```ruby ... # TYPE DATABASE USER ADDRESS METHOD # "local" is for Unix domain socket connections only local all all peer ... ``` 在`pg_hba.conf`中，我們具有`peer`默認認證方法。在這種方法中，數據庫用戶名必須與操作系統用戶名匹配。無需密碼即可建立連接。 ```ruby con = PG.connect :dbname => 'testdb', :user => 'janbodnar' ``` 使用`connect`方法，我們連接到數據庫。在連接字符串中，我們提供用戶名和數據庫名。 ```ruby rescue PG::Error => e puts e.message ``` 我們檢查錯誤。這很重要，因為使用數據庫容易出錯。 ```ruby ensure con.close if con end ``` 最后，我們釋放資源。 ```ruby $ ./server_version.rb 90309 ``` 運行程序，我們獲得數據庫服務器版本。 ## 密碼認證接下來，我們將使用密碼對數據庫服務器進行認證。在本教程的所有其他示例中，我們假設使用`peer`或`trust`認證模式。我們將`pg_hba.conf`文件中本地連接的認證類型更改為`md5`。 ```ruby $ sudo service postgresql restart ``` 要應用更改，必須重新啟動數據庫服務器。 password_authentication.rb ```ruby #!/usr/bin/ruby require 'pg' begin con = PG.connect :dbname => 'testdb', :user => 'janbodnar', :password => 'pswd37' user = con.user db_name = con.db pswd = con.pass puts "User: #{user}" puts "Database name: #{db_name}" puts "Password: #{pswd}" rescue PG::Error => e puts e.message ensure con.close if con end ``` 在示例中，我們使用密碼連接到數據庫。我們打印用戶名，數據庫名稱和當前數據庫連接的密碼。 ```ruby con = PG.connect :dbname => 'testdb', :user => 'janbodnar', :password => 'pswd37' ``` 在連接字符串中，我們添加了密碼選項。 ```ruby user = con.user ``` `user`方法返回連接的用戶名。 ```ruby db_name = con.db ``` `db`方法返回連接的數據庫名稱。 ```ruby pswd = con.pass ``` `pass`方法返回連接的密碼。 ```ruby $ ./password_authentication.rb User: janbodnar Database name: testdb Password: pswd37 ``` 該程序將打印數據庫用戶，數據庫名稱和使用的密碼。 ## 創建數據庫表在本節中，我們將創建一個數據庫表并用數據填充它。 create_table.rb ```ruby #!/usr/bin/ruby require 'pg' begin con = PG.connect :dbname => 'testdb', :user => 'janbodnar' con.exec "DROP TABLE IF EXISTS Cars" con.exec "CREATE TABLE Cars(Id INTEGER PRIMARY KEY, Name VARCHAR(20), Price INT)" con.exec "INSERT INTO Cars VALUES(1,'Audi',52642)" con.exec "INSERT INTO Cars VALUES(2,'Mercedes',57127)" con.exec "INSERT INTO Cars VALUES(3,'Skoda',9000)" con.exec "INSERT INTO Cars VALUES(4,'Volvo',29000)" con.exec "INSERT INTO Cars VALUES(5,'Bentley',350000)" con.exec "INSERT INTO Cars VALUES(6,'Citroen',21000)" con.exec "INSERT INTO Cars VALUES(7,'Hummer',41400)" con.exec "INSERT INTO Cars VALUES(8,'Volkswagen',21600)" rescue PG::Error => e puts e.message ensure con.close if con end ``` 創建的表稱為`Cars`，它具有三列：ID，汽車名稱及其價格。 ```ruby con.exec "DROP TABLE IF EXISTS Cars" ``` `exec`方法將 SQL 命令提交到服務器并等待結果。如果表已經存在，我們的 SQL 命令將刪除該表。 ```ruby $ ./create_table.rb $ psql testdb psql (9.3.9) Type "help" for help. testdb=> SELECT * FROM Cars; id | name | price ----+------------+-------- 1 | Audi | 52642 2 | Mercedes | 57127 3 | Skoda | 9000 4 | Volvo | 29000 5 | Bentley | 350000 6 | Citroen | 21000 7 | Hummer | 41400 8 | Volkswagen | 21600 (8 rows) ``` 我們執行程序，并使用`psql`工具驗證創建的表。 ## 簡單查詢在本節中，我們執行一個簡單的查詢命令。 query_version.rb ```ruby #!/usr/bin/ruby require 'pg' begin con = PG.connect :dbname => 'testdb', :user => 'janbodnar' rs = con.exec 'SELECT VERSION()' puts rs.getvalue 0, 0 rescue PG::Error => e puts e.message ensure con.close if con end ``` 該示例獲取數據庫服務器的版本。 ```ruby rs = con.exec 'SELECT VERSION()' ``` `SELECT VERSION()` SQL 語句檢索數據庫的版本。 ```ruby puts rs.getvalue 0, 0 ``` `getvalue`方法返回返回結果集的一行的單個字段值。 ```ruby $ ./query_version.rb PostgreSQL 9.3.9 on x86_64-unknown-linux-gnu, compiled by gcc (Ubuntu 4.8.4-2ubuntu1~14.04) 4.8.4, 64-bit ``` 程序將打印此輸出。 ## 檢索多行數據以下示例執行一個查詢，該查詢返回多行數據。 multiple_rows.rb ```ruby #!/usr/bin/ruby require 'pg' begin con = PG.connect :dbname => 'testdb', :user => 'janbodnar' rs = con.exec "SELECT * FROM Cars LIMIT 5" rs.each do |row| puts "%s %s %s" % [ row['id'], row['name'], row['price'] ] end rescue PG::Error => e puts e.message ensure rs.clear if rs con.close if con end ``` 該程序將打印`Cars`表的前五行的數據。 ```ruby rs = con.exec "SELECT * FROM Cars LIMIT 5" ``` 此 SQL 查詢返回五行數據。 ```ruby rs.each do |row| puts "%s %s %s" % [ row['id'], row['name'], row['price'] ] end ``` 使用`each`方法，我們遍歷結果集并打印一行的雜項。 ```ruby $ ./multiple_rows.rb 1 Audi 52642 2 Mercedes 57127 3 Skoda 9000 4 Volvo 29000 5 Bentley 350000 ``` 這是`multiple_rows.rb`程序的輸出。 ## 預備語句預備語句可防止 SQL 注入并提高性能。在使用預備語句時，我們使用占位符，而不是直接將值寫入語句中。 prepared_statement.rb ```ruby #!/usr/bin/ruby require 'pg' if ARGV.length != 1 then puts "Usage: prepared_statement.rb rowId" exit end rowId = ARGV[0] begin con = PG.connect :dbname => 'testdb', :user => 'janbodnar' con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1" rs = con.exec_prepared 'stm1', [rowId] puts rs.values rescue PG::Error => e puts e.message ensure rs.clear if rs con.close if con end ``` 該程序將一個行 ID 作為其參數。它獲取指定行的數據并進行打印。由于程序從用戶那里獲取了一個不可信任的值，因此有必要使用預備語句。 ```ruby rowId = ARGV[0] ``` 命令行參數存儲在`rowId`變量中。 ```ruby con.prepare 'stm1', "SELECT * FROM Cars WHERE Id=$1" ``` `prepare`方法準備一個具有給定名稱的 SQL 語句，以便稍后執行。我們的 SQL 語句返回`Cars`表的一行。 `$1`是一個占位符，稍后會填充一個實際值。 ```ruby rs = con.exec_prepared 'stm1', [rowId] ``` `exec_prepared`方法執行由語句名稱指定的預備命名語句。第二個參數是 SQL 查詢的綁定參數數組。 ```ruby puts rs.values ``` `values`方法打印行的字段值。 ```ruby $ ./prepared_statement.rb 4 4 Volvo 29000 ``` 這是示例的輸出。以下示例顯示了另一種創建預備語句的方法。 prepared_statement2.rb ```ruby #!/usr/bin/ruby require 'pg' begin con = PG.connect :dbname => 'testdb', :user => 'janbodnar' stm = "SELECT $1::int AS a, $2::int AS b, $3::int AS c" rs = con.exec_params(stm, [1, 2, 3]) puts rs.values rescue PG::Error => e puts e.message ensure rs.clear if rs con.close if con end ``` 該示例使用`exec_params`創建并執行預備語句。 ```ruby stm = "SELECT $1::int AS a, $2::int AS b, $3::int AS c" ``` 在語句中，我們將預期參數的數據類型附加到占位符。 ```ruby rs = con.exec_params(stm, [1, 2, 3]) ``` `exec_params`方法使用占位符作為參數將 SQL 查詢請求發送到數據庫。 ```ruby $ ./prepared_statement2.rb 1 2 3 ``` 這是示例的輸出。 ## 元數據元數據是有關數據庫中數據的信息。以下內容屬于元數據：有關存儲數據的表和列的信息，受 SQL 語句影響的行數或結果集中返回的行和列數。 ### 列標題在第一個示例中，我們打印列標題。 column_headers.rb ```ruby #!/usr/bin/ruby require 'pg' begin con = PG.connect :dbname => 'testdb', :user => 'janbodnar' rs = con.exec 'SELECT * FROM Cars WHERE Id=0' puts 'There are %d columns ' % rs.nfields puts 'The column names are:' puts rs.fields rescue PG::Error => e puts e.message ensure rs.clear if rs con.close if con end ``` 該示例將可用列的數量及其名稱打印到控制臺。 ```ruby rs = con.exec 'SELECT * FROM Cars WHERE Id=0' ``` 在 SQL 語句中，我們選擇一行的所有列。 ```ruby puts "There are %d columns " % rs.nfields ``` `nfields`方法返回查詢結果行中的列數。 ```ruby puts rs.fields ``` `fields`方法返回一個字符串數組，該字符串表示結果中字段的名稱。 ```ruby $ ./column_headers.rb There are 3 columns The column names are: id name price ``` 這是示例的輸出。 ### 表的清單 PostgreSQL 的信息模式由一組視圖組成，這些視圖包含有關當前數據庫中定義的對象的信息。 `tables`視圖包含當前數據庫中定義的所有表和視圖。 list_tables.rb ```ruby #!/usr/bin/ruby require 'pg' begin con = PG.connect :dbname => 'testdb', :user => 'janbodnar' rs = con.exec "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'" rs.each do |row| puts row['table_name'] end rescue PG::Error => e puts e.message ensure rs.clear if rs con.close if con end ``` 該示例打印`testdb`數據庫中的所有表。 ```ruby rs = con.exec "SELECT table_name FROM information_schema.tables WHERE table_schema = 'public'" ``` 該 SQL 語句從當前數據庫中選擇所有表。 ```ruby rs.each do |row| puts row['table_name'] end ``` 表將打印到控制臺。 ```ruby $ ./list_tables.rb authors books cars ``` `list_tables.rb`程序在`testdb`數據庫中打印可用表。 ## 事務事務是針對一個或多個數據庫中數據的數據庫操作的基本單位。事務中的 SQL 語句可以全部提交給數據庫，也可以全部回滾。為了數據安全和完整性，將 SQL 語句放入事務中。 PostgreSQL 在自動提交模式下運行。每個 SQL 語句都在一個事務中執行：每個單獨的語句周圍都包含一個隱式`BEGIN`和（如果成功）`COMMIT`。顯式事務以`BEGIN`命令開始，以`COMMIT`或`ROLLBACK`命令結束。 transaction.rb ```ruby #!/usr/bin/ruby require 'pg' begin con = PG.connect :dbname => 'testdb', :user => 'janbodnar' con.transaction do |con| con.exec "UPDATE Cars SET Price=23700 WHERE Id=8" con.exec "INSERT INTO Car VALUES(9,'Mazda',27770)" end rescue PG::Error => e puts e.message ensure con.close if con end ``` 在示例中，我們更新了汽車的價格并插入了新汽車。這兩個操作包含在單個事務中。這意味著要么執行兩個操作，要么不執行任何操作。 ```ruby con.transaction do |con| con.exec "UPDATE Cars SET Price=23700 WHERE Id=8" con.exec "INSERT INTO Car VALUES(9,'Mazda',27770)" end ``` `transaction`方法在單個事務中在塊內運行代碼。它在塊的開頭執行`BEGIN`，在塊的末尾執行`COMMIT`，如果發生任何異常，則執行`ROLLBACK`。 [Tweet](https://twitter.com/share) 這是 PostgreSQL Ruby 教程。您可能也對 [SQLite Ruby 教程](/db/sqliteruby/)， [MySQL Ruby 教程](/db/mysqlrubytutorial/)， [PostgreSQL Python 教程](/db/postgresqlpythontutorial/)， [MongoDB Ruby 教程](/db/mongodbruby/)感興趣。或 ZetCode 上的 [PostgreSQL PHP 教程](/db/postgresqlphp/)。