# Inserting into sqlite3

**URL:** <https://www.ruby-forum.com/t/inserting-into-sqlite3/172933>\
**Category:** Ruby\
**Created:** [July 30, 2009, 4:31am UTC](https://www.ruby-forum.com/t/inserting-into-sqlite3/172933 "2009-07-30T04:31:32Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![bloodniece](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/47e85d/32.png) [@bloodniece](https://www.ruby-forum.com/u/bloodniece)\
**Post date:** [July 30, 2009, 4:31am UTC](https://www.ruby-forum.com/t/inserting-into-sqlite3/172933/1 "2009-07-30T04:31:32Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![bloodniece](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/47e85d/32.png) [@bloodniece](https://www.ruby-forum.com/u/bloodniece)\
**Post date:** [July 30, 2009, 4:55am UTC](https://www.ruby-forum.com/t/inserting-into-sqlite3/172933/2 "2009-07-30T04:55:49Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![bloodniece](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/47e85d/32.png) [@bloodniece](https://www.ruby-forum.com/u/bloodniece)\
**Post date:** [August 7, 2009, 4:11am UTC](https://www.ruby-forum.com/t/inserting-into-sqlite3/172933/3 "2009-08-07T04:11:56Z")

</div>

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=<removed_email_address@domain.invalid>, 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

---

<div class="post-metadata">

**Author:** ![bloodniece](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/b/47e85d/32.png) [@bloodniece](https://www.ruby-forum.com/u/bloodniece)\
**Post date:** [August 7, 2009, 5:04am UTC](https://www.ruby-forum.com/t/inserting-into-sqlite3/172933/4 "2009-08-07T05:04:05Z")

</div>

Hi,

2009/8/7 Derek S. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid):

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