First ruby script mysql data input


#1

Hello all,

I’m trying to use ruby for the first time. I want to make a script
that is going to parse through my file and pick stuff up and place it
into a database. When I put it into the database I would like to first
ask the database if there is something already like it and if there is
an “empty” primary key to use.
The key ID’s in the database go something like:
1 2 3 5 6 7 8 10 11 12 18 22 23 24 and so on. I want to put stuff into
ID 4, 9, 13 and so on. I was wondering what modules I should use and
how to go about this.
I come from Perl and I’m pretty use to doing this type of thing in
Perl, expect for asking what ID is “free”,

So just ideas of how to do it and what tools you all think I will
need

Thank


#2

On 7/22/07, PB0711 removed_email_address@domain.invalid wrote:

how to go about this.
I come from Perl and I’m pretty use to doing this type of thing in
Perl, expect for asking what ID is “free”,

So just ideas of how to do it and what tools you all think I will
need

Thank

You would need a library capable of handling connection to mysql db.
You can try:

sudo gem install mysql

Or you can use DBI bindings(which in some form exist in perl also i
believe).

you can do google search on “mysql+ruby” and find many meaningful
tutorials and documents.


#3

On 7/21/07, PB0711 removed_email_address@domain.invalid wrote:

Hello all,

I’m trying to use ruby for the first time. I want to make a script
that is going to parse through my file and pick stuff up and place it
into a database. When I put it into the database I would like to first
ask the database if there is something already like it and if there is
an “empty” primary key to use.
The key ID’s in the database go something like:

I think you meant the key ID’s in a table.

1 2 3 5 6 7 8 10 11 12 18 22 23 24 and so on. I want to put stuff into
ID 4, 9, 13 and so on. I was wondering what modules I should use and
how to go about this.
I come from Perl and I’m pretty use to doing this type of thing in
Perl, expect for asking what ID is “free”,

So just ideas of how to do it and what tools you all think I will
need

I’ve never used the mysql gem, but it might look something like this
after you successfully connect to the database:

ids = []
res = db.query( “select id from mytable”)
while row = res.fetch_row do
ids << row[0]
end
res.free
ids.map! { |i| i.to_i } # do this just in case mysql doesn’t return
integers

at this point you have an array of integers, how you get here is up to

you

now, for the ruby chocolate …

free_ids = (1…ids.max).to_a - ids

There are other gems for mysql access (activerecord, sequel, and I
think there’s an odbc one).

hth,
Todd


#4

ids = []
res = db.query( “select id from mytable”)
while row = res.fetch_row do
ids << row[0]
end
res.free
ids.map! { |i| i.to_i } # do this just in case mysql doesn’t return integers

at this point you have an array of integers, how you get here is up to you

now, for the ruby chocolate …

free_ids = (1…ids.max).to_a - ids

here’s the sequel version:

require ‘sequel/mysql’
DB = Sequel.open(‘mysql://user@localhost:/mydb’)
free_ids = (1…ids.max).to_a - DB[:mytable].map(:id)

best
sharon