| Message: 66335 |
 |
BY: Gavin Kistner (phrogz)
DATE: 2009-03-20 04:43
SUBJECT: RE: help with array to sqlite3 A) Oops, english should be text, not float. The beauty(?) of sqlite is that it doesn't really matter, though.
B) If you want reasonable performance for your 5,000 records, you should wrap all the inserts into a transaction. With Sequel, put:
DB.transaction do
...
end
around the File.foreach block.
BTW, I'm sure that Sequel (http://sequel.rubyforge.org/) is overkill for this simple case, but I happen to use it all the time instead of Active Record (which you'll likely use instead on Rails) and I didn't know off the top of my head what the sqlite3-ruby direct bindings look like.
In retrospect, seems rather rude to have used it at all in this forum. Untested, the code might look something like this:
db = SQLite3::Database.new( "test.db" )
make_tables = <<ENDSQL
CREATE TABLE parts_of_speech (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT
);
CREATE TABLE words (
id INTEGER PRIMARY KEY AUTOINCREMENT,
greek TEXT,
english TEXT,
part_of_speech_id INTEGER,
occurrences INTEGER
);
ENDSQL
db.execute_batch( make_tables );
db.transaction do
File.foreach( SOURCE_FILE ) do |line|
pieces = line.scan( /"([^"]+)"/ ).flatten
gr, en, part, hits = *pieces
if gr then
part_id = PART_ID_BY_NAME[ part ]
unless part_id
db.execute( "insert into parts_of_speech (name) values (?)", part )
part_id = db.last_insert_row_id
PART_ID_BY_NAME[ part ] = part_id
end
db.execute( "insert into words values (?,?,?,?)", gr, en, part_id, hits.to_i )
end
end
end | |
Start New Thread
help with array to sqlite3