Hi i am a newbie to python.i am creating a small program which parses the load log file of a particular website and stores the valid data in particular feild of a database.but some of the feild has weired character like '推广频道'.(non-ascii character ' xe6').
i also tried to apply the solution of this question. Inserting unicode into sqlite? but this codec is unable to decode and providing UnicodeDecodeError: 'charmap' codec can't decode byte 0x81 in position 84: chara cter maps to
def db_log_entry(filename):
conn = sqlite3.connect('log.db')
c= conn.cursor()
""" creating log table
Table structure:
Name Type
id integer
identifier text
url text
user_ip text
datetime text
timezone_mod text
query_type text
status text
opt_id text
user_agent text
opt_ip text
opt_ip_port text
"""
c.execute(''' CREATE TABLE log
(id integer, identifier text, url text, user_ip text, datetime text, timezone_mod text, query_type text, query_url text, status text,
opt_id text, user_agent text, opt_ip text, opt_ip_port text)''')
f=codecs.open(filename,encoding='cp1252') ### opening file to read data
loop_count=0 # variable to keep record of successful loop iteration
id_count=0 # variable to be entered in id feild of log table
""" Reading each line of log file for performing iterative opertion on each line to parse valid data
make a entry in database """
for log_line in f:
loop_count= loop_count+1
id_count= id_count+1
list=[]
db_list=[]
(txt1,txt2)=log_line.split('HTTP/') #split the log_line in two parts txt1,txt2 for comparison with reg1,reg2
reg1= r'(\d{6}_\d{5})\s([\w.-]+)\s([\d.]+)\s-\s-\s\[(\d{2}/\w{3}/\d{4}:\d{2}:\d{2}:\d{2})\s([+-]?\d+)\]\s"(\w+)\s([\w.\-\/]+)'
reg2= r'[1.1"]?[1.0"]?\s(\d*)\s(\d*)([\s\"\-]*)([\w\.\%\/\s\;\(\:\+\)\?\S\"]+)"\s(\d{2,3}.\d{2,3}.\d{2,3}.\d{2,3})\:(\d+)'
print 'starting loop ',loop_count
match= re.search(reg1,txt1)
if match: # If regex match found between (reg1,txt1) than append the data in db_list
db_list.append(id_count)
db_list.append(match.group(1))
print match.group(1)
db_list.append(match.group(2))
print match.group(2)
db_list.append(match.group(3))
print match.group(3)
db_list.append(match.group(4))
print match.group(4)
db_list.append(match.group(5))
print match.group(5)
db_list.append(match.group(6))
print match.group(6)
db_list.append(match.group(7))
print match.group(7)
print 'yes 1'
else:
print 'match not found in reg1'
match= re.search(reg2,txt2) # If regex match found between (reg2,txt2) than append the data in db_list
if match:
db_list.append(match.group(1))
print match.group(1)
db_list.append(match.group(2))
print match.group(2)
#print match.group(3)
db_list.append(match.group(4))
print match.group(4)
db_list.append(match.group(5))
print match.group(5)
db_list.append(match.group(6))
print match.group(6)
print 'yes 2'
else:
print 'match not found in reg2'
print 'inserting value of',loop_count
c.execute('INSERT INTO log VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?)',db_list)
conn.commit()
print 'success...'
conn.commit()
def main():
db_log_entry('access_log.txt')
if __name__ == '__main__':
main()
