Storing a result in mysql-database - how to?

Ruby
1

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 = "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
    }

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… :wink:

2
  1. Install the mysql2 gem:

gem install mysql2

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

  2. 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

3

hello 7stud,

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

Again Thanks to you

greetings dil bert

4

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

5

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 cats:
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=>]

my_cat.name = “Roger”
my_cat.save

p Cat.all #=>[#<Cat @id=1 @name=“Roger” @breeding=>]