from log with regex extracted values fail correct insertion into sqlite table

[killerpopiller]

Problem with storing extracted values in SQLite - How to adress or index list of values for proper insertion into table.

The Regex works fine:

input logging data looks like:

0.0.0(06026104)
1.6.1(0.1501)(1011111000)
1.6.1*32(0.1446)(1010190800)
1.8.1(02484.825)
1.8.1*32(02449.574)

correct regex output:

06026104
0.1501
02484.796

with open("usage.log") as fp:
	  for line in fp:
		match = re.match(r'(0\.0\.0|1\.6\.1|1\.8\.1)\(([0-9\.]+)', line)
		if not match: continue
		version, value = match.groups()
        mylist.append(value)
    for item in mylist:
	   t = (item)
       cursor.execute('INSERT INTO energielog(sernr) values (?)', [t])
	   cursor.execute('INSERT INTO energielog(peak) values (?)', [t])
	   cursor.execute('INSERT INTO energielog(kwh) values (?)', [t])

How can I achieve the proper line by line read into SQLite?

Now each line gets fed into table wrong: 06026104 is in line1 row1 and line2 row2 and line3 row3. Second value is only in line4 row1 and line5 row2 so forth..

For example, if I let

print t[0:3]

, the output is:

060
0.01
024

did it make rows out of it? How can I feed value 1 (06026104) into table row "sernr" and value2 (0.1501) into table row "peak" ..

[d5e5]

When you talk about the names of your rows you confuse me. Do you mean you have a table named energielog having three columns named sernr, peak and kwh?

[killerpopiller]

When you talk about the names of your rows you confuse me. Do you mean you have a table named energielog having three columns named sernr, peak and kwh?

yes, this is correct. plus ROWID as an INTEGER PRIMARY KEY

with open("verbrauch.log") as fp:
	for line in fp:
		match = re.search(r'(0\.0\.0|1\.6\.1|1\.8\.1)\(([0-9\.]+)', line)
		if match: 
			_,value = match.groups() 
			extrakt.append(value)
cursor.execute('INSERT INTO energielog values (?,?,?,?)', extrakt)

I am struggeling with the sqlite insertion of the extracted values in the list named extrakt into the table with 4 columns.

[d5e5]

Your example has only enough data for inserting one row.

#!/usr/bin/env python
#python 2
import re
import sqlite3

conn = sqlite3.connect('example')
c = conn.cursor()

mylist = []
with open("usage.log") as fp:
      for line in fp:
            match = re.match(r'(0\.0\.0|1\.6\.1|1\.8\.1)\(([0-9\.]+)', line)
            if not match: continue
            version, value = match.groups()
            mylist.append(value)

#Execute the cursor   
c.execute('INSERT INTO energielog (sernr, peak, kwh) VALUES (?, ?, ?)', mylist)

# Save (commit) the changes
conn.commit()

#Retrieve and display all rows from your table
c.execute('select * from energielog order by sernr')
for row in c:
    print row

# Close the cursor
c.close()

Running the above gives the following output:

(u'06026104', u'0.1501', u'02484.825')

Note: I don't specify a value for ROWID because I assume it will autoincrement.

[killerpopiller]

You Sir are true scholar, THANK YOU very much. I am so glad, I was stuck with this problem.
Perfect.

I would like to ask you if the full script is working, since I can't test serial connection due to bad weather at the moment:

#!/usr/bin/env python
# -*- coding: iso-8859-1 -*- 

import serial
import time
import re
import sqlite3

connection = sqlite3.connect(w"/var/httpdocs/dabaPyTest.s3db")
cursor = connection.cursor()
mylist = []

# open serial USB
ser = serial.Serial()
ser.baudrate = 300
ser.port = /dev/ttyUSB0    
ser.timeout = 2
ser.parity = serial.PARITY_EVEN
ser.stopbits = serial.STOPBITS_ONE
ser.bytesize = serial.SEVENBITS
ser.open()

# initiating seriell request
ser.write("/?!")
time.sleep(1.0)                    #1ms
ser.write("\r")                    #CR LF

#device should answer

print ser.readlines(eol='!') = fp   
#with open("verbrauch.log") as fp:
for line in fp:
		match = re.search(r'(0\.0\.0|1\.6\.1|1\.8\.1)\(([0-9\.]+)', line)
		if match: 
			version,value = match.groups() 
			extrakt.append(value)
#print extrakt
cursor.execute('INSERT INTO energielog (sernr, peak, kwh) values (?, ?, ?)', extrakt)

ser.close()
connection.close()
cursor.close()