Create sqlite database from blacklists repo to save historical data

create_db.py

import sqlite3
import requests
from datetime import datetime
import os

# SQLite setup
DB_PATH = "blacklisted.db"

def setup_db():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    c.execute('''CREATE TABLE IF NOT EXISTS domains
                 (fqdn TEXT, timestamp TEXT)''')
    conn.commit()
    conn.close()

# Fetch the text file
def fetch_blacklist_txt():
    URL = "https://get.domainsblacklists.com/blacklist.txt"
    response = requests.get(URL)
    
    with open("blacklist.txt", "w") as file:
        file.write(response.text)

# Insert data into SQLite
def insert_data():
    conn = sqlite3.connect(DB_PATH)
    c = conn.cursor()
    
    with open("blacklist.txt", "r") as file:
        for line in file:
            fqdn = line.strip()
            timestamp = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
            c.execute("INSERT INTO domains (fqdn, timestamp) VALUES (?, ?)", (fqdn, timestamp))
    
    conn.commit()
    conn.close()

# Main function to execute all tasks
def main():
    setup_db()
    fetch_blacklist_txt()
    insert_data()

if __name__ == "__main__":
    main()