#!/usr/bin/python


'''
Database-modulet opretter forbindelse til databasen og soerger for 
kommunikationen dertil.
Foelgende fire variabler er globale variabler:
USER, PASSWD, HOST og DATABASE.
En del af funktionerne har til formaal at lette overgangen mellem Python og sql. Deres parametre
er kendte Python-datastruktuerer som lister og dictionaries. Modulet oversaetter disse til
elementer i et sql-kald. Modulet 'wrapper' sql i en Python-dragt.

I skal huske at rette USER, PASSWD og DATABASE saa de passer med jeres egne
data'''

USER="pba" #brugernavnet
PASSWD="8tmMkOT2" #password
HOST = "student.hum.au.dk" #adressen paa databaseserveren
DATABASE = "pba" #databasens navn


import MySQLdb, sys


def connectToDB():
    ''' connectToDB opretter forbindelse til databasen DATABASE som ligger 
    paa serveren HOST for brugernavnet USER med password PASSWD.
    Den returnerer et objekt, 'connection', som anvendes ved enhver 
    manipulation af databasen.'''
    # Her opretter vi forbindelse til databasen:
    try:
        connection = MySQLdb.connect(host = HOST, db=DATABASE, user=USER, passwd=PASSWD)
    except MySQLdb.Error, e:
        #ved fejl returneres en fejlmeddelelse
        print "Error %d: %s" % (e.args[0], e.args[1])
        sys.exit (1)
    # 'connection' er en repraesentation af forbindelsen til databasen.
    # Vi returnerer connection til den metode, der kaldte connectToDB()
    return connection


def queryDB(SQLordre):
    '''Udfoerer en sql ordre paa databasen. Den tager en SQLordre som argument og
    returnerer resultatet af denne ordre. Foerst laver den en cursor til databasen.
    Herefter kaldes dennes execute methode.
    Try-except bruges til at teste om det er overhovedet er muligt at sende 
    en forespoergsel til databasen. Hvis der opstaar en MySQLdb-fejl,
    kommer der en exception og der printes
    en fejlmeddelelse, ellers returneres resultatet af forespoergslen'''

    #opret en forbindelse til databasen
    connection = connectToDB()
    cursor = connection.cursor()

    # try og except soerger for, at en fejlmeddelelse tilbagesendes, hvis der opstaar fejl
    try:
        # Udfoerer vores query ved at udfoere et execute() kald
        cursor.execute(SQLordre)
        # Gemmer resultatet i 'result' ved at lave et fetchall() kald
        result = cursor.fetchall()
        connection.close()
    except MySQLdb.Error, e:
        #en eventuel fejl printes her
       print "Error %d: %s" % (e.args[0], e.args[1])
       sys.exit (1)
    return result

# Nedenfor ligger funktioner der indkapsler hyppigt brugte sql-kommendoer.
# Kommandoerne vedroerer kun indsaettelser, fremfindinger, slettelser, og opdateringer paa een tabel
# Er flere tabeller indblandet maa I bruge queryDB.

def insertRecord(table, valueList, mode = 'list'):
    ''' Denne funktion forenkler indsaettelse af poster i databasen.
    ValueList er en liste [a,b,c] af de vaerdier man oensker at indsaette eller 
    den kan vaere en dictionary {A:a, B:b...} hvor A, B er kolonnenavne
    og a,b er vaerdier af kolonnerne. I foerste tilfaelde skal listen
    matche databasen noejagtigt og resultatet er INSERT INTO table VALUES (a,b,c).
    I andet tilfaeldet kan man blot angive vaerdierne til nogle af
    kolonnerne. Resultatet er INSERT INTO table (A,B,C) VALUES (a,b,c). I 
    dette tilfaelde skal man angive mode-parametren. Den skal vaere "dictionary", altsaa
    insertRecord(table, valueList, 'dictionary')'''
    if mode == 'list':
        #Dette betyder, at hvis valueList er en liste, saa skal den oprette forbindelse til databasen,
        #foelge ordren og returnere resultatet af forespoergelsen.
        sqlOrdre = 'INSERT INTO '+ table + listToValueClause(valueList)
        result = queryDB(sqlOrdre)
    else:
        #hvis valuelist er en dictionary.....
        sqlOrdre = 'INSERT INTO '+ table + dictionaryToValueClause(valueList)
        result = queryDB(sqlOrdre)
    return result


def findRecords(table,fields,criteria={}):
    ''' Denne funktion forenkler fremfindingen af poster i databasen.
    Fields er en liste af navne paa de kolonner der skal fremvises.
    Hvis fields er tom dvs. [], fremfindes alle kolonner.
    Criteria er en dictionary {A:a, B:b...}. Den oversaettes til en
    SQL WHERE-liste: WHERE A = a AND B = b.... Hvis criteria er tom, dvs. {}, fremfindes 
    hele databasen. Output er en liste af lister [[v,v,v][v,v,v)...].
    Den enkelte [v,v,v] repraesenterer en raekke i tabellen'''
    
    fieldList = listToFieldClause(fields)
    selectPart = 'SELECT ' + fieldList + ' FROM '
    if criteria == {}:
        #hvis ikke der er nogen selektionskriterier, saa skal den
        #returnere alt fra databasen.
        result = queryDB(selectPart+ table)
    else:
        #hvis der er selektionskriterier, saa skal den returnere alt det som
        #stemmer overens med de kriterier der er for kaldet.
        result = queryDB(selectPart + table + dictionaryToWhereClause(criteria))
    #convert the tuples to a list
    listResult = []
    for v in result:
        listResult.append(list(v))
    return listResult

    
def deleteRecords(table,criteria):
    ''' Denne funktion forenkler slettelse af poster i databasen.
    Criteria er en dictionary {A:a, B:b...}. Den oversaettes til en
    SQL WHERE-liste: WHERE A = a AND B = b...., og kun de raekker der opfylder kriterierne
    bliver slettet. Hvis criteria = {} slettes hele databasen'''

    if criteria =={}:
        result =  queryDB('DELETE FROM '+ table)
    else:
        result = queryDB('DELETE FROM '+ table + dictionaryToWhereClause(criteria))
    return result


def updateRecords(table, updateList, criteria):
    ''' Denne funktion forenkler opdatering af poster i databasen.
    UpdateList er en dictionary {A:a,...}. Den oversaettes til en SET liste: SET A = a,...
    dvs. at kolonnen med navne A faar vaerdien a.
    Criteria er en dictionary {A:a, B:b...}. Den oversaettes til en
    SQL WHERE-liste: WHERE A = a AND B = b.... Kun de raekker der opfylder kriteriet
    bliver opdateret.
    '''
    if updateList == {}:
        return 'No updates'
    if criteria == {}:
        return 'No criteria'
    sqlCommand = 'UPDATE ' + table + dictionaryToUpdateClause(updateList) +\
                       dictionaryToWhereClause(criteria)
    result = queryDB(sqlCommand)
    return result


def getSchema(table):
    ''' Denne funktion returnerer en tuple (primarykey, [kolonnenavn1,...kolonnenavnn])
    som beskriver databaseskemaet. Primarykey angiver navnet paa tabellens primarykey
    hvis der er nogen. [kolonnenavn1,...kolonnenavnn] angiver kolonnenavnene'''
    sqlCommand = 'SHOW  COLUMNS FROM ' + table
    theSchema = queryDB(sqlCommand)
    schemaList = []
    primaryKey = ''
    for f in theSchema:
        schemaList.append(f[0])
        if f[3] == 'PRI':
            primaryKey = f[0]
    return (primaryKey,schemaList)


def getColumnNames(table):
    '''Denne funktion returnerer en liste af kolonnenavne'''
    return getSchema(table)[1]

def getPrimaryKey(table):
    '''Denne funktion returnerer en primarykey fra table hvis nogen
    ellers returneres den tomme streng'''
    return getSchema(table)[0]

#Nedenfor ses hjaelpefunktioner, der omdanner lister og dictionaries til strenge,
#der forstaas af SQL

def dictionaryToWhereClause(aDictionary):
    ''' omdanner en dictionary {A:a, B:b,...} til
    en WHERE-liste af formen "WHERE A = a AND B = b" ...'''
    whereClause = ' WHERE '
    i = 0
    for k in aDictionary.keys():
        i += 1
        if type(aDictionary[k]) == str:
            value = "'" + aDictionary[k]+ "'"
        else:
            value = str(aDictionary[k])
        whereClause += k + ' = ' + value
        if i < len(aDictionary.keys()):
            whereClause += ' AND '
        else:
            whereClause += ';'
    return whereClause

def dictionaryToUpdateClause(aDictionary):
    ''' omdanner en dictionary {A:a,B:b...} til
    en SET liste af formen "SET A = a, B = b" ...'''
    whereClause = ' SET '
    i = 0
    for k in aDictionary.keys():
        i += 1
        if type(aDictionary[k]) == str:
            value = "'" + aDictionary[k]+ "'"
        else:
            value = str(aDictionary[k])
        whereClause += k + ' = ' + value
        if i < len(aDictionary.keys()):
            whereClause += ', '
        else:
            whereClause += ''
    return whereClause

def dictionaryToValueClause(aDictionary):
    ''' omdanner en dictionary {A:a,B:b} til en tekst
    (A,B) VALUES (a,b) hvor A og B er kolonnenavne og a,b er kolonnernes 
    vaerdier'''
    if aDictionary == {}:
        return '() VALUES ()'
    keylist = []
    valuelist = []
    for k in aDictionary:
        keylist.append(k)
        valuelist.append(aDictionary[k])
    valueClause = listToValueClause(valuelist)
    keyClause = '('
    i = 0
    for k in keylist:
        i += 1
        keyClause += str(k)
        if i < len(keylist):
            keyClause += ', '
        else:
            keyClause += ') '
    return keyClause + valueClause

def listToFieldClause(aList):
    ''' omdanner en liste [a,b,c...] til
    en liste af formen a,b,c.
    Hvis listen er tom returneres symbolet *'''
    if aList == []:
        return ' * '
    columns = ' '
    for c in aList:
        columns += c + ','
    columns = columns[:-1] + ' '
    return columns

def listToValueClause(aList):
    ''' omdanner en liste [a,b,c...] til
    en VALUE liste af formen VALUES (a,b,c...)'''
    if aList == []:
        return ' VALUES ()'
    valueClause = ' VALUES ('
    i = 0
    for v in aList:
        i += 1
        if type(v) == str:
            value = "'" + v + "'"
        else:
            value = str(v)
        valueClause += value
        if i < len(aList):
            valueClause += ', '
        else:
            valueClause += '); '
    return valueClause

#Nedenstaaende funktion omdanner output fra et databasekald til
# en liste af dictionaries.

def rowListToDictList(table,rowList):
    '''input: a table and a tuple of tuples of complete row values
    output: a list of dictionaries where keys are columnnames and values are row values'''
    theSchema = getColumnNames(table)
    result =[]
    for row in rowList:
        result.append(rowToDictionary(theSchema,row))
    return result

def rowToDictionary(columnnames,columnvalues):
    ''' input: a list of columnnames and a list of columnvalues
    output: a dictionary where the keys are columnnnames and the values are
    rowvalues'''
    aDictionary = {}
    for i in range(len(columnnames)):
        aDictionary[columnnames[i]] = columnvalues[i]
    return aDictionary


if __name__ == "__main__":
    print 'find all records in table Child'
    aRow = findRecords('Child',[],{})
    print aRow
    print
    print 'fetch a row from table Child with cpr = 1111111111'
    aRow = findRecords('Child',['cpr','firstname','lastname'],{'cpr':'1111111111'})
    print aRow
    print
    print 'change the first name to Jeppe in record where  cpr = 1111111111'
    updateRecords('Child', {'firstname':'Jeppe'}, {'cpr':'1111111111'})
    aRow = findRecords('Child',['cpr','firstname','lastname'],{'cpr':'1111111111'})
    print aRow
    print
    print 'restore the first name to Jeppe Boegh in record where  cpr = 1111111111'
    updateRecords('Child', {'firstname':'Jeppe Boegh'}, {'cpr':'1111111111'})
    aRow = findRecords('Child',['cpr','firstname','lastname'],{'cpr':'1111111111'})
    print aRow
    print 
    print 'add a new record'
    insertRecord('Child', {'cpr': '7777777','firstname': 'Clark','lastname':'Kent'}, mode = 'dictionary')
    aRow = findRecords('Child',[],{'cpr':'7777777'})
    print aRow
    print
    print 'delete superman'
    deleteRecords('Child',{'cpr':'7777777'})
    print
    print 'find all records in table Child'
    aRow = findRecords('Child',[])
    print aRow
    print
    print 'find all children that live at the same address as their mother'
    SQLordre = '''SELECT Child.cpr, Child.firstname, Child.lastname 
FROM Child, Person
WHERE Child.hasMother = Person.cpr AND Child.address = Person.address'''
    aSelection = queryDB(SQLordre)
    print aSelection