Ruby PostgreSQL tutorials [closed]

Closed. This question does not meet Stack Overflow guidelines. It is not currently accepting answers.

---

We don’t allow questions seeking recommendations for books, tools, software libraries, and more. You can edit the question so it can be answered with facts and citations.

Closed 7 years ago.

Improve this question

I am trying to write a ruby script that interacts with a PostgreSQL database. I am trying to piece together how to do this from the documentation, but a nice tutorial or sample code would work wonders to decrease the amount of time to get this working. If anyone has a link, some tips or has some code they could share I would be most grateful.

Edit, made this note more clear:

Note: this isn't to do with rails ActiveRecord, I am writing a Ruby script that will be involved in a pro开发者_JAVA百科gram that is completely independent from Rails.

---

Please be more specific about what postgresql library you're using.

I'm going to assume the 'pg' gem, apart from ActiveRecord.

The project source has an html file that might be helpful. Go to https://bitbucket.org/ged/ruby-pg/src/b477174160c8/doc/postgres.html Then click "raw" at the upper right side of the html. Open the file in your web browser.

This sample code helps you connect (copied from the html file):

require "postgres"
conn = PGconn.connect("localhost", 5432, "", "", "test1")
# or: conn = PGconn.open('dbname=test1')
res = conn.exec("select * from a;")

The res object is a PGResult. Scroll down to that section in the html to see what methods you can call.

This link has a PGResult example: http://rubydoc.info/gems/pg/0.10.0/PGresult

Excerpt:

require 'pg'
conn = PGconn.open(:dbname => 'test')
res  = conn.exec('SELECT 1 AS a, 2 AS b, NULL AS c')
res.getvalue(0,0) # '1'
res[0]['b']       # '2'
res[0]['c']       # nil

---

I confirm, "postgres" package is outdated, you need "pg".

It tooks me lot of time just to get a basic select * from films working with ruby and postgres. As I am kind, here is my code:

postgres preparation (database=megatest user=roger pass=123456 table=films)

$ su postgres 
psql 
CREATE USER roger WITH PASSWORD '123456';

GRANT ALL PRIVILEGES ON DATABASE megatest to roger;

megatest=# GRANT SELECT ON films TO PUBLIC;

PG package preparation

sudo gem install pg

Ruby Code

require 'pg'

conn=PGconn.connect( :hostaddr=>"127.0.0.1", :port=>5432, :dbname=>"megatest", :user=>"roger", :password=>'123456')
# or for a non IP address :host => 'my.host.name.com' instead of hostaddr

# run the query
res = conn.exec("SELECT * FROM films")

# Ran only once in order to get field Name
fieldArray=res.fields()
fieldArray.each do |elem|
    print "elem="+elem+"\n"
end

# print data from the query
res.each{ |row|
    puts "Code="+row["code"]  +" title="+row["title"] +" did="+row["did"] +" date_prod="+row["date_prod"] +" kind="+row["kind"] +" len="+row["len"]
}

Results

root@eblain-VirtualBox:/home/eblain/ruby# ruby postgresTest.rb
Code=UA502 title=Bananas did=105 date_prod=1971-07-13 kind=Comedy len=01:22:00
Code=UA503 title=Cowboy did=105 date_prod=1979-07-13 kind=Horror len=01:32:00
Code=UA544 title=YoBro did=105 date_prod=1981-07-13 kind=Action len=01:42:00

---

You only need to require the pg gem and establish the connection to the DB:

require 'pg'
# require 'active_record'  # uncomment for not Rails environment

ActiveRecord::Base.establish_connection(:adapter => "postgresql",
                                        :username => "username",
                                        :password => "password",
                                        :database => "database")

When you define models to inherit from ActiveRecord::Base they will use this database connection. Everything else should work like it does in Rails.

---

For parametrized SQL statements, you should use PGconn#exec_params, e.g.

conn = PGconn.new(:dbname => 'test')
conn.exec_params(
    'INSERT INTO comedians (first_name, last_name) VALUES ($1, $2)',
    ['Louis', 'CK'])
conn.close

Source: http://deveiate.org/code/pg/PGconn.html

Look here for a complete list of parameters that can be passed to the PGconn constructor.