#!/usr/bin/env python3
# Generate export

import json
import sqlite3

EXPORTED_TABLES = [
    "proxy_host",
    "setting",
    "stream",
]

if __name__ == "__main__":
    # open database file
    with sqlite3.connect("database.sqlite") as conn:
        # builtin Row object is easy to convert to dict
        conn.row_factory = sqlite3.Row
        c = conn.cursor()
        # get the names of the tables
        c.execute("SELECT name FROM sqlite_master WHERE type='table'")
        tables = [a["name"] for a in c.fetchall()]
        # get content from each table
        # TODO: make this into a dict comprehension (if possible)
        db_content = {}
        for table in tables:
            if table in EXPORTED_TABLES:
                c.execute("SELECT * FROM {0}".format(table))
                db_content[table] = [dict(a) for a in c.fetchall()]
    # dump contents to json file
    with open("nginx.exported.json", "w") as f:
        json.dump(db_content, f, indent=4)

#!/usr/bin/env python3

# Import exported data

import json
import sqlite3

if __name__ == "__main__":
    with open("nginx.exported.json", "r") as f:
        db_content = json.load(f)
        print(db_content)
    # open database file
    with sqlite3.connect("database.sqlite") as conn:
        # builtin Row object is easy to convert to dict
        conn.row_factory = sqlite3.Row
        c = conn.cursor()

        for table, data in db_content.items():
            for row in data:
                cols = ', '.join('"{}"'.format(col) for col in row.keys())
                vals = ', '.join(':{}'.format(col) for col in row.keys())
                update_set = ', '.join('{}=excluded.{}'.format(col, col) for col in row.keys())
                sql = 'INSERT INTO "{0}" ({1}) VALUES ({2}) ON CONFLICT(id) DO UPDATE SET {3}'.format(table, cols,
                                                                                                      vals, update_set)
                conn.cursor().execute(sql, row)
                conn.commit()