inserting into sqlite3

[Derek Smith]

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

 

[steve]

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

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:atabase::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:atabase.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.

 

[Derek Smith]

# 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:atabase.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:atabase.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:in
`send'
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:in
`new'
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: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

 

[Heesob Park]

Hi,

# Load all required gems
require "rubygems"
require "Amalgalite"

fn=3D"tester.sq3"
FileUtils::rm(fn) if File.exist?(fn) =C2=A0# start with a clean slate
db=3DAmalgalite:atabase.new(fn)
db.execute("create table test ( colone, coltwo, colthree, colfour )")
db.commit
insert_sql=3D"insert into test values (?,?,?,?)"
stmt=3Ddb.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=3D1;

mailog =C2=A0=3D File.read("/home/derek/Desktop/maillog")
mailog2 =3D "/home/derek/Desktop/maillog"
runlog =C2=A0=3D "/usr/local/vrep/logs/mail_log_miner.log"
db =3D
Amalgalite:atabase.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|
=C2=A0 =C2=A0if mlog.flock(File::LOCK_SH) =3D=3D 0
=C2=A0 =C2=A0 =C2=A0 =C2=A0#mlog.each do |line|
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0insert_sql =3D "insert into mail= log values '#{mailog}'"
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0stmt=3Ddb.prepare(insert_sql)
=C2=A0 =C2=A0 =C2=A0 =C2=A0#end ### END DO ###
=C2=A0 =C2=A0else
=C2=A0 =C2=A0 =C2=A0 =C2=A0string =3D 'LOCK_SH was not obtained on /var/l= og/maillog!'
=C2=A0 =C2=A0 =C2=A0 =C2=A0File.open(runlog, 'a') { |rlog| rlog.puts Date= today.to_s,
string }
=C2=A0 =C2=A0end ### END IF ###

=C2=A0 =C2=A0mlog.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...120d20ddd5a4616169c5d0b9f4b9cdb97d6465e929690c7561159010ad410590= e405ad2951adad29c0ad40ed75f004c07175d971042d152581...
(Amalgalite::SQLi 'Jul 14 21:09:39 test postfix/smtp[39630]: 926B34608F:
to=3D<[email protected]>, relay=3Dnone, delay=3D38352,
delays=3D38322/0.02/30/0, dsn=3D4.4.1, status=3Ddeferred (connect to
us.panasoni.com[216.8.179.24]:25: Operation timed out)
'": syntax error
=C2=A0from
/home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/statement= rb:33:in

`send'
=C2=A0from
/home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/statement= rb:33:in
`initialize'
=C2=A0from
/home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/database.= rb:264:in
`new'
=C2=A0from
/home/derek/.gem/ruby/1.8/gems/amalgalite-0.10.1/lib/amalgalite/database.=

rb:264:in
`prepare'
=C2=A0from mail_log_miner.rb:22
=C2=A0from mail_log_miner.rb:18:in `open'
=C2=A0from 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. =C2=A0This 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=3D1

mailog =3D File.read("/home/derek/Desktop/maillog")
mailog2 =3D "/home/derek/Desktop/maillog"
runlog =3D "/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 NU=
LL \
)")
db.commit

insert_sql =3D "insert into maillog values (?,?,?,?)"
stmt =3D db.prepare(insert_sql)
File.open(mailog2, 'r+') do |mlog|
if mlog.flock(File::LOCK_SH) =3D=3D 0
mlog.each do |line|
stmt.execute(*line.chomp.scan(/^(.{15})\s+(\S+)\s+(\S+)\s+(.+)$/)=
first)
db.commit
end ### END DO ###
else
string =3D '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 Heesob