Thread: ruby/postgres - getting assoc array of rows?
Looking at the docs here: http://ruby.scripting.ca/postgres/reference.html there doesn't appear to be an easy way to get an associative row of rows returns. Looking in Rails PG code, I see this convolution: def select(sql, name = nil) res = execute(sql, name) results = res.result rows = [] if results.length > 0 fields = res.fields results.each do |row| hashed_row = {} row.each_index do |cel_index| column = row[cel_index] if res.type(cel_index) == BYTEA_COLUMN_TYPE_OID column = unescape_bytea(column) end hashed_row[fields[cel_index]]= column end rows << hashed_row end end returnrows end Is there a much easier and simpler way? csn __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
On Sat, Nov 19, 2005 at 08:14:40PM -0800, CSN wrote: > Looking at the docs here: > http://ruby.scripting.ca/postgres/reference.html > > there doesn't appear to be an easy way to get an associative row > of rows returns. What exactly are you looking for? The example you posted returns an array of hashes, but depending on what you're doing all that work might not be necessary. PGconn#exec returns a PGresult object, the PGresult#each iterator yields PGrow objects, and PGrow#[] accepts both numeric and text indexes. Example: % psql -d test -c 'SELECT id, name FROM people'id | name ----+------- 1 | Alice (1 row) % cat test.rb require 'postgres' conn = PGconn.new('dbname=test') res = conn.exec('SELECT id, name FROM people') res.each do |row| puts "by name: #{row['id']} #{row['name']}" puts "by position: #{row[0]} #{row[1]}" end res.clear conn.close % ruby test.rb by name: 1 Alice by position: 1 Alice You could also convert the PGresult object into an array of PGrow objects with a one-liner, although you wouldn't get automatic bytea handling as in the function you posted: rows = res.collect Will any of this work for you? If not then please provide more detail. -- Michael Fuhr
On Sat, Nov 19, 2005 at 10:32:47PM -0700, Michael Fuhr wrote: > You could also convert the PGresult object into an array of PGrow > objects with a one-liner, although you wouldn't get automatic bytea > handling as in the function you posted: > > rows = res.collect Other possibilities are res.to_a, res.entries, res.result, and res.rows, which all do the same thing: return an array of PGrow objects. The collect, to_a, and entries methods are provided by the Enumerable mixin; the result and rows methods are aliases for entries, which is synonymous with to_a. Using any of these methods ultimately results in calling PGresult#each, which yields the PGrow objects. -- Michael Fuhr
I tried:
puts "by name: #{row['id']} #{row['name']}"
but it exits with:
pg.rb:16:in `[]': can't convert String into Integer (TypeError)
The by position line does work though.
thanks
csn
--- Michael Fuhr <mike@fuhr.org> wrote:
> On Sat, Nov 19, 2005 at 08:14:40PM -0800, CSN wrote:
> > Looking at the docs here:
> > http://ruby.scripting.ca/postgres/reference.html
> >
> > there doesn't appear to be an easy way to get an associative row
> > of rows returns.
>
> What exactly are you looking for? The example you posted returns
> an array of hashes, but depending on what you're doing all that
> work might not be necessary. PGconn#exec returns a PGresult object,
> the PGresult#each iterator yields PGrow objects, and PGrow#[] accepts
> both numeric and text indexes. Example:
>
> % psql -d test -c 'SELECT id, name FROM people'
> id | name
> ----+-------
> 1 | Alice
> (1 row)
>
> % cat test.rb
> require 'postgres'
> conn = PGconn.new('dbname=test')
> res = conn.exec('SELECT id, name FROM people')
> res.each do |row|
> puts "by name: #{row['id']} #{row['name']}"
> puts "by position: #{row[0]} #{row[1]}"
> end
> res.clear
> conn.close
>
> % ruby test.rb
> by name: 1 Alice
> by position: 1 Alice
>
> You could also convert the PGresult object into an array of PGrow
> objects with a one-liner, although you wouldn't get automatic bytea
> handling as in the function you posted:
>
> rows = res.collect
>
> Will any of this work for you? If not then please provide more
> detail.
>
> --
> Michael Fuhr
>
__________________________________
Yahoo! Mail - PC Magazine Editors' Choice 2005
http://mail.yahoo.com
On Sun, Nov 20, 2005 at 02:31:45AM -0800, CSN wrote:
> I tried:
> puts "by name: #{row['id']} #{row['name']}"
>
> but it exits with:
> pg.rb:16:in `[]': can't convert String into Integer (TypeError)
What versions of Ruby and ruby-postgres are you using? Did you use
exactly the code I posted or did you do something different? The
only way to get a PGrow object (other than building it yourself)
is from PGresult#each or some method that calls it, so this won't
work:
conn = PGconn.new('dbname=test')
result = conn.exec('SELECT 1 AS x')
row = result[0] # returns an Array
puts row['x'] # raises TypeError
but this does work:
row = result.to_a[0] # returns a PGrow
puts row['x']
I'm not sure if the PGrow behavior is version-specific. I don't
see it mentioned in the ruby-postgres ChangeLog, so I'd have to dig
a little more to find out.
--
Michael Fuhr
--- Michael Fuhr <mike@fuhr.org> wrote:
> On Sun, Nov 20, 2005 at 02:31:45AM -0800, CSN wrote:
> > I tried:
> > puts "by name: #{row['id']} #{row['name']}"
> >
> > but it exits with:
> > pg.rb:16:in `[]': can't convert String into Integer (TypeError)
>
> What versions of Ruby and ruby-postgres are you using? Did you use
> exactly the code I posted or did you do something different? The
> only way to get a PGrow object (other than building it yourself)
> is from PGresult#each or some method that calls it, so this won't
> work:
I'm using Ruby 1.8 and postgres 0.7.1 (installed via gem). Doing 'gem list -r' I see there's also
this, which I don't have installed:
postgres-pr (0.4.0, 0.3.6, 0.3.5, 0.3.4, 0.3.3, 0.3.2, 0.3.1, 0.3.0, 0.2.2, 0.2.1, 0.2.0, 0.1.1,
0.1.0, 0.0.1) A pure Ruby interface to the PostgreSQL (>= 7.4) database
My code is nearly identical to what you posted, however PGconn.new('dbname=mydb') gives this
error:
pg.rb:6:in `new': could not translate host name "dbname=mydb" to address: Name or service not
known (PGError)
This in the only connection code I could get to work:
conn = PGconn.connect("localhost", 5432, '', '', "mydb", "user", "password")
My code also differs in that I need to put "require 'rubygems'" before "require 'postgres'",
otherwise I get this error:
`require': no such file to load -- postgres (LoadError)
> conn = PGconn.new('dbname=test')
> result = conn.exec('SELECT 1 AS x')
> row = result[0] # returns an Array
> puts row['x'] # raises TypeError
>
> but this does work:
>
> row = result.to_a[0] # returns a PGrow
> puts row['x']
With that, I get (I'm doing "select * from items order by id limit 10"):
nil
nil
nil
nil
nil
nil
nil
undefined method `[]' for nil:NilClass (NoMethodError)
Here's my complete code I'm using with various lines comments/uncommented. Using "result.to_a[0]"
vs. "result.each", etc. appears to give a different type of row and some of the "puts" lines give
errors where they worked before, or work when they previously gave errors.
require 'rubygems'
require 'postgres'
conn = PGconn.connect("localhost", 5432, '', '', "mydb", "user", "password")
#conn = PGconn.new("dbname=mydb")
#conn = PGconn.connect(:pghost=>"localhost", :dbname=> "mydb", :login=>"user",
:password=>"password")
res = conn.exec("select * from items order by id limit 10;")
#rows=res.collect
#for row in res.result
res.each do |row|
#for row in res.to_a[0] #puts row[0] + row[1] puts "#{row['title']}" # error: undefined method `[]' for nil:NilClass
(NoMethodError)#puts "by name: #{row['id']} #{row['title']}" # gives error #print row # works #print row.inspect #
works
#puts row[0] # works #puts row.getvalue(0,0) # error: undefined method `getvalue' for "7518":String (NoMethodError)
end
> I'm not sure if the PGrow behavior is version-specific. I don't
> see it mentioned in the ruby-postgres ChangeLog, so I'd have to dig
> a little more to find out.
Thanks for your help!
csn
>
> --
> Michael Fuhr
>
__________________________________
Start your day with Yahoo! - Make it your home page!
http://www.yahoo.com/r/hs
On Sun, Nov 20, 2005 at 11:36:20AM -0800, CSN wrote: > --- Michael Fuhr <mike@fuhr.org> wrote: > > What versions of Ruby and ruby-postgres are you using? Did you use > > exactly the code I posted or did you do something different? The > > only way to get a PGrow object (other than building it yourself) > > is from PGresult#each or some method that calls it, so this won't > > work: > > I'm using Ruby 1.8 and postgres 0.7.1 (installed via gem). That explains it. I've been using the ruby-postgres-20050412 snapshot; I just grabbed the 0.7.1 source code and it doesn't have the PGrow type, nor the PGconn.new syntax that I've been using. I see that a 20051119 snapshot is now available: http://ruby.scripting.ca/postgres/ -- Michael Fuhr
Ah.. I installed the snapshot manually and the script works fine now. They should update the gem version - Jan. 2003 is quite a while ago. Does it matter if the postgres library is installed via 'gem' or not? Thanks again for your help! csn --- Michael Fuhr <mike@fuhr.org> wrote: > On Sun, Nov 20, 2005 at 11:36:20AM -0800, CSN wrote: > > --- Michael Fuhr <mike@fuhr.org> wrote: > > > What versions of Ruby and ruby-postgres are you using? Did you use > > > exactly the code I posted or did you do something different? The > > > only way to get a PGrow object (other than building it yourself) > > > is from PGresult#each or some method that calls it, so this won't > > > work: > > > > I'm using Ruby 1.8 and postgres 0.7.1 (installed via gem). > > That explains it. I've been using the ruby-postgres-20050412 > snapshot; I just grabbed the 0.7.1 source code and it doesn't have > the PGrow type, nor the PGconn.new syntax that I've been using. > > I see that a 20051119 snapshot is now available: > > http://ruby.scripting.ca/postgres/ > > -- > Michael Fuhr > __________________________________ Yahoo! Mail - PC Magazine Editors' Choice 2005 http://mail.yahoo.com
On Sun, Nov 20, 2005 at 12:28:32PM -0800, CSN wrote: > Ah.. I installed the snapshot manually and the script works fine now. > They should update the gem version - Jan. 2003 is quite a while ago. > Does it matter if the postgres library is installed via 'gem' or not? I wouldn't think so, except maybe for how your program loads the code (require vs. require_gem). -- Michael Fuhr
It looks like the snapshot version causes problems for Rails :( http://dev.rubyonrails.org/ticket/2977 http://www.ruby-forum.com/topic/9114#new reals and numerics get converted to values like nil, 1, 2, true, ... I had to uninstall it and go back to the 0.71 version. The postgres.so files get installed in different locations - perhaps I can configure Rails and commandline scripts to load the one they need. csn --- Michael Fuhr <mike@fuhr.org> wrote: > On Sun, Nov 20, 2005 at 12:28:32PM -0800, CSN wrote: > > Ah.. I installed the snapshot manually and the script works fine now. > > They should update the gem version - Jan. 2003 is quite a while ago. > > Does it matter if the postgres library is installed via 'gem' or not? > > I wouldn't think so, except maybe for how your program loads the > code (require vs. require_gem). > > -- > Michael Fuhr > __________________________________ Yahoo! FareChase: Search multiple travel sites in one click. http://farechase.yahoo.com