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" ..

Recommended Answers

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?

Jump to Post

All 4 Replies

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?

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.

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.

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()
Be a part of the DaniWeb community

We're a friendly, industry-focused community of 1.20 million developers, IT pros, digital marketers, and technology enthusiasts learning and sharing knowledge.