Loop and strings in Ruby/SQLite3

Question

Hello,

So I need to run a loop in Ruby to pass some strings into SQLite. Basically I have a table that looks like this:

    pID          Data
1649,1650,1651|Some data
1643,3|some more data
23,4,5,6,7|More data

Now in my SQLite queries, I will sometimes need to pass all the pIDs for a given line through as one whole string, which I can easily do with the 'i' variable in the loop below:

pID = db.execute( "select distinct pID from pmesh")
pID.each do |i|
end

Where 'i' will equal each pID line and each string looks like this:

1649,1650,1651
1643,3
23,4,5,6,7

But sometimes I will need each string to look like this for me to pass them through:

"1649","1650","1651"
"1643","3"
"23,"4","5","6","7"    

Is there any easy loop that I can do that will put quotations and proper commas to make the string appear as I want them to? I know I can do "i = i.to_s() and a = i.split(",")", but I really don't know much else loop-wise after that.

Thanks, Bobby

Answer 1

I think you're looking for this:

i.split(",").map { |x| '"' + x + '"' }.join(",")

Or this:

i.gsub(/[0-9]+/, '"\0"')

Answer 2

Use Enumerable.collect

Here's an example:

x = [1,2,4,5,6]
=> [1,2,4,5,6]
x.collect { |i| "#{i}" }
= > ["1","2","4","5","6"]

or, if you want to join them into one string:

x.collect { |i| "#{i}" }.join(', ')

or, if your input is a csv string:

   x.split(",").collect { |i| "#{i}" }.join(', ')

Edit:

Here is an example of what I think you're trying to do.

 require 'sqlite3'
 db = SQLite3::Database.new("test.db")
 db.execute("create table bobby (pID varchar2(50), Data varchar(100))")
 db.execute("select * from bobby")
 db.execute("insert into bobby values ( ? , ?)", "1649,1650,1651", "Some Data1")
 db.execute("insert into bobby values ( ? , ?)", "1643,3", "Some Data2")
 db.execute("insert into bobby values ( ? , ?)", "23,4,5,6,7", "Some Data3")
 db.execute("select * from bobby").each do |row|
    p
 end
# outputs [["1649,1650,1651", "Some Data1"], ["1643,3", "Some Data2"], ["23,4,5,6,7", "Some Data3"]]

db.results_as_hash = true #allows hashing row via column name

puts "Each ID as array of strings"
 db.execute("select * from bobby").each do |row|
    p row['pID'].split(",").collect { |i| "#{i}" }
 end

puts "Each ID as single string"
 db.execute("select * from bobby").each do |row|
    p row['pID'].split(",").collect { |i| "#{i}" }.join(", ")
 end

Here is the output:

C:\Users\Jim\Desktop>ruby sqlLiteExample.rb
Each ID as array of strings
["1649", "1650", "1651"]
["1643", "3"]
["23", "4", "5", "6", "7"]
Each ID as single string
"1649, 1650, 1651"
"1643, 3"
"23, 4, 5, 6, 7"

edit2: if you want your values to be a string of , for instance, ""1643", "3"", then you'll have to do the following:

x.map{|i| '"' + "#{i}" + '"' }.join(',')
# outputs "\"124\",\"5525\",\"23525\""