# First ruby script mysql data input

**URL:** <https://www.ruby-forum.com/t/first-ruby-script-mysql-data-input/110051>\
**Category:** Ruby\
**Created:** [July 22, 2007, 4:57am UTC](https://www.ruby-forum.com/t/first-ruby-script-mysql-data-input/110051 "2007-07-22T04:57:43Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![PB0711](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@PB0711](https://www.ruby-forum.com/u/PB0711)\
**Post date:** [July 22, 2007, 4:57am UTC](https://www.ruby-forum.com/t/first-ruby-script-mysql-data-input/110051/1 "2007-07-22T04:57:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![PB0711](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@PB0711](https://www.ruby-forum.com/u/PB0711)\
**Post date:** [July 22, 2007, 5:29am UTC](https://www.ruby-forum.com/t/first-ruby-script-mysql-data-input/110051/2 "2007-07-22T05:29:58Z")

</div>

On 7/22/07, PB0711 [removed\_email\_address@domain.invalid](mailto: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.

---

<div class="post-metadata">

**Author:** ![PB0711](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@PB0711](https://www.ruby-forum.com/u/PB0711)\
**Post date:** [July 22, 2007, 2:50pm UTC](https://www.ruby-forum.com/t/first-ruby-script-mysql-data-input/110051/3 "2007-07-22T14:50:29Z")

</div>

On 7/21/07, PB0711 [removed\_email\_address@domain.invalid](mailto: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

---

<div class="post-metadata">

**Author:** ![PB0711](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/p/ee59a6/32.png) [@PB0711](https://www.ruby-forum.com/u/PB0711)\
**Post date:** [July 22, 2007, 3:13pm UTC](https://www.ruby-forum.com/t/first-ruby-script-mysql-data-input/110051/4 "2007-07-22T15:13:43Z")

</div>

> 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