Vorschau

#!/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