diff options
author | nico <nico@magicbroccoli.de> | 2019-07-21 22:46:25 +0200 |
---|---|---|
committer | nico <nico@magicbroccoli.de> | 2019-07-21 22:46:25 +0200 |
commit | c3685dc52c219a2ebace65f063fbf256d49fdc66 (patch) | |
tree | 0611a272422ddf1274a7d8658f24b46bd168fa90 /main.py | |
parent | 142c7db609a2b7e48105e6cbfd28c5385f83c705 (diff) |
missing create method0.1.1
+ add check method to prevent sqlite3.OperationalError tracebacks
+ add create method to create base schema if missing
thanks to @weiss for reporting this
Diffstat (limited to 'main.py')
-rwxr-xr-x | main.py | 28 |
1 files changed, 22 insertions, 6 deletions
@@ -31,6 +31,8 @@ class AbuseReport: def main(self): """main method guiding the actions to take""" + # run check method before each execution + self.check() if self.infile is None: # infile unset -> report top10 @@ -43,6 +45,23 @@ class AbuseReport: # close sqlite connection self.conn.close() + def check(self): + # check if the minimum requirements are met + table = ('table', 'spam') + master = self.conn.execute('''SELECT type, name from sqlite_master;''').fetchall() + + # if not run create method + if table not in master: + self.create() + + def create(self): + # open and execute base schema file + script = "/".join([self.path, "schema.sql"]) + with open(script) as file: + schema = file.read() + + self.conn.executescript(schema) + def egest(self): """egest method returning the database results""" # init result list @@ -74,12 +93,9 @@ class AbuseReport: else: # in any other case return top 10 view - if self.config.get_at("top10_view"): - result = self.conn.execute('''SELECT * FROM "top10"''').fetchall() - else: - result = self.conn.execute('''SELECT COUNT(*) AS messages, COUNT(DISTINCT user) AS bots, domain AS - domain FROM spam WHERE ts BETWEEN DATE('now','-14 days') AND DATE('now') GROUP BY domain - ORDER BY 1 DESC LIMIT 10;''').fetchall() + result = self.conn.execute('''SELECT COUNT(*) AS messages, COUNT(DISTINCT user) AS bots, domain AS + domain FROM spam WHERE ts BETWEEN DATE('now','-14 days') AND DATE('now') GROUP BY domain + ORDER BY 1 DESC LIMIT 10;''').fetchall() # tabelize data spam_table = tabulate.tabulate(result, headers=["messages", "bots", "domain", "first seen", "last seen"], |