def conn_actions(request):
hostname = request.POST['hostname']
username = request.POST['username']
password = request.POST['password']
service = request.POST['servicename']
dsn_tns = cx_Oracle.makedsn(hostname, 1521, service)
if request.POST['action'] == 'Test':
message = 'TEST: ' + 'Hostname: ' + hostname + ' username: '******' password: '******' service: ' + service
if username == 'sys':
db = cx_Oracle.connect(username, password, dsn_tns, mode = cx_Oracle.SYSDBA)
print db.version
else:
db = cx_Oracle.connect(username, password, dsn_tns)
print db.version
else:
message = 'SAVE: ' + 'Hostname: ' + hostname + ' username: '******' password: '******' service: ' + service
name = username + '@' + service
try:
n = DbConnections(conname=name,hostname=hostname,username=username,password=password,servicename=service)
n.save()
message = message + ' OK SAVED ...'
return HttpResponseRedirect('/')
except:
pass
return HttpResponse(message)
def __init__(self, host, port, username, password, db=None,
service_name=None, charset="utf8", flag=True):
self.host = host
self.port = port
self.username = username
self.password = password
self.db = db
if flag:
if int(self.port) != 1521:
self.db = db
self.conn = MySQLdb.connect(host=self.host,
port=int(self.port),
user=self.username,
passwd=self.password, db=self.db,
charset=charset)
self.cursor = self.conn.cursor()
else:
import cx_Oracle
# self.sid = sid
self.service_name = service_name
if self.db:
self.dsn = cx_Oracle.makedsn(self.host,
str(self.port), self.db)
self.conn = cx_Oracle.connect(
self.username, self.password, self.dsn)
elif self.service_name:
self.handle = "%s:%s/%s" % \
(self.host, str(self.port), self.service_name)
self.conn = cx_Oracle.connect(
self.username, self.password, self.handle)
self.cursor = self.conn.cursor()
else:
self.db = db
def connect(self, conn_info):
"""
Method: connect
Description: 实现数据库的连接
Parameters:
1. conn_info: 数据库连接信息
"""
user = conn_info.info['username']
password = conn_info.info['password']
sid = None
service_name = None
if 'db' in conn_info.info:
sid = conn_info.info['db']
if 'service_name' in conn_info.info:
service_name = conn_info.info['service_name']
host = conn_info.info['host']
port = conn_info.info['port']
kargs = {}
if conn_info.info['sysdba']:
kargs['mode'] = cx_Oracle.SYSDBA
if host is None:
self._connection = cx_Oracle.connect(user=user, password=password, dsn=sid, **kargs)
return
dsn = self._makedsn(host, port, sid, service_name = service_name)
#print("dsn=%s (%s:%s)" % (dsn, user, password))
self._connection = cx_Oracle.connect(user=user, password=password, dsn=dsn, **kargs)
def __connect(self):
"""
Function: __connect
Summary: InsertHere
Examples: self.__connect()
Attributes:
@param (self):Call __connect metod Oracle.__connect()
Returns: connection
"""
# Set lang environment
NLS_LANG = config.oracle['NLS_LANG']
os.environ['NLS_LANG'] = NLS_LANG
if not cx_oracle_exists:
msg = """The cx_Oracle module is required. 'pip install cx_Oracle' \
should do the trick. If cx_Oracle is installed, make sure ORACLE_HOME \
& LD_LIBRARY_PATH is set"""
logwrite.LogWrite(logmessage=msg, loglevel='errorLogger').write_log()
dsn = cx_Oracle.makedsn(host=self.host, port=self.port, service_name=self.instance)
try:
if self.mode == 'sysdba' or self.username == 'sys':
self.connection = cx_Oracle.connect(self.username, self.password, dsn, \
mode=cx_Oracle.SYSDBA)
else:
self.connection = cx_Oracle.connect(self.username, self.password, dsn)
except cx_Oracle.DatabaseError as cx_msg:
msg = 'Could not connect to database: %s, dsn: %s ' % (cx_msg, dsn)
logwrite.LogWrite(logmessage=msg, loglevel='errorLogger').write_log()
else:
logwrite.LogWrite(logmessage="Connect oracle "+ self.connection.version+ \
" successfully!", loglevel='infoLogger').write_log()
return self.connection
def __init__(self,user=None,pwd=None,tnsname=None,log=None):
self.user = user
self.pwd = pwd
self.tnsname = tnsname
self.log = log
if not self.user :
if os.path.isfile('setini.ini'):
self.config = Configini('setini.ini')
self.user,self.pwd,self.tnsname=self.config.Get('oracle',['user', 'pwd', 'tnsname'])
else:
self.user = '******'
self.pwd = 'SIS'
self.tnsname = 'SIS'
if (self.user and self.pwd and self.tnsname):
#print(type(self.user),type(self.tnsname),type(self.tnsname))
try:
self.conn = cx_Oracle.connect(self.user, self.pwd, self.tnsname)
except cx_Oracle.DatabaseError as e:
if self.log:
self.log.Info(e)
else:
raise OracleError(e)
elif self.user:
try:
self.conn = cx_Oracle.connect(self.user)
except cx_Oracle.DatabaseError as e:
if self.log:
self.log.Info(e)
else:
raise OracleError(e)
if self.log:
self.log.Info('Oracle初始化连接成功')
def get_connection(repo_name):
SID = service_name = None
if repo_name.lower() == 'odsi' :
host = import_properties['odsi_host']
port = import_properties['odsi_port']
if 'odsi_sid' in import_properties :
SID = import_properties['odsi_sid']
else :
service_name = import_properties['odsi_service_name']
user_name = import_properties['odsi_username']
password = import_properties['odsi_password']
else :
host = import_properties['discovery_host']
port = import_properties['discovery_port']
if 'discovery_sid' in import_properties :
SID = import_properties['discovery_sid']
else :
service_name = import_properties['discovery_service_name']
user_name = import_properties['discovery_username']
password = import_properties['discovery_password']
if service_name :
connection_str = user_name + '/' + password + '@' + host + '/' + service_name
con = cx_Oracle.connect(connection_str)
else :
dsn_tns = cx_Oracle.makedsn(host, port, SID)
con = cx_Oracle.connect(user_name, password, dsn_tns)
return con
def __init__(self, connectString, mode=""):
# Instance attributs
self.connection = None
self.cursor = None
# Read Conf
self.conf = PysqlConf.getConfig()
# Keep connection string to allow future connection
self.connectString = connectString
# Connect to Oracle
try:
if mode == "sysoper":
try:
self.connection = connect(self.connectString, mode=SYSOPER, threaded=True)
except (DatabaseError) as e:
print(CYAN + _("Connected to an idle instance") + RESET)
self.connection = connect(self.connectString, mode=SYSOPER | PRELIM_AUTH, threaded=True)
elif mode == "sysdba":
try:
self.connection = connect(self.connectString, mode=SYSDBA, threaded=True)
except (DatabaseError) as e:
print(CYAN + _("Connected to an idle instance") + RESET)
self.connection = connect(self.connectString, mode=SYSDBA | PRELIM_AUTH, threaded=True)
else:
self.connection = connect(self.connectString, threaded=True)
except (DatabaseError, RuntimeError, InterfaceError) as e:
raise PysqlException(_("Cannot connect to Oracle: %s") % e)
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 connection(self,threaded =True, stopIfError=False):
'''
Connection to the database
'The threaded argument is expected to be a boolean expression which indicates whether or not Oracle
should use the mode OCI_THREADED to wrap accesses to connections with a mutex. Doing so in single threaded
applications imposes a performance penalty of about 10-15% which is why the default is False.'
If stopIfError == True, stop if connection error
'''
try:
if self.args['SYSDBA'] == True :
self.args['dbcon'] = cx_Oracle.connect(self.args['connectionStr'], mode=cx_Oracle.SYSDBA,threaded=threaded)
elif self.args['SYSOPER'] == True :
self.args['dbcon'] = cx_Oracle.connect(self.args['connectionStr'], mode=cx_Oracle.SYSOPER,threaded=threaded)
else :
self.args['dbcon'] = cx_Oracle.connect(self.args['connectionStr'],threaded=threaded)
self.args['dbcon'].autocommit = True
if self.remoteOS == '' and self.oracleDatabaseversion=='' : self.loadInformationRemoteDatabase()
return True
except Exception, e:
if self.ERROR_CONN_IMPOSS in str(e) or self.ERROR_UNABLE_TO_ACQUIRE_ENV in str(e):
logging.critical("Impossible to connect to the remost host")
exit(EXIT_BAD_CONNECTION)
elif self.ERROR_NOT_SYSDBA in str(e):
logging.info("Connection as SYS should be as SYSDBA or SYSOPER, try to connect as SYSDBA")
self.args['SYSDBA'] = True
return self.connection(threaded=threaded, stopIfError=stopIfError)
elif self.ERROR_INSUFF_PRIV_CONN in str(e):
logging.info("Insufficient privileges, SYSDBA or SYSOPER disabled")
self.args['SYSDBA'] = False
self.args['SYSOPER'] = False
return self.connection(threaded=threaded, stopIfError=stopIfError)
elif stopIfError == True:
logging.critical("Impossible to connect to the remote database: {0}".format(self.cleanError(e)))
exit(EXIT_BAD_CONNECTION)
else : return ErrorSQLRequest(e)
def test_find_one_with_lock(self):
db_conn = cx.connect(
'%s/%s@%s' % ('risk', 'oracle123!', '180.2.31.130:1521/interdb1'))
dao = RiskOrderDao(db_conn)
order = dao.find_one_with_lock(local_order_no='81905070000009',
seat_id='701211')
db2 = cx.connect('%s/%s@%s' %
('risk', 'oracle123!', '180.2.31.130:1521/interdb1'))
dao2 = RiskOrderDao(db2)
try:
order2 = dao2.find_one_with_lock(nowait=True,
local_order_no='81905070000009',
seat_id='701211')
print(order2)
order2.order_no = '200200'
db_conn.commit()
except Exception as e:
print(e)
db2.rollback()
finally:
db2.close()
order.order_no = '100100'
order.update()
db_conn.commit()
def create_connect_oracle(self):
"""create and return a connection"""
dsn = cx_Oracle.makedsn(host=self.host,
port=self.port,
sid=self.sid
)
if self.mode:
con = cx_Oracle.connect(user=self.user,
password=self.passwd,
dsn=dsn,
threaded=True,
mode=cx_Oracle.SYSDBA
)
else:
con = cx_Oracle.connect(user=self.user,
password=self.passwd,
dsn=dsn,
threaded=True
)
con.clientinfo = 'cx_Oracle in Python'
con.module = 'cx_Oracle demo'
con.action = 'BatchJob #1'
return con
def importdata_text(dbstr,tablename,fileinfo,filesplit):
if dbstr=="tran1" :
connect=cx_Oracle.connect('tran1','bfbusicrm','132.80.247.18:1521/ywqydb')
elif dbstr=="ngcrm" :
connect=cx_Oracle.connect('uop_crm1','ailkcubj','132.80.241.214:11521/ngcrm')
couttotal = len(open(fileinfo,'rU').readlines())
instance = open(fileinfo)
lines =[]
countpercent=0
countnow=0
column=0
column_str=""
for line in instance.readlines():
column=len(line.split(filesplit))
lines.append(line.split(filesplit))
countpercent+=1
countcomp=countpercent*100/couttotal
if countcomp>countnow :
tem='%d' %countcomp
print "load %s%s...." %(tem,'%')
countnow=countcomp
instance.close()
for i in range(0,column):
column_str+=":%d,"%(i+1)
column_str = column_str[:-1]
var = '''insert into %s values(%s)''' % (tablename,column_str)
cursor = connect.cursor()
cursor.executemany(var,lines);
cursor.close();
connect.commit();
connect.close();
def conexion(ubicacion):
'''
conexion
Parametros de Entrada: String con el nombre de la base de datos.
Parametros de salida: Objeto tipo ConexioDB
'''
if ubicacion.lower() == 'pre-opendata-oracle':
devolver = ConexionDB(cx_Oracle.connect(configuracion.OPENDATA_USR + "/" + configuracion.OPENDATA_PASS + "@" + configuracion.OPENDATA_CONEXION_BD_PRE),"oracle")
elif ubicacion.lower() == 'pro-opendata-oracle':
devolver = ConexionDB(cx_Oracle.connect(configuracion.OPENDATA_USR + "/" + configuracion.OPENDATA_PASS + "@" + configuracion.OPENDATA_CONEXION_BD),"oracle")
elif ubicacion.lower() == 'des-opendata-oracle':
devolver = ConexionDB(cx_Oracle.connect(configuracion.OPENDATA_USR + "/" + configuracion.OPENDATA_PASS + "@" + configuracion.OPENDATA_CONEXION_BD_DES),"oracle")
elif ubicacion.lower() == 'app1': #Es la conexion de storage.py correspondiente a app1
devolver = ConexionDB(cx_Oracle.connect(configuracion.AST_USR + "/" + configuracion.AST_PASS + "@" + configuracion.AST1_CONEXION_BD),"oracle")
elif ubicacion.lower() == 'app2': # Es la conexion de storage.py correspondiente a app2
devolver = ConexionDB(cx_Oracle.connect(configuracion.AST_USR + "/" + configuracion.AST_PASS + "@" + configuracion.AST2_CONEXION_BD),"oracle")
elif ubicacion.lower() == 'app3':# Es la conexion de storage.py correspondiente a app3 que es la de senderos
devolver = ConexionDB(psycopg2.connect(configuracion.AST3_CONEXION_BD),"postgre")
elif ubicacion.lower() == 'opendata-postgre':
devolver= ConexionDB(psycopg2.connect(configuracion.OPENDATA_POSTGRE_CONEXION_BD),"postgre")
elif (ubicacion.lower() == 'app4'): # Es la conexion de storage.py correspondiente a app4
devolver = ConexionDB(pymssql.connect(host='bov-domus-01.aragon.local', user='******', password='******', database='BD_COLECCION_WEB'),"sqlserver")
elif (ubicacion.lower() == 'app5'): # Conexion para MySLQ app5
devolver = ConexionDB(MySQLdb.connect(host='194.179.110.14',port=3306, user='******', passwd='0p3n-DATA',db='webiaf'),"mysql")
else:
print 'No hay conexion para el tipo para la ubicacion : ', ubicacion
return None;
return devolver
def _cursor(self, settings):
cursor = None
if not self._valid_connection():
if len(settings.DATABASE_HOST.strip()) == 0:
settings.DATABASE_HOST = 'localhost'
if len(settings.DATABASE_PORT.strip()) != 0:
dsn = Database.makedsn(settings.DATABASE_HOST, int(settings.DATABASE_PORT), settings.DATABASE_NAME)
self.connection = Database.connect(settings.DATABASE_USER, settings.DATABASE_PASSWORD, dsn, **self.options)
else:
conn_string = "%s/%s@%s" % (settings.DATABASE_USER, settings.DATABASE_PASSWORD, settings.DATABASE_NAME)
self.connection = Database.connect(conn_string, **self.options)
cursor = FormatStylePlaceholderCursor(self.connection)
# Set oracle date to ansi date format. This only needs to execute
# once when we create a new connection.
cursor.execute("ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD' "
"NLS_TIMESTAMP_FORMAT = 'YYYY-MM-DD HH24:MI:SS.FF'")
try:
self.oracle_version = int(self.connection.version.split('.')[0])
except ValueError:
pass
try:
self.connection.stmtcachesize = 20
except:
# Django docs specify cx_Oracle version 4.3.1 or higher, but
# stmtcachesize is available only in 4.3.2 and up.
pass
if not cursor:
cursor = FormatStylePlaceholderCursor(self.connection)
# Default arraysize of 1 is highly sub-optimal.
cursor.arraysize = 100
return cursor
def db_connect(envir):
# # EXAMPLE
# print("Connecting to AT prod database")
# connection = db_connect('prod')
# print(" found version "+ str(connection.version))
# querystring = "select * from dima_occasion where rownum = 1"
# cursor = connection.cursor()
# cursor.execute(querystring)
# if len(cursor.fetchone()) >=1:
# print(' data fetched')
# else:
# print(' NO data fetched')
# connection.close()
db_connectstrings = {
'dev' :'dmcg/[email protected]/ATTRCKD'
,'test':'dmcg/[email protected]/ATTRCKT'
,'acc' :'dmcg/[email protected]/ATTRCKA'
,'prod':'dmcg/[email protected]/ATTRCKP'
}
if envir == 'dev':
dsnStr = cx_Oracle.makedsn("ATTRCKD.lsnr.persgroep.be", "1521", "ATTRCKD")
con = cx_Oracle.connect(user="******", password="******", dsn=dsnStr)
return con
elif envir == 'test':
dsnStr = cx_Oracle.makedsn("ATTRCKT.lsnr.persgroep.be", "1521", "ATTRCKT")
con = cx_Oracle.connect(user="******", password="******", dsn=dsnStr)
return con
else:
con = cx_Oracle.connect(db_connectstrings[envir])
return con
def connectORA(LOG):
try:
ip = '10.196.1.140'
port = 1521
SID = 'vrl1'
dsn_tns = cxo.makedsn(ip,port,SID)
#con = cxo.connect('vlps/vlps@failover_db.virologic.com/vrl1') # get a connection or raise exception
vlps = cxo.connect('vlps','vlps',dsn_tns)
vlps_cursor = vlps.cursor() # connection returns a cursor object which is used to perform queries
LOG.write(gt()+"\tSuccessful connection made to Oracle as vlps\n")
except:
exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
LOG.write(gt()+"\tvlps Oracle Database connection failed!\n ->%s" % (exceptionValue))
#sys.exit(gt()+"\tOracle Database connection failed!\n ->%s" % (exceptionValue))
return 'failed','',''
try:
etag = cxo.connect('etag','etag',dsn_tns)
etag_cursor = etag.cursor()
LOG.write(gt()+"\tSuccessful connection made to Oracle as etag\n")
except:
exceptionType, exceptionValue, exceptionTraceback = sys.exc_info()
LOG.write(gt()+"\tetag Oracle Database connection failed!\n ->%s" % (exceptionValue))
return 'failed','',''
return 'ok', vlps_cursor, etag_cursor
def nodeconnect(connstr,preference=None):
"""connect to oracle, possibly looking for a particular node"""
try:
if preference is None:
return cx_Oracle.connect(connstr)
else:
while True:
conn=cx_Oracle.connect(connstr)
myinstance=instance(conn)[0]
print now(),'got instance...',myinstance
if myinstance==preference:
return conn
else:
conn.close()
except cx_Oracle.DatabaseError,e:
# ORA-01033: ORACLE initialization or shutdown in progress
# ORA-12537: TNS:connection closed
# ORA-12528: TNS:listener: all appropriate instances are blocking
# new connections
# ORA-12521: TNS:listener does not currently know of instance
# requested in connect descriptor
# ORA-12520: TNS:listener could not find available handler for
# requested type of server
# ORA-12514: TNS:listener does not currently know of service
# requested in connect descriptor
if e.message.code in [1033,12514,12537,12528,12521,12520]:
print 'RETRYING:', e.message.message.strip()
time.sleep(2)
return nodeconnect(connstr,preference)
else:
raise(e)
def oracle_handling(ip, port, username, passwd, db_name, query, sid_type, db_type):
try:
cursor = None
result = None
dbconn = None
is_socket_up = False
is_socket_up = socket_monitor(ip, port)
if is_socket_up:
if sid_type == 2:
dsn = cx_Oracle.makedsn(ip, port, db_name)
dbconn = cx_Oracle.connect(username, passwd, dsn)
else:
dbconn = cx_Oracle.connect(username, passwd, '%s:%s/%s'%(ip, port, db_name))
if dbconn:
cursor = dbconn.cursor()
cursor.execute(query)
result = cursor.fetchall()
return True, "Success"
else:
return False, "Could not Get connection object"
else:
return False, "Socket Connection Fail"
except Exception, ex:
return False, "Exception while making connection"
def __init__(self, connectString, mode=""):
# Instance attributs
self.connection = None
self.cursor = None
# Read Conf
self.conf = PysqlConf.getConfig()
# Keep connection string to allow future connection
self.connectString = connectString.encode(self.conf.getCodec(), "ignore")
# Connect to Oracle
try:
if mode == "sysoper":
try:
self.connection = connect(self.connectString, mode=SYSOPER)
except (DatabaseError), e:
print CYAN + _("Connected to an idle instance") + RESET
self.connection = connect(self.connectString, mode=SYSOPER | PRELIM_AUTH)
elif mode == "sysdba":
try:
self.connection = connect(self.connectString, mode=SYSDBA)
except (DatabaseError), e:
print CYAN + _("Connected to an idle instance") + RESET
self.connection = connect(self.connectString, mode=SYSDBA | PRELIM_AUTH)
def importdata_excel(dbstr,tablename,fileinfo):
if dbstr=="tran1" :
connect=cx_Oracle.connect('tran1','bfbusicrm','132.80.247.18:1521/ywqydb')
elif dbstr=="ngcrm" :
connect=cx_Oracle.connect('uop_crm1','ailkcubj','132.80.241.214:11521/ngcrm')
data = xlrd.open_workbook(fileinfo)
table = data.sheets()[0]
nrows = table.nrows #行数
ncols = table.ncols #列数
lines =[]
countpercent=1
countnow=0
column_str=""
for rownum in range(1,nrows):
row = table.row_values(rownum)
lines.append(row)
countpercent+=1
countcomp=countpercent*100/nrows
if countcomp>countnow :
tem='%d' %countcomp
print "load %s%s...." %(tem,'%')
countnow=countcomp
for i in range(0,ncols):
column_str+=":%d,"%(i+1)
column_str = column_str[:-1]
var = '''insert into %s values(%s)''' % (tablename,column_str)
cursor = connect.cursor()
cursor.executemany(var,lines);
cursor.close();
connect.commit();
connect.close();
def main():
"""main program"""
p = optparse.OptionParser(usage="usage: %prog options pkg...")
p.add_option("-C","--conn",action="store",type="string",dest="conn",
help="Database connection string")
p.add_option("-O","--output",action="store",type="string",dest="output",
help="Output file")
p.add_option("","--lang",action="store",type="string",dest="lang",
help="Language binding")
p.add_option("-P","--pass",action="store",type="string",dest="password",
help="Database password")
p.add_option("","--dump",action="store_true",dest="dump",default=False,
help="dump datastructures for debugging")
p.add_option("","--sys",action="store_true",dest="sys",default=False,
help="connect as sysdba")
p.add_option("","--helpfmt",action="store_true",dest="helpfmt",
default=False, help="show help for formatting")
(opts,args) = p.parse_args()
if not (opts.conn and args):
p.print_help()
sys.exit(1)
oraconnre = re.compile("^([^/@]*)(/([^@]*))?(@(.*))?$")
user,password,host=oraconnre.match(opts.conn).groups()[0::2]
if opts.password:
password=opts.password
if not password:
password=getpass.getpass()
connstr="%s/%s"%(user,password)
if host:
connstr+="@%s" % host
if opts.sys:
conn=cx_Oracle.connect(connstr,mode=cx_Oracle.SYSDBA)
else:
conn=cx_Oracle.connect(connstr)
if opts.output:
output=open(opts.output,"w")
else:
output=sys.stdout
if opts.dump:
trans=Trans(conn,output)
trans.dump(args)
sys.exit(0)
if opts.lang is None or opts.lang=="python" or opts.lang=='py':
trans=PyTrans(conn,output)
elif opts.lang=='cxx':
trans=None
elif opt.lang=='rb':
trans=None
else:
print >>sys.stderr,'unsupported language:',opt.lang
sys.exit(1)
trans.dofile(args)
sys.exit(0)
def _connect(self,host=None,user=None,password=None,database=None):
if user == None and password == None and database == None:
self._conn = cx_Oracle.connect('','',host)
else:
if host == None: self._conn = cx_Oracle.connect(user,password)
else: self._conn = cx_Oracle.connect(user,password,host)
self._query("ALTER SESSION SET NLS_DATE_FORMAT='"+self.NLS_DATE_FORMAT+"'")
def __try_to_connect__(self,args):
'''
'''
import cx_Oracle
try:
cx_Oracle.connect("{0}/{1}@{2}:{3}/{4}".format(self.args['user'],self.args['password'],self.args['server'],self.args['port'],self.args['sid']))
except Exception, e:
pass
def demo(conn,curs):
# normal user connect -- connstr looks like scott/tiger@orcl
conn = cx_Oracle.connect(connstr)
# normal user connect, three parms
conn = cx_Oracle.connect('scott', 'tiger', 'orcl')
# connect to the database as sysdba
conn = cx_Oracle.connect('sys/secret@mydb', mode=cx_Oracle.SYSDBA)
def getCursor():
if DBUtil.con is None:
if ('--local' in sys.argv):
# SSH port forwarding is done, use localhost
DBUtil.con = oracle.connect("aaron/ppppppp@localhost/orcl")
else:
# We are running on UF domain, connect to oracle.ci...
DBUtil.con = oracle.connect("aaron/[email protected]/orcl")
return DBUtil.con.cursor()
def get_db(self):
"""return cx_Oracle Connection() instance
"""
if self.oracle_db is None:
if self.mode:
self.oracle_db = cx_Oracle.connect(self.user, self.password, self.dsn, self.mode)
else:
self.oracle_db = cx_Oracle.connect(self.user, self.password, self.dsn)
return self.oracle_db
def __init__(self) :
# Connect to the Database
hostname = socket.gethostname()
if hostname.find('lxplus') > -1: self.dsn_ = 'cms_omds_adg' #offline
else: self.dsn_ = 'cms_omds_lb' #online
orcl = cx_Oracle.connect(user='******',password='******',dsn=self.dsn_)
orcl = cx_Oracle.connect(user='******',password='******',dsn=self.dsn_)
# Create a DB cursor
self.curs = orcl.cursor()
def __try_to_connect__(self, user):
'''
Establish a connection to the database
'''
import cx_Oracle
try:
connectString = "{0}/{1}@{2}:{3}/{4}".format(user, 'aaaaaaa', self.args['server'], self.args['port'], self.args['sid'])
logging.debug("Connecting with {0}".format(connectString))
cx_Oracle.connect(connectString)
except Exception, e:
pass
def cursor(self):
from django.conf import settings
if not self._valid_connection():
if len(settings.DATABASE_HOST.strip()) == 0:
settings.DATABASE_HOST = 'localhost'
if len(settings.DATABASE_PORT.strip()) != 0:
dsn = Database.makedsn(settings.DATABASE_HOST, int(settings.DATABASE_PORT), settings.DATABASE_NAME)
self.connection = Database.connect(settings.DATABASE_USER, settings.DATABASE_PASSWORD, dsn, **self.options)
else:
conn_string = "%s/%s@%s" % (settings.DATABASE_USER, settings.DATABASE_PASSWORD, settings.DATABASE_NAME)
self.connection = Database.connect(conn_string, **self.options)
return FormatStylePlaceholderCursor(self.connection)
def __try_to_connect__(self, args):
"""
"""
import cx_Oracle
try:
cx_Oracle.connect(
"{0}/{1}@{2}:{3}/{4}".format(
self.args["user"], self.args["password"], self.args["server"], self.args["port"], self.args["sid"]
)
)
except Exception, e:
pass
# @Time : 2018/11/16 14:41
# @Author : lg
# @File : initConstructData.py
import time
import cx_Oracle
import pandas as pd
import pandas.io.sql as psql
from AllSql_2014 import table_list_2014
from AllSql_2014_big import table_list_2014_big
from AllSql_2015 import table_list_2015
from AllSql_all import table_list_all
conn = cx_Oracle.connect('tax/[email protected]:1521/tax')
print(conn.version)
all_table_prefix = "2014_big_"
# 数据获取
def get_feature(key, conn):
lowKey = key.lower()
# 全局变量table_list是dict, key是当前key
sentence = table_list_2014_big[key]
for fname in [all_table_prefix]:
sqlline = sentence
starttime = time.time()
s1 = time.strftime("%Y-%m-%d-%H-%M-%S")
print("current table is %s, time: %s" % (key, s1))
def setUp(self):
global cx_Oracle, TestEnv
self.connection = cx_Oracle.connect(TestEnv.USERNAME, TestEnv.PASSWORD,
TestEnv.TNSENTRY)
self.cursor = self.connection.cursor()
self.cursor.arraysize = TestEnv.ARRAY_SIZE
import os
import cx_Oracle
import matplotlib.pyplot as plt
os.chdir("C:\\instantclient_18_5")
# Connection string
connection = cx_Oracle.connect("hr", "inf5180", "144.217.163.57/XE")
sql = "SELECT job_title, avg(SALARY) " \
"FROM EMPLOYEES E JOIN JOBS J " \
"ON E.JOB_ID = J.JOB_ID group by JOB_TITLE"
curs = connection.cursor()
curs.execute(sql)
departments = []
name = []
for row in curs:
departments.append(row[0])
name.append(row[1])
curs.close()
connection.close()
plt.bar(departments, name, color="red")
plt.xticks(departments, rotation="vertical")
plt.xlabel("Job Title")
plt.ylabel("Average Salary")
plt.title("Average Salary per Job Title")
# -*- encoding:utf8 -*-
# coding:utf8
import os
import cx_Oracle
import pandas as pd
import numpy as np
import datetime
from dateutil.relativedelta import relativedelta
[userName, password, hostIP, dbName,
tablePrefix] = ['reader', 'reader', '172.16.50.232:1521', 'dfcf', 'wind']
fund_db = cx_Oracle.connect(user=userName,
password=password,
dsn=hostIP + '/' + dbName)
# 投研平台库
[userNamepif, passwordpif, hostIPpif,
dbNamepif] = ['pra_info', 'pra_info', '172.16.126.23:1521', 'pra']
# [userNamepif, passwordpif, hostIPpif, dbNamepif] = ['pif', 'pif', '172.16.125.151', 'pif']
try:
fund_dbpra = cx_Oracle.connect(user=userNamepif,
password=passwordpif,
dsn=hostIPpif + '/' + dbNamepif)
cu_pra = fund_dbpra.cursor()
except cx_Oracle.DatabaseError as e:
print('数据库链接失败')
path = os.path.dirname(os.path.realpath(__file__)) + '/'
def main():
con = cx_Oracle.connect('om', 'om', 'xe')
cur = con.cursor()
quit = False
while quit == False:
option = raw_input("== YELP ACADEMIC DATASET ANALYTICS TOOL ==\n\
Choose one of the following options below (e.g. '3'):\n\
>>OPTION 1: Categorical breakdown by city of stars by review count\n\
>>OPTION 2: Categorical breakdown by city of reviews by review count\n\
>>OPTION 3: Average rating by franchise\n\
>>OPTION 4: Average rating across all users\n\
>>OPTION 5: Categorical count distribution by average stars for all users\n\
>>OPTION 6: Average stars by year for a particular user\n\
>>QUIT: Quit application\n\n>> ")
if option == "1":
input_city = raw_input("Enter city name: ")
input_city = input_city.rstrip('\n')
star_distribution = cur.execute(
"SELECT COUNT(*) AS STAR_TOTAL FROM BUSINESS WHERE CITY = \'" +
input_city + "\' GROUP BY STARS ORDER BY STARS")
star_distribution = list(star_distribution)
star_1 = star_distribution[0][0]
star_15 = star_distribution[1][0]
star_2 = star_distribution[2][0]
star_25 = star_distribution[3][0]
star_3 = star_distribution[4][0]
star_35 = star_distribution[5][0]
star_4 = star_distribution[6][0]
star_45 = star_distribution[7][0]
star_5 = star_distribution[8][0]
star_list = [
'1 ', '1.5', '2 ', '2.5', '3 ', '3.5', '4 ', '4.5', '5 '
]
avg_stars = cur.execute(
"SELECT COUNT(*) AS CITY_REVIEW_COUNT FROM BUSINESS WHERE CITY = \'"
+ input_city + "\'")
avg_stars = list(avg_stars)
avg_stars = avg_stars[0][0]
avg_stars = float(avg_stars)
print("Rating | Total Reviews in Range | Percentile of Total")
for i in range(len(star_distribution)):
star = star_distribution[i][0]
print(" %s %d %.2f" %
(star_list[i], star, (float(star) / avg_stars) * 100))
break_loop = raw_input("Press any key to continue.....")
if option == "2":
input_city = raw_input("Enter city name: ")
input_city = input_city.rstrip('\n')
rev_count_100 = cur.execute(
"SELECT \'100\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'"
+ input_city + "\' and REVIEW_COUNT < 101 GROUP BY \'100\'")
rev_count_100 = list(rev_count_100)
rev_count_100 = rev_count_100[0][1]
rev_count_200 = cur.execute(
"SELECT \'200\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'"
+ input_city +
"\' and REVIEW_COUNT BETWEEN 101 AND 200 GROUP BY \'200\'")
rev_count_200 = list(rev_count_200)
rev_count_200 = rev_count_200[0][1]
rev_count_300 = cur.execute(
"SELECT \'300\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'"
+ input_city +
"\' and REVIEW_COUNT BETWEEN 201 AND 300 GROUP BY \'300\'")
rev_count_300 = list(rev_count_300)
rev_count_300 = rev_count_300[0][1]
rev_count_400 = cur.execute(
"SELECT \'400\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'"
+ input_city +
"\' and REVIEW_COUNT BETWEEN 301 AND 400 GROUP BY \'400\'")
rev_count_400 = list(rev_count_400)
rev_count_400 = rev_count_400[0][1]
rev_count_500 = cur.execute(
"SELECT \'500\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'"
+ input_city +
"\' and REVIEW_COUNT BETWEEN 401 AND 500 GROUP BY \'500\'")
rev_count_500 = list(rev_count_500)
rev_count_500 = rev_count_500[0][1]
rev_count_600 = cur.execute(
"SELECT \'600\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'"
+ input_city +
"\' and REVIEW_COUNT BETWEEN 501 AND 600 GROUP BY \'600\'")
rev_count_600 = list(rev_count_600)
rev_count_600 = rev_count_600[0][1]
rev_count_700 = cur.execute(
"SELECT \'700\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'"
+ input_city +
"\' and REVIEW_COUNT BETWEEN 601 AND 700 GROUP BY \'700\'")
rev_count_700 = list(rev_count_700)
rev_count_700 = rev_count_700[0][1]
rev_count_800 = cur.execute(
"SELECT \'800\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'"
+ input_city +
"\' and REVIEW_COUNT BETWEEN 701 AND 800 GROUP BY \'800\'")
rev_count_800 = list(rev_count_800)
rev_count_800 = rev_count_800[0][1]
rev_count_900 = cur.execute(
"SELECT \'900\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'"
+ input_city +
"\' and REVIEW_COUNT BETWEEN 801 AND 900 GROUP BY \'900\'")
rev_count_900 = list(rev_count_900)
rev_count_900 = rev_count_900[0][1]
rev_count_1000 = cur.execute(
"SELECT \'1000\' AS REVIEW_RANGE, COUNT(*) AS REVIEW_TOTAL FROM BUSINESS WHERE CITY = \'"
+ input_city +
"\' and REVIEW_COUNT BETWEEN 901 AND 1000 GROUP BY \'1000\'")
rev_count_1000 = list(rev_count_1000)
rev_count_1000 = rev_count_1000[0][1]
review_print_list = [
"0-100", "100-200", "200-300", "300-400", "400-500", "500-600",
"600-700", "800-900", "900-1000"
]
review_count_list = [
rev_count_100, rev_count_200, rev_count_300, rev_count_400,
rev_count_500, rev_count_600, rev_count_700, rev_count_800,
rev_count_900, rev_count_1000
]
rev_count_avg = cur.execute(
"SELECT COUNT(*) FROM BUSINESS WHERE CITY = \'" + input_city +
"\'")
rev_count_avg = list(rev_count_avg)
rev_count_avg = rev_count_avg[0][0]
rev_count_avg = float(rev_count_avg)
#OUTPUT
print("Range | Number of Businesses | Percent of Total")
for i in range(len(review_count_list) - 1):
print(" %s %s %.2f" %
(review_print_list[i], review_count_list[i],
(float(review_count_list[i]) / rev_count_avg) * 100))
break_loop = raw_input("Press any key to continue.....")
if option == "3":
franchise = raw_input("Enter franchise name: ")
franchise = franchise.rstrip('\n')
franch_avg_stars = cur.execute(
"SELECT AVG(STARS), COUNT(*) FROM BUSINESS WHERE NAME_DESC Like \'%"
+ franchise + "%\' ")
franch_avg_stars = list(franch_avg_stars)
franch_stars, franch_count = franch_avg_stars[0]
print("Average Rating for %s: %f" % (franchise, franch_stars))
print("Total Locations of %s Sampled: %d" %
(franchise, franch_count))
break_loop = raw_input("Press any key to continue.....")
if option == "4":
avg_all_users = cur.execute("SELECT AVG(AVERAGE_STARS) FROM USERS")
avg_all_users = list(avg_all_users)
avg_all_users = avg_all_users[0][0]
print("Average rating across all users: %s" % avg_all_users)
break_loop = raw_input("Press any key to continue.....")
if option == "5":
avg_star_2 = cur.execute(
"SELECT AVG(REVIEW_COUNT) FROM USERS WHERE AVERAGE_STARS < 2")
avg_star_2 = list(avg_star_2)
avg_star_2 = avg_star_2[0][0]
avg_star_3 = cur.execute(
"SELECT AVG(REVIEW_COUNT) FROM USERS WHERE AVERAGE_STARS BETWEEN 2 AND 3"
)
avg_star_3 = list(avg_star_3)
avg_star_3 = avg_star_3[0][0]
avg_star_4 = cur.execute(
"SELECT AVG(REVIEW_COUNT) FROM USERS WHERE AVERAGE_STARS BETWEEN 3 AND 4"
)
avg_star_4 = list(avg_star_4)
avg_star_4 = avg_star_4[0][0]
avg_star_5 = cur.execute(
"SELECT AVG(REVIEW_COUNT) FROM USERS WHERE AVERAGE_STARS BETWEEN 4 AND 5"
)
avg_star_5 = list(avg_star_5)
avg_star_5 = avg_star_5[0][0]
#OUTPUT
print("Rating Range | Average Review Count")
print(" < 2 %f" % (avg_star_2))
print(" 2 - 3 %f" % (avg_star_3))
print(" 3 - 4 %f" % (avg_star_4))
print(" 4 - 5 %f" % (avg_star_5))
break_loop = raw_input("Press any key to continue.....")
if option == "6":
#user_id for demo: kGgAARL2UmvCcTRfiscjug
user_id = raw_input("Enter user_id: ")
user_id = user_id.rstrip('\n')
user_by_year = cur.execute(
"SELECT AVG(STARS), to_char(REVIEW_DATE, \'YYYY\') AS REVIEW_YEAR FROM REVIEWS WHERE USER_ID = \'"
+ user_id +
"\' GROUP BY to_char(REVIEW_DATE, \'YYYY\') ORDER BY REVIEW_YEAR"
)
user_by_year = list(user_by_year)
print("")
print("REVIEW YEAR | AVERAGE RATING")
print("")
for i in range(len(user_by_year)):
user_avg_star, review_year = user_by_year[i]
print(" %s %s" % (review_year, user_avg_star))
break_loop = raw_input("Press any key to continue.....")
if option == "quit" or option == "QUIT" or option == "Quit":
print("Quitting application")
cur.close()
quit = True
import tushare as ts
import time
import cx_Oracle
conn = cx_Oracle.connect('stock/123456@localhost:1521/orcl')
def query(conn):
"""
查询数据库获取所有股票code
:return:
"""
cursor = conn.cursor()
sql = "select code from STOCK_BASICS"
rs = cursor.execute(sql)
result = rs.fetchall()
tables = [i[0] for i in result]
print(tables, type(tables))
return tables
def get_data(code, start=None, end=None, index=False):
"""
获取原始数据
:param code:
:param start:
:param end:
:param index:
:return:
"""
end = time.strftime('%Y-%m-%d', time.localtime(time.time()))
mailcontent = []
#这里打开oracle_list文件并读取其中内容
file1 = open(r"/home/oracle/script/oracle_list.txt")
oracle_list = file1.readlines()
file1.close()
#循环读取每行的内容并使用split函数获取到相关数据库信息
for i in oracle_list:
info = i.split()
ipaddress = info[0].strip()
username = info[1].strip()
password = info[2].strip()
port = info[3].strip()
tnsname = info[4].strip()
try:
db = cx_Oracle.connect(username + '/' + password + '@' +
ipaddress + ':' + port + '/' + tnsname,
mode=cx_Oracle.SYSDBA)
except Exception as e:
content = (tnsname + ' is Unreachable,The reason is ' +
str(e)).strip()
mailcontent.append(content)
else:
cursor = db.cursor()
data = oraclesql(cursor)
cursor.close()
db.close()
for i in data:
usage = int(i[4])
if usage >= 90:
tablespace = i[0]
mailcontent.append('Be Careful tablespace ' + tablespace +
def getoracleconntns(tns):
conn = cx_Oracle.connect('baiqiao/ali88@%s'%tns ,mode=cx_Oracle.SYSDBA )
#conn = cx_Oracle.connect('/', mode=cx_Oracle.SYSDBA)
return conn
def getoracleconntnspa(tns):
conn = cx_Oracle.connect('baiqiao/ali88@%s'%tns ,mode = cx_Oracle.SYSDBA | cx_Oracle.PRELIM_AUTH)
#conn = cx_Oracle.connect('/', mode=cx_Oracle.SYSDBA)
return conn
def __init__(self):
self.__db = cx_Oracle.connect(user_name, password, server)
self.__cursor = self.__db.cursor()
'''
Created on 2018. 5. 19.
@author: Administrator
'''
import cx_Oracle
from pandas import Series
import matplotlib.pyplot as plt
id = "oraman/oracle@localhost:1521/xe"
con = cx_Oracle.connect(id)
cur = con.cursor()
sql = "select * from BUNGITABLE"
cur.execute(sql)
Bungi = []
Count = []
total = 0
for pars in cur:
print(pars)
total += pars[1]
Bungi.append(pars[0])
Count.append(pars[1])
RealSeries = []
for parsent in range(len(Count)):
RealSeries.append(Bungi[parsent] + "\n" + str(round(100*(Count[parsent]/total),2)))
print(RealSeries)
def analyze_runs(runs, partitions, end_delay, rerun=False):
# First make connection to database
conn_str = u'cms_trk_r/1A3C5E7G:FIN@cms_omds_lb'
#os.system('getConfDb CONFDB_ONLINE')
#conn_str = os.getenv('CONFDB')
conn = cx_Oracle.connect(conn_str)
c = conn.cursor()
# Loop through list of runs
for run in runs:
run = int(run[3:])
hasEDM = False
hasComm = False
dbValid = False
timeValid = False
print '\n***************************************************'
print 'Starting run', run
# first get run information
c.execute(
u'select PARTITIONNAME,RUNMODE,STARTTIME,ENDTIME from RUN a join PARTITION b on a.PARTITIONID=b.PARTITIONID where RUNNUMBER=:run',
{'run': run})
db_results = c.fetchall()
print db_results
if len(db_results):
(partition, runmode, start, end) = db_results[0]
else:
# run number not in database
print 'Run number', run, 'not in RUN database, aborting run'
continue
# For spy channel, make sure that you check that you retreive all partitions that were used in run
if runmode == 15:
partition = [result[0] for result in db_results]
else:
partition = [partition]
# Make sure that there is an end time in the db
if not end:
print 'Run has not yet finished'
continue
# Now check that the run finished at least N hours ago
# Then make sure all time info in DB makes sense
if (datetime.datetime.now() - end) > end_delay:
timeValid = validate_time(start, end)
else:
print 'Run recently finished, skipping and will check again later'
continue
# Check if run is of the type that we want to run
modes_to_analyze = [2, 4, 5, 6, 7, 11, 14, 15, 16, 17, 21, 27]
# If running certain partitions on different machines, then add CALIBRATION=3,CALIBRATION_DECO=33,CALIBRATION_SCAN=19,CALIBRATION_SCAN_DECO=20 modes
#modes_to_analyze += [3, 19, 20, 33]
if runmode not in modes_to_analyze: continue
# Check if this partition run on machine (only matters if spltting jobs onto srv 29-01 and 30-01)
# Exception is spy run which only gets run on srv-s2b17-XX-01
if any(part in partition[0] for part in partitions) and runmode != 15:
pass
elif runmode == 15 and partitions[0] == 'TI':
pass
else:
continue
# See if a run is currently being analyzed, by looking for the lock file created in analsis script
# If analysis is running, skip run and verify integrity of outputs in later loop
if any([
os.path.exists('/opt/cmssw/Data/lock_{part}_{run}'.format(
part=part, run=run)) for part in partition
]):
continue
# For runs that are stopped and started multiple times, they will produce extra 0 event runs
# For those runs, just ignore them and add the run number to list of good runs
if len(glob.glob('/raid/fff/run{run}/*'.format(run=run))) == 4:
# will have fu.lock, hlt, jsd, and run{run}_ls0000_EoR.jsn
print 'This was an empty run'
write_to_file(run, True)
continue
# first check if EDM file exists for run
if os.path.exists('{rundir}/run{run}/run{run}.root'.format(
rundir=rundir, run=run)):
print 'run was analyzed'
# If EDM file exists, check that analysis finished properly
hasEDM = validate_events(run)
(hasComm, dbValid) = validate_db(run, conn)
hasRaw = len(
glob.glob('{rundir}/run{run}/*.raw'.format(rundir=rundir,
run=run))) > 0
if not hasEDM and hasRaw:
print 'Problem in existing EDM file and raw files still exist.\nWill delete EDM file and re-run analysis'
#os.system('ls {rundir}/run{run}/run{run}.root'.format(rundir=rundir, run=run))
os.system('rm {rundir}/run{run}/run{run}.root'.format(
rundir=rundir, run=run))
elif not hasEDM and not hasRaw:
print '***warning*** Problem validating EDM file but no raw files exist...Will continue with existing file, but problems may ensue'
elif hasEDM and hasRaw:
# EDM file is good and raw still exists, delete the no longer needed raw files to save space
print 'Run has already been analyzed, but raw files still exist. Deleting raw files'
delete_raw(run)
# For spy runs, still need to check that analysis was run on each partition
if not (hasEDM and dbValid) or runmode == 15:
# Pack the raw files together and into EDM format and analyze based on run analysis
print 'Will now perform analysis on run'
if len(partition) == 1:
os.system(
'sh /opt/cmssw/scripts/run_analysis_CC7.sh {run} False True {partition} False False True True'
.format(run=run, partition=partition[0]))
(hasComm, dbValid) = validate_db(run, conn)
else:
# for spy runs need to loop over all partitions
dbValid_part = []
for part in partition:
# Check if we have already succesfully analyzed this partition
if all(validate_db(run, conn, part)):
dbValid_part.append((True, True))
else:
os.system(
'sh /opt/cmssw/scripts/run_analysis_CC7.sh {run} False True {partition} False False True True'
.format(run=run, partition=part))
dbValid_part.append(validate_db(run, conn, part))
# Make sure check returns true for all partitions
hasComm = all([valid[0] for valid in dbValid_part])
dbValid = all([valid[1] for valid in dbValid_part])
hasEDM = validate_events(run)
if hasEDM:
delete_raw(run)
# Finally, make sure all validation checks return True
print '**** Analysis Validation Checks ****'
print 'Run start and end time are consistent:', timeValid
print 'EDM File Good:', hasEDM
print 'Commissioning and source files exist:', hasComm
print 'Analysis added to DB:', dbValid
print '**** Result ****'
if timeValid and hasEDM and dbValid:
print 'Run %d is good\n' % run
write_to_file(run, True)
elif not rerun:
# If any check fails, try running it again
print 'One or more check failed, will re-run over run\n', run
analyze_runs(['run%d' % run], partitions, end_delay, rerun=True)
else:
# If run fails second time, add to list of failed runs and continue
print 'Analysis failed a second time, adding run to fail list\n'
write_to_file(run, False, [timeValid, hasEDM, hasComm, dbValid])
return
return
def get_connection():
conn = cx_Oracle.connect('scott/cf6024584@localhost/orcl') # 用户名/密码@服务器地址/数据库名称
return conn
import tkinter as tk
from tkinter import *
import cx_Oracle
import time
T = time.strftime("%H:%M:%S%p")
con = cx_Oracle.connect("system/vasumalik1")
cur = con.cursor()
win = tk.Tk()
win.geometry("963x649+240+50")
win.title("HOTEL MANGMENT")
font14 = "-family {Courier New} -size 15 -weight normal -slant " \
"roman -underline 0 -overstrike 0"
font10 = "-family {Courier New} -size 10 -weight normal -slant" \
" roman -underline 0 -overstrike 0"
font11 = "-family {ENGRAVERS MT} -size 15 -weight bold -slant " \
"roman -underline 0 -overstrike 0"
font9 = "-family {Segoe UI} -size 9 -weight normal -slant " \
"roman -underline 0 -overstrike 0"
Frame1 = Frame(win)
Frame1.place(relx=0.01, rely=0.05, relheight=0.12, relwidth=0.97)
Frame1.configure(relief=GROOVE)
Frame1.configure(borderwidth="2")
Frame1.configure(relief=GROOVE)
#self.Frame1.configure(background="#ffffff")
Frame1.configure(highlightbackground="#ffffff")
Frame1.configure(highlightcolor="black")
Frame1.configure(width=995)
code2uri_sider = self.merge_dicts(code2uri, uri2uri)
# print u'J05AB01', code2uri_sider[u'J05AB01']
code2ingredients = self.merge_dicts(code2uri_sider, uri2ingredient)
# print u'J05AB01', code2uri_sider[u'J05AB01']
return code2ingredients
if __name__ == '__main__':
# in terminal: python integrate.py ../Desktop/indication.csv ../Desktop/effects.csv ../Desktop/ingredients.csv
# ... or import from SQL (uncomment)
import cx_Oracle
rows = cx_Oracle.connect('datamart', 'datamart',
'10.67.201.10:1521/XE').cursor()
rows.execute('''SELECT med.atc_code
FROM AA_KOP_patient_dates dts
LEFT JOIN medicaties med ON dts.patientnummer = med.patientnummer''')
idx = 0 # we only query atc codes
# enrich
indications, effects, ingredients = atc_enrichment(rows, idx)
# export
io.dict2csv(indications, sys.argv[1])
io.dict2csv(effects, sys.argv[2])
io.dict2csv(ingredients, sys.argv[3])
import cx_Oracle
import csv
import os
os.environ["NLS_LANG"] = ".UTF8"
os.environ[
"NLS_LANG"] = "Russian.AL32UTF8" #"American_America.AL32UTF8" ".UTF8"
dsnStr = cx_Oracle.makedsn("s34.birt.localka", "1231", "bird")
conn = cx_Oracle.connect(user="******",
password="******",
dsn=dsnStr,
encoding='utf-8')
curs = conn.cursor()
printHeader = True
outputFile = open('cohort_analytics.csv', 'w') # 'wb'
output = csv.writer(outputFile, delimiter='|', lineterminator='\n')
sql = '''with store as
(SELECT distinct storeid,store FROM db.store_base),
cohorts as (SELECT min(TO_CHAR(dato,'YYYY-MM')) as mindate,carnumb as users
FROM db.sales WHERE dato >= TO_DATE('2011-01-01','YYYY-MM-DD')
AND dato < TO_DATE('2019-04-01','YYYY-MM-DD') GROUP BY carnumb)
SELECT TO_CHAR(dato,'YYYY-MM') as Period,store, mindate as Cohort, COUNT(distinct carnumb) as Quant_clnt
FROM db.ret INNER JOIN store ON store.storeid=ret.storeid
INNER JOIN cohorts ON cohorts.users=ret.carnumb
WHERE dato >= TO_DATE('2011-01-01','YYYY-MM-DD') AND dato < TO_DATE('2019-04-01','YYYY-MM-DD')
AND b2b_flag=0 GROUP BY TO_CHAR(dato,'YYYY-MM'),store, mindate '''
# Code Sample from the tutorial at https://learncodeshare.net/2015/06/02/select-crud-using-cx_oracle/
# section titled "Extra Fun 1"
# Using the base template, the example code executes a simple query, uses fetchall to retrieve the data
# and displays the results.
import cx_Oracle
import os
connectString = os.getenv(
'DB_CONNECT'
) # The environment variable for the connect string: DB_CONNECT=user/password@database
con = cx_Oracle.connect(connectString)
cur = con.cursor()
statement = 'select id, name, age, notes from lcs_people order by age'
cur.execute(statement)
res = cur.fetchall()
print(res)
#------------------------------------------------------------------------------
# connect_drcp.py (Section 2.3 and 2.5)
#------------------------------------------------------------------------------
#------------------------------------------------------------------------------
# Copyright 2017, 2018, Oracle and/or its affiliates. All rights reserved.
#------------------------------------------------------------------------------
from __future__ import print_function
import cx_Oracle
import db_config
con = cx_Oracle.connect(db_config.user, db_config.pw, db_config.dsn + ":pooled",
cclass="PYTHONHOL", purity=cx_Oracle.ATTR_PURITY_SELF)
print("Database version:", con.version)
import cgi
import cx_Oracle as Oracle
import json
form = cgi.FieldStorage()
#do form["key"] or form.getvalue("key","default value"). second is better
#can also do form.getlist
#connection, cursor = None,None
cursor = None
out = {"success": False}
try:
#connection = Oracle.connect("cbowley","secrets","oracle1.aero.und.edu:1521/cs513.aero.und.edu")
with Oracle.connect(
"cbowley", "secrets",
"oracle1.aero.und.edu:1521/cs513.aero.und.edu") as connection:
cursor = connection.cursor()
param = {"name": form.getvalue("name", "")}
if (param["name"] != ""):
check_query = "SELECT id FROM customers WHERE name = :name"
cursor.execute(check_query, param)
result = cursor.fetchone()
cid = int(result[0]) if result else -1
if (cid != -1): #already exists
out["Id"] = cid
out["success"] = True
else: # need to add
insert_query = "INSERT INTO customers(name,account) VALUES (:name,purchased_game_table())"
cursor.execute(insert_query, param)
cursor.execute(check_query, param)
import cx_Oracle
con = cx_Oracle.connect('system','******','localhost:1522/XE')
cur = con.cursor()
cur.execute('drop table Account')
cur.execute("""create table Account(
CustomerId number(10) primary key,
AccountNo varchar2(15) not null,
AccountType varchar2(15) check(AccountType in ('Savings','Current','Recurring')),
Balance number(12)
)""")
cur.executemany("insert into Account values(:1,:2,:3,:4)",
[(101,'IBI1001','Savings',0),(102,'IBI1002','Current',1200),
(103,'IBI1003','Savings',6543),(104,'IBI1004','Recurring',7500),
(105,'IBI1005','Current',0)])
cur.execute('''select customerid, balance from Account where
balance=(select max(balance) from account)''')
print(cur.description)
print(cur.fetchall())
cur.execute('select balance from Account where CustomerId=102')
acct_bal = int(cur.fetchall()[0][0])
print(acct_bal)
acct_bal+=2000
cur.execute('update Account set balance=:1 where CustomerId=102',[(acct_bal)])
cur.execute('select balance from Account where CustomerId=102')
def getX(start_date, end_date):
dataX = []
column_list = []
# DBMS 활용
try:
conn = mysql.connect("seungsu", "tmdtn12", "orcl")
cur = conn.cursor()
# #특정속성(upload_date등) 제외하고 워드 속성만 가져오기
# # Oracle1 - 속성명 받아와 UPLOAD_DATE 없애기
# sql_select_column= "select COLUMN_NAME from ALL_TAB_COLUMNS where table_name='INPUT1'"
# #cur.execute(sql_select_column)
# for column in cur.fetchall():
# column_list.append(column[0])
# column_list.remove("UPLOAD_DATE")
# #print(len(column_list))
#
# columns = ""
# str_conn = ", "
# for i in range(0, len(column_list)):
# if i < len(column_list):
# columns += column_list[i] + str_conn
# else:
# columns += column[i-1]
# # print(columns)
# # print(type(columns))
#
# sql_select_data= "select " + str(column)[2:-3] + " from INPUT1 " \
# "where UPLOAD_DATE between to_date('" + start_date + "', 'YYYYMMDDHH24MISS') " \
# "and to_date('" + end_date + "', 'YYYYMMDDHH24MISS') " \
# "order by UPLOAD_DATE asc"
# print(sql_select_data)
# cur.execute(sql_select_data)
# print(cur.fetchall())
sql_select_tables = "select * from input1 " \
"where UPLOAD_DATE between to_date('" + start_date + "', 'YYYYMMDDHH24MISS') " \
"and to_date('" + end_date + "', 'YYYYMMDDHH24MISS') " \
"order by UPLOAD_DATE asc"
cur.execute(sql_select_tables)
# Oracle2 전체 다 꺼내서 맨 앞 column만 제거(첫번째 column : upload_date)
for _data in cur.fetchall():
data = _data[1:]
print(data)
dataX.append(data)
# print(dataX)
# for MySQL
# sql_set = "set @qry = CONCAT('SELECT ', (SELECT REPLACE(GROUP_CONCAT(COLUMN_NAME), " \
# "'<upload_date>,', '') FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = " \
# "'<대상 테이블명>'AND TABLE_SCHEMA = '<seungu>'), ' FROM <input0> " \
# "where pcode ='" + company + "' " \
# "and st_date between to_date('" + start_date + "', 'YYYYMMDDHH24MISS') " + \
# "and to_date('" + end_date + "', 'YYYYMMDDHH24MISS') " \
# "order by st_date asc')"
# print(sql_set)
# cur.execute(sql_set)
# print(cur.fetchall())
# sql_check = "SELECT @qry;"
# cur.execute(sql_check)
# print(cur.fetchone())
#
# sql_prepare = "PREPARE result FROM @qry"
# cur.execute(sql_prepare)
#
# sql_execute = "EXECUTE result"
# cur.execute(sql_execute)
# print(cur.fetchall())
# #
# record_list = cur.fetchall()
#
# #words 속성들로 행렬만들기
# for i in range(0, len(record_list)/data_dim):
# _x = record_list[i*data_dim : (i+1)*data_dim]
# dataX.append(_x)
except mysql.DatabaseError as e:
print('getX Error : ', e)
finally:
cur.close()
conn.close()
return dataX
def get_conn(self) -> 'OracleHook':
"""
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 data source name for the Oracle server
:param service_name: the db_unique_name of the database
that you are connecting to (CONNECT_DATA part of TNS)
:param sid: Oracle System ID that identifies a particular
database on a system
You can set these parameters in the extra fields of your connection
as in
.. code-block:: python
{
"dsn": (
"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)"
"(HOST=host)(PORT=1521))(CONNECT_DATA=(SID=sid)))"
)
}
see more param detail in
`cx_Oracle.connect <https://cx-oracle.readthedocs.io/en/latest/module.html#cx_Oracle.connect>`_
"""
conn = self.get_connection(
self.oracle_conn_id) # type: ignore[attr-defined]
conn_config = {'user': conn.login, 'password': conn.password}
sid = conn.extra_dejson.get('sid')
mod = conn.extra_dejson.get('module')
schema = conn.schema
service_name = conn.extra_dejson.get('service_name')
port = conn.port if conn.port else 1521
if conn.host and sid and not service_name:
conn_config['dsn'] = cx_Oracle.makedsn(conn.host, port, sid)
elif conn.host and service_name and not sid:
conn_config['dsn'] = cx_Oracle.makedsn(conn.host,
port,
service_name=service_name)
else:
dsn = conn.extra_dejson.get('dsn')
if dsn is None:
dsn = conn.host
if conn.port is not None:
dsn += ":" + str(conn.port)
if service_name:
dsn += "/" + service_name
elif conn.schema:
warnings.warn(
"""Using conn.schema to pass the Oracle Service Name is deprecated.
Please use conn.extra.service_name instead.""",
DeprecationWarning,
stacklevel=2,
)
dsn += "/" + conn.schema
conn_config['dsn'] = dsn
if 'encoding' in conn.extra_dejson:
conn_config['encoding'] = conn.extra_dejson.get('encoding')
# if `encoding` is specific but `nencoding` is not
# `nencoding` should use same values as `encoding` to set encoding, inspired by
# https://github.com/oracle/python-cx_Oracle/issues/157#issuecomment-371877993
if 'nencoding' not in conn.extra_dejson:
conn_config['nencoding'] = conn.extra_dejson.get('encoding')
if 'nencoding' in conn.extra_dejson:
conn_config['nencoding'] = conn.extra_dejson.get('nencoding')
if 'threaded' in conn.extra_dejson:
conn_config['threaded'] = conn.extra_dejson.get('threaded')
if 'events' in conn.extra_dejson:
conn_config['events'] = conn.extra_dejson.get('events')
mode = conn.extra_dejson.get('mode', '').lower()
if mode == 'sysdba':
conn_config['mode'] = cx_Oracle.SYSDBA
elif mode == 'sysasm':
conn_config['mode'] = cx_Oracle.SYSASM
elif mode == 'sysoper':
conn_config['mode'] = cx_Oracle.SYSOPER
elif mode == 'sysbkp':
conn_config['mode'] = cx_Oracle.SYSBKP
elif mode == 'sysdgd':
conn_config['mode'] = cx_Oracle.SYSDGD
elif mode == 'syskmt':
conn_config['mode'] = cx_Oracle.SYSKMT
elif mode == 'sysrac':
conn_config['mode'] = cx_Oracle.SYSRAC
purity = conn.extra_dejson.get('purity', '').lower()
if purity == 'new':
conn_config['purity'] = cx_Oracle.ATTR_PURITY_NEW
elif purity == 'self':
conn_config['purity'] = cx_Oracle.ATTR_PURITY_SELF
elif purity == 'default':
conn_config['purity'] = cx_Oracle.ATTR_PURITY_DEFAULT
conn = cx_Oracle.connect(**conn_config)
if mod is not None:
conn.module = mod
# if Connection.schema is defined, set schema after connecting successfully
# cannot be part of conn_config
# https://cx-oracle.readthedocs.io/en/latest/api_manual/connection.html?highlight=schema#Connection.current_schema
# Only set schema when not using conn.schema as Service Name
if schema and service_name:
conn.current_schema = schema
return conn
import cx_Oracle
con = cx_Oracle.connect('SYSTEM/dbms@localhost')
print('ok')
#1521
def leeEspecies():
connectionMaestros = cx_Oracle.connect("maestros",
"maestros",
"oracle.world",
threaded=True)
#sql = "select esp.descripcion, dcientifico,dingles,dfrances,ditaliano,dotros,fam.descripcion,grp.descripcion, par.descripcion from especies esp, familias fam, grupos grp, partidas_arancelarias par where familia_familia_id = familia_id and fam.grupo_grupo_id= grp.grupo_id and esp.p_arancela_p_arancela_id = par.p_arancela_id"
#sql = "select familias.descripcion, grupos.descripcion from familias, grupos where grupo_grupo_id = grupo_id"
#sql = "select descripcion from grupos"
#sql = "select descripcion , codigo_estadistico from partidas_arancelarias"
#sql = "select descripcion from tipos"
#sql = "select descripcion from unidades_medidas "
#sql = "select descripcion from canales"
#sql = "select * from marcas"
#sql = "select descripcion,f_master,peso_ud,peso_envase,agranel,volumen from envases"
#sql = "select dim.descripcion,dim.maximo,dim.minimo,dim.piezas,uni.descripcion from dimensiones dim, unidades_medidas uni where u_medida_id= u_medida_u_medida_id"
sql = "select dim.descripcion,esp.descripcion,tip.descripcion,codigo_barra,nvl(can.descripcion,'---') canal, env.descripcion, NVL(elaborado,'N'), nvl(mar.descripcion,'---') marcas, estado, fecha_baja, codarticulo , env.f_master from mercancias mer , dimensiones dim , especies esp , tipos tip , canales can, envases env ,marcas mar where mer.especie_especie_id = esp.especie_id and mer.tipo_tipo_id = tip.tipo_id and mer.marca_marca_id(+) = mar.marca_id and mer.envase_envase_id = env.envase_id and mer.dimension_dimension_id = dim.dimension_id"
cursor = connectionMaestros.cursor()
cursor.execute(sql)
array = []
array = cursor.fetchall()
# for partidas in array:
# PartidasArancelarias.objects.create( descripcion= partidas[0],codigo_estadistico = partidas[1])
# for grupos in array:
# Grupos.objects.create(descripcion=grupos[0])
# for descrip in array:
# print "Descripcion %s " % descrip[0]
# Familias.objects.create(descripcion=descrip[0],grupo_grupo=Grupos.objects.get(descripcion=descrip[1]))
#
# for especies in array:
# uespecies = []
# i=0
# for x in especies:
# if x is None:
# uespecies.append(elimina_tildes(unicode(x)))
# else:
# print x
# uespecies.append(elimina_tildes(unicode(x.decode('utf-8'))))
# Especies.objects.create(descripcion=uespecies[0],dcientifico=uespecies[1],denglish=uespecies[2],dfrench=uespecies[3],ditalian=uespecies[4],dotros=uespecies[5],familia_familia=Familias.objects.get(descripcion=uespecies[6], grupo_grupo=Grupos.objects.get(descripcion=uespecies[7])), p_arancela_p_arancela = PartidasArancelarias.objects.filter(descripcion=uespecies[8])[0])
# for tipos in array:
# Tipos.objects.create(descripcion=tipos[0])
#
# for umedidas in array:
# UnidadesMedidas.objects.create(descripcion=umedidas[0])
# for ucanales in array:
# Canales.objects.create(descripcion = ucanales[0])
#
# for umarcas in array:
# Marcas.objects.create(descripcion = umarcas)
# for tpenv in array:
# if tpenv[0] is None:
# cdesc="."
# else:
# cdesc=tpenv[0]
# TiposEnvases.objects.create(descripcion=cdesc,f_master=tpenv[1],peso_ud=tpenv[2],peso_envase=tpenv[3],agranel=tpenv[4],volumen=tpenv[5])
# for dime in array:
# Dimensiones.objects.create(descripcion=dime[0],maximo=dime[1],minimo=dime[2],piezas=dime[3],u_medida_u_medida = UnidadesMedidas.objects.get(descripcion=dime[4]))
for prod in array:
print prod
try:
espe = Especies.objects.get(descripcion=prod[1])
try:
dime = Dimensiones.objects.get(descripcion=prod[0])
except ObjectDoesNotExist:
pass
tipos = Tipos.objects.get(descripcion=prod[2])
try:
can = Canales.objects.get(descripcion=prod[4])
except ObjectDoesNotExist:
pass
try:
enva = TiposEnvases.objects.get(descripcion=prod[5],
f_master=prod[11])
except ObjectDoesNotExist:
pass
try:
marc = Marcas.objects.get(descripcion=prod[7])
except ObjectDoesNotExist:
pass
Productos.objects.create(dimension=dime,
especie=espe,
tipo=tipos,
codigo_barra=prod[3],
canal=can,
envase=enva,
elaborado=prod[6],
marca=marc,
estado=prod[8],
codarticulo=prod[10])
except ObjectDoesNotExist:
pass
import csv
import cx_Oracle
import numpy as np
import config
user = config.ORACLE_DATABASE_CONFIG['user']
password = config.ORACLE_DATABASE_CONFIG['password']
host = config.ORACLE_DATABASE_CONFIG['host']
port = str(config.ORACLE_DATABASE_CONFIG['port'])
dbname = config.ORACLE_DATABASE_CONFIG['dbname']
con = cx_Oracle.connect(user + '/' + password + '@' + host + ':' + port + '/' +
dbname)
cur = con.cursor()
cur.execute('select * from HR.employees')
result_qery = np.asarray(cur.fetchall())
print(result_qery)
with open('foo.csv', "w") as output:
writer = csv.writer(output, lineterminator='\n')
writer.writerows(result_qery)
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" }``
see more param detail in
`cx_Oracle.connect <https://cx-oracle.readthedocs.io/en/latest/module.html#cx_Oracle.connect>`_
"""
conn = self.get_connection(self.oracle_conn_id)
conn_config = {'user': conn.login, 'password': conn.password}
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)
port = conn.port if conn.port else 1521
if dsn and sid and not service_name:
conn_config['dsn'] = cx_Oracle.makedsn(dsn, port, sid)
elif dsn and service_name and not sid:
conn_config['dsn'] = cx_Oracle.makedsn(dsn,
port,
service_name=service_name)
else:
conn_config['dsn'] = conn.host
if 'encoding' in conn.extra_dejson:
conn_config['encoding'] = conn.extra_dejson.get('encoding')
# if `encoding` is specific but `nencoding` is not
# `nencoding` should use same values as `encoding` to set encoding, inspired by
# https://github.com/oracle/python-cx_Oracle/issues/157#issuecomment-371877993
if 'nencoding' not in conn.extra_dejson:
conn_config['nencoding'] = conn.extra_dejson.get('encoding')
if 'nencoding' in conn.extra_dejson:
conn_config['nencoding'] = conn.extra_dejson.get('nencoding')
if 'threaded' in conn.extra_dejson:
conn_config['threaded'] = conn.extra_dejson.get('threaded')
if 'events' in conn.extra_dejson:
conn_config['events'] = conn.extra_dejson.get('events')
mode = conn.extra_dejson.get('mode', '').lower()
if mode == 'sysdba':
conn_config['mode'] = cx_Oracle.SYSDBA
elif mode == 'sysasm':
conn_config['mode'] = cx_Oracle.SYSASM
elif mode == 'sysoper':
conn_config['mode'] = cx_Oracle.SYSOPER
elif mode == 'sysbkp':
conn_config['mode'] = cx_Oracle.SYSBKP
elif mode == 'sysdgd':
conn_config['mode'] = cx_Oracle.SYSDGD
elif mode == 'syskmt':
conn_config['mode'] = cx_Oracle.SYSKMT
elif mode == 'sysrac':
conn_config['mode'] = cx_Oracle.SYSRAC
purity = conn.extra_dejson.get('purity', '').lower()
if purity == 'new':
conn_config['purity'] = cx_Oracle.ATTR_PURITY_NEW
elif purity == 'self':
conn_config['purity'] = cx_Oracle.ATTR_PURITY_SELF
elif purity == 'default':
conn_config['purity'] = cx_Oracle.ATTR_PURITY_DEFAULT
conn = cx_Oracle.connect(**conn_config)
if mod is not None:
conn.module = mod
return conn
# 0. 패키지(모듈) 임포트
import cx_Oracle as oci
# 1. 연결객체 얻어오기
conn = oci.connect('scott/tiger@localhost:1521/orcl')
# 2. 커서객체 얻어오기
# cursor = conn.cursor()
# 3. sql 문장
# sql = "SELECT * FROM emp"
# 4. sql 문장 실행
# cursor.execute(sql)
# datas = cursor.fetchall()
# print(datas)
# 5. 커서객체 닫기
# cursor.close()
# 6. 커넥션 닫기
with oci.connect('scott/tiger@localhost:1521/orcl') as conn:
with conn.cursor() as cursor:
sql = "SELECT * FROM emp"
cursor.execute(sql)
datas = cursor.fetchall()
for data in datas:
print(data)
for row in res:
r = row["user_story"]
query = (
"SELECT distinct file_name FROM vdeploy.vDeploy_build where user_story='"
+ r + "'")
file_prod = "C:\\Users\\amedishetti\\Desktop\\sp\\" + r + "_prod.txt"
file_env = "C:\\Users\\amedishetti\\Desktop\\sp\\" + r + "_" + z + ".txt"
file_output = "C:\\Users\\amedishetti\\Desktop\\sp\\outputfile.txt"
f = open(file_output, "w")
cursor.execute(query)
results = cursor.fetchall()
for row in results:
s = row["file_name"]
if (s[0:2] == "wf"):
con = cx_Oracle.connect(
'INFAREP_PROD_READ/[email protected]:1521/INFPRD'
)
cur = con.cursor()
query1 = (
"SELECT * FROM (SELECT WF.WORKFLOW_NAME, COUNT(TSK.INSTANCE_NAME) AS TSK_COUNT FROM INFAREP_PROD.REP_WORKFLOWS WF INNER JOIN INFAREP_PROD.REP_TASK_INST TSK ON WF.WORKFLOW_ID=TSK.WORKFLOW_ID GROUP BY WF.WORKFLOW_NAME) S WHERE S.WORKFLOW_NAME='"
+ s[0:-4] + "'")
cur.execute(query1)
for result in cur:
print(result, file=open(file_prod, "a"))
query2 = (
"SELECT * FROM (SELECT WF.WORKFLOW_NAME, COUNT(TSK.INSTANCE_NAME) AS TSK_COUNT FROM INFAREP_"
+ z + ".REP_WORKFLOWS WF INNER JOIN INFAREP_" + z +
".REP_TASK_INST TSK ON WF.WORKFLOW_ID=TSK.WORKFLOW_ID GROUP BY WF.WORKFLOW_NAME) S WHERE S.WORKFLOW_NAME='"
+ s[0:-4] + "'")
con = cx_Oracle.connect(
'infarep_dev15/[email protected]/infdev15'
import cx_Oracle as oracle
db = oracle.connect('l3main/[email protected]:1521/l3dev')
cursor = db.cursor
cursor.execute('select column_name,tale_name from user_tab_column_name like '*'')
data = cursor.fetchone
print(data)
cursor.close()
de.close()
import cx_Oracle
con = cx_Oracle.connect('system/[email protected]/orcl')
cur = con.cursor()
cur.execute('select * from pyth order by id')
#for result in cur:
# print result
row = cur.fetchone()
print row
row = cur.fetchmany(numRows=1)
print row
res = cur.fetchall()
for r in res:
print r
cur.close()
con.close()
