CODEWITHSUNDEEP
rubysqlite
SamTalks
SamTalks

Reputation: 283

How to insert Ruby array items into SQLite?

I want to execute my INSERT strings within a Ruby file that has the following array:

names_arr = ["Jack", "Susan", "Peter"]

In the same Ruby file, I've required the SQLite3 gem and have this code:

names_arr.each do |each_name|
  db.execute('INSERT INTO users (name) VALUES ("#{each_name}");')
end

I am expecting this in my "users" table:

id  name
1   Jack
2   Susan
3   Peter

Instead, I'm getting this:

id  name
1   #{each_name}
2   #{each_name}
3   #{each_name}

It's clear I'm not interpolating properly, but I've tried a lot of different things and can't figure out where I'm wrong.

I am using SQLite3 and Ruby.

Upvotes: 1

Views: 1287

Answers (1)

mu is too short
mu is too short

Reputation: 434635

You have several problems in this code:

names_arr.each do |each_name|
  db.execute('INSERT INTO users (name) VALUES ("#{each_name}");')
end
  1. You're trying to use Ruby's string interpolation inside a single quoted string but string interpolation only works in double quoted strings (and heredocs, %Q{...} strings, regex literals, ...).
  2. Standard SQL uses single quotes for string literals, not double quotes. Some databases let you get away with double quotes but it is a really bad habit to get into.
  3. You shouldn't use string interpolation to build SQL anyway, you're not hacking PHP in 1999 so you should use placeholders and let the database library deal with the quoting.

The SQLite driver's execute understands ? placeholders so you can say this:

names_arr.each do |each_name|
  db.execute('INSERT INTO users (name) VALUES (?)', each_name)
end

Upvotes: 2

Related Questions