#!/usr/bin/env python

#  Copyright 2009, Ralf Weingarten
#
#  Inspired by Corey Goldberg's DBO.py, see http://www.goldb.org/dbopython.html
#
#  pyodbcDBO.py is free software; you can redistribute it and/or modify
#  it under the terms of the GNU General Public License as published by
#  the Free Software Foundation; either version 2 of the License, or
#  (at your option) any later version.

"""Klasse DBO in pyodbcDBO.py stellt Methoden zum einfachen Gebrauch des pyodbc-Moduls zur Verfuegung.
Methoden implementieren select (einzelne Spalten oder ganze Tabelle), update, insert, delete.
Methode execute fuehrt ein fertiges SQL-Statement aus."""
 
import pyodbc

class DBO:
    
    def __init__(self, dsn):
        self.dsn = dsn        
        
    def select_columns(self, desired, table, colsmap={}):
        '''select desired columns and return a list of tuples 
        containing the query results'''
        conn = pyodbc.connect('%s' % (self.dsn))
        cur = conn.cursor()
        select = "select "
        select += self.__liststr(desired)
        select += " from %s"%(table)
        select += self.__buildwhere(colsmap)
        rows = cur.execute(select).fetchall()
        return rows       
        
    def select_all(self, table, colsmap={}):
        '''select the whole table and return a list of tuples 
        containing the query results'''
        conn = pyodbc.connect('%s' % (self.dsn))
        cur = conn.cursor()
        rows = cur.execute("select * from %s%s" % (table, self.__buildwhere(colsmap))).fetchall()
        return rows
        
    def update(self, table, to_set, to_set_value, colsmap={}):
        '''build sql string for update from parms
        and execute update'''
        conn = pyodbc.connect('%s' % (self.dsn))
        cur = conn.cursor()
        update = "update %s set %s = '%s'" % (table, to_set, to_set_value)
        update += self.__buildwhere(colsmap)
        db_response = cur.execute(update)
        conn.commit()
        return db_response
        
    def delete(self, table, colsmap={}):
        '''build sql string for delete from parms
        and execute delete'''
        conn = pyodbc.connect('%s' % (self.dsn))
        cur = conn.cursor()
        delete = "delete from %s" % (table)
        delete += self.__buildwhere(colsmap)
        db_response = cur.execute(delete)
        conn.commit()
        return db_response
        
    def insert(self, table, colsmap):
        '''build sql string for insert from parms
        and execute insert'''
        conn = pyodbc.connect('%s' % (self.dsn))
        cur = conn.cursor()
        
        colstr = ''
        valstr = ''
        cols = colsmap.keys()
        values = []
        for col in cols:
            values.append(colsmap[col])
        
        insert = "insert into %s " % (table)
        colstr += self.__liststr(cols)
        insert += "(%s) values " % (colstr)
        # quot=True in the following call of __liststr applies to sqlite3 and MySQL 5.0. 
        # Modify it according to your needs.
        valstr = self.__liststr(values, True) 
        insert += "(%s)" % (valstr)
        db_response = cur.execute(insert)
        conn.commit()
        return db_response
        
    def execute(self, sql):
        '''execute sql against the db.
        this is a general method which requires a ready sql string as a parameter'''
        conn = pyodbc.connect('%s' % (self.dsn))
        cur = conn.cursor()
        db_response = cur.execute(sql)
        conn.commit()
        return db_response
        
    def __liststr(self, rawlist, quot=False):
        '''return a string (comma separated enumeration) from list'''
        retstr = ''
        for rawl in rawlist:
            if quot:
                retstr += '"%s", ' % (rawl)
            else:
                retstr += '%s, ' % (rawl)
        return retstr[:-2] # cut off last comma
        
    def __buildwhere(self, colsmap):
        '''compose 'where' clause from dict containing columns to check (keys)
        and their values'''
        if not colsmap:
            return ''
            
        where = " where"
        ckey_flg = False
        for ckey in colsmap.keys():
            if ckey_flg:
                where += " and"
            where += " %s = '%s'" % (ckey, colsmap[ckey])
            ckey_flg = True
            
        return where