DBO.py | Python SQL/ODBC Database Access Classs by Corey Goldberg

DBO.py | Python SQL/ODBC Database Access Class

This is a data access class used with SQL databases that provide ODBC.

To run on Windows, you need to install Python with the Python for Windows Extensions. Make sure you have a DSN configured for the Database and the correct ODBC driver installed.

Code:

#!/usr/bin/env python

#  Copyright 2005-2006, Corey Goldberg
#
#  DBO.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.
 

import dbi
import odbc


class DBO:
    
    def __init__(self, dsn, user_name, password):
        self.dsn = dsn
        self.user_name = user_name
        self.password = password
        
        
    def fetch(self, sql):
        # run a query and return a list of tuples containing the query results
        conn = odbc.odbc('%s/%s/%s' % (self.dsn, self.user_name, self.password))
        cur = conn.cursor()
        cur.execute(sql)
        rows = [] 
        while True:
            rec = cur.fetchmany(1)
            if not rec: break
            rows.append(rec[0])             
        return rows
        
        
    def fetch_single(self, sql):
        # run a query and return a single value instead of all results wrapped in a tuple and list
        single = self.fetch(sql)
        return single[0][0]


    def execute(self, sql):
        # execute sql against the db.
        conn = odbc.odbc('%s/%s/%s' % (self.dsn, self.user_name, self.password))
        cur = conn.cursor()
        db_response = cur.execute(sql)
        return db_response
        


# Sample script using this class:
# -------------------------------------------    
#    #!/usr/bin/env python
#    import DBO
#    
#    dsn = 'MY_DSN'
#    user_name = 'sa'
#    password = 'welcome'
#    
#    db = DBO.DBO(dsn, user_name, password)
#    db.execute("insert into Foo values ('hello world')")
#    print db.fetch("select * from Foo")
# -------------------------------------------