class Connection(object):
def __init__(self, conn_name, db):
self.conn = None
self.conn_name = conn_name
self.db = db
if conn_name == 'mysql':
return self.connectMySQL()
elif conn_name == 'mssql':
return self.connectMSSQL()
elif conn_name == 'oracle':
return self.connectOracle()
elif conn_name == 'elasticsearch':
return self.connectElasticsearch()
def getName(self):
return self.conn_name
def getDB(self):
return self.db.lower()
def connectMySQL(self):
self.conn = pymysql.connect(host='10.149.7.20',
port=3306,
user='******',
passwd='ibdo1234',
db='mes_db')
return
def connectMSSQL(self):
server = '10.149.1.195,3000'
database = 'fqmdb'
username = '******'
password = '******'
self.conn = pyodbc.connect(
'DRIVER={ODBC Driver 17 for SQL Server};SERVER=' + server +
';DATABASE=' + database + ';UID=' + username + ';PWD=' + password)
return
def connectOracle(self):
dsn = cx_Oracle.makedsn('10.132.128.66', 1521, 'D11')
self.conn = cx_Oracle.connect("IBDO",
"ibdo!data",
dsn,
encoding="UTF-8",
nencoding="UTF-8")
return
def connectElasticsearch(self):
self.conn = Elasticsearch([{'host': 'elasticsearch', 'port': 9200}])
return
def getCursor(self):
return self.conn.cursor()
def getConnection(self):
return self.conn
class DBConnection(object):
"""
Class DBConnection:
connect db
"""
MYSQL = 1
MSSQL = 2
IMPALA = 3
ORACLE = 4
HIVE = 5
ES = 6
def __init__(self, **kwargs):
"""
"""
self.host = kwargs.get('host', 'localhost')
self.database = kwargs.get('database')
self.engine = kwargs.get('engine', self.MYSQL)
self.conn = None
if self.engine is None or self.engine == self.MYSQL:
self.user = kwargs.get('user')
self.password = kwargs.get('password')
self.charset = kwargs.get('charset', 'utf8')
self.port = kwargs.get('port', 3306)
if self.user and self.password:
from pymysql import connect
self.conn = connect(host=self.host,
port=int(self.port),
user=self.user,
password=self.password,
database=self.database,
charset=self.charset)
elif self.engine == self.MSSQL:
self.user = kwargs.get('user')
self.password = kwargs.get('password')
self.charset = kwargs.get('charset', 'utf8')
self.port = kwargs.get('port', 2521)
if self.user and self.password:
from pymssql import connect
if not self.port:
self.port = 2521
self.conn = connect(host=self.host,
user=self.user,
password=self.password,
database=self.database,
charset=self.charset)
elif self.engine == self.IMPALA:
self.backup_host = kwargs.get('backup')
self.port = kwargs.get('port', 21050)
if self.host:
if not self.port:
self.port = 21050
from impala.dbapi import connect
try:
self.conn = connect(self.host, int(self.port),
self.database)
except Exception as ex:
if self.backup_host:
print(
'connect to server:%s failed, try the backup: %s' %
(self.host, self.backup_host))
try:
self.conn = connect(self.backup_host,
int(self.port), self.database)
except Exception as ex:
raise ex
elif self.engine == self.ORACLE:
# self.user = kwargs.get('user')
# self.password = kwargs.get('password')
# self.charset = kwargs.get('charset', 'utf8')
# self.instance = kwargs.get('instance')
# self.port = kwargs.get('port', 1125)
# from cx_Oracle import connect
# self.connect_cmd = "%s/%s@%s:%s/%s" %(
# self.user,
# self.password,
# self.host,
# int(self.port),
# self.instance)
# self.conn = connect(self.connect_cmd)
pass
elif self.engine == self.HIVE:
# self.port = kwargs.get('port', 10000)
# self.authMechanism = kwargs.get('authMechanism', 'PLAIN')
# self.user = kwargs.get('user')
# self.password = kwargs.get('password')
# self.conn = connect(host=self.host, port=self.port,
# authMechanism=self.authMechanism, user=self.user,
# password=self.password, database=self.database)
pass
elif self.engine == self.ES:
self.port = kwargs.get('port', 10000)
from elasticsearch import Elasticsearch
self.conn = Elasticsearch([{'host': self.host, 'port': self.port}])
else:
self.conn = None
def query(self, sql):
if sql and self.conn:
try:
data = None
with self.conn.cursor() as cursor:
cursor.execute(sql)
data = cursor.fetchall()
import re
if re.findall(r'[insert|update|delete]', sql.lower()):
self.conn.commit()
return True, data
except Exception as ex:
raise ex
def __del__(self):
if self.conn:
try:
self.conn.close()
except Exception as ex:
print(ex)
