129 lines
3.0 KiB
Python
Executable File
129 lines
3.0 KiB
Python
Executable File
#!/usr/bin/python
|
|
|
|
from pysqlite2 import dbapi2 as sqlite
|
|
from sys import exit
|
|
import datetime
|
|
|
|
def connectdb (dbfile):
|
|
"""
|
|
Create connection to database
|
|
return DB Object
|
|
user gets information if failure
|
|
"""
|
|
try:
|
|
db = sqlite.connect (dbfile)
|
|
return db
|
|
except:
|
|
print ("Couldnt connct to DB")
|
|
exit (1)
|
|
|
|
|
|
class DB:
|
|
def __init__ (self, dbfile):
|
|
self.db = connectdb (dbfile)
|
|
|
|
|
|
def executesql (self, dbstring, arguments = ()):
|
|
"""
|
|
exectutes sql in dbstring
|
|
and returns cursor
|
|
"""
|
|
try:
|
|
cursor = self.db.cursor ()
|
|
cursor.execute (dbstring, arguments)
|
|
except sqlite.OperationalError:
|
|
print ("SQL exectution failed. Caused by: " + dbstring)
|
|
raise Warning
|
|
except sqlite.IntegrityError:
|
|
print ("Double entry. Caused by: " + dbstring)
|
|
raise Warning
|
|
except sqlite.ProgrammingError:
|
|
print ("Invalid SQL: " + dbstring)
|
|
raise Warning
|
|
except sqlite.Warning:
|
|
pass
|
|
|
|
self.db.commit ()
|
|
return cursor
|
|
|
|
|
|
def geterrorlog (self):
|
|
"""
|
|
returns all errorlogs
|
|
"""
|
|
|
|
try:
|
|
cursor = self.executesql ("SELECT timestamp, errornr, count, action FROM errorlog")
|
|
except:
|
|
print ("couldn't get Errors")
|
|
raise Warning
|
|
|
|
return cursor.fetchall ()
|
|
|
|
|
|
|
|
def getallmsglogs (self):
|
|
"""
|
|
returns all msglog entrys
|
|
"""
|
|
|
|
try:
|
|
cursor = self.executesql ("SELECT m.id, c.client, l.location, i.iopoint, ms.msg, m.timestamp FROM msglog m \
|
|
LEFT JOIN clients c ON (c.ID = m.client) \
|
|
LEFT JOIN locations l ON (l.id = m.location) \
|
|
LEFT JOIN iopoints i ON (i.id = m.iopoint) \
|
|
LEFT JOIN messages ms ON (ms.id = m.msg)")
|
|
except:
|
|
print ("couldn't get msglog")
|
|
raise Warning
|
|
|
|
return cursor.fetchall ()
|
|
|
|
|
|
|
|
def logmsg (self, timestamp, errornr, count, action):
|
|
"""
|
|
save errormsg
|
|
"""
|
|
actions = { 1 : 'Entstanden',
|
|
2 : 'Verschwunden nach SW Quittierung',
|
|
3 : 'Verschwunden ohne Quittierung',
|
|
4 : 'HW Quittierung',
|
|
5 : 'SW Quittierung',
|
|
6 : 'Auf Wartung gesetzt',
|
|
7 : 'Auf Wartung gesetzt',
|
|
8 : 'aus Wartung rueckgesetzt',
|
|
9 : 'Behoben'
|
|
}
|
|
atext = 'undefiniert'
|
|
if action in actions:
|
|
atext = actions[action]
|
|
try:
|
|
cursor = self.executesql ("INSERT INTO errorlog VALUES ('%s', %d, %d, '%s')" %(timestamp, errornr, count, atext))
|
|
except:
|
|
print ("couldn't insert logmsg")
|
|
raise Warning
|
|
|
|
|
|
def logvalue (self, client, location, iopoint, value, dimension, timestamp):
|
|
"""
|
|
saves a valuelog
|
|
"""
|
|
|
|
try:
|
|
cursor = self.executesql ("INSERT INTO valuelog VALUES (null, %d, %d, %d, %d, %d, '%s')" %(client, location, iopoint, value, dimension, timestamp))
|
|
except:
|
|
print ("couldn't insert valuelog")
|
|
raise Warning
|
|
|
|
|
|
if __name__ == "__main__":
|
|
conn = DB ("test.db3")
|
|
|
|
print ("Error Logs")
|
|
print ("-------------")
|
|
for i in conn.geterrorlog ():
|
|
print (i)
|
|
|
|
#conn.logvalue ( client = 1, location = 2, iopoint = 3, value = 4623, dimension = 1, timestamp = datetime.datetime(2007, 7, 6, 2, 2))
|