basic DB agent class

gistfile1.py

from sqlalchemy import create_engine, func
from sqlalchemy.orm import scoped_session, sessionmaker
from yaml import load
import urllib
import logging

# can be removed when using as a library
logging.basicConfig(format="%(asctime)s %(levelname)s\t%(message)s", level=logging.DEBUG)

class LSDB():
    def __init__(self, db_server, db_name, db_login='sa', db_password='pass'):
        self.logger = logging.getLogger('LSDB')
        self._connect(db_server, db_name, db_login, db_password)

    def _connect(self, db_server, db_name, db_login, db_password):
        """ Establishes connection to LSDB """
        db_string = urllib.quote_plus("DRIVER={SQL Server};Server=%s;Database=%s;UID=%s;PWD=%s;Port=1433;TDS_Version=4.2" % (
                db_server, db_name, db_login, db_password))
        db_string = 'mssql+pyodbc:///?odbc_connect=%s' % db_string
        self.logger.debug("Estabishing connection: {}".format(db_string))
        self.engine = create_engine(db_string)
        self.db_session = scoped_session(sessionmaker(autocommit=False, autoflush=False, bind=self.engine))

    def exec_procecure(self, proc_name, **kwargs):
        """ Executes stored procedure on LSDB. Returns a list of results """
        exec_string = "exec {0} ".format(proc_name)
        for name, value in kwargs.items():
            exec_string += '@{0}=:{0} '.format(name, value)
        return self.db_session.execute(exec_string, params=kwargs).fetchall()

    def get_server_by_rom(self, rom):        
        """ Example of using exec_procecure() """
        return self.exec_procecure('AdmCon_Servers_Get', ServerROM=rom)[0]

greenls_db = LSDB('192.168.37.90', 'GREENLSNEW0420')
print greenls_db.get_server_by_rom('39130')