#!/usr/bin/python
# -*- coding: utf-8 -*-

import sqlite3 as lite
import sys

def show_sqlite_version():
    print "SQLite version: %s" % lite.version
    
def create_new_database(PfadzurDatenbank):
    try:
        con = lite.connect(PfadzurDatenbank) 
        con.commit()
    
    except lite.Error, e:
    
        if con:
            con.rollback()
        
            print "Error %s:" % e.args[0]
            sys.exit(1)
    
    finally:
    
        if con:
            con.close() 

def create_new_temperature_sensor_table(PathToDatabase, TableName):
    try:
        con = lite.connect(PathToDatabase)
        cur = con.cursor()  
        cur.executescript("""
            CREATE TABLE %s(    Timestamp INT PRIMARY KEY, 
                                Temp_MIN REAL, 
                                Temp_MAX REAL); 
            """ % TableName) 
        con.commit()
    
    except lite.Error, e:
    
        if con:
            con.rollback()
        
            print "Error %s:" % e.args[0]
            sys.exit(1)
    
    finally:
    
        if con:
            con.close() 

def insert_new_line_in_temperature_sensor_table(PathToDatabase, TableName, 
                                                Timestamp, Temp_MIN, Temp_MAX):
    try:
        con = lite.connect(PathToDatabase)
        cur = con.cursor()  
        cur.execute("""
            INSERT INTO %s 
            VALUES( %i , %.3f, %.3f)
            """ % (TableName, Timestamp, Temp_MIN, Temp_MAX))
        con.commit()
    
    except lite.Error, e:
    
        if con:
            con.rollback()
        
            print "Error %s:" % e.args[0]
            sys.exit(1)
    
    finally:
    
        if con:
            con.close() 

def show_all_table_entries(PathToDatabase, TableName):
    try:
        con = lite.connect(PathToDatabase)
        cur = con.cursor()  
        cur.execute("SELECT * FROM %s" % TableName)
        lines = cur.fetchall()
        for line in lines:
            print line
        con.commit()
    
    except lite.Error, e:
        if con:
            con.rollback()
            print "Error %s:" % e.args[0]
            sys.exit(1)
    
    finally:
        if con:
            con.close() 
            
def list_all_tables_in_DB(PathToDatabase):
    try:
        con = lite.connect(PathToDatabase)

        with con:
    
            cur = con.cursor()    
            cur.execute("SELECT name FROM sqlite_master WHERE type='table'")

            rows = cur.fetchall()

            for row in rows:
                print row[0]
    
    except lite.Error, e:
    
        if con:
            con.rollback()
        
            print "Error %s:" % e.args[0]
            sys.exit(1)
    
    finally:
    
        if con:
            con.close() 

#show_sqlite_version()
#create_new_database("/home/pi/sqlite3/BeispielDatenbank.db")
#create_new_temperature_sensor_table("/home/pi/sqlite3/BeispielDatenbank.db", "Fuehler1")
#list_all_tables_in_DB("/home/pi/sqlite3/BeispielDatenbank.db")
#insert_new_line_in_temperature_sensor_table("/home/pi/sqlite3/BeispielDatenbank.db", 
#                                            "Fuehler1", 1445432400, 15.0, 17.22)
show_all_table_entries("/home/pi/sqlite3/BeispielDatenbank.db", "Fuehler1")