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 connect_to_database(self, user_name, password, host, port, sid_service, encoding="American_America.AL32UTF8"):
environ["NLS_LANG"] = encoding
if sid_service.split("=")[0] == "sid":
dsn = cx_Oracle.makedsn(host, port, sid_service.split("=")[1])
else:
dsn = cx_Oracle.makedsn(host, port, service_name=sid_service.split("=")[1])
self.connection = cx_Oracle.connect(user_name, password, dsn)
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 get_dsn_tns(database):
if len(config.get(database,'sid'))==0:
dsn_tns = cx_Oracle.makedsn(config.get(database,'host'), config.get(database,'port'), service_name=config.get(database,'service_name'))
else:
dsn_tns = cx_Oracle.makedsn(config.get(database,'host'), config.get(database,'port'), config.get(database,'sid'))
return dsn_tns
def _makedsn(self, host, port, sid, service_name = 'ORCL'):
"""
Method: _makedsn
Description: 生成连接字符串DSN
Parameter:
host: 数据库服务器名
port: 数据库服务器端口
sid: 数据库名
service_name: 数据库服务名
Return:
非None: Oracle数据库连接字符串
Others:
"""
if host.find(',') == -1:
versions = cx_Oracle.version.split('.')
if tuple(int(x) for x in versions) >= (5, 1, 1):
if service_name is not None:
return cx_Oracle.makedsn(host, port, service_name = service_name)
return cx_Oracle.makedsn(host, port, sid)
dsn = cx_Oracle.makedsn(host, port, sid)
if service_name is not None:
dsn.replace('SID', 'SERVICE_NAME')
else:
hosts = host.split(',')
index = 0
address = ''
for host in hosts:
address += '(ADDRESS=(PROTOCOL=TCP)(HOST=%s)(PORT=%s))' % (host, port)
rac = '(LOAD_BALANCE=ON)(FAILOVER=ON)'
if service_name is not None:
dsn = '(DESCRIPTION=%s(ADDRESS_LIST=%s)(CONNECT_DATA=(SERVICE_NAME=%s)))' % (rac, address, service_name)
else:
dsn = '(DESCRIPTION=%s(ADDRESS_LIST=%s)(CONNECT_DATA=(SID=%s)))' % (rac, address, sid)
return dsn
def db_connect(envir):
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 connect_to_database(self, username, password, host, port, sid_service, encoding="American_America.AL32UTF8"):
"""
Example usage:
| Connect To Database | user1 | qwerty | localhost | 1521 | service=db_serv1 | Russian.AL32UTF8 |
For set database sid: sid=db_sid_name.\n
Encoding is optional parameter. By default is American_America.AL32UTF8.
"""
os.environ['NLS_LANG'] = encoding
if sid_service.split("=")[0] == "sid":
dsn = cx_Oracle.makedsn(host, port, sid_service.split("=")[1])
else:
dsn = cx_Oracle.makedsn(host, port, service_name=sid_service.split("=")[1])
self.connection = cx_Oracle.connect(username, password, dsn)
def __enter__(self):
if self.service:
ora_dsn = cx_Oracle.makedsn('localhost',
CONF.get(MANAGER).listener_port,
service_name=self.sid)
else:
ora_dsn = cx_Oracle.makedsn('localhost',
CONF.get(MANAGER).listener_port,
self.sid)
self.conn = cx_Oracle.connect(user=self.user_id,
password=self.password,
dsn=ora_dsn, mode=self.mode)
return self.conn
def create_connection(module, user, password, host, port, sid=None, service=None, mode=None):
if sid:
dsn = cx_Oracle.makedsn(host=host, port=port, sid=sid)
else:
dsn = cx_Oracle.makedsn(host=host, port=port, service_name=service)
try:
if mode:
conn = cx_Oracle.connect(user=user, password=password, dsn=dsn, mode=map_mode(mode))
else:
conn = cx_Oracle.connect(user=user, password=password, dsn=dsn)
return conn
except cx_Oracle.DatabaseError as e:
module.fail_json(msg='{dsn}: {err}'.format(dsn=dsn, err=str(e)))
def __getcon(dbtype="test"):
_conf = DBConf.getInst()
if Oracle._oracle_con_pool is None:
if dbtype == "hbdt79":
dsn_tns = cx_Oracle.makedsn(_conf.get_oracle('iphbdt79'), _conf.get_oracle('porthbdt79'), _conf.get_oracle("sidhbdt79"))
Oracle._oracle_con_pool = cx_Oracle.connect(_conf.get_oracle('usernamehbdt79'), _conf.get_oracle('passwordhbdt79'), dsn_tns)
elif dbtype == "hbgj90":
dsn_tns = cx_Oracle.makedsn(_conf.get_oracle('iphbgj90'), _conf.get_oracle('porthbgj90'), _conf.get_oracle("sidhbgj90"))
Oracle._oracle_con_pool = cx_Oracle.connect(_conf.get_oracle('usernamehbgj90'), _conf.get_oracle('passwordhbgj90'), dsn_tns)
else:
dsn_tns = cx_Oracle.makedsn(_conf.get_oracle('iptest'), _conf.get_oracle('porttest'), _conf.get_oracle("sidtest"))
Oracle._oracle_con_pool = cx_Oracle.connect(_conf.get_oracle('usernametest'), _conf.get_oracle('passwordtest'), dsn_tns)
return Oracle._oracle_con_pool
def oracle_connect(module,host,port,user,password,
sid=None,service=None,as_sysdba=False):
if sid is not None:
dsn = cx_Oracle.makedsn(host,port,sid)
elif service is not None:
dsn = cx_Oracle.makedsn(host,port,service_name=service)
try:
if as_sysdba:
conn = cx_Oracle.connect(user,password,dsn,
mode=cx_Oracle.SYSDBA)
else:
conn = cx_Oracle.connect(user,password,dsn)
except Exception, e:
module.fail_json(msg='Cannot connect to database (err: %s)' % (str(e)))
def connect(username, password, connstr):
parts = connstr.split(':')
if parts[0] == 'oracle':
# oracle:HOST:1521:SID
host = parts[1]
port = int(parts[2])
sid = parts[3]
import cx_Oracle
try:
conn = cx_Oracle.Connection(username, password, dsn=cx_Oracle.makedsn(host, port, sid))
except cx_Oracle.DatabaseError:
conn = cx_Oracle.Connection(username, password, dsn=cx_Oracle.makedsn(host, port, service_name=sid))
else:
raise NotImplementedError("Unknown database type '%s'" % parts[0])
return conn
def oraclelogin(target,user,password,database,port):
print "[+] Trying Default User and Password (%s----->%s)" % (user,password)
try:
conn = cx_Oracle.connect(user,password,cx_Oracle.makedsn(target,port,database))
conn.close()
return (True,user,password)
except DatabaseError:
try:
conn = cx_Oracle.connect(user,password,cx_Oracle.makedsn(target,port,database),cx_Oracle.SYSDBA)
conn.close()
return (True,user,password)
except:
return (False,user,password)
except:
return (False,user,password)
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 _dsn(self):
settings_dict = self.settings_dict
if not settings_dict['HOST'].strip():
settings_dict['HOST'] = 'localhost'
if settings_dict['PORT']:
return Database.makedsn(settings_dict['HOST'], int(settings_dict['PORT']), settings_dict['NAME'])
return settings_dict['NAME']
def __init__(self, config, schema):
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
config["dsn"] = cx_Oracle.makedsn(host=config["host"], port=config["port"], sid=config["sid"])
self.db = cx_Oracle.connect(user=config["user"], password=config["password"], dsn=config["dsn"]) #用自己的实际数据库用户名、密码、主机ip地址 替换即可
self.db.current_schema = schema
self.db.autocommit = 1
self.cursor = self.db.cursor()
def checkDatabaseConnection(self,expectedDatabases,database_username,database_password,listener_port):
"""
Check if the connection to databases are working.
"""
try:
error = False
# Process expected databases
expected_databases = expectedDatabases.split(',')
for vmname in self.vminfo.keys():
self.logger.info("Checking " + vmname)
host = self.vminfo[vmname]
port = int(listener_port)
for database in expected_databases:
self.logger.info("Checking " + database + " connection")
connectstring = cx_Oracle.makedsn(host, port, service_name=database.upper())
try:
# try connect to the database
connection_obj = cx_Oracle.connect(database_username,database_password,connectstring)
except:
error = True
self.logger.error("Vm "+vmname+" Cannot connect to database"+database)
if error:
raise ECUTestFailureError("Database connection test failed")
except:
self.logger.error(traceback.format_exc());
raise ECUTestFailureError("Database test Fail")
def connect_oracle(self):
dsn = cx_Oracle.makedsn(db_host, 1521, "xe")
dg(1, "connect_oracle | DSN:" + str(dsn))
self.con = cx_Oracle.connect(db_user, db_pass, dsn)
dg(1, "connect_oracle | CON:" + str(self.con.version))
self.cursor = self.con.cursor()
return self.cursor
def connect_ora(g_dbid):
global oracledb,oraclecur,oraclecur2
g_completed='N'
g_gmt_create=datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')
g_gmt_modify=g_gmt_create
table_name='b_oracle_quota_collect_day'
get_ora_pass(g_dbid)
if h_type=='standby':
get_ora_standby(g_dbid)
else:
tns_name = cx_Oracle.makedsn(ip_addr,port,sid)
oracledb = cx_Oracle.connect(username,password, tns_name)
oraclecur=oracledb.cursor()
if h_type=='awr':
get_awr()
if h_type=='sqltext':
get_sqltext()
if h_type=='stat':
get_ora_stat(g_dbid)
if h_type=='longsql':
get_ora_long_sql(g_dbid)
if h_type=='trans':
get_trans_info(g_dbid)
if h_type=='level':
get_index_level(g_dbid)
mysqlcur.close()
mysqldb.close()
def createuser():
opts={}
user = '******'
password = '******'
host = 'localhost'
port = '1521'
SID = 'orcl'
dsn_tns = cx_Oracle.makedsn(host,port,SID)
try:
conn = cx_Oracle.connect(user,password,dsn_tns)
if request.method == 'POST':
try:
username = request.form["username"]
userpass = request.form["userpass"]
cursor = conn.cursor()
cursor.execute("""CREATE USER %s IDENTIFIED BY %s DEFAULT TABLESPACE USERS TEMPORARY TABLESPACE TEMP QUOTA UNLIMITED ON USERS""" % (username, userpass))
cursor.execute("grant CREATE DATABASE LINK, CREATE MATERIALIZED VIEW, CREATE PROCEDURE, CREATE PUBLIC SYNONYM, CREATE ROLE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE TYPE, CREATE VIEW to %s" % (username))
cursor.execute("grant resource to %s" %(username))
cursor.execute("grant connect to %s" % (username))
conn.commit()
except Exception as e:
print e
opts["errors"] = u"Error en la conexión de la base de datos. Verifique el host o el usuario"
except cx_Oracle.DatabaseError, exc:
return redirect(url_for('errordb'))
def return_cursor(creds_path):
'''
Method to return an Oracle DB cursor which is connected to database
instance using credentials found in a local file.
Parameters
----------
creds_path : string (filepath)
path to the csv file with 'DB_USER' as the header and the
corresponding ASCII text for the user name underneath; same
'DB_PASSWD', 'DB_HOST', 'DB_PORT', and 'DB_SID'headers and text
Returns
-------
cursor : OracleCursor
a cx_Oracle cursor object which is used to query and modify an
Oracle database
'''
# Import packages
import cx_Oracle
# Get DB connection variables
user, passwd, host, port, sid = return_rds_vars(creds_path)
# Setup connection and cursor
dsn = cx_Oracle.makedsn(host, port, sid)
conn = cx_Oracle.connect(user, passwd, dsn)
cursor = conn.cursor()
# Return cursor
return cursor
def loop(self):
## use DBMS_PIPE based notification
self.usePipe = True
## DBMS_PIPE.receive_message timeout in secs
self.pipeTimeout = int(1)
## Iff true, recheck event table on every pipe timeout
self.recheckOnPipeTimeout = False
import time, json
import os
os.environ["NLS_LANG"] = "AMERICAN_AMERICA.UTF8"
import cx_Oracle
try:
dsn = cx_Oracle.makedsn(self.connect.host,
self.connect.port,
self.connect.sid)
self.conn = cx_Oracle.connect(self.connect.user,
self.connect.password,
dsn,
threaded = False)
self.conn.autocommit = True
log.msg("pusher Oracle connection established")
except Exception, e:
#log.msg(str(e))
raise e
def getParent(self, childBatchUid):
sql = """
select * from bblearn.course_main
where
pk1 in (
select crsmain_pk1 from bblearn.course_course
where crsmain_parent_pk1=(
select pk1 from bblearn.course_main
where batch_uid='%s'
)
)
"""%childBatchUid
ezConStr = cx_Oracle.makedsn("devlmshost1.ucc.nau.edu",1521,"learn")
connection = cx_Oracle.Connection(
dsn=ezConStr,
user="******",
password="******")
cursor=cx_Oracle.Cursor(connection);
cursor.execute(sql)
i=0
for row in cursor.fetchall():
i=i+1
cursor.close()
connection.close()
return i
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 _executeQuery(self, query):
self.getLogger().debug("Executing query:\n%s" % repr(query))
try:
dsn = dbapi.makedsn(str(self['host']), self['port'], str(self['sid']))
conn = dbapi.connect(str(self['user']), str(self['password']), dsn)
cursor = conn.cursor()
self.logSentPayload(query.split(' ')[0].upper(), query)
try:
cursor.execute(str(query))
conn.commit()
except Exception, e:
self.getLogger().warning("Exception while executing query: %s" % getBacktrace())
conn.rollback()
cursor.close()
conn.close()
raise e
res = []
if cursor.description: # equivalent to "the previous execution() provided a set ?"
columnNames = map(lambda x: x[0], cursor.description)
for row in cursor.fetchall():
res.append(dict(zip(columnNames, row)))
self.triEnqueueMsg(('result', res))
cursor.close()
conn.close()
def get_cursor(username="", password="", host="datadb.admsec.wwu.edu",
port=1521, db="DATAW"):
"""Prompt for username and password if they are not present, and return a
cursor to the database connection."""
# Get the username to use for the connection.
if not username:
if version_info[0] >= 3:
get_input = input
else:
get_input = raw_input
username = get_input("Enter Database Username: "******"Enter Password: "******"DRIVER={{Microsoft ODBC for Oracle}};"
"SERVER={};UID={};PWD={}".format(host, username, password))
cnxn = pyodbc.connect(cnxnstr)
else:
import cx_Oracle
dsn = cx_Oracle.makedsn(host, port, db)
cnxn = cx_Oracle.connect(username, password, dsn)
# Clear out the password.
password = ""
return cnxn.cursor()
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 update_sql(values, column):
for i in values:
conn = None
try:
dsn = cx_Oracle.makedsn('localhost', 1521, 'urbandeve')
conn = cx_Oracle.connect(user='******', password='******', dsn=dsn)
try:
cursor = conn.cursor()
for i in values:
sql = '''update TBL_DPSHOPS set %s = '%s' where SHOPNO = '%s' '''
t = (column,) + i
sql = sql % t
cursor.prepare(sql)
cursor.execute(None)
print(cursor.rowcount)
if cursor.rowcount == 0:
values = (values[1], values[0])
cursor.prepare('''insert into TBL_DPSHOPS
(SHOPNO, %s)
values(:1, :2)''' % column)
cursor.execute(None, values)
conn.commit()
finally:
cursor.close()
finally:
if conn is not None:
conn.close()
def handle(self, *args, **options):
# For making the logs easier to interpret later, log out when we started.
self.stdout.write("Nightly Force Aggregation started at: {}".format(datetime.datetime.now()))
# Get banner connection settings.
banner = settings.ORACLE_MANAGEMENT['banner']
oracle_dsn = cx_Oracle.makedsn(banner['HOST'], banner['PORT'], banner['SID'])
oracle_connection = cx_Oracle.Connection(banner['USER'], banner['PASS'], oracle_dsn)
read_cursor = oracle_connection.cursor()
read_cursor.execute(settings.ORACLE_MANAGEMENT['nightly_force']['EXPIRE_SQL'])
write_cursor = oracle_connection.cursor()
write_cursor.prepare(settings.ORACLE_MANAGEMENT['nightly_force']['INSERT_SQL'])
write_cursor.setinputsizes(udc_id=225)
for record in read_cursor:
udc_id = record[0];
self.stdout.write("Adding forced delta record for: " + udc_id)
write_cursor.execute(None, {'udc_id': udc_id})
oracle_connection.commit()
# Finally, print out when we finished.
self.stdout.write("Nightly Force Aggregation finished at: {}".format(datetime.datetime.now()))
def set_corrections(self):
if self.mode:
self.mode = getattr(cx_Oracle, self.mode.upper())
if self.hostname:
self.dsn = cx_Oracle.makedsn(self.hostname, self.port, self.database)
else:
self.dsn = self.database
def __init__(self):
self.tns = cx.makedsn('npc.che.volvocars.net', 49957, sid='DPQ')
self.db = cx.connect('cip_sys', 'cip_sys1', self.tns, encoding="UTF-8", nencoding="UTF-8")
self.cur = self.db.cursor()
def __init__(self,ip,port,sid,user, password):
self.constring = cx_Oracle.makedsn(ip, port, sid)
self.password = password
self.user = user
def handle(req):
if (re.search('ATOM_ERROR:', req)):
return req
try:
with open('function/config.json') as con:
jsn = json.load(con)
except:
return ("ATOM_ERROR: get-db-to-json: Invalid Configuration file\n")
fetch_field = ''
txt_output = json.loads(req)
for j in jsn['field_details']:
fetch_field = fetch_field + j['db_col_name'] + ', '
fetch_field = fetch_field[:-2]
ip = jsn['database_details'][0]['ip']
port = jsn['database_details'][0]['port']
SID = jsn['database_details'][0]['sid']
dsn_tns = cx_Oracle.makedsn(ip, port, SID)
#Preparing database connection
try:
db = cx_Oracle.connect(jsn['database_details'][0]['db_name'],
jsn['database_details'][0]['password'], dsn_tns)
except:
return (
"ATOM_ERROR: get-db-to-json: Unable to connect to Billing database\n"
)
cur = db.cursor()
#Prepare the query
#TODO: For the pelatom we have taken a scenario of single table, with a single primary key.
#This can be enhanced to be more generic in the future
query = 'select ' + fetch_field + ' from ' + jsn['database_details'][0][
'table_name'] + ' where ' + jsn['database_details'][0][
'table_key'] + ' = :tab_key'
cur.prepare(query)
cur.execute(None, tab_key=txt_output[jsn['field_details'][0]['entity']])
db_result = cur.fetchall()
db.close()
i = 0
db_values = {}
if not db_result:
return (
"ATOM_ERROR: get-db-to-json:No record returned from Billing database\n"
)
#Converting extracted data to string for JSON comparison
#if db_result:
for j in jsn['field_details']:
db_values[j['entity']] = str(db_result[0][i])
i += 1
#TODO: Below code can be moved to another atom once parallel/persistence issue around JESI is resolved
final_result = ''
diffkeys = [k for k in db_values if db_values[k] != txt_output[k]]
#If there is any mismatch in any attribute, diffkeys will be non-empty
if diffkeys:
final_result = 'PDF Validation Fail for \n'
else:
final_result = 'PDF Validation Success \n'
for k in diffkeys:
#print(k, ':', db_values[k], '->', txt_output[k])
final_result = final_result + k + ' : ' + db_values[
k] + ' (DB Value) -> ' + txt_output[k] + ' (PDF Value)\n'
return final_result
import pandas as pd
import numpy as np
from my_functions import marc_parser_1_field
import pandasql
from my_functions import cSplit
from my_functions import gsheet_to_df
import cx_Oracle
dsn_tns = cx_Oracle.makedsn('pbl.ibl.poznan.pl', '1521', service_name='xe')
connection = cx_Oracle.connect(user='******',
password='******',
dsn=dsn_tns,
encoding='windows-1250')
pbl_bn_magazines = gsheet_to_df('1V6BreA4_cEb3FRvv53E5ri2Yl1u3Z2x-yQxxBbAzCeo',
'Sheet1')
pbl_bn_magazines = pbl_bn_magazines[pbl_bn_magazines['decyzja'] == 'tak']
bn_magazines = pd.read_excel('bn_magazines_to_statistics.xlsx')[[
'id', '008', '773', 'decision'
]]
bn_magazine_title = marc_parser_1_field(bn_magazines, 'id', '773',
'\$')[['id', '$t']]
bn_magazine_title.columns = ['id', 'bn_magazine']
bn_magazine_title = pd.merge(bn_magazine_title,
pbl_bn_magazines[['pbl_magazine', 'bn_magazine']],
'left', 'bn_magazine')
pbl_query = 'select zr.zr_zrodlo_id, zr.zr_tytul from pbl_zrodla zr'
pbl_magazines = pd.read_sql(pbl_query,
connection).rename(columns={
'ZR_TYTUL': 'pbl_magazine',
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] # pylint: disable=no-member
)
conn_config = {'user': conn.login, 'password': conn.password}
sid = conn.extra_dejson.get('sid')
mod = conn.extra_dejson.get('module')
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 or conn.schema:
dsn += "/" + (service_name or 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
return conn
import cx_Oracle
import re
dsn_tns = cx_Oracle.makedsn('LAPTOP-LBV8A2GC', '1521', service_name='XE')
conn = cx_Oracle.connect(user=r'kandarp', password='******', dsn=dsn_tns)
def queryFirst():
c = conn.cursor()
print("________________________________________________________________")
print("100|D-Mart |Surat |Utran")
print("110|D-Mart |Surat |Vesu")
print("120|Big Bazaar|Surat |Utran")
print("130|Big Bazaar|Surat |Vesu")
print("200|D-Mart |Ahmedabad|Chandkheda")
print("210|D-Mart |Ahmedabad|Lal Darwaja")
print("220|Big Bazaar|Ahmedabad|Chandkheda")
print("230|Big Bazaar|Ahmedabad|Lal Darwaja")
print("________________________________________________________________")
sid = input("Enter supermarketid to find out deapartmentwise total sell: ")
print("supermarketid\t\ttotalsell")
print("_____________\t\t__________")
c.execute(
"select departmentid,sum(totalcost) from (select * from purchasedetails natural join productproj where supermarketid="
+ sid + ") group by departmentid order by departmentid asc")
for row in c:
print(row[0], "\t\t", row[1])
def querySecond():
c = conn.cursor()
def select_all_from(table, cursor):
sql = f'select * from {table.upper()}'
# from 구문에서 테이블 이름은 ''로 감싸면 안되기 때문에
# data binding 방식을 사용할 수 없다.
cursor.execute(sql)
# data = cursor.fetchall() # [row for row in cursor]
data_frame = pd.DataFrame(cursor) # pd.DataFrame(data)
# 데이터 프레임에 컬럼 이름을 설정
data_frame.columns = get_column_names_of(table, cursor)
return data_frame
if __name__ == '__main__':
# 오라클 DB 서버에 접속
dsn = cx_Oracle.makedsn('localhost', 1521, 'orcl')
with cx_Oracle.connect('scott', 'tiger', dsn) as connection:
# Cursor 객체 생성
with connection.cursor() as cursor:
emp_columns = get_column_names_of('emp', cursor)
print(emp_columns) # ['empno', 'enam', 'job', ...]
emp_df = select_all_from('emp', cursor) # pandas.DataFrame
# DataFrame은 테이블의 컬럼 이름(인덱스)를 포함하고 있어야 함.
print(emp_df)
dept_df = select_all_from('DEPT', cursor)
print(dept_df)
salgrade_df = select_all_from('salgrade', cursor)
print(salgrade_df)
@author: SH-NoteBook
"""
import xlsxwriter
import openpyxl
import pandas as pd
import numpy as np
import cx_Oracle
import itertools
from calculate_bm_updown import calculate_bm_updown
#from one_factor_v4 import QVGSM_VALUE
from one_factor_v5 import QVGSM_VALUE
from Performance_Evaluation_v2 import Performance_Evaluation
#이거 두개 반드시 선언!
cx0 = cx_Oracle.makedsn("localhost", 1521, "xe")
connection = cx_Oracle.connect("lshlsh135", "2tkdgns2", cx0) #이게 실행이 안될때가 있는데
#그때는 services에 들어가서 oracle listner를 실행시켜줘야함
kospi_day = pd.read_sql("""select * from kospi_day_prc_20190628""",
con=connection) # 코스피 일별 종가
kospi_day.set_index('TRD_DATE', inplace=True)
kospi200_day = pd.read_sql("""select * from kospi200_day_prc_20190628""",
con=connection) # 코스피 일별 종가
kospi200_day.set_index('TRD_DATE', inplace=True)
#a=calculate_bm_updown(kospi200_day,net_wealth)
#a.make_kospi200_plot()
#kospi_quarter = pd.read_sql("""select * from kospi_quarter_return_20180928""",con=connection)
#kospi_month = pd.read_sql("""select * from kospi_month_return_20180928""",con=connection)
#DATA를 가져온다!!
kospi = pd.read_sql(
import cx_Oracle
#TNSnames.ora
#ORCL =
# (DESCRIPTION =
# (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
# (CONNECT_DATA =
# (SERVICE_NAME = orcl)
# )
# )
# ("Ip_servidor" ,"Porta", "Serviço")
TNS = cx_Oracle.makedsn("187.44.254.2", "1521", "WINT")
con = cx_Oracle.connect(user="******", password="******", dsn=TNS)
# print(con.version)
# abre o cursor
class Pesquisa():
codcli = input(('escolha um codigo'))
cur = con.cursor()
cur.prepare('select enderent,numeroent,bairroent,municent,estent,cepent from pcclient where codcli = :codcli')
cur.execute(None, {'codcli': codcli})
row = cur.fetchall()[0]
endereco = row
print(endereco)
numero = row[1]
bairro = row[2]
cidade = row[3]
import cx_Oracle
# info about the oracle connection
dsn_tns = cx_Oracle.makedsn('grad.icmc.usp.br', 15215, 'orcl')
user = '******'
password = '******'
# open the oracle server connection
def openConnection():
global con
con = cx_Oracle.connect(user, password, dsn_tns)
global cur
cur = con.cursor()
# close the oracle server connection
def closeConnection():
cur.close()
con.close()
#
from subprocess import Popen, PIPE
import cx_Oracle
def disp_rec(cur):
while 1:
one = cur.fetchone()
if one != None:
print one
else:
break
tns_name = cx_Oracle.makedsn('10.169.8.59', '1521', 'orcl')
db = cx_Oracle.connect('v3xuser', 'Www123456', tns_name)
print db.dsn
cur_src = db.cursor()
sql = 'SELECT * FROM logon_log'
info = cur_src.execute(sql)
disp_rec(info)
cur_src.close()
db.close()
# Simplified program for calculating ROP from annual usage and lead time data
import time
import cx_Oracle
import numpy as np
import pandas as pd
from scipy.stats import norm
from sqlalchemy import create_engine
import onccfg as cfg
user = cfg.username
password = cfg.password
sid = cx_Oracle.makedsn(cfg.host, cfg.port, sid=cfg.sid) # host, port, sid
cstr = f"oracle://{user}:{password}@{sid}"
engine = create_engine(cstr,
convert_unicode=False,
pool_recycle=20,
pool_size=100,
echo=False)
def read_sql(filename):
sql_file = open(filename, "r")
query = sql_file.read()
sql_file.close()
df_from_sql = pd.DataFrame(pd.read_sql_query(query, engine))
df_from_sql.columns = map(str.upper, df_from_sql.columns)
import cx_Oracle
dsn_tns = cx_Oracle.makedsn('64.101.193.207', '1541', service_name='abcnd.cisco.com')
this is my first program
def run(args):
# The write log handle
sout = args.sout
desthumbs.tilefinder.SOUT = args.sout
desthumbs.thumbslib.SOUT = args.sout
# Read in CSV file with pandas
df = pandas.read_csv(args.inputList)
# Decide if we do search by RA,DEC or by COADD_ID
if 'COADD_OBJECTS_ID' in df.columns:
searchbyID = True
if not args.coaddtable:
raise Exception(
"ERROR: Need to provide --coaddtable <tablename> when using COADD_OBJECTS_ID"
)
coadd_id = dec = df.COADD_OBJECTS_ID.values
nobj = len(coadd_id)
req_cols = ['COADD_OBJECTS_ID']
else:
searchbyID = False
ra = df.RA.values #if you only want the values otherwise use df.RA
dec = df.DEC.values
nobj = len(ra)
req_cols = ['RA', 'DEC']
# Check columns for consistency
check_columns(df.columns, req_cols)
# Check the xsize and ysizes
xsize, ysize = check_xysize(df, args, nobj)
# Get DB handle
try:
dbh = desdbi.DesDbi(section=args.db_section)
except:
if args.db_section == 'desoper' or args.db_section == 'db-desoper':
host = 'desdb.ncsa.illinois.edu'
port = '1521'
name = 'desoper'
elif args.db_section == 'oldsci' or args.db_section == 'db-oldsci':
host = 'desdb-dr.ncsa.illinois.edu'
port = '1521'
name = 'desdr'
kwargs = {'host': host, 'port': port, 'service_name': name}
dsn = cx_Oracle.makedsn(**kwargs)
dbh = cx_Oracle.connect(args.user, args.password, dsn=dsn)
# Define the schema
if args.tag[0:4] == 'SVA1' or args.tag[0:4] == 'Y1A1':
schema = 'des_admin'
elif args.tag[0:3] == 'DR1':
schema = 'dr1'
elif args.tag[0:3] == 'DR2':
schema = 'dr2'
else:
schema = 'prod'
print "SCHEMA is", schema
# Get archive_root
archive_root = desthumbs.get_archive_root(dbh, schema=schema, verb=True)
# Make sure that outdir exists
if not os.path.exists(args.outdir):
if args.verb: sout.write("# Creating: %s\n" % args.outdir)
os.makedirs(args.outdir)
# Find all of the tilenames, indices grouped per tile
if args.verb: sout.write("# Finding tilename for each input position\n")
if searchbyID:
tilenames, ra, dec, indices, tilenames_matched = desthumbs.find_tilenames_id(
coadd_id, args.coaddtable, dbh, schema=schema)
else:
tilenames, indices, tilenames_matched = desthumbs.find_tilenames_radec(
ra, dec, dbh, schema=schema)
# Add them back to pandas dataframe and write a file
df['TILENAME'] = tilenames_matched
# Get the thumbname base names and the them the pandas dataframe too
df['THUMBNAME'] = get_base_names(tilenames_matched,
ra,
dec,
prefix=args.prefix)
matched_list = os.path.join(args.outdir,
'matched_' + os.path.basename(args.inputList))
df.to_csv(matched_list, index=False)
sout.write("# Wrote matched tilenames list to: %s\n" % matched_list)
# Make sure that all found tilenames *are* in the tag (aka data exists for them)
#tilenames_intag = desthumbs.get_tilenames_in_tag(dbh,args.tag)
# Loop over all of the tilenames
t0 = time.time()
Ntile = 0
for tilename in tilenames:
t1 = time.time()
Ntile = Ntile + 1
sout.write("# ----------------------------------------------------\n")
sout.write("# Doing: %s [%s/%s]\n" % (tilename, Ntile, len(tilenames)))
sout.write("# ----------------------------------------------------\n")
# 1. Get all of the filenames for a given tilename
filenames = desthumbs.get_coaddfiles_tilename_bytag(tilename,
dbh,
args.tag,
bands=args.bands,
schema=schema)
# ------------------
# IMPORTANT NOTE
# For SV1/Y2A1/Y3A1 we get one entry per band in the array,
# but for DR1, we get all entries independently, so the shape of the record arrays are different
# ------------------
if filenames is False:
sout.write("# Skipping: %s -- not in TAG:%s \n" %
(tilename, args.tag))
continue
# Fix compression for SV1/Y2A1/Y3A1 releases
else:
filenames = desthumbs.fix_compression(filenames)
indx = indices[tilename]
if schema == 'dr1':
avail_bands = desthumbs.get_avail_bands_dr1(filenames)
else:
avail_bands = filenames.BAND
# 2. Loop over all of the filename -- We could use multi-processing
p = {}
n_filenames = len(avail_bands)
for k in range(n_filenames):
# Rebuild the full filename with COMPRESSION if present
if 'COMPRESSION' in filenames.dtype.names:
filename = os.path.join(
archive_root, filenames.PATH[k],
filenames.FILENAME[k]) + filenames.COMPRESSION[k]
elif schema == 'dr1':
#filename = filenames[0][k].replace('/easyweb/files/dr1/','/des004/despublic/dr1_tiles/')
filename = filenames[0][k].replace(
'http://desdr-server.ncsa.illinois.edu', '/des004')
else:
filename = os.path.join(archive_root, filenames.PATH[k])
print filename
ar = (filename, ra[indx], dec[indx])
kw = {
'xsize': xsize[indx],
'ysize': ysize[indx],
'units': 'arcmin',
'prefix': args.prefix,
'outdir': args.outdir,
'tilename': tilename,
'verb': args.verb
}
if args.verb: sout.write("# Cutting: %s\n" % filename)
if args.MP:
NP = len(avail_bands)
p[filename] = mp.Process(target=desthumbs.fitscutter,
args=ar,
kwargs=kw)
p[filename].start()
else:
NP = 1
desthumbs.fitscutter(*ar, **kw)
# Make sure all process are closed before proceeding
if args.MP:
for filename in p.keys():
p[filename].join()
# 3. Create color images using stiff for each ra,dec and loop over (ra,dec)
for k in range(len(ra[indx])):
desthumbs.color_radec(ra[indx][k],
dec[indx][k],
avail_bands,
prefix=args.prefix,
colorset=args.colorset,
outdir=args.outdir,
verb=args.verb,
stiff_parameters={'NTHREADS': NP})
if args.verb:
sout.write("# Time %s: %s\n" %
(tilename, desthumbs.elapsed_time(t1)))
sout.write("\n*** Grand Total time:%s ***\n" % desthumbs.elapsed_time(t0))
return
# query.py
import cx_Oracle
cx_Oracle.init_oracle_client(lib_dir="/Users/xuansong/.bin/instantclient_19_8")
print("Client version: " + str(cx_Oracle.clientversion()))
# Establish the database connection
# dsn is short for data source name
dsn_tns = cx_Oracle.makedsn('localhost',
'1521',
service_name='ORCLCDB.localdomain')
conn = cx_Oracle.connect(user=r'hr', password='******', dsn=dsn_tns)
# Obtain a cursor
cursor = conn.cursor()
# Data for binding
managerId = 145
firstName = "Peter"
# Execute the query
# The data values in managerId and firstName are ‘bound’ to the statement placeholder ‘bind variables’: mid and : fn when the statement
# is executed. This separates the statement text from the data, which helps avoid SQL Injection security risks. Binding is also important
# for performance and scalability.
sql = """SELECT first_name, last_name
FROM employees
WHERE manager_id = :mid AND first_name = :fn"""
cursor.execute(sql, mid=managerId, fn=firstName)
# WITH SID
import cx_Oracle
dsn_tns = cx_Oracle.makedsn('localhost', '1521', 'xe')
conn = cx_Oracle.connect(user='******', password='******', dsn=dsn_tns)
c = conn.cursor()
c.execute('select count(*) from customers')
for row in c:
print(row)
conn.close()
# WITH SERVICE
import cx_Oracle
dsn_tns = cx_Oracle.makedsn('server', 'port', service_name='service_name')
conn = cx_Oracle.connect(user='******', password='******', dsn=dsn_tns)
c = conn.cursor()
c.execute('select count(*) from TABLE_NAME')
for row in c:
print(row)
conn.close()
def set_db_config(db_type, db_name=None, env="dev"):
if env == "dev":
if db_type == "mysql":
if not db_name:
db_name = "test"
host = "127.0.0.1"
user = "******"
passwd = "122901"
db_config = {
"host": host,
"port": 3306,
"user": user,
"passwd": passwd,
"db": db_name,
"charset": 'utf8',
"cursorclass": pymysql.cursors.
DictCursor # 加上cursorclass之后就可以直接把字段名捞出来，和字段值组成键值对的形式
}
else:
host = "rm-bp1t54ibu89062yg2lo.mysql.rds.aliyuncs.com"
user = "******"
passwd = "o7JFQIqhaEo8FGu^"
db_config = {
"host": host,
"port": 3306,
"user": user,
"passwd": passwd,
"db": db_name,
"charset": 'utf8',
"cursorclass": pymysql.cursors.
DictCursor # 加上cursorclass之后就可以直接把字段名捞出来，和字段值组成键值对的形式
}
elif db_type == "sqlite":
if not db_name:
db_name = "identify_record.db"
db_config = {'database': r'../case/{}'.format(db_name)}
elif db_type == "redis":
db_config = {
"host": "172.16.3.210",
"port": 6379,
"password": "******",
"max_connections": 1024
}
else:
if not db_name:
db_name = "testing"
tns = cx_Oracle.makedsn("127.0.0.1", 1521, db_name)
db_config = {
'user': "******",
'password': "******",
'dsn': tns,
'nencoding': "utf8"
}
else:
if db_type == "mysql":
host = "rm-bp1t54ibu89062yg2lo.mysql.rds.aliyuncs.com"
user = "******"
passwd = "o7JFQIqhaEo8FGu^"
db_config = {
"host": host,
"port": 3306,
"user": user,
"passwd": passwd,
"db": db_name,
"charset": 'utf8',
"cursorclass": pymysql.cursors.
DictCursor # 加上cursorclass之后就可以直接把字段名捞出来，和字段值组成键值对的形式
}
return db_config
from __future__ import print_function
import cx_Oracle
# Connect as user "hr" with password "welcome" to the "orclpdb1" service running on this computer.
dsn = cx_Oracle.makedsn("localhost", 32799, sid="ORCLCDB")
connection = cx_Oracle.connect("rdms", "rdms", dsn, encoding="UTF-8")
cursor = connection.cursor()
cursor.execute("""
SELECT *
FROM Airline
""")
for id, name, _ in cursor:
print("Values:", id, name)
# connection.autocommit = True
dataToInsert = []
for i in range(1, 21):
dataToInsert.append((i, "name" + str(i), "type" + str(i)))
cursor = connection.cursor()
# sql = ' ({}, \'{}\', \'{}\')'.format(i, 'name'+str(i), 'type'+str(i))
cursor.executemany("insert into AIRLINE values (:i, :j, :k)", dataToInsert)
cursor.close()
connection.commit()
cursor = connection.cursor()
cursor.execute("""
# dsn_tns = cx_Oracle.makedsn(r'ORAODSTB578B.azul.corp', '1521', service_name='TRAX_ODS')
# conn = cx_Oracle.connect(user=r'userselect', password=r'userselect', dsn=dsn_tns)
# fh = conn.cursor()
# fh.execute("""SELECT
# '01'||'/'||TO_CHAR(FLIGHT_DATE, 'MM')||'/'||TO_CHAR(FLIGHT_DATE, 'YYYY') MES,
# CASE
# WHEN AM.AC_TYPE = 'PILATUS-12' THEN 'PILATUS'
# WHEN AM.AC_TYPE = 'ATR72' THEN 'ATR' ELSE AM.AC_TYPE END AC_TYPE,
# ((SUM(AF.FLIGHT_HOURS)*60)+SUM(AF.FLIGHT_MINUTES))/60 FH
# FROM ODB.AC_ACTUAL_FLIGHTS AF INNER JOIN ODB.AC_MASTER AM ON AF.AC = AM.AC
# WHERE AF.FLIGHT_DATE>='01-JAN-2020' AND AF.FLIGHT_DATE<='31-JAN-2020'
# GROUP BY '01'||'/'||TO_CHAR(FLIGHT_DATE, 'MM')||'/'||TO_CHAR(FLIGHT_DATE, 'YYYY'), AM.AC_TYPE """)
# dffh = pd.DataFrame(fh, columns=['MES', 'FLEET', 'FH'])
# print(dffh)
import pandas as pd
import os
os.chdir(r'C:\Users\paulo.roberto\Documents\Movto Estoque Consolidado')
import cx_Oracle
dsn_tns = cx_Oracle.makedsn(r'ORAODSTB578B.azul.corp',
'1521',
service_name='TRAX_ODS')
conn = cx_Oracle.connect(user=r'userselect',
password=r'userselect',
dsn=dsn_tns)
fh = conn.cursor()
fh.execute(
"""SELECT WO.WO, WO.WO_DESCRIPTION, WO.WO_CATEGORY, WO.LOCATION, WO.SITE, WO.EXPENDITURE FROM ODB.WO"""
)
dffh = pd.DataFrame(fh, columns=['MES', 'FLEET', 'FH'])
print(dffh)
from bs4 import BeautifulSoup
from selenium import webdriver
from selenium.webdriver.common.keys import Keys
import requests
import time
import cx_Oracle
cx_Oracle.init_oracle_client(lib_dir=r"C:\instantclient_19_10")
url = 'https://www.youtube.com/gaming/games'
driver = webdriver.Chrome('chromedriver')
driver.get(url)
SCROLL_PAUSE_SEC = 1
body = driver.find_element_by_css_selector('body')
dsn = cx_Oracle.makedsn("222.232.15.205", 1521, "orcl")
db = cx_Oracle.connect("rootweb2020", "1234", dsn)
for i in range(10):
# 끝까지 스크롤 다운
body.send_keys(Keys.END)
# 1초 대기
time.sleep(SCROLL_PAUSE_SEC)
html = driver.page_source
soup = BeautifulSoup(html, 'html.parser')
i = 1
for tag in soup.select('div[id = "game"]'):
cursor = db.cursor()
print(
i,
def selects(request):
# Cargar archivo de configuración principal
config = configparser.ConfigParser()
config.read('settings.ini')
# Conexión
ip = config.get('database', 'DATABASE_HOST')
port = config.get('database', 'DATABASE_PORT')
SID = config.get('database',
'DATABASE_NAME') # Oracle System ID - Instancia
dsn_tns = cx_Oracle.makedsn(
ip, port, SID) # Realizar Data Source Name (Nombre de origen de datos)
cnxn = cx_Oracle.connect(config.get('database', 'DATABASE_USER'),
config.get('database', 'DATABASE_PASSWORD'),
dsn_tns)
# Aplicar filtros
socio_seleccionado = request.POST.getlist('socios')
socio_seleccionado = [i for i in socio_seleccionado if i != '']
socio_seleccionado = [
sub_item for item in socio_seleccionado for sub_item in item.split(",")
]
linea_fina_seleccionado = request.POST.getlist('linea_negocio')
linea_fina_seleccionado = [i for i in linea_fina_seleccionado if i != '']
linea_fina_seleccionado = [
sub_item for item in linea_fina_seleccionado
for sub_item in item.split(",")
]
periodo_seleccionado = request.POST.getlist('periodo')
periodo_seleccionado = [i for i in periodo_seleccionado if i != '']
periodo_seleccionado = [
sub_item for item in periodo_seleccionado
for sub_item in item.split(",")
]
canal_seleccionado = request.POST.getlist('canal')
canal_seleccionado = [i for i in canal_seleccionado if i != '']
canal_seleccionado = [
sub_item for item in canal_seleccionado for sub_item in item.split(",")
]
producto_seleccionado = request.POST.getlist('productos')
producto_seleccionado = [i for i in producto_seleccionado if i != '']
producto_seleccionado = [
sub_item for item in producto_seleccionado
for sub_item in item.split(",")
]
where2 = ' WHERE 1=1 '
list_2 = []
if len(socio_seleccionado) > 0:
a = tuple(socio_seleccionado)
b = '1=1'
l = list(a)
l.append(b)
socio_seleccionado = tuple(l)
where2 = where2 + " AND NOM_SOCIO IN " + str(socio_seleccionado)
if len(linea_fina_seleccionado) > 0:
a = tuple(linea_fina_seleccionado)
b = '1=1'
l = list(a)
l.append(b)
linea_fina_seleccionado = tuple(l)
where2 = where2 + " AND LINEA_NEGOCIO IN " + str(
linea_fina_seleccionado)
if len(periodo_seleccionado) > 0:
a = tuple(periodo_seleccionado)
b = '1=1'
l = list(a)
l.append(b)
periodo_seleccionado = tuple(l)
where2 = where2 + " AND PERIODO IN " + str(periodo_seleccionado)
if len(canal_seleccionado) > 0:
a = tuple(canal_seleccionado)
b = '1=1'
l = list(a)
l.append(b)
canal_seleccionado = tuple(l)
where2 = where2 + " AND CANAL IN " + str(canal_seleccionado)
if len(producto_seleccionado) > 0:
a = tuple(producto_seleccionado)
b = 0
l = list(a)
l.append(b)
producto_seleccionado = tuple(l)
where2 = where2 + " AND COD_PROD IN " + str(producto_seleccionado)
#############################################################################################################
# Consultar Base de Datos
# Query Unicos Tabla Productos
sql1 = """ SELECT DISTINCT COD_PROD, PERIODO, LINEA_NEGOCIO, NOM_SOCIO, CANAL
FROM ACTUARIA.TABLA_PRODUCTOS
""" + where2 + """ AND ESTADO != 'NO-DATA' """
# Dataframe Unicos
df_unicos = pd.read_sql(sql1, cnxn)
lista = list(df_unicos['COD_PROD'].unique())
for i in range(len(lista)):
lista[i] = int(lista[i])
lista.insert(0, 0)
# Query CART_VIGENTES_FACTOR
sql2 = """ SELECT FECHA_INICIO_EMITE AS FECHA_EMISION, FECHA_INICIO AS FECHA_CORTE, SUM(TOTAL_VIGENTES) AS TOTAL_VIGENTES
FROM ACTUARIA.CART_VIGENTES_TRIANG
WHERE PRODUCTO IN {0}
AND FECHA_INICIO_EMITE >= 201501
GROUP BY FECHA_INICIO_EMITE,FECHA_INICIO""".format(tuple(lista))
# Dataframe VIGENTES_TRIANG
frame_ = pd.read_sql(sql2, cnxn)
print('01-OK', len(df_unicos))
# sort values
frame_.sort_values(['FECHA_EMISION', 'FECHA_CORTE'], inplace=True)
# selected.fillna(0, inplace=True)
frame_['FECHA_EMISION'] = frame_['FECHA_EMISION'].astype(int)
frame_['FECHA_CORTE'] = frame_['FECHA_CORTE'].astype(int)
frame_['TOTAL_VIGENTES'] = frame_['TOTAL_VIGENTES'].astype(int)
if len(frame_) != 0:
unique_month = pd.DataFrame(frame_.FECHA_CORTE.unique())
unique_month.columns = ['MONTH']
month_join = pd.DataFrame(frame_.FECHA_CORTE.unique())
month_join.columns = ['FECHA_CORTE']
month_join['FECHA_EMISION'] = month_join['FECHA_CORTE']
list_end = []
frame_2 = pd.DataFrame()
n = 0
for i in range(0, len(month_join)):
list_mer = list(np.arange(n, len(month_join), 1))
zmonth_join2 = month_join.iloc[list_mer]
maximo = zmonth_join2['FECHA_CORTE'].min()
zmonth_join2['FECHA_EMISION'] = maximo
result = zmonth_join2
list_end.append(result)
n += 1
frame_2 = pd.concat(list_end)
# Merge Simetria
frame = pd.merge(frame_2,
frame_,
how='left',
on=['FECHA_EMISION', 'FECHA_CORTE'])
frame = frame.replace(np.inf, np.nan).fillna(0)
#frame.to_excel(
# 'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/frame-simetria-merge.xlsx',
# sheet_name='frame-simetria')
unique_sime = pd.DataFrame(frame.FECHA_CORTE.unique())
unique_sime.columns = ['FECHA_CORTE']
unique_sime = unique_sime[-3:]
# unique_sime['FECHA_CORTE'] = unique_sime['FECHA_CORTE'].astype(int)
unique_sime = list(unique_sime['FECHA_CORTE'])
frame = frame[(frame['FECHA_CORTE'].isin(unique_sime) == False)]
#frame.to_excel(
# 'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/2-frame-simetria-merge.xlsx',
# sheet_name='frame-simetria')
# delete temp dataframe
del [[frame_, frame_2, month_join]]
frame['FECHA_CORTE'] = frame['FECHA_CORTE'].astype(str)
frame['QUARTER_YEAR'] = frame['FECHA_CORTE'].str[:4]
frame['QUARTER_MONTH'] = frame['FECHA_CORTE'].str[4:]
frame['FECHA_EMISION'] = frame['FECHA_EMISION'].astype(str)
frame['Q_OCURR_YEAR'] = frame['FECHA_EMISION'].str[:4]
frame['Q_OCURR_MONTH'] = frame['FECHA_EMISION'].str[4:6]
# Calculo LAG = ((dt_two.year - dt_one.year)*12 + (dt_two.month - dt_one.month))/3.0
frame['LAG'] = ((frame['QUARTER_YEAR'].astype(int) -
frame['Q_OCURR_YEAR'].astype(int)) * 12 +
(frame['QUARTER_MONTH'].astype(int) -
frame['Q_OCURR_MONTH'].astype(int))) # / 3
# PIVOTE DATA FRAMES
tria_frame = pd.pivot_table(data=frame,
values='TOTAL_VIGENTES',
index=['FECHA_EMISION'],
columns=['LAG'],
aggfunc=np.sum) # ;print(tria_count)
#tria_frame.to_excel(
# 'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/1-tria_frame.xlsx',
# sheet_name='tria_frame')
#######################################################################################################################################################################################
################################################################################## Triangulo Polizas ##################################################################################
#######################################################################################################################################################################################
copy_frame = tria_frame.copy()
def triangulo_factors(tria_a):
def isnumpy(item):
return 'numpy' in str(type(item))
def Lapply(_list, fun, **kwargs):
iSize = _list.__len__()
out = list()
for index in range(0, iSize):
out.append(fun(_list[index], **kwargs))
if isnumpy(_list):
out = np.array(out)
return out
def GetFactor(index, mTri):
colSum = mTri[:np.negative(index + 1),
index:(index + 2)].sum(0)
colSum2 = colSum[1] / colSum[0]
col_mean = np.nan_to_num(colSum2)
if (col_mean == 0):
col_mean = col_mean + 1
else:
col_mean
return col_mean
def GetChainSquare(mClaimTri):
iSize = mClaimTri.shape[1]
dFactors = Lapply(np.arange(iSize - 1),
GetFactor,
mTri=mClaimTri)
print('dFactors:', dFactors)
list_2.append(dFactors)
dAntiDiag = mClaimTri[:, ::-1].diagonal()[1:]
for index in range(dAntiDiag.size):
mClaimTri[index + 1, np.negative(index + 1):] = dAntiDiag[
index] * dFactors[np.negative(index + 1):].cumprod()
return mClaimTri
dff_2 = tria_a.values
factor_ = GetChainSquare(dff_2)
factor2_a = pd.DataFrame(factor_)
factor2 = factor2_a.replace(np.inf, np.nan).fillna(0)
return (factor2)
# call function triangle
ultim_polizas = triangulo_factors(copy_frame)
ultim_polizas = pd.DataFrame(ultim_polizas)
ultim_polizas = pd.concat([unique_month, ultim_polizas], axis=1)
# concatenar lista & get last 3 factors
frame_2 = pd.DataFrame(np.concatenate(list_2))
frame_2.columns = ['FACTORS']
frame_2 = frame_2[-3:].reset_index()
frame_2 = frame_2.drop(['index'], axis=1)
# Minimo 3 factores para Calcular
if len(frame_2) == 3:
# Calcular 60 factores
factors_60 = frame_2.copy()
for i in range(3, 63):
factors_60.loc[i, 'FACTORS'] = np.minimum(
(((factors_60.loc[i - 1, 'FACTORS'] +
factors_60.loc[i - 2, 'FACTORS'] +
factors_60.loc[i - 3, 'FACTORS']) / 3) +
((factors_60.loc[i - 3:i - 1, 'FACTORS']).std(axis=0)) *
0.5), 1)
# print('frame_3::', frame_3)
#factors_60.to_excel(
# 'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/factors_60.xlsx',
# sheet_name='factors_60')
# separate last 60 factors
factors_60 = factors_60[-60:].reset_index()
factors_60 = factors_60.drop(['index'], axis=1)
############################# Proyectar a 60 meses ############################################################################
months = range(1, 61)
# name last column
ultim_polizas['ep0'] = ultim_polizas.iloc[:, -1]
j = 0
for month in months:
colname = 'ep%d' % month
prev_colname = 'ep%d' % (month - 1)
ultim_polizas[colname] = ultim_polizas[
prev_colname] * factors_60.loc[j, 'FACTORS']
j += 1
ultim_polizas.drop('ep0', axis=1, inplace=True)
#ultim_polizas.to_excel(
# 'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/2-ultim_polizas.xlsx',
# sheet_name='ultim_polizas')
# delete temp dataframe
del [[
frame, frame_2, tria_frame, unique_month, factors_60,
copy_frame
]]
################################################################################## End Triangulo Polizas ##############################################################################
# %%
#######################################################################################################################################################################################
################################################################################## Triangulo Porcentajes ##############################################################################
#######################################################################################################################################################################################
porcen_all = ultim_polizas.copy()
# Rename Columns
cols = porcen_all.columns.tolist()
porcen_all.columns = cols[:1] + [
'ep%i' % i for i in range(0,
len(cols[2:]) + 1)
]
ultim_porc = porcen_all[['MONTH', 'ep0']]
# limit of columns range
number_cols = ultim_polizas.shape[1] - 1
months = range(1, number_cols)
k = 1
for month in months:
colname = 'ep%d' % month # ;print('colname::',colname)
prev_colname = 'ep%d' % (
month - k) # ;print('prev_colname::',prev_colname)
ultim_porc[
colname] = porcen_all[colname] / porcen_all[prev_colname]
k += 1
ultim_porc = ultim_porc.replace(np.inf, np.nan).fillna(0)
#ultim_porc.to_excel(
# 'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/3-ultim_porc.xlsx',
# sheet_name='ultim_porc')
# delete temp dataframe
del [[porcen_all]]
#######################################################################################################################################################################################
################################################################################# % Persistencia mes a mes por año de venta############################################################
#######################################################################################################################################################################################
ultim_v_pre = pd.DataFrame(
(ultim_porc['MONTH'].astype(str).str[:4]).unique())
ultim_v_pre.columns = ['MONTH']
# limit of columns range
number_cols2 = ultim_porc.shape[1] - 1
months = range(1, number_cols2)
k = 1
for month in months:
colname = 'ep%d' % month # ;print('colname::',colname)
prev_colname = 'ep%d' % (
month - k) # ;print('prev_colname::',prev_colname)
colname2 = 'ep%d' % month # ;print('colname2::',colname2)
def my_func2(row2):
# Suma-Producto / Suma
d = ((row2[prev_colname] *
row2[colname]).sum()) / np.maximum(
((row2[prev_colname]).sum()),
0.00000000001) # ;print('d:', d)
return pd.Series({colname2: d})
result = ultim_porc.groupby(
(ultim_porc['MONTH'].astype(str).str[:4]
)).apply(my_func2).reset_index()
ultim_persis = pd.merge(ultim_v_pre,
result,
how='left',
on=['MONTH'])
ultim_v_pre = ultim_persis
k += 1
####################################################################################################
# Calculate Column ep0
def my_func0(row_0):
colname = 'ep0'
d = (row_0[colname].sum()) / np.maximum(
(row_0[colname].sum()), 0.00000000001)
return pd.Series({colname: d})
result_0 = ultim_porc.groupby(
(ultim_porc['MONTH'].astype(str).str[:4]
)).apply(my_func0).reset_index()
ultim_persis = pd.merge(ultim_persis,
result_0,
how='left',
on=['MONTH'])
# Move position ep0
ep0 = ultim_persis['ep0']
ultim_persis.drop(labels=['ep0'], axis=1, inplace=True)
ultim_persis.insert(1, 'ep0', ep0)
####################################################################################################
ultim_persis.rename(columns={'MONTH': 'ANUAL'}, inplace=True)
# mean column ep0
mean_ep0 = ultim_persis['ep0'].mean()
# Calcular Totals Persistencia mes a mes por año de venta
ultim_persis.loc[len(ultim_persis)] = [
((ultim_porc['ep0'] * ultim_porc[col]).sum()) /
((ultim_porc['ep0']).sum()) for col in ultim_porc.columns
]
# Change values Total and ep0
ultim_persis.at[ultim_persis.index[-1],
['ANUAL', 'ep0']] = ['Total', mean_ep0]
#ultim_persis.to_excel(
# 'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/4-ultim_persis.xlsx',
# sheet_name='ultim_persis')
del [[ultim_polizas, ultim_v_pre, result]]
#######################################################################################################################################################################################
################################################################################# Tasa de Caida Mensual############################################################
#######################################################################################################################################################################################
ultim_tasa = pd.DataFrame(ultim_persis['ANUAL'])
# limit of columns range
number_cols3 = ultim_persis.shape[1] - 1
months = range(0, number_cols3)
k = 1
for month in months:
colname = 'ep%d' % month # ;print('columns:',colname)
prev_colname = k - 1
ultim_tasa[colname] = (-np.log(ultim_persis[colname]) /
prev_colname)
k += 1
ultim_tasa = ultim_tasa.replace(np.inf, np.nan).fillna(0)
#ultim_tasa.to_excel(
# 'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/5-ultim_tasa.xlsx',
# sheet_name='ultim_tasa')
del [[ultim_porc]]
#######################################################################################################################################################################################
################################################################################# % Persistencia meses trimestrales por año de venta ############################################################
#######################################################################################################################################################################################
list_C = list(np.arange(1, len(ultim_persis), 1))
list_C.insert(0, 0)
list_Q = list(np.arange(1, 46, 3))
list_Q.insert(0, 0)
# Quarters
ultim_persis_Q = ultim_persis.iloc[list_C, list_Q]
ultim_persis_Q['Duration'] = ultim_persis.iloc[:, 1:62].sum(axis=1)
# ultim_persis_Q['Duration'] = ultim_persis_Q['Duration'] / 100
ultim_persis_Q['Lapse_rate'] = ultim_tasa.iloc[:,
2:62].mean(axis=1)
#ultim_persis_Q.to_excel(
# 'Q:/Transversal/Scripts-Outputs-IT/Bokeh_Projects/Dash_Persistencia/bokeh_app_v4/excel_dash/6-ultim_persis_Q.xlsx',
# sheet_name='ultim_persis_Q')
resultado = ultim_persis_Q
del [[ultim_tasa, ultim_persis]]
else:
resultado = pd.DataFrame(
[[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]],
columns=[
'ANUAL', 'ep0', 'ep3', 'ep6', 'ep9', 'ep12', 'ep15',
'ep18', 'ep21', 'ep24', 'ep27', 'ep30', 'ep33', 'ep36',
'ep39', 'ep42', 'Duration', 'Lapse_rate'
])
else:
resultado = pd.DataFrame(
[[0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0]],
columns=[
'ANUAL', 'ep0', 'ep3', 'ep6', 'ep9', 'ep12', 'ep15', 'ep18',
'ep21', 'ep24', 'ep27', 'ep30', 'ep33', 'ep36', 'ep39', 'ep42',
'Duration', 'Lapse_rate'
])
print('02-OK')
#resultado.to_excel('C:/Users/b89591/Desktop/test01.xlsx', sheet_name='test01')
#############################################################################################################################################################
# Data Tables
data_table_1 = resultado.copy()
decimals = 4
data_table_1['ep0'] = data_table_1['ep0'].apply(
lambda x: round(x, decimals))
data_table_1['ep3'] = data_table_1['ep3'].apply(
lambda x: round(x, decimals))
data_table_1['ep6'] = data_table_1['ep6'].apply(
lambda x: round(x, decimals))
data_table_1['ep9'] = data_table_1['ep9'].apply(
lambda x: round(x, decimals))
data_table_1['ep12'] = data_table_1['ep12'].apply(
lambda x: round(x, decimals))
data_table_1['ep15'] = data_table_1['ep15'].apply(
lambda x: round(x, decimals))
data_table_1['ep18'] = data_table_1['ep18'].apply(
lambda x: round(x, decimals))
data_table_1['ep21'] = data_table_1['ep21'].apply(
lambda x: round(x, decimals))
data_table_1['ep24'] = data_table_1['ep24'].apply(
lambda x: round(x, decimals))
data_table_1['ep27'] = data_table_1['ep27'].apply(
lambda x: round(x, decimals))
data_table_1['ep30'] = data_table_1['ep30'].apply(
lambda x: round(x, decimals))
data_table_1['ep33'] = data_table_1['ep33'].apply(
lambda x: round(x, decimals))
data_table_1['ep36'] = data_table_1['ep36'].apply(
lambda x: round(x, decimals))
data_table_1['Duration'] = data_table_1['Duration'].apply(
lambda x: round(x, decimals))
data_table_1['Lapse_rate'] = data_table_1['Lapse_rate'].apply(
lambda x: round(x, decimals))
data_table_01 = []
for i, row in data_table_1.iterrows():
ep0 = '{:.2%}'.format(row['ep0'])
ep3 = '{:.2%}'.format(row['ep3'])
ep6 = '{:.2%}'.format(row['ep6'])
ep9 = '{:.2%}'.format(row['ep9'])
ep12 = '{:.2%}'.format(row['ep12'])
ep15 = '{:.2%}'.format(row['ep15'])
ep18 = '{:.2%}'.format(row['ep18'])
ep21 = '{:.2%}'.format(row['ep21'])
ep24 = '{:.2%}'.format(row['ep24'])
ep27 = '{:.2%}'.format(row['ep27'])
ep30 = '{:.2%}'.format(row['ep30'])
ep33 = '{:.2%}'.format(row['ep33'])
ep36 = '{:.2%}'.format(row['ep36'])
Lapse_rate = '{:.2%}'.format(row['Lapse_rate'])
Duration = '{0:,.2f}'.format(row['Duration'])
data_table_01.append({
"ANUAL": str(row['ANUAL']),
"ep0": ep0,
"ep3": ep3,
"ep6": ep6,
"ep9": ep9,
"ep12": ep12,
"ep15": ep15,
"ep18": ep18,
"ep21": ep21,
"ep24": ep24,
"ep27": ep27,
"ep30": ep30,
"ep33": ep33,
"ep36": ep36,
"Duration": Duration,
"Lapse_rate": Lapse_rate
})
# ***************************************************************************************************#
# Data Graph 1
graph_1 = resultado.copy()
# traspuesta
graph_1 = graph_1.set_index('ANUAL').T.reset_index()
graph_1 = graph_1.drop(['index'], axis=1)
if '2015' not in graph_1:
graph_1['2015'] = 0
if '2016' not in graph_1:
graph_1['2016'] = 0
if '2017' not in graph_1:
graph_1['2017'] = 0
if '2018' not in graph_1:
graph_1['2018'] = 0
graph_1['MONTH'] = graph_1.index
graph_1['MONTH'] = graph_1['MONTH'] * 3
# reorder
graph_1 = graph_1[['MONTH', '2015', '2016', '2017', '2018', 'Total']]
#print('data_trans01:', data_trans)
graph_1.columns = graph_1.columns.astype(str).str.replace(r"[2]", "_2")
# data_trans.rename(columns={'2015':'_2015', '2016':'_2016', '2017':'_2017', '2018':'_2018' }, inplace=True)
list_C = list(np.arange(0, (len(graph_1) - 2), 1))
list_Q = list(np.arange(0, (len(graph_1.columns))))
# Quarters
graph_1 = graph_1.iloc[list_C, list_Q]
#graph_1.to_excel('C:/Users/b89591/Desktop/data_trans.xlsx', sheet_name='data_trans', index = None)
# Format Graph AmCharts (Multiplica *100)
graph_1['_2015'] = graph_1['_2015'] * 100
graph_1['_2016'] = graph_1['_2016'] * 100
graph_1['_2017'] = graph_1['_2017'] * 100
graph_1['_2018'] = graph_1['_2018'] * 100
graph_1['Total'] = graph_1['Total'] * 100
#decimals = 3
#graph_3['q_x'] = graph_3['q_x'].apply(lambda x: round(x, decimals))
graphs_1 = []
for i, row in graph_1.iterrows():
MONTH = '{0:,.0f}'.format(row['MONTH'])
graphs_1.append({
"MONTH": MONTH,
"2015": str(row['_2015']),
"2016": str(row['_2016']),
"2017": str(row['_2017']),
"2018": str(row['_2018']),
"Total": str(row['Total'])
})
################################################################################################
# Data Graph 2
graph_2 = resultado.copy()
graph_2 = graph_2[['ANUAL', 'Duration', 'Lapse_rate']]
# data_rate = data_rate[1:(len(data_rate)-1)]
graph_2 = graph_2[:-2]
#Remove last column
#graph_2 = graph_2.iloc[:, :-1]
#graph_2['ANUAL'] = graph_2['ANUAL'].astype(int)
#graph_2['ANUAL_STR'] = graph_2['ANUAL'].astype(str)
# Format Graph AmCharts (Multiplica *100)
graph_2['Lapse_rate'] = graph_2['Lapse_rate'] * 100
# decimals = 3
# graph_3['q_x'] = graph_3['q_x'].apply(lambda x: round(x, decimals))
graphs_2 = []
for i, row in graph_2.iterrows():
Duration = '{0:,.1f}'.format(row['Duration'])
graphs_2.append({
"ANUAL": str(row['ANUAL']),
"Duration": Duration,
"Lapse_rate": str(row['Lapse_rate']),
})
#print(graphs_2)
# UNICOS SELECT
socios = list(df_unicos['NOM_SOCIO'].unique())
socios.sort()
# print('socios', socios)
# linea = list(df_unicos['LINEA'].unique());
# linea.sort();
linea_negocio = list(df_unicos['LINEA_NEGOCIO'].unique())
# linea_negocio.sort();
# print('linea_negocio', linea_negocio)
periodo = list(df_unicos['PERIODO'].unique())
periodo.sort()
# print('periodo', periodo)
canal = list(df_unicos['CANAL'].unique())
canal.sort()
# print('canal', canal)
df_unicos['COD_PROD'] = df_unicos['COD_PROD'].astype(str)
productos = list(df_unicos['COD_PROD'].unique())
productos.sort(key=int)
# tipo = list(df_unicos['TIPO'].unique());
# tipo.sort();
cnxn.close()
return JsonResponse([
socios, linea_negocio, periodo, canal, productos, data_table_01,
graphs_1, graphs_2
],
safe=False)
logging.warning('warning message')
logging.error('error message')
logging.critical('critical message')
logging.basicConfig(
level=logging.DEBUG, # 控制台打印的日志级别
filename='new.log',
filemode='a', ##模式，有w和a，w就是写模式，每次都会重新写日志，覆盖之前的日志,a是追加模式，默认如果不写的话，就是追加模式
format=
'%(asctime)s - %(pathname)s[line:%(lineno)d] - %(levelname)s: %(message)s'
)
# 数据库参数设置
options_barra_database = {
'user': '******',
'password': '******',
'dsn': cx_Oracle.makedsn('172.16.100.188', '1522', 'markdb')
}
connect_barra = ConnectOracle(options_barra_database['user'],
options_barra_database['password'],
options_barra_database['dsn'])
options_xrisk_database = {
'user': '******',
'password': '******',
'dsn': cx_Oracle.makedsn('172.16.100.230', '1521', 'xrisk')
}
connect_xrisk = ConnectOracle(options_xrisk_database['user'],
options_xrisk_database['password'],
options_xrisk_database['dsn'])
options_winddb_datebase = {
'server': '172.16.100.7',
'user': '******',
def __get_project(region: str, data_source: str):
""" the main function, include three part: 1st is getting objects from oracle; 2nd is getting flag and coordinate
by the api of amap, 3rd is update data and compute the distances of name and address' coordinates
:param:
region {str} - the name of region
data_source {str} - the code of region
:return:
any
"""
__connect = {
"host": "172.29.251.71",
"port": "1521",
"sid": "orcl",
"account": "assessprice",
"psw": "assessprice",
}
__region_name = region
__region_code = data_source
try:
__dsn = oracle.makedsn(__connect["host"], __connect["port"],
__connect["sid"])
__conn = oracle.connect(__connect["account"], __connect["psw"], __dsn)
__cursor = __conn.cursor()
__cursor.prepare(
"SELECT ID, PROJECT_NAME, GD_NAME, LJ_NAME, DISTRICT_ADD, "
"REGION, ADD_LNG, ADD_LAT, NAME_LNG, NAME_LAT "
"FROM ASSESSPRICE.TMP_LJ_DISTRICT_REL "
"WHERE REGION =:region")
__cursor.execute(None, {"region": __region_name})
__data = __rows_as_dicts(__cursor)
__m = len(__data)
__n = 0
for __d in __data:
__r = amp_class.or_proj(__d)
__addr = "%s%s" % (__region_name, __r.ds_addr)
__flag, __p_lng, __p_lat = __addr_2_coor(__addr, __region_code)
if __flag == '1':
__update_date = datetime.datetime.now().strftime(
'%Y-%m-%d %H:%M:%S')
__fid = __r.r_id
__distance = coor_dis(__r.name_lng, __r.name_lat, __r.addr_lng,
__r.addr_lat)
print(__r.r_id, __r.pj_name, __r.ds_addr, __r.addr_lng,
__r.addr_lat, __p_lng, __p_lat, __distance)
print("\n")
__cursor.prepare(
"UPDATE ASSESSPRICE.TMP_LJ_DISTRICT_REL "
"SET ADD_LNG = :addr_lng, ADD_LAT = :addr_lat, DISTANCE = :distance, "
"SYS_UPDATE_TIME = to_date(:update_time,'yyyy-mm-dd hh24:mi:ss')"
"WHERE ID = :fid")
__cursor.execute(
None, {
"addr_lng": __p_lng,
"addr_lat": __p_lat,
"distance": __distance,
"update_time": __update_date,
"fid": __fid
})
__conn.commit()
__n = __n + 1
if __n == 1000:
time.sleep(20)
print("等待20s！")
else:
time.sleep(0.2)
__cursor.close()
__conn.close()
print("执行完成！%s 共 %s 条数据, 更新 %s 条！" % (__region_name, __m, __n))
except Exception as e:
raise e
finally:
pass
# -*- coding:utf-8 -*-
import cx_Oracle
import os
import sys
from influxdb import InfluxDBClient
os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.UTF8'
#os.environ['NLS_LANG'] = 'SIMPLIFIED CHINESE_CHINA.AL32UTF8'
#os.environ['NLS_LANG'] = 'AMERICAN_AMERICA.AL32UTF8'
reload(sys)
sys.setdefaultencoding('utf8')
#client = InfluxDBClient('localhost',8086,'root',",")
cardno_entry = '$45CA30662C7F368F11D6DCA7BDF2F1901A48A0E6792BE92F'
dsn_tns = cx_Oracle.makedsn('10.214.2.71', 1530, 'efsdb')
db = cx_Oracle.connect('devro', 'SLI8fE05', dsn_tns)
cursor = db.cursor()
cursor.execute("""select
t.cardno ,t.name ,t.pair_similarity ,h.acct_id ,
h.req_time ,
y.file_name||'.jpg' ,
replace(y.file_path,'/opt/netdata/Creditdata','/u01/data/sata2/pics_cd')
from cpdb_ds.t_ds_yitu_auth_result@kqcd_cpdb_ds t ,cpdb_app.t_sys_req_header@kqcd_cpdb_app h,
cpdb_app.t_sys_file_factory@kqcd_cpdb_app y
where t.cardno = '%s'
and t.photo_id is not null
and t.trade_id = h.trade_id and y.id = t.photo_id
and h.acct_id in ('coc_act_app_user','coc_kyh_user','coc_zyt_user','coc_kyh2_user')
and t.pair_similarity >= 66
union all
SEND_MESSAGE_URL = 'http://58.87.84.167:5000/send-message'
FIND_ALL_URL = 'https://op.stariboss.com/message-center-service/send-message/all'
GROUP_NAME = '新BOSS告警'
HOST = 'starboss.csxbifqn7nwb.eu-west-1.rds.amazonaws.com'
PORT = '1521'
SID = 'STARBOSS'
USER = '******'
PASSWORD = '******'
SEND_NOT_RECEIVE_SQL = 'SELECT company_id,count(id) FROM BOSS_PMS_PARTITION.EQUIPMENT_INSTRUCTION ei where ei.STATUS = 1 and ei.DEAL_STATUS in (4, 6) and ei.LAST_SEND_INSTANT < sysdate - 1/24 GROUP BY company_id'
dsn = cx_Oracle.makedsn(HOST, PORT, SID)
dsn_tns = dsn.replace('SID', 'SERVICE_NAME')
def monitor():
try:
conn = cx_Oracle.connect(USER, PASSWORD, dsn_tns)
handle_send_not_receive_message(conn)
conn.close()
except Exception as e:
logging.exception(e)
def handle_send_not_receive_message(conn):
not_receive_cursor = conn.cursor()
not_receive_cursor.execute(SEND_NOT_RECEIVE_SQL)
def make_dsn(self, dsn):
if "DESCRIPTION" in dsn.upper():
return dsn
else:
_dsn = re.search(r"(.+):(\d+)/(\w+)", dsn)
return cx_Oracle.makedsn(*_dsn.groups())
def process_data(iterator):
sql_insert = generateInsertSQL("ITR23.RLT_ESCLT_DVC_D",
"./RLT_ESCLT_DVC_D.conf")
dsn = cx_Oracle.makedsn(info["host"], info["port"], info["sid"])
con = cx_Oracle.connect(info["username"], info["passward"], dsn)
cursor = con.cursor()
for oneArray in iterator:
oneArray = list(eval(oneArray))
for i in range(len(oneArray)):
afterProcessArray = []
oneTuple = oneArray[i]
delElement = [13, 12, 11, 9]
for i in delElement:
del oneTuple[i]
checkDataMd5 = ''
dvc_id = str(oneTuple[0])
# operating a tuple of a list
for j in range(len(oneTuple)):
oneElement = oneTuple[j]
if oneElement != None:
if type(oneElement) is str:
proData = oneElement.strip()
if proData == "t" and j == 8 or j == 14:
proData = "y"
if proData == "f" and j == 8 or j == 14:
proData = "n"
afterProcessArray.append(proData)
checkDataMd5 = checkDataMd5 + proData
else:
afterProcessArray.append(oneElement)
checkDataMd5 = checkDataMd5 + str(oneElement)
else:
afterProcessArray.append('NULL')
#check if the md5 are the same or existence
md5_str = hashlib.md5(checkDataMd5).hexdigest()
operation = []
operation.append("get")
operation.append(dvc_id)
rs = RedisOp(operation)
dvc_id = 'RLT_ESCLT_DVC_D' + dvc_id
if rs[0]:
r_s = list(eval(rs[1]))
if r_s[1] != md5_str:
r_s[1] = md5_str
afterProcessArray.insert(0, r_s[0])
r = tuple(afterProcessArray)
#update the data int the database
update_sql = generateUpdateSql("ITR23.RLT_ESCLT_DVC_D",
"RLT_ESCLT_DVC_D.conf",
["ESCLT_DVC_D_KY", r_s[0]])
res = cursor.execute(update_sql % r)
con.commit()
RedisOp(["set", dvc_id, r_s])
else:
res = RedisOp(["get", "ESCLT_DVC_D_count"])
r_s = []
if res[0]:
count = int(res[1]) + 1
r_s.append(count)
#inster data to the database
afterProcessArray.insert(0, count)
r = tuple(afterProcessArray)
sql_v = sql_insert % r
res = cursor.execute((sql_insert % r))
con.commit()
RedisOp(["set", "ESCLT_DVC_D_count", count])
else:
r_s.append(1)
#inster data to the database
afterProcessArray.insert(0, 1)
r = tuple(afterProcessArray)
sql_v = sql_insert % r
res = cursor.execute(sql_v)
con.commit()
RedisOp(["set", "ESCLT_DVC_D_count", 1])
r_s.append(md5_str)
RedisOp(["set", dvc_id, r_s])
def initOracleConnect(self):
oracle_tns = cx_Oracle.makedsn('XXX.XXX.XXX.XXX', 1521, 'XX')
if self.connCnt == 0:
self.connectObj = cx_Oracle.connect('oracleUserName', 'password',
oracle_tns)
self.connCnt += 1
"""
Create Time: 2020/3/27 9:56
Author: FengkaiXiao
"""
import cx_Oracle
import random
NUM = 0
'''
方法三：配置监听并连接
'''
tns = cx_Oracle.makedsn('192.168.125.135', 1521, 'helowin')
db = cx_Oracle.connect('system', 'system', tns)
cursor = db.cursor()
for i in range(100):
Ageint = 20 + random.randint(0, 40)
IMCONME = 10000 + random.randint(0, 1000)
NUM += 1
NAME = 'name' + str(NUM)
Nation = 'CHINA', 'JAPAN', 'UK', 'USA', 'GERMAN', 'FRANCE', 'KOREA', 'BRAZIL'
Lastname = 'SMTH', 'KOBE', 'MICHEAEL', 'JASON', 'F**K', 'STUPID'
Hobby = 'FOOTBALL', 'BASKETBALL', 'VOLLEBALL'
COME = random.choice(Nation)
LASTNAME = random.choice(Lastname)
HOBBY = random.choice(Hobby)
sql = "INSERT INTO XFK_ORACLE_10.XFK10(id, first_name, middle_name, last_name, come_from, age, income, hobby) VALUES ('%d', '%s','%s','%s', '%s', '%d', '%d' ,'%s')" % (
NUM, NAME, COME, LASTNAME, COME, Ageint, IMCONME, HOBBY)
cursor.execute(sql)
db.commit()
from selenium import webdriver
import re
from time import sleep
import pymongo
from selenium.common.exceptions import NoSuchElementException
import os
import cx_Oracle # @UnresolvedImport
from konlpy.tag import Twitter
#몽고디비 접속
conn = pymongo.MongoClient('192.168.1.28', 27017)
db = conn.gaduda
collection = db.rep_test
#오라클 접속
ora_dsn = cx_Oracle.makedsn('192.168.1.20', 1521, 'orcl')
ora_conn = cx_Oracle.connect('ora_user', '1234', ora_dsn)
ora_cursor = ora_conn.cursor()
#driver = webdriver.Chrome('D:\chromedriver_win32\chromedriver')
driver = webdriver.PhantomJS(
executable_path=r'C:\phantomjs-2.1.1-windows\bin\phantomjs')
#site = 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1322253347&trTypeCd=20&trCtgrNo=585021&lCtgrNo=1001364&mCtgrNo=1002273'
#site= 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=96531135&trTypeCd=20&trCtgrNo=1002273'
#site = 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1183869688&trTypeCd=21&trCtgrNo=1009053'
#site = 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1367968916&trTypeCd=20&trCtgrNo=1002276'
#site = 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1335741053&trTypeCd=21&trCtgrNo=1002276'
#site = 'http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1319927303&trTypeCd=20&trCtgrNo=1002276'
"""
site_arr = ['http://www.11st.co.kr/product/SellerProductDetail.tmall?method=getSellerProductDetail&prdNo=1319927303&trTypeCd=20&trCtgrNo=1002276',
