[Flask] Store Data in a SQLite-Database with Flask

[Andreas Dorfner]

Hello everyone,

I'm new to python and flask web development.
I have to realize a data logger using flask. By using another framework 
called 'pymodbus', I get some data from
an energy meter and save that data to a variable called 'crv_data' (see 
file attached). This part is working so far!

The second part of the project contains the storage of 'crv_data' to a 
SQLite-Database. For that, I'm using flask.
For creating a table with five columns, a file 'schema.sql' (also 
attached) is included.

If the file 'logger.py' is running, a database is produced with the 
desired layout and some data in 'crv_data' is available.
But there is no data included in the database.

I think there is an error in function 'add_entry()' (starting in line 72 
of logger.py file), because if I call the function
in the infinite loop (line 103), the program stops running.
Maybe I have to say that I using Beaglebone Black with running Debian on it.

Does anybody have an idea, why the program doesn't work?
Many thanks,
Andreas


---
Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft.
https://www.avast.com/antivirus
-------------- next part --------------
#!/usr/bin/python
#all the imports for pymodbus
import Adafruit_BBIO.UART as UART
import time, serial
from pymodbus.client.sync import ModbusSerialClient as ModbusClient
from pymodbus.transaction import ModbusRtuFramer

#all the imports for flask
import sqlite3
from flask import Flask, request, session, g, redirect, url_for, \
     abort, render_template, flash
from contextlib import closing
#----------------------------------------------------------------------------------------


#initialization of BBB's UART4 (device tree overlay and UART parameters)
UART.setup("UART4")
client = ModbusClient(method = 'rtu', framer = ModbusRtuFramer, port = '/dev/ttyO4', timeout = 1, baudrate = 19200, stopbits = 1, parity = 'E', bytesize = 8)
#----------------------------------------------------------------------------------------


# configuration for the database
DATABASE = '/root/project/logger.db'
DEBUG = True
SECRET_KEY = 'development key'
#USERNAME = 'admin'
#PASSWORD = 'default'
#----------------------------------------------------------------------------------------


# create my little application
app = Flask(__name__)
app.config.from_object(__name__)
#----------------------------------------------------------------------------------------


#connects to the specific database
def connect_db():
   return sqlite3.connect(app.config['DATABASE'])
#----------------------------------------------------------------------------------------


#initializes the database
def init_db():
   with closing(connect_db()) as db:
        with app.open_resource('schema.sql', mode='r') as f:
            db.cursor().executescript(f.read())
        db.commit()
#----------------------------------------------------------------------------------------


#open the database before each request
@app.before_request
def before_request():
    g.db = connect_db()
#----------------------------------------------------------------------------------------


#shut down the database afterwards
@app.teardown_request
def teardown_request(exception):
    db = getattr(g, 'db', None)
    if db is not None:
        db.close()
#----------------------------------------------------------------------------------------


#This view lets the user add new entries if he is logged in. There is a check 
#if the user is logged in as well.
#Update: logged in/out function has been removed
@app.route('/add', methods=['POST'])
def add_entry():
#    if not session.get('logged_in'):
#        abort(401)
    g.db.execute('insert into entries (URMS1, IRMS1, PRMS1, QRMS1, cosqhi1) values (?,?,?,?,?)',
                 [request.form['rcv_data.register[0]'], request.form['rcv_data.register[1]'], request.form['rcv_data.register[2]'], request_form['rcv_data.register[3]'], request_form['rcv_data.register[4]']])
    g.db.commit()
    flash('New entry was successfully posted')
#    return redirect(url_for('show_entries'))
#---------------------------------------------------------------------------




#create the database
init_db()

#energy meter needs about 15 sec for initialization
time.sleep(20)

#infinite loop
while 1:

	val = client.connect()

	if not val:
        	print "Error in Client Connection"

	#store data to rcv_data (array[5])
	rcv_data = client.read_holding_registers(35, count = 5, unit = 1)

	#store the data in the database
	add_entry()

	#print the stored data to screen
	print("Registers are")
	for i in range (5):     #range (17) to show all the usefull data
		print(rcv_data.registers[i])
		
	client.close()
	
	#data is updated every 10s
	time.sleep(10)
-------------- next part --------------
drop table if exists entries;
create table entries (
  id integer primary key autoincrement,
  URMS1 integer not null,
  IRMS1 integer not null,
  PRMS1 integer not null,
  QRMS1 integer not null,
  cosphi1 integer not null
);