PiHole ETL v2

#!/usr/bin/python3
# -*- coding: utf-8 -*-

import sqlite3
from sqlite3 import Error
import mysql.connector as mariadb
import platform
import time

exportLimit = 10000
moreToDo = 1

def create_connection(db_file):
    """ create a database connection to the SQLite database
        specified by the db_file
    :param db_file: database file
    :return: Connection object or None
    """
    try:
        conn = sqlite3.connect(db_file)
        conn.execute("PRAGMA read_uncommitted = true;");
        return conn
    except Error as e:
        print(e)
    return None

def create_mariaConnection():
    try:
        mysqlconn = mariadb.connect(user='pi', password='sabaac', database='pihole', host='castaway-dock.local')
        return mysqlconn
    except Error as e:
        print(e)
    return None


def select_all_tasks(mariaConn):
    """
    Query all rows in the tasks table
    :param conn: the Connection object
    :return:
    """
    lastTimeStamp = 0
    lastId = 0
    hostname = platform.node()
    mCur = mariaConn.cursor(prepared=True)
    mCur.execute("SELECT ifnull(value,0) FROM ftl WHERE hostname = ? AND id = 1",(hostname,))
    ret = mCur.fetchone()
    if ret!=None:
        lastTimeStamp = ret[0]
    mCur.execute("SELECT ifnull(value,0) FROM ftl WHERE hostname = ? AND id = 2", (hostname,))
    ret = mCur.fetchone()
    if ret!=None:
        lastId = ret[0]
    #print (lastId)
    return lastTimeStamp,lastId


def processData(conn, mariaConn, lastTimeStamp, lastId):
    """
    Get the current timestamp and ID
    Get the data between last and current keys
    """
    global moreToDo
    endId = 0
    endTimeStamp = 0
    sourceID = 0
    timestamp = 0
    ctr = 0
    hostname = platform.node()

    mCur = mariaConn.cursor(prepared=True)
    stmt = "INSERT INTO queries (hostname,sourceid,timestamp,type,status,domain,client,forward) VALUES (?,?,?,?,?,?,?,?)"

    cur = conn.cursor()
    cur.execute("SELECT id, timestamp, type, status, domain, client, forward FROM queries where ID > ? LIMIT ?", (lastId,exportLimit))
    rows = cur.fetchall()

    for row in rows:
        ctr+=1
        sourceID = row[0]
        timestamp = row[1]
        type = row[2]
        status = row[3]
        domain = row[4]
        client = row[5]
        forward = row[6]
        #print(hostname,sourceID,timestamp,type,status,domain,client,forward)
        mCur.execute(stmt,(hostname,sourceID,timestamp,type,status,domain,client,forward))

    mariaConn.commit()
    endId = sourceID
    endTimeStamp = timestamp
    print ("Processed %s records, pausing for 5 seconds." % (ctr))
    if (endId - lastId) < exportLimit:
        moreToDo = 0

    return endTimeStamp, endId

def writeTimeStamps(mariaConn, endTimeStamp, endId):
    """
    Update the FTL Table rows with the ending timestamp and end id
    """
    if endId > 0:
        hostname = platform.node()
        mCur = mariaConn.cursor(prepared=True)
        stmt = "INSERT INTO ftl (hostname, id, value) VALUES (?,?,?) ON DUPLICATE KEY UPDATE VALUE=?;"

        mCur.execute(stmt,(hostname,1,endTimeStamp,endTimeStamp))
        mCur.execute(stmt,(hostname,2,endId,endId))
        mariaConn.commit()

    return None

def main():
    database = "/etc/pihole/pihole-FTL.db"
    lastTimeStamp = 0
    # create a database connection
    conn = create_connection(database)
    mariaConn = create_mariaConnection()
    with conn:
        print("Get last run TimeStamps.")
        lastKeys = select_all_tasks(mariaConn)
        print ("Last Processed ID = %s." % (lastKeys[1]))
        while moreToDo > 0:
            endKeys = processData(conn, mariaConn, lastKeys[0], lastKeys[1])
            writeTimeStamps(mariaConn, endKeys[0],endKeys[1])
            lastKeys = endKeys
            print ("More Work? %s!" % (moreToDo))
            if moreToDo > 0:
                time.sleep(5)
    writeTimeStamps(mariaConn, endKeys[0],endKeys[1])
    conn.close()
    mariaConn.close()
    print ("Complete")
if __name__ == '__main__':
    main()