toys/fastapi/sql/create.py

#!/usr/bin/env python3
# -*- coding: utf-8; mode: python; tab-width: 4; indent-tabs-mode: nil -*-
# vim: tabstop=4 expandtab shiftwidth=4 softtabstop=4 fileencoding=utf-8

__author__ = ['"anjingyu" <anjingyu@navinfo.com>']

import os
import sqlite3

DEFAULT_ENTRIES = [
("audi-t3", "v1.0.3", "http://172.23.140.35:12345/audi-t3/v1.0.3/", "Base URL of Audi T3 map server, 1.0.3"),
("audi-t3", "v1.0.4", "http://172.23.140.35:12345/audi-t3/v1.0.4/", "Base URL of Audi T3 map server, 1.0.4")
]

if __name__ == '__main__':

    con = None

    try:
        con = sqlite3.connect("mapserver.db")
        # Create db file if not exist
        if os.path.isfile("mapserver.db"):
            print("DB creation: creating database schema: mapserver.db")
            ddl = open('init.sql').read()
            sqlite3.complete_statement(ddl)
            con.executescript(ddl)

        # Create MapServer table and insert some data
        cur = con.cursor()
        print("Table creation: MapServer")
        for entry in DEFAULT_ENTRIES:
            print(f"Insert record: {entry}")
            cur.execute(f"""INSERT INTO MapServer (id, catalog, version, value, description) values (NULL, "{entry[0]}", "{entry[1]}", "{entry[2]}", "{entry[3]}")""")
        con.commit()

    except Exception as e:
        print("Failed to create database: ", e)
    finally:
        if con is not None:
            con.close()