Inserting SQLite rows via Ruby iteration

Question

Inserting 5 rows of 4-column data into an existing SQLite db, and I get these errors...

/Users/Sam/.rvm/gems/ruby-2.0.0-p247/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:91:in `initialize': near "tagline": syntax error (SQLite3::SQLException)
        from /Users/Sam/.rvm/gems/ruby-2.0.0-p247/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:91:in `new'
        from /Users/Sam/.rvm/gems/ruby-2.0.0-p247/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:91:in `prepare'
        from /Users/Sam/.rvm/gems/ruby-2.0.0-p247/gems/sqlite3-1.3.8/lib/sqlite3/database.rb:134:in `execute'
        from insert_code_sam.rb:60:in `block in <main>'
        from insert_code_sam.rb:59:in `times'
        from insert_code_sam.rb:59:in `<main>'

Here are last lines (59-61) from my .rb file:

    5.times do
      db.execute('INSERT INTO index1 (name tagline blurb photo_url) 
        VALUES (?, ?, ?, ?)', ["name", "tagline", "blurb", "photo"])
    end

Any suggestions?

Are you opposed to using the wonderful, convenient [Sequel](http://sequel.rubyforge.org) library? It makes so many SQL-related tasks a snap. — Phrogz, Oct 07 '13 at 03:12
@Phrogz thanks for the link. I just skimmed the README and it looks helpful; I'm on a deadline now so will peruse later. I'm also a SQL noob so want fundamentals under my belt. Would using Sequel take away from this endeavor you think? — SamTalks, Oct 07 '13 at 03:28

score 4 · Accepted Answer · answered Oct 07 '13 at 03:05

4

You just have a small syntax error in your SQL. The column list for an INSERT should be comma delimited:

INSERT INTO index1 (name, tagline, blurb, photo_url) ...
                        ^        ^      ^

so your Ruby should look like this:

5.times do
  db.execute('INSERT INTO index1 (name, tagline, blurb, photo_url) VALUES (?, ?, ?, ?)', ["name", "tagline", "blurb", "photo"])
end

See the "column-name" loop in the SQLite INSERT syntax diagram:

enter image description here

answered Oct 07 '13 at 03:05

mu is too short

426,620
70
833
800

1

+1 for adding their railroad diagrams. There are many great things about SQLite, and many sub-optimal things, but those diagrams are just plain awesome. – Phrogz Oct 07 '13 at 03:12
Perfect! It works. Thanks again @mu! @Phrogz diagram is awesome indeed. Found the original googling sqlite insert syntax. – SamTalks Oct 07 '13 at 03:19
1

@Phrogz: I'm pretty happy with the SQLite documentation overall, the diagrams are particularly useful syntax summaries. – mu is too short Oct 07 '13 at 04:40
FYI, I just learned that another term for placeholders mu is using is "sanitation replacement." – SamTalks Oct 10 '13 at 17:17

Inserting SQLite rows via Ruby iteration

1 Answers1