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)