#! /usr/bin/env python3

import sys
import pymysql
# import sqlite3 as dbdriver
# import psycopg2 as dbdriver

sys.path.insert(0, '../Mittwoch')
from utilities import hide_exception


def db_connect(credentials: dict):
    connection = pymysql.connect(**credentials)

    return connection


def list_hosts(connection):
    cursor = connection.cursor()
    # Simple: SELECT * FROM hosts
    sql = """
          SELECT name, domain, address
          FROM hosts
          ORDER BY name
          """
    cursor.execute(sql)
    # TODO: cursor.description enthaelt die Feldnamen

    # cursor.fetchone()
    # cursor.fetchall()
    # cursor ist Iterator
    for row in cursor:
        print("{0:20} {2:15} {1}".format(*row))

    # for name, domain, address in cursor:
    #     pass


def add_host_variante_0_unvollstaendig(connection, name, domain, address):
    cursor = connection.cursor()
    sql = """
          INSERT INTO hosts
          (name, domain, address)
          VALUES ('{0}', '{1}', '{2}')
          """.format(name, domain, address)


def add_host_variante_1_unvollstaendig(connection, *args):
    cursor = connection.cursor()
    sql = """
          INSERT INTO hosts
          (name, domain, address)
          VALUES ('{0}', '{1}', '{2}')
          """.format(*args)


# Achtung: https://xkcd.com/327/
def add_host(connection, data):
    cursor = connection.cursor()
    sql = """
          INSERT INTO hosts
          (name, domain, address)
          VALUES (%(name)s, %(domain)s, %(address)s)
          """

    # Platzhalter %(name)s erfordert Dict mit passenden Keys
    # In der Datenbank-Schicht wird passend maskiert
    cursor.execute(sql, data)
    connection.commit()


def add_host_flexi(connection, data, table='hosts'):
    cursor = connection.cursor()
    sql = """
          INSERT INTO {table}
          (name, domain, address)
          VALUES ('{name}', '{domain}', '{address}')
          """.format(table=table, **data)

    cursor.execute(sql)
    connection.commit()


def main():
    db = db_connect(credentials)

    # list_hosts(db)
    row = dict(name='notebook99',
               domain='linuxhotel.de',
               address='192.168.1.299')
    add_host(db, row)
    # add_host(db, 'notebook99', 'linuxhotel.de', '192.168.1.299')
    # add_host(db, name='notebook99', domain='linuxhotel.de', a='192.168.1.299')
    # db_insert(db, 'hosts', row)    # noch allgemeiner

    # Verbindung sauber trennen
    db.close()


# Bitte aus Konfigurationsdatei lesen
credentials = dict(host='notebook14',
                   user='python',
                   password='villa',
                   database='inventory')

hide_exception(main)
