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

[CODE]

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

    def query_overpass(object_type, left,bottom,right,top, key, value)
       base_url = "http://www.overpass-api.de/api/xapi?"
       query_string =
"#{object_type}[bbox=#{left},#{bottom},#{right},#{top}][#{key}=#{value}]"
       url = "#{base_url}#{URI.encode(query_string)}"
       resp = Net::HTTP.get_response(URI.parse(url))
       data = resp.body
       return data
    end

    overpass_result = REXML::Document.new(query_overpass("node",
7.1,51.2,7.2,51.3,"amenity","restaurant|pub|ice_cream|food_court|fast_food|cafe|biergarten|bar|bakery|steak|pasta|pizza|sushi|asia|nightclub"))

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

      if !x.elements["tag[@k='addr:postcode']"].nil?
        print x.elements["tag[@k='addr:postcode']"].attributes["v"]
        print ", "
      end
      if !x.elements["tag[@k='addr:city']"].nil?
        print x.elements["tag[@k='addr:city']"].attributes["v"]
        print ", "
      end
      if !x.elements["tag[@k='addr:street']"].nil?
        print x.elements["tag[@k='addr:street']"].attributes["v"]
        print ", "
      end
      if !x.elements["tag[@k='addr:housenumber']"].nil?
        print x.elements["tag[@k='addr:housenumber']"].attributes["v"]
      end
      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"]
      end
      print " | "
      if !x.elements["tag[@k='amenity']"].nil?
        print x.elements["tag[@k='amenity']"].attributes["v"]
        print " | "
      end
      puts
    }

[/CODE]

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'

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

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

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

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

rescue Mysql2::Error => e
  puts e.errno
  puts e.error
ensure
  client.close if client
end
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
all.

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
end

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

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

p dogs.select.to_a

--output:--
[{: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')
#mysql://user:password@hostname/database_name'

#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
end

#Check all defined tables for validity:
DataMapper.finalize

#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>>]

my_cat.name = "Roger"
my_cat.save

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.