How to insert multiple rows in SQLite Dbase

[afandi]

Hi,

Generally, it involves SQL statement such as
follow

INSERT INTO <tablename>(fie ld1,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?optio n=com_content&t ask=view&id=55& Itemid=19
HTTP/1.1
ErrorCode: 200
Bytes: 6458
Referel:http://www.joomla.org/index.php?
option=com_cont ent&task=view&i d=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?optio n=com_content&t ask=view&id=55& Itemid=19
HTTP/1.1
ErrorCode: 200
Bytes: 6458
Referel:http://www.joomla.org/index.php?
option=com_cont ent&task=view&i d=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

[=?ISO-8859-15?Q?Gerhard_H=E4ring?=]

Re: How to insert multiple rows in SQLite Dbase

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

[afandi]

Re: How to insert multiple rows in SQLite Dbase

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?

[Gabriel Genellina]

Re: How to insert multiple rows in SQLite Dbase

En Mon, 31 Mar 2008 11:22:40 -0300, afandi <afandimscit@gm ail.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.executeman y("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

[Miki]

Re: How to insert multiple rows in SQLite Dbase

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 <miki.tebeka@gm ail.com>

http://pythonwise.blogspot.com

[afandi]

Re: How to insert multiple rows in SQLite Dbase

On Apr 1, 12:22 am, afandi <afandims...@gm ail.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