How to insert multiple rows in SQLite Dbase

[afandi]

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&Itemid=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&Itemid=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

 

[Gabriel Genellina]

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&Itemid=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
How toI insert into SQLite database? by using SQL statement.TQ

sqlite3 allows for named parameters; see
http://docs.python.org/lib/sqlite3-Cursor-Objects.html

You should build a list of dictionaries, with a dictionary per log entry,
and call the executemany cursor method. Assuming you have a table created
like this:

create table log (
IP text,
EntryDate timestamp,
Requestt text,
ErrorCode integer )

you should build a list like this:
values = [
{'ip': '61.5.65.101',
'date': a datetime object combining all parts,
'request': "GET /index.php?op...",
'errorcode': 200,
},
{'ip': '21.5.65.101',
'date': a datetime object,
'request': "...",
'errorcode': 200,
},
...
]

and execute:
cur.executemany("insert into log (IP, EntryDate, Requestt, ErrorCode)
values ip, :date, :request, :errorcode)", values)

 

[Gerhard Häring]

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

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

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.

 

[Miki]

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,

 

[afandi]

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