You would need to add a column to raw_data to hold the status of whether a row has been processed or not. My example usues ack.
The results of the parsing would go into a different table. My example uses parsed_data, as that is the one you have mentioned.
You would need to get the indexes of the rows that were selected. My example uses myIndexColumn, as I don't know what the actual column is or where it appears in the column order.
Use the list of indexes to update the ack value to 1.
I recommend putting this as a project script, then call it from a Gateway Timer Script.
Example project script:
import re
def processSectorData():
db = 'MyDBConnection'
limit = 50
query = "SELECT * FROM raw_data WHERE ack != 1 LIMIT ?"
dataIn = system.db.runPrepQuery(query, [limit], db)
if len(dataIn) > 0:
ndxList = dataIn.getColumnAsList(0)
dataOut = []
for row in dataIn:
message = row['Message']
for item in message.split('\n\n'):
newRow = re.split(' was identified| at ', item)
newRow[0] = int(newRow[0].split(' ')[-1])
sector = newRow[1].split(' ')[-1]
if sector == 'sector':
newRow[1] = 'N/A'
else:
newRow[1]=sector
newRow[2] = system.date.parse(newRow[2], 'h:mm a, MMM DD, yyyy')
dataOut.append(newRow)
qmarks = ('(?,?,?),' * limit)[:-1]
query = 'INSERT INTO parsed_data (tag_id, sector, time_stamp) VALUES {}'.format(qmarks)
x = system.db.runPrepUpdate(query, dataOut, myDBConnection)
if x > 0:
indexList = dataIn.getColumnAsList(0)
query = 'UPDATE raw_data SET ack = 1 where myIndexColumn IN ({})'.format(','.join(['?']*limit))
system.db.runPrepUpdate(query, indexList, myDBConnection)
What isn't working? What have you tried as troubleshooting?
For instance, are you sure your script is firing? If you add logging (system.util.getLogger("myLogger").info("script firing")) -- is it actually executing?
Are any rows being added in any database tables? Are there any errors in the gateway logs?
The code I am using is unaltered from the post above mine, I just copied and pasted it to test. And I did post screen grabs, or maybe I'm misunderstanding what you mean by screengrab.
Here's the code, Edit: fixed the 'mydbconnection' part
import re
def processSectorData():
db = 'ATLAS_DB'
limit = 50
query = "SELECT * FROM raw_data WHERE ack != 1 LIMIT ?"
dataIn = system.db.runPrepQuery(query, [limit], db)
if len(dataIn) > 0:
ndxList = dataIn.getColumnAsList(0)
dataOut = []
for row in dataIn:
message = row('Message')
for item in message.split('\n\n'):
newRow = re.split(' was identified| at ', item)
newRow[0] = int(newRow[0].split(' ')[-1])
sector = newRow[1].split(' ')[-1]
if sector == 'sector':
newRow[1] = 'N/A'
else:
newRow[1]=sector
newRow[2] = system.date.parse(newRow[2], 'h:mm a, MMM DD, yyyy')
dataOut.append(newRow)
qmarks = ('(?,?,?),' * limit)[:-1]
query = 'INSERT INTO parsed_data (tag_id, sector, time_stamp) VALUES {}'.format(qmarks)
x = system.db.runPrepUpdate(query, dataOut, myDBConnection)
if x > 0:
indexList = dataIn.getColumnAsList(0)
query = 'UPDATE raw_data SET ack = 1 where myIndexColumn IN ({})'.format(','.join(['?']*limit))
system.db.runPrepUpdate(query, indexList, myDBConnection)
The script that is supposed to parse the data from the raw_data isn't working. It is executing because it is adding rows to the parsed_data table as shown in the screen shots, except that all the values of the rows are "null".
Edit: I forgot about checking the logs. There were some errors, in spelling and variable naming. It is working without errors now, but it is still not doing what it needs too. All table values are still 'null'.
You need to add the ack column to the table where your raw data actually is. I put raw_data in the script, because that was the table you mentioned earlier.
I don't know your data, or db configurations, so you will need to make it fit.
This will work if you only have a few rows of 'raw data', The more frequent the message is received and the number of tags is big, then you have to worry about affecting the performance of the client.
In defense of SQLite, it is a fantastic in-process database engine and a perfectly acceptable, even preferred, solution for applications that don't require high concurrency or the ability to handle very large amounts of data. It excels in the use cases it is designed for.
Specifically I like SQLite a lot for single user applications because:
I don't need to worry about a DB server process
The DB file format is stable and portable.
Queries don't rely on a mediating network connection.
That being said, I wouldn't recommend using SQLite for Ignition in most (but not necessarily all) cases.
Have you defined myLogger further up in the script? If not then this wont work.
You'll notice that in @PGriffith's example there are " around myLogger.
logger = system.util.getLogger("myLogger")
The only way I can see that rows are entered as null, is if dataOut is empty (doesn't seem likely I would expect an error, not a null entry) either way, do all of the messages have two newLines in them? for item in message.split('\n\n')
Exactly. Push it hard, it falls over. SCADA applications, even if started small, tend to grow dramatically once end-users discover how valuable the data is. I strongly recommend against ever using SQLite in Ignition.
