#! /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 db_insert_into(connection, table, data):
    columns = []
    values = []
    for col in data.keys():
        col = col.replace("'", '') # gegen SQL Injection
        columns.append(col)
        values.append('%('+col+')s')
        # values.append('%({})s'.format(col))
        
    sql = """
          INSERT INTO {table}
          ({columns})
          VALUES ({placeholder})
          """.format(
              table=table,
              columns=','.join(columns),
              placeholder=','.join(values))
    
    # Beim Verlassen des Kontextes wird automatisch commit aufgerufen
    with connection as cursor:
        cursor.execute(sql, data)


def main():
    db = db_connect(credentials)

    # list_hosts(db)
    row = dict(name='notebook99',
               domain='linuxhotel.de',
               address='192.168.1.299')
    db_insert_into(db, 'hosts', row)

    # Verbindung sauber trennen
    db.close()


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

hide_exception(main)
