Talking Directly to a PostgreSQL Database

Problem

You want to send SQL queries and commands directly to a PostgreSQL database.

Solution

As with the MySQL recipe preceding this one, ask: do you really need to do this? The generic DBI library usually works just fine. As before, the main exception is when you need to make low-level SQL calls from within a Rails application.

There are two APIs for communicating with a PostgreSQL database, and both are available as gems. The postgres gem provides a Ruby binding to the C client library, and the postgres-pr gem provides a pure Ruby interface.

Heres a Postgres-specific version of the method with_db, defined in the chapter intro. It returns a PGconn object, which you can use to run queries or get server information. This code assumes you e accessing the database through TCP/IP on port 5432 of your local machine.

	require 
ubygems
	require postgres

	def with_db
	 db = PGconn.connect(localhost, 5432, \, \, cookbook,
	 cookbook_user, password)
	 begin
	 yield db
	 ensure
	 db.close
	 end
	end

The PGconn#exec method runs any SQL statement, whether its a SELECT query or something else. When it runs a query, the return value is a result-set object (a PGresult); otherwise, its nil. Here it is running some SQL commands:

	with_db do |db|
	 begin
	 db.exec(drop table secrets)
	 rescue PGError
	 # Unlike MySQL, Postgres does not have a "drop table unless exists"
	 # command. We can simulate it by issuing a "drop table" command and
	 # ignoring any error due to the table not existing in the first place.
	 # This is essentialy what MySQLs "drop table unless exists" does.
	 end

	 db.exec(create table secrets( id SERIAL PRIMARY KEY,
	 secret TEXT ))
	 db.exec(%{insert into secrets(secret) values
	 (Oh, Postgres, you\
e the only one who really understands me.)})
	end

Heres a query:

	with_db do |db|
	 res = db.query(select * from secrets)
	 res.each { |row| puts "#{row[0]}: #{row[1]}" }
	end
	# 1: Oh, Postgres, you
e the only one who really understands me.

Discussion

Note the slight differences between the Postgres implementation of SQL and the MySQL implementation. The "drop table if exists" syntax is MySQL-specific. Postgres names the data types differently, and expects string values to be single-quoted.

Like the database connection itself, the result set you get from exec wants to be closed when you e done with it. As we did with query in the MySQL binding, we can alter exec to take an optional code block and do the cleanup for us:

	class PGconn
	 alias :exec_no_block :exec
	 def exec(sql)
	 res = exec_no_block(sql)
	 return res unless block_given?
	 begin
	 yield res
	 ensure
	 res.clear if res
	 end
	 end
	end

Now we can write more concise query code, and not have to worry about freeing the result set:

	with_db do |db|
	 db.exec(select * from secrets) do |res|
	 res.each { |row| puts "#{row[0]}: #{row[1]}" }
	 end
	end
	# 1: Oh, Postgres, you
e the only one who really understands me.

The method PGresult#each yields you the rows of a result set as arrays, and PGresult#num_tuples gives you the number of rows matched by a query. The Postgres database binding has no equivalent of the MySQL bindings each_hash, but you can write one pretty easily:

	class PGresult
	 def each_hash
	 f = fields
	 each do |array|
	 hash = {}
	 fields.each_with_index do |field, i|
	 hash[field] = array[i]
	 end
	 yield hash
	 end
	 end
	end

Here it is in action:

	with_db do |db|
	 db.exec("select * from secrets") do |res|
	 puts "#{res.num_tuples} row(s) matched:"
	 res.each_hash do |hash|
	 hash.each { |k,v| puts " #{k} = #{v}" }
	 end
	 end
	end
	# 1 row(s) matched:
	# id = 1
	# secret = Oh, Postgres, you
e the only one who really understands me.

See Also

  • The Postgres reference (http://www.postgresql.org/docs/manuals/ )
  • The reference for the Ruby Postgres binding (http://ruby.scripting.ca/postgres/)
  • If you can get the native Postgres binding installed, try the postgres-pr gem; it implements a pure Ruby client to the Postgres server, with more or less the same interface as the native binding
  • The PGconn.quote method helps you defend against SQL injection attacks; see Recipe 13.15, "Preventing SQL Injection Attacks," for more


Strings

Numbers

Date and Time

Arrays

Hashes

Files and Directories

Code Blocks and Iteration

Objects and Classes8

Modules and Namespaces

Reflection and Metaprogramming

XML and HTML

Graphics and Other File Formats

Databases and Persistence

Internet Services

Web Development Ruby on Rails

Web Services and Distributed Programming

Testing, Debugging, Optimizing, and Documenting

Packaging and Distributing Software

Automating Tasks with Rake

Multitasking and Multithreading

User Interface

Extending Ruby with Other Languages

System Administration



Ruby Cookbook
Ruby Cookbook (Cookbooks (OReilly))
ISBN: 0596523696
EAN: 2147483647
Year: N/A
Pages: 399

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net