Populate MySQL database with AIPscan data for use as Grafana datasource

mysql_aipfiles.py

import pymysql.cursors
import sqlite3

mysqlConnection = pymysql.connect(
    host="localhost",
    user="grafana",
    password="grafana",
    db="grafana",
    charset="utf8mb4",
    cursorclass=pymysql.cursors.DictCursor,
)

mysqlCursor = mysqlConnection.cursor()

sql = """DROP TABLE aipfiles"""
mysqlCursor.execute(sql)
mysqlConnection.commit()

sql = """CREATE TABLE aipfiles(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    uuid VARCHAR(255),
    type VARCHAR(255),
    size INTEGER,
    puid VARCHAR(255),
    format VARCHAR(255),
    format_version VARCHAR(255),
    related_uuid VARCHAR(255),
    creation_date DATETIME,
    ingestion_date DATETIME,
    normalization_date DATETIME,
    aip_id INTEGER NOT NULL
)"""

mysqlCursor.execute(sql)
mysqlConnection.commit()

sqliteDb = sqlite3.connect("aipscan.db")
sqliteCursor = sqliteDb.cursor()

sqliteCursor.execute("SELECT * FROM files")
allaipfiles = sqliteCursor.fetchall()

for aipfile in allaipfiles:
    sql = "INSERT INTO `aipfiles` (`id`, `name`, `uuid`, `type`, `size`, `puid`, `format`, `format_version`, `related_uuid`, `creation_date`, `ingestion_date`, `normalization_date`, `aip_id`) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
    mysqlCursor.execute(
        sql,
        (
            aipfile[0],
            aipfile[1],
            aipfile[2],
            aipfile[3],
            aipfile[4],
            aipfile[5],
            aipfile[6],
            aipfile[7],
            aipfile[8],
            aipfile[9],
            aipfile[10],
            aipfile[11],
            aipfile[12],
        ),
    )
    mysqlConnection.commit()

mysqlConnection.close()
sqliteDb.close()