By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,676 Members | 2,249 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,676 IT Pros & Developers. It's quick & easy.

How to insert multiple rows in SQLite Dbase

P: n/a
Hi,

Generally, it involves SQL statement such as
follow

INSERT INTO <tablename>(field1,field2,.......fieldn) VALUES
('abc','def'...........)

If I have data taken from Apache Server Log,let say 100 lines which
is printed output of 8 fields such
as:

data 1
IP: 61.5.65.101
Date: 26/Sep/2007
Time: 20:43:25
GMT: +0900
Requestt: GET /index.php?option=com_content&task=view&id=55&Itemi d=19
HTTP/1.1
ErrorCode: 200
Bytes: 6458
Referel:http://www.joomla.org/index.php?
option=com_content&task=view&id=35&Itemid=19
Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.4)
Gecko/20070515 Firefox/2.0.0.4

data 2
IP: 21.5.65.101
Date: 26/Sep/2007
Time: 20:43:25
GMT: +0900
Requestt: GET /index.php?option=com_content&task=view&id=55&Itemi d=19
HTTP/1.1
ErrorCode: 200
Bytes: 6458
Referel:http://www.joomla.org/index.php?
option=com_content&task=view&id=35&Itemid=19
Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.4)
Gecko/20070515 Firefox/2.0.0.4
..
..
..
..
until the 100 data

How toI insert into SQLite database? by using SQL statement.TQ
Mar 28 '08 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Gabriel Genellina wrote:
[...]
and execute:
cur.executemany("insert into log (IP, EntryDate, Requestt, ErrorCode)
values (:ip, :date, :request, :errorcode)", values)
It's probably worth mentioning that pysqlite's executemany() accepts
anything iterable for its parameter. So you don't need to build a list
beforehand to enjoy the performance boost of executemany().

The deluxe version with generators could look like this:

def parse_logfile():
logf = open(...)
for line in logf:
if ...:
row = (value1, value2, value3)
yield row
logf.close()

....

cur.executemany("insert into ... values (c1, c2, c3)", parse_logfile())

-- Gerhard

PS: pysqlite internally has a statement cache since verson 2.2, so
multiple execute() calls are almost as fast as executemany().
Mar 29 '08 #2

P: n/a
On Mar 30, 4:46 am, Gerhard Häring <g...@ghaering.dewrote:
Gabriel Genellina wrote:
[...]
and execute:
cur.executemany("insert into log (IP, EntryDate, Requestt, ErrorCode)
values (:ip, :date, :request, :errorcode)", values)

It's probably worth mentioning that pysqlite's executemany() accepts
anything iterable for its parameter. So you don't need to build a list
beforehand to enjoy the performance boost of executemany().

The deluxe version with generators could look like this:

def parse_logfile():
logf = open(...)
for line in logf:
if ...:
row = (value1, value2, value3)
yield row
logf.close()

...

cur.executemany("insert into ... values (c1, c2, c3)", parse_logfile())

-- Gerhard

PS: pysqlite internally has a statement cache since verson 2.2, so
multiple execute() calls are almost as fast as executemany().
Thanks regards to your suggestion, but I don't understand why we have
to put the IF statement?
Mar 31 '08 #3

P: n/a
En Mon, 31 Mar 2008 11:22:40 -0300, afandi <af*********@gmail.com>
escribió:
On Mar 30, 4:46 am, Gerhard Häring <g...@ghaering.dewrote:
>>
The deluxe version with generators could look like this:

def parse_logfile():
logf = open(...)
for line in logf:
if ...:
row = (value1, value2, value3)
yield row
logf.close()

...

cur.executemany("insert into ... values (c1, c2, c3)", parse_logfile())

Thanks regards to your suggestion, but I don't understand why we have
to put the IF statement?
Which if statement? The if inside parse_logfile quoted above is just an
example, it's not essential.

--
Gabriel Genellina

Mar 31 '08 #4

P: n/a
Thanks regards to your suggestion, but I don't understand why we have
to put the IF statement?
It's just an example, one possible implementation could be:
def parse_data(data):
mapping = {}
for line in data.splitlines():
if not line.strip():
continue
key, value = line.split(":", 1)
mapping[key] = value

return mapping

HTH,
--
Miki <mi*********@gmail.com>
http://pythonwise.blogspot.com
Apr 1 '08 #5

P: n/a
On Apr 1, 12:22 am, afandi <afandims...@gmail.comwrote:
On Mar 30, 4:46 am, Gerhard Häring <g...@ghaering.dewrote:
Gabriel Genellina wrote:
[...]
and execute:
cur.executemany("insert into log (IP, EntryDate, Requestt, ErrorCode)
values (:ip, :date, :request, :errorcode)", values)
It's probably worth mentioning that pysqlite's executemany() accepts
anything iterable for its parameter. So you don't need to build a list
beforehand to enjoy the performance boost of executemany().
The deluxe version with generators could look like this:
def parse_logfile():
logf = open(...)
for line in logf:
if ...:
row = (value1, value2, value3)
yield row
logf.close()
...
cur.executemany("insert into ... values (c1, c2, c3)", parse_logfile())
-- Gerhard
PS: pysqlite internally has a statement cache since verson 2.2, so
multipleexecute() calls are almost as fast as executemany().

Thanks regards to your suggestion, but I don't understand why we have
to put the IF statement?
I have the solution.Thanks
split it using REgex to [] [] []
parse to Database
Jun 27 '08 #6

This discussion thread is closed

Replies have been disabled for this discussion.