Sqlite3 `step': constraint failed (SQLite3::ConstraintException)

Question

I'm trying to convert some JSON data and get it inserted into a sqlite3 db using Ruby. The JSON is coming from githubarchive.com I followed the advice on this question: Escaping Strings For Ruby SQLite Insert and my code looks like this:

Yajl::Parser.parse(open(input).read) do |event|
r = CSV::Row.new(headers, [])
flatmap({}, event).each do |k,v|
  v = (Time.parse(v).utc.strftime('%Y-%m-%d %T') rescue '') if k =~ /_at$/
  if r.include? k
    r[k] = v
  else

   puts "Unknown field: #{k}, value: #{v}"
        end
    end 
#   tmp << r.to_s
db = SQLite3::Database.open( "../github.sqlite" )
val = (['?'] * 186).join(',')
ins = db.prepare("insert into Sheet1_copy values (#{val})")
ins.execute(r.to_s)

The top part of this is taken from their git project. Now, when I try to run this with any of the data, I get

gems/sqlite3-1.3.5/lib/sqlite3/statement.rb:67:in `step': constraint failed (SQLite3::ConstraintException)

Any thoughts? Thanks!

Why are you using CSV::Row at all? Why not just use an Array? — mu is too short, Apr 20 '12 at 01:49
working with code from here and trying to repurpose it: https://github.com/igrigorik/githubarchive.org/blob/master/bigquery/transform.rb So I need it to go from JSON to SQlite — pash, Apr 20 '12 at 01:53
Then go from JSON straight to SQLite and leave the CSV stuff out of it, it is just causing trouble. — mu is too short, Apr 20 '12 at 01:54
@muistooshort I tried that, need a little guidance: `js = File.open(input, "r") Yajl::Parser.parse(js) do |event| # print event db = SQLite3::Database.open( "../github.sqlite" ) val = (['?'] * 186).join(',') ins = db.prepare("insert into Sheet1_copy values (#{val})") ins.execute(event) end` — pash, Apr 20 '12 at 01:56
I'd guess that you'll have to convert the Ruby booleans to 1 and 0 before you try to insert them into the database. — mu is too short, Apr 20 '12 at 02:39
That's why I was trying to use what's already been done. If we look back at the CSV row.to_s, it's a flat string which should be able to be inserted in the db. It's a string similar to "val, val, val,...". I'm still very confused about what my original error was. — pash, Apr 20 '12 at 02:51

score 0 · Answer 1 · answered Apr 20 '12 at 06:36

You'll need a few more things in place to load it correctly.. Here's a working copy: https://gist.github.com/2426614

Note that I'm not using prepared statements, since that would take a bit more gymnastics to fill in empty rows across all the different types of events. Having said that, it runs pretty darn fast as is.

Sqlite3 `step': constraint failed (SQLite3::ConstraintException)

1 Answers1