83 lines
2.4 KiB
Python
83 lines
2.4 KiB
Python
# -*- coding: utf-8 -*-
|
|
"""
|
|
pyjeeves
|
|
~~~~~~~~~~~~~~~
|
|
|
|
Global objects
|
|
"""
|
|
from pyjeeves import logging, config
|
|
|
|
from sqlalchemy import create_engine
|
|
from sqlalchemy.orm import sessionmaker, scoped_session
|
|
# from sqlalchemy.orm.exc import UnmappedClassError
|
|
from pymssql import OperationalError
|
|
|
|
from sqlservice import Database
|
|
|
|
logger = logging.getLogger("PyJeeves." + __name__)
|
|
|
|
|
|
class DBConnector(object):
|
|
"""This class is used to control the SQLAlchemy integration"""
|
|
|
|
def __init__(self, enabled_clients=['raw'], metadata=None):
|
|
logger.info("Creating engines and sessionmakers")
|
|
|
|
self.enabled_clients = enabled_clients
|
|
self.raw_db, self.raw_session, self.raw_engine = (
|
|
self.raw_client() if 'raw' in enabled_clients else {})
|
|
self.meta = (self.meta_session() if 'meta' in enabled_clients else {})
|
|
|
|
def callproc(self, procedure="", params=[]):
|
|
conn = self.raw_engine.raw_connection()
|
|
|
|
with conn.cursor() as cursor:
|
|
try:
|
|
retval = cursor.callproc(procedure, params)
|
|
try:
|
|
cursor.nextset()
|
|
retval = cursor.fetchall()
|
|
except OperationalError:
|
|
logger.debug("Executed statement has no resultset")
|
|
|
|
conn.commit()
|
|
|
|
finally:
|
|
conn.close()
|
|
|
|
return retval
|
|
|
|
def execute(self, operation=""):
|
|
conn = self.raw_engine
|
|
|
|
with conn.connection.cursor(as_dict=True) as cursor:
|
|
try:
|
|
cursor.execute(operation)
|
|
results = cursor.fetchall()
|
|
finally:
|
|
conn.close()
|
|
return results
|
|
|
|
def raw_client(self):
|
|
if 'raw' not in self.enabled_clients:
|
|
logger.error('Raw client is not enabled')
|
|
|
|
logger.info("Using DB %s" % config.config['databases']['raw']['db'])
|
|
|
|
uri = 'mssql+pymssql://{user}:{pw}@{host}:{port}/{db}?charset=utf8'.format(
|
|
**config.config['databases']['raw'])
|
|
|
|
db = Database(uri, echo=False)
|
|
|
|
return db, db.session(), db.connect()
|
|
|
|
def set_model_class(self, model_class):
|
|
self.raw_db.model_class = model_class
|
|
|
|
def meta_session(self):
|
|
|
|
meta_engine = create_engine(
|
|
'mysql+pymysql://{user}:{pw}@{host}:{port}/{db}?charset=utf8mb4'.format(
|
|
**config.config['databases']['meta']))
|
|
|
|
return scoped_session(sessionmaker(bind=meta_engine))
|