Inserting into sqlite3

Ruby
1

I have a text file, called maillog that I want to insert into a table.
My pseudo-code is

open mail file
connect to database => ‘require dbi’ here?
create table for maillog data
create primary key in table

for each line in mailfile
do
insert line from mailfile into rows
done

close file
close database

Am I far off?
Please advise with tips maybe and or actual code!
Thank you

2

Derek S. wrote:

insert line from mailfile into rows

done

close file
close database

Am I far off?
Please advise with tips maybe and or actual code!
Thank you

Not bad Derek. If you are only ever going to use sqlite3 then perhaps
the Amalgalite gem will suit your purposes.

  1. Specify a primary key at create table time, and it will be created
    for you.
  2. create an insert statement with parameter markers. Prepare it once
    and execute it multiple times.
  3. if you have a lot of rows to insert wrap your insert loop in a
    transaction. In amalgalite you start a transaction with
    db.transaction(Amalgalite::Database::TransactionBehavior::IMMEDIATE)
    and end with
    db.commit
    DBI should have some sort of equivalent.

Sample code?

Load all required gems

require “rubygems”
require “Amalgalite”

fn=“tester.sq3”
FileUtils::rm(fn) if File.exist?(fn) # start with a clean slate
db=Amalgalite::Database.new(fn)
db.execute(“create table test ( colone, coltwo, colthree, colfour )”)
db.commit
insert_sql=“insert into test values (?,?,?,?)”
stmt=db.prepare(insert_sql)
stmt.execute(%w{one two three four})
stmt.execute(%w(five six seven eight))
db.commit

Hope this helps

Steve.

3

steve wrote:

Derek S. wrote:

insert line from mailfile into rows

done

close file
close database

Am I far off?
Please advise with tips maybe and or actual code!
Thank you

Load all required gems

require “rubygems”
require “Amalgalite”

fn=“tester.sq3”
FileUtils::rm(fn) if File.exist?(fn) # start with a clean slate
db=Amalgalite::Database.new(fn)
db.execute(“create table test ( colone, coltwo, colthree, colfour )”)
db.commit
insert_sql=“insert into test values (?,?,?,?)”
stmt=db.prepare(insert_sql)
stmt.execute(%w{one two three four})
stmt.execute(%w(five six seven eight))
db.commit

Hope this helps

Steve.

It did help, but I am having issues still.

#!/usr/bin/env ruby

require ‘rubygems’;
require ‘amalgalite’;
require ‘date’;
$VERBOSE=1;

mailog = File.read("/home/derek/Desktop/maillog")
mailog2 = “/home/derek/Desktop/maillog”
runlog = “/usr/local/vrep/logs/mail_log_miner.log”
db =
Amalgalite::Database.new("/usr/local/vrep/repo/db/development.sqlite3")

db.execute(“create table maillog ( month datetime DEFAULT NULL NULL,
day integer DEFAULT NULL NULL, time datetime DEFAULT NULL NULL, host \
varchar(25) DEFAULT NULL NULL,
daemon varchar(25) DEFAULT NULL NULL, mailmsgs varchar(200) DEFAULT NULL
NULL \ )”)
db.commit

File.open(mailog2, ‘r+’) do |mlog|
if mlog.flock(File::LOCK_SH) == 0
#mlog.each do |line|
insert_sql = “insert into maillog values ‘#{mailog}’”
stmt=db.prepare(insert_sql)
#end ### END DO ###
else
string = ‘LOCK_SH was not obtained on /var/log/maillog!’
File.open(runlog, ‘a’) { |rlog| rlog.puts Date.today.to_s,
string }
end ### END IF ###

mlog.flock(File::LOCK_UN)

end ### END DO mailog ###

No matter how many lines the file is, I get this error.

ERROR
ruby mail_log_miner.rb
/home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/statement.rb:33:in
prepare': Failure to prepare statement insert into maillog values 'Jul 14 21:09:09 test postfix/smtp[39626]: A186A46072: host mx.dca.untd.com[64.136.44.37] refused to talk to me: 550 Access denied...120d20ddd5a4616169c5d0b9f4b9cdb97d6465e929690c7561159010ad410590e405ad2951adad29c0ad40ed75f004c07175d971042d152581... (Amalgalite::SQLi 'Jul 14 21:09:39 test postfix/smtp[39630]: 926B34608F: to=<[email protected]>, relay=none, delay=38352, delays=38322/0.02/30/0, dsn=4.4.1, status=deferred (connect to us.panasoni.com[216.8.179.24]:25: Operation timed out) '": syntax error from /home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/statement.rb:33:insend’
from
/home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/statement.rb:33:in
initialize' from /home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/database.rb:264:innew’
from
/home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/database.rb:264:in
prepare' from mail_log_miner.rb:22 from mail_log_miner.rb:18:inopen’
from mail_log_miner.rb:18
te3::Error)

Also, when I File.read it sucks it into memory all at once and comes out
as one line. This is probably not good for large files so will you
recommend a better alternative such as .each do… or is this OK?

Each line in the file begins with the Month abbreviated such as “Jul”
Thank you

4

Hi,

2009/8/7 Derek S. [email protected]:

Please advise with tips maybe and or actual code!
db.commit
It did help, but I am having issues still.
mailog2 = “/home/derek/Desktop/maillog”

  end ### END IF ###
prepare': Failure to prepare statement insert into maillog values 'Jul send’
 from mail_log_miner.rb:18:in `open’
 from mail_log_miner.rb:18
te3::Error)

Also, when I File.read it sucks it into memory all at once and comes out
as one line. Â This is probably not good for large files so will you
recommend a better alternative such as .each do… or is this OK?

Each line in the file begins with the Month abbreviated such as “Jul”
Thank you

Here is a working version for you:

require ‘rubygems’
require ‘amalgalite’
require ‘date’
$VERBOSE=1

mailog = File.read(“/home/derek/Desktop/maillog”)
mailog2 = “/home/derek/Desktop/maillog”
runlog = “/usr/local/vrep/logs/mail_log_miner.log”

db.execute(“create table maillog ( time datetime DEFAULT NULL NULL,
host varchar(25) DEFAULT NULL NULL,
daemon varchar(25) DEFAULT NULL NULL, mailmsgs varchar(200) DEFAULT NULL
NULL
)”)
db.commit

insert_sql = “insert into maillog values (?,?,?,?)”
stmt = db.prepare(insert_sql)
File.open(mailog2, ‘r+’) do |mlog|
if mlog.flock(File::LOCK_SH) == 0
mlog.each do |line|
stmt.execute(*line.chomp.scan(/^(.{15})\s+(\S+)\s+(\S+)\s+(.+)$/).first)
db.commit
end ### END DO ###
else
string = ‘LOCK_SH was not obtained on /var/log/maillog!’
File.open(runlog, ‘a’) { |rlog| rlog.puts Date.today.to_s,string
}
end ### END IF ###

mlog.flock(File::LOCK_UN)
end ### END DO mailog ###

db.execute(“select * from maillog”) do |row|
p row
end

Regards,

Park H.