def __init__(self, oracle, logger, db_host=None, db_user='root', db_name=None, db_schema=None, db_pwd=None, db_port=None):
super(Oracle, self).__init__()
self.__db_name = db_name
self.__db_user = db_user
self.__db_schema = db_schema
self.__db_dsn = pyoracle.makedsn(host=db_host, port=int(db_port) if None != db_port else 1521, service_name=db_name)
self.__conn = oracle.build(user=db_user, password=db_pwd, dsn=self.__db_dsn)
if self.__db_schema is not None:
cursor = self.__conn.cursor()
cursor.execute("ALTER SESSION SET CURRENT_SCHEMA = {schema}".format(schema=self.__db_schema))
self.__db_connection_string = 'jdbc:oracle:thin:@//' + db_host + ((':' + db_port) if db_port else '') + (('/' + db_name) if db_name else '')
self.__illegal_characters = re.compile(r'[\000-\010]|[\013-\014]|[\016-\037]|[\xa1]|[\xc1]|[\xc9]|[\xcd]|[\xd1]|[\xbf]|[\xda]|[\xdc]|[\xe1]|[\xf1]|[\xfa]|[\xf3]')
self.__logger = logger
python类makedsn()的实例源码
def get_conn(self):
"""
Returns a oracle connection object
Optional parameters for using a custom DSN connection (instead of using a server alias from tnsnames.ora)
The dsn (data source name) is the TNS entry (from the Oracle names server or tnsnames.ora file)
or is a string like the one returned from makedsn().
:param dsn: the host address for the Oracle server
:param service_name: the db_unique_name of the database that you are connecting to (CONNECT_DATA part of TNS)
You can set these parameters in the extra fields of your connection
as in ``{ "dsn":"some.host.address" , "service_name":"some.service.name" }``
"""
conn = self.get_connection(self.oracle_conn_id)
dsn = conn.extra_dejson.get('dsn', None)
sid = conn.extra_dejson.get('sid', None)
mod = conn.extra_dejson.get('module', None)
service_name = conn.extra_dejson.get('service_name', None)
if dsn and sid and not service_name:
dsn = cx_Oracle.makedsn(dsn, conn.port, sid)
conn = cx_Oracle.connect(conn.login, conn.password, dsn=dsn)
elif dsn and service_name and not sid:
dsn = cx_Oracle.makedsn(dsn, conn.port, service_name=service_name)
conn = cx_Oracle.connect(conn.login, conn.password, dsn=dsn)
else:
conn = cx_Oracle.connect(conn.login, conn.password, conn.host)
if mod is not None:
conn.module = mod
return conn
def connect(self):
self.initConnection()
self.__dsn = cx_Oracle.makedsn(self.hostname, self.port, self.db)
self.__dsn = utf8encode(self.__dsn)
self.user = utf8encode(self.user)
self.password = utf8encode(self.password)
try:
self.connector = cx_Oracle.connect(dsn=self.__dsn, user=self.user, password=self.password, mode=cx_Oracle.SYSDBA)
logger.info("successfully connected as SYSDBA")
except (cx_Oracle.OperationalError, cx_Oracle.DatabaseError, cx_Oracle.InterfaceError):
try:
self.connector = cx_Oracle.connect(dsn=self.__dsn, user=self.user, password=self.password)
except (cx_Oracle.OperationalError, cx_Oracle.DatabaseError, cx_Oracle.InterfaceError), msg:
raise SqlmapConnectionException(msg)
self.initCursor()
self.printConnected()
def connect(self):
self.initConnection()
self.__dsn = cx_Oracle.makedsn(self.hostname, self.port, self.db)
self.__dsn = utf8encode(self.__dsn)
self.user = utf8encode(self.user)
self.password = utf8encode(self.password)
try:
self.connector = cx_Oracle.connect(dsn=self.__dsn, user=self.user, password=self.password, mode=cx_Oracle.SYSDBA)
logger.info("successfully connected as SYSDBA")
except (cx_Oracle.OperationalError, cx_Oracle.DatabaseError, cx_Oracle.InterfaceError):
try:
self.connector = cx_Oracle.connect(dsn=self.__dsn, user=self.user, password=self.password)
except (cx_Oracle.OperationalError, cx_Oracle.DatabaseError, cx_Oracle.InterfaceError), msg:
raise SqlmapConnectionException(msg)
self.initCursor()
self.printConnected()
def connect(self):
self.initConnection()
self.__dsn = cx_Oracle.makedsn(self.hostname, self.port, self.db)
self.__dsn = utf8encode(self.__dsn)
self.user = utf8encode(self.user)
self.password = utf8encode(self.password)
try:
self.connector = cx_Oracle.connect(dsn=self.__dsn, user=self.user, password=self.password, mode=cx_Oracle.SYSDBA)
logger.info("successfully connected as SYSDBA")
except (cx_Oracle.OperationalError, cx_Oracle.DatabaseError, cx_Oracle.InterfaceError):
try:
self.connector = cx_Oracle.connect(dsn=self.__dsn, user=self.user, password=self.password)
except (cx_Oracle.OperationalError, cx_Oracle.DatabaseError, cx_Oracle.InterfaceError), msg:
raise SqlmapConnectionException(msg)
self.initCursor()
self.printConnected()
def connect(self):
self.initConnection()
self.__dsn = cx_Oracle.makedsn(self.hostname, self.port, self.db)
self.__dsn = utf8encode(self.__dsn)
self.user = utf8encode(self.user)
self.password = utf8encode(self.password)
try:
self.connector = cx_Oracle.connect(dsn=self.__dsn, user=self.user, password=self.password, mode=cx_Oracle.SYSDBA)
logger.info("successfully connected as SYSDBA")
except (cx_Oracle.OperationalError, cx_Oracle.DatabaseError, cx_Oracle.InterfaceError):
try:
self.connector = cx_Oracle.connect(dsn=self.__dsn, user=self.user, password=self.password)
except (cx_Oracle.OperationalError, cx_Oracle.DatabaseError, cx_Oracle.InterfaceError), msg:
raise SqlmapConnectionException(msg)
self.initCursor()
self.printConnected()
def get_conn(self):
"""
Returns a oracle connection object
Optional parameters for using a custom DSN connection (instead of using a server alias from tnsnames.ora)
The dsn (data source name) is the TNS entry (from the Oracle names server or tnsnames.ora file)
or is a string like the one returned from makedsn().
:param dsn: the host address for the Oracle server
:param service_name: the db_unique_name of the database that you are connecting to (CONNECT_DATA part of TNS)
You can set these parameters in the extra fields of your connection
as in ``{ "dsn":"some.host.address" , "service_name":"some.service.name" }``
"""
conn = self.get_connection(self.oracle_conn_id)
dsn = conn.extra_dejson.get('dsn', None)
sid = conn.extra_dejson.get('sid', None)
service_name = conn.extra_dejson.get('service_name', None)
if dsn and sid and not service_name:
dsn = cx_Oracle.makedsn(dsn, conn.port, sid)
conn = cx_Oracle.connect(conn.login, conn.password, dsn=dsn)
elif dsn and service_name and not sid:
dsn = cx_Oracle.makedsn(dsn, conn.port, service_name=service_name)
conn = cx_Oracle.connect(conn.login, conn.password, dsn=dsn)
else:
conn = cx_Oracle.connect(conn.login, conn.password, conn.host)
return conn
def findTotal(patronID):
# function to get the total amount of fines owed for a patron, not just the weekly fines
# Does lookup based on patron id
sum = 0.0;
# make connection to the database
dsn = cx_Oracle.makedsn("localhost","1521","VGER")
con = cx_Oracle.connect(user="READ-ONLY-DBA-USER",password="READ-ONLY-DBA-PASS",dsn=dsn)
cur = con.cursor()
# run the query
query = """
SELECT sum(fine_fee_balance)
FROM fine_fee
WHERE patron_id='{pID}'
"""
cur.execute(query.format(pID=patronID));
try:
sum = cur.fetchone()[0]
except:
sum = 0.0
return sum
def findSSAN(patronID):
# function to get a users SSAN (bannerID) based on patronID
# returns String NONE if value is None (alumni users, etc)
# added to allow for banner id to go in summary, as per TKT-193
ssan = "NONE";
# make connection to the database
dsn = cx_Oracle.makedsn(db_host,db_port,db_SID)
con = cx_Oracle.connect(user=db_user,password=db_password,dsn=dsn)
cur = con.cursor()
# run the query
query = """
SELECT
pt.ssan
FROM
patron pt
WHERE
pt.patron_id IN ({pID})
"""
cur.execute(query.format(pID=patronID));
try:
ssan = cur.fetchone()[0]
except:
ssan = "NONE"
if ssan == None:
ssan = "NONE"
else:
ssan = "@" + ssan[1:]
return ssan;
def findItemType(barCode):
# function to get the type of an item, based on itemId.
# checks for temporary item ID being set, if so, returns that type instead
# Note: dicts field itemId isn't really itemId, it's item barcode
# dict's barcode field is PATRON barcode
itemType = "NONE";
# make connection to the database
dsn = cx_Oracle.makedsn(db_host,db_port,db_SID)
con = cx_Oracle.connect(user=db_user,password=db_password,dsn=dsn)
cur = con.cursor()
# run the query
query = """
SELECT
itt.item_type_name
FROM
item it,
item_type itt,
item_barcode ib
WHERE
ib.item_barcode IN ('{itemNumber}') AND
ib.item_id = it.item_id AND
itt.item_type_id = CASE WHEN (it.temp_item_type_id = '0') THEN it.item_type_id ELSE it.temp_item_type_id END
"""
cur.execute(query.format(itemNumber=barCode));
try:
itemType = cur.fetchone()[0]
except:
itemType = "NONE"
if itemType == None:
itemType = "NONE"
return itemType;
##########Utility Functions
def __init__(self, **keywords):
import cx_Oracle as db
if 'pw' in keywords:
keywords['password'] = keywords.pop('pw')
#@@ TODO: use db.makedsn if host, port is specified
keywords['dsn'] = keywords.pop('db')
self.dbname = 'oracle'
db.paramstyle = 'numeric'
self.paramstyle = db.paramstyle
# oracle doesn't support pooling
keywords.pop('pooling', None)
DB.__init__(self, db, keywords)
def __init__(self, **keywords):
import cx_Oracle as db
if 'pw' in keywords:
keywords['password'] = keywords.pop('pw')
#@@ TODO: use db.makedsn if host, port is specified
keywords['dsn'] = keywords.pop('db')
self.dbname = 'oracle'
db.paramstyle = 'numeric'
self.paramstyle = db.paramstyle
# oracle doesn't support pooling
keywords.pop('pooling', None)
DB.__init__(self, db, keywords)
def __init__(self, **keywords):
import cx_Oracle as db
if 'pw' in keywords:
keywords['password'] = keywords.pop('pw')
#@@ TODO: use db.makedsn if host, port is specified
keywords['dsn'] = keywords.pop('db')
self.dbname = 'oracle'
db.paramstyle = 'numeric'
self.paramstyle = db.paramstyle
# oracle doesn't support pooling
keywords.pop('pooling', None)
DB.__init__(self, db, keywords)
def __init__(self, **keywords):
import cx_Oracle as db
if 'pw' in keywords:
keywords['password'] = keywords.pop('pw')
#@@ TODO: use db.makedsn if host, port is specified
keywords['dsn'] = keywords.pop('db')
self.dbname = 'oracle'
db.paramstyle = 'numeric'
self.paramstyle = db.paramstyle
# oracle doesn't support pooling
keywords.pop('pooling', None)
DB.__init__(self, db, keywords)
def __init__(self, **keywords):
import cx_Oracle as db
if 'pw' in keywords:
keywords['password'] = keywords.pop('pw')
#@@ TODO: use db.makedsn if host, port is specified
keywords['dsn'] = keywords.pop('db')
self.dbname = 'oracle'
db.paramstyle = 'numeric'
self.paramstyle = db.paramstyle
# oracle doesn't support pooling
keywords.pop('pooling', None)
DB.__init__(self, db, keywords)
def __init__(self, **keywords):
import cx_Oracle as db
if 'pw' in keywords:
keywords['password'] = keywords.pop('pw')
#@@ TODO: use db.makedsn if host, port is specified
keywords['dsn'] = keywords.pop('db')
self.dbname = 'oracle'
db.paramstyle = 'numeric'
self.paramstyle = db.paramstyle
# oracle doesn't support pooling
keywords.pop('pooling', None)
DB.__init__(self, db, keywords)
def metricCollector(self):
c=None
conn=None
try:
import cx_Oracle
except Exception as e:
self.data['status'] = 0
self.data['msg'] = str(e)
return self.data
try:
dsnStr = cx_Oracle.makedsn(self.host, self.port, self.sid)
conn = cx_Oracle.connect(user=self.username, password=self.password, dsn=dsnStr, mode=cx_Oracle.SYSDBA)
c = conn.cursor()
c.execute('select dff.percentused,dff.tablespace_name, dtf.status from sys.dba_tablespaces dtf , (select tablespace_name,percentused from (select round((d.sizeMb-round(sum(f.bytes))/1048576)/d.maxMb*100) percentused, f.tablespace_name from dba_free_space f, (select tablespace_name, sum(MAXBYTES)/1048576 maxMb, sum(bytes)/1048576 sizeMb from dba_data_files group by tablespace_name) d where f.tablespace_name (+)=d.tablespace_name group by f.tablespace_name, d.sizeMb, d.maxMb order by percentused desc)) dff where dff.tablespace_name (+)=dtf.tablespace_name order by dff.percentused')
for row in c:
usage, name ,status= row
if name in TABLESPACE_NAME:
self.data[name+'_usage'] = usage
self.data[name+'_status'] = status
except Exception as e:
self.data['status'] = 0
self.data['msg'] = str(e)
finally:
if c!= None : c.close()
if conn != None : conn.close()
return self.data
def __init__(self, **keywords):
import cx_Oracle as db
if 'pw' in keywords:
keywords['password'] = keywords.pop('pw')
#@@ TODO: use db.makedsn if host, port is specified
keywords['dsn'] = keywords.pop('db')
self.dbname = 'oracle'
db.paramstyle = 'numeric'
self.paramstyle = db.paramstyle
# oracle doesn't support pooling
keywords.pop('pooling', None)
DB.__init__(self, db, keywords)
def metricCollector(self):
c=None
conn=None
try:
import cx_Oracle
except Exception as e:
self.data['status'] = 0
self.data['msg'] = 'cx_Oracle module is not installed'
return self.data
try:
try:
dsnStr = cx_Oracle.makedsn(self.host, self.port, self.sid)
conn = cx_Oracle.connect(user=self.username, password=self.password, dsn=dsnStr, mode=cx_Oracle.SYSDBA)
c = conn.cursor()
except Exception as e:
self.data['status']=0
self.data['msg']='Exception while connecting to '+self.host
c.execute("select * from (SELECT t.tablespace_name,t.logging,t.contents,t.status,NVL(df.allocated_bytes,0)-NVL((NVL(f.free_bytes,0)+df.max_free_bytes),0) usedBytes,NVL((NVL(f.free_bytes,0)+df.max_free_bytes),0) freeBytes,NVL(f.free_blocks,0) freeBlocks FROM sys.dba_tablespaces t, (select ff.tablespace_name,sum(ff.free_bytes) free_bytes,sum(ff.free_blocks) free_blocks from (SELECT fs.tablespace_name, SUM(fs.bytes) free_bytes, SUM(fs.blocks) free_blocks FROM sys.dba_free_space fs,sys.dba_data_files dfs where fs.file_id=dfs.file_id and fs.tablespace_name='"+TABLESPACE_NAME+"' GROUP BY fs.tablespace_name,dfs.autoextensible) ff group by tablespace_name) f, (select dff.tablespace_name,sum(dff.allocated_bytes) allocated_bytes,sum(dff.max_free_bytes) max_free_bytes from (select tablespace_name,autoextensible,sum(decode(sign(maxbytes-bytes),1,maxbytes,bytes)) allocated_bytes,sum(decode(sign(maxbytes-bytes),1,abs(maxbytes-bytes),0)) max_free_bytes from dba_data_files where tablespace_name='"+TABLESPACE_NAME+"' group by tablespace_name,autoextensible) dff group by tablespace_name) df WHERE t.tablespace_name = f.tablespace_name(+) and t.tablespace_name=df.tablespace_name(+) and t.tablespace_name='"+TABLESPACE_NAME+"'),(SELECT SUM(rw.phyrds) phyrds, SUM(rw.phywrts) phywrts FROM sys.dba_data_files drw, V$filestat rw WHERE drw.file_id = rw.file# and drw.tablespace_name = '"+TABLESPACE_NAME+"' GROUP BY drw.tablespace_name)")
for row in c:
name, log, content, status, usedbytes, freebytes, free_blocks, reads, writes = row
if name == TABLESPACE_NAME :
self.data['logging'] = log
self.data['content']=content
self.data['tablespace_status']=status
self.data['used_space']=convertToMB(usedbytes)
self.data['free_space']=convertToMB(freebytes)
self.data['reads']=reads
self.data['writes']=writes
self.data['free_blocks']=free_blocks
self.data['tablespace_usage_percent']=round((float(usedbytes)/float(usedbytes+freebytes))*100,2)
self.data['tablespace_name']=name
else:
self.data['status'] = 0
self.data['msg'] = 'Please check the Tablespace Name in the configuration section'
except Exception as e:
self.data['status'] = 0
self.data['msg'] = str(e)
finally:
if c!= None : c.close()
if conn != None : conn.close()
return self.data
