Forum: Ruby storing a result in mysql-database - how to ?

600fc90e36ff81dfaba666708cf91dc5?d=identicon&s=25 Dil Bert (lin)
on 2014-06-08 13:16
helllo dear ruby experts

new to Ruby - i need some advices -

i plan to do some requests in osm-files. (openstreetmap)

Question - how can i store the results on a Database -
eg mysql or - (if you prefer postgresql) -

note: my favorite db - at least at the moment is mysql

here the code


    require 'open-uri'
    require "net/http"
    require 'rexml/document'

    def query_overpass(object_type, left,bottom,right,top, key, value)
       base_url = ""
       query_string =
       url = "#{base_url}#{URI.encode(query_string)}"
       resp = Net::HTTP.get_response(URI.parse(url))
       data = resp.body
       return data

    overpass_result ="node",

    overpass_result.elements.each('osm/node') {|x|
      if !x.elements["tag[@k='name']"].nil?
        print x.elements["tag[@k='name']"].attributes["v"]
      print " | "

      if !x.elements["tag[@k='addr:postcode']"].nil?
        print x.elements["tag[@k='addr:postcode']"].attributes["v"]
        print ", "
      if !x.elements["tag[@k='addr:city']"].nil?
        print x.elements["tag[@k='addr:city']"].attributes["v"]
        print ", "
      if !x.elements["tag[@k='addr:street']"].nil?
        print x.elements["tag[@k='addr:street']"].attributes["v"]
        print ", "
      if !x.elements["tag[@k='addr:housenumber']"].nil?
        print x.elements["tag[@k='addr:housenumber']"].attributes["v"]
      print " | "
      print x.attributes["lat"]
      print " | "
      print x.attributes["lon"]
      print " | "
      if !x.elements["tag[@k='website']"].nil?
        print x.elements["tag[@k='website']"].attributes["v"]
      print " | "
      if !x.elements["tag[@k='amenity']"].nil?
        print x.elements["tag[@k='amenity']"].attributes["v"]
        print " | "


look forward to hear from you

again - i would love to store it on a mysql - database - if possible. If
you would prefer postgresql - then i would
takte this one.... ;-)
54404bcac0f45bf1c8e8b827cd9bb709?d=identicon&s=25 7stud -- (7stud)
on 2014-06-10 01:52
1) Install the mysql2 gem:

gem install mysql2

2) Install mysql on your local machine.  If you already have it
installed(which I assume you do), then start the mysql server.

3) Retrieve, insert, or create stuff:

require 'mysql2'

  client = => "localhost", :username => "root")
  client.query("USE my_db")
  results = client.query("SELECT * FROM people")

  results.each do |row|
    puts row['name']

  client.query("INSERT INTO people(name, info) VALUES('Diane', '7 8
  results = client.query("SELECT * FROM people")

  results.each do |row|
    puts "#{row['id']} #{row['name']} #{row['info']}"

rescue Mysql2::Error => e
  puts e.errno
  puts e.error
  client.close if client
600fc90e36ff81dfaba666708cf91dc5?d=identicon&s=25 Dil Bert (lin)
on 2014-06-10 08:33
hello 7stud,

many thanks - i do as adviced (later the week) and come back & report

Again Thanks to you

greetings dil bert
54404bcac0f45bf1c8e8b827cd9bb709?d=identicon&s=25 7stud -- (7stud)
on 2014-06-11 02:04
Another way using the Sequel gem:

$ gem install sequel
$ gem install mysql


require 'sequel'
require 'mysql'

DB = Sequel.connect('mysql://root:@localhost/my_db')
#Or more generally: mysql://user:password@localhost/db_name

#select * from people:
p DB[:people].select.to_a

#Create a table:
DB.create_table :dogs do
  primary_key :id
  String      :name, :size => 50
  Integer     :age

#Create a DataSet for the dogs table:
dogs = DB.from(:dogs)

#Use the DataSet to insert a new row into the table:
  :name => "Ralph",
  :age  => 10,


[{:id=>1, :name=>"Ralph", :age=>10}]
54404bcac0f45bf1c8e8b827cd9bb709?d=identicon&s=25 7stud -- (7stud)
on 2014-06-11 02:53
Another way with DataMapper:

$ gem install dm-mysql-adapter
$ gem install data_mapper


require 'data_mapper'

DataMapper.setup(:default, 'mysql://root:@localhost/my_db')

#Define a table named cat*s*:
class Cat
  include DataMapper::Resource

  property :id,       Serial #An auto-increment integer key
  property :name,     String #A varchar type string, for short strings
  property :breeding, Text   #For longer strings

#Check all defined tables for validity:

#Create all the tables you defined:
DataMapper.auto_migrate!  #drops table first if it already exists!

#Insert a row in the cats table:
my_cat = Cat.create(
  :name     =>  "Fluffy",
  :breeding =>  "Lot's of breeding history here.."

p Cat.all   #=>[#<Cat @id=1 @name="Fluffy" @breeding=<not loaded>>] = "Roger"

p Cat.all  #=>[#<Cat @id=1 @name="Roger" @breeding=<not loaded>>]
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.