Demo entry 6361533

test

   

Submitted by anonymous on May 04, 2017 at 15:43
Language: Python. Code size: 4.6 kB.

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# This script will be run a query against OTCnet database and save results to a CSV File
# Last modified on 1/15/2017 @ 11:37am
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
import os
import cx_Oracle
import csv
import time
import datetime
import pandas as pd
import xlsxwriter
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# DB variables
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
QAEC = 'cbaxs01/Ramb$500@10.20.103.141:26385/OTCNE'
QAIF = 'cbaxs01/Ramb$500@10.20.103.116:29085/OTCNIF'
QAIC = 'cbaxs01/Ramb$500@10.20.103.134:26285/OTCNI'
DB = QAIC
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#  *** SQL Queries *****
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
#SQL="SELECT * FROM OTCNET.AGENCY_SITE"
#SQL = "SELECT * FROM OTCNET.CHECK_PAYMENT where created_ts > sysdate-3"
#SQL = "SELECT * FROM OTCNET.agency_site"
#SQL = "SELECT count(*) FROM OTCNET.BATCH WHERE BATCH_STATUS_CODE='O' AND ACTIVE_FLG='N'  AND CURR_DOLLAR_AMT = '10'AND CREATED_TS > '12-AUG-13' AND CREATED_LOGONID IN ('batchloader')"
# SQL = "SELECT A.*, B.*, C.*, D.* FROM (SELECT COUNT(*) AS CHECKS_SAVED FROM OTCNET.CHECK_PAYMENT WHERE SCANNER_SERIAL_NUM!='batchloader'AND CREATED_TS BETWEEN TO_DATE('01/23/2017 01:00:00 PM', 'MM/DD/YYYY HH:MI:SS PM') AND TO_DATE('01/23/2017 11:59:59 PM', 'MM/DD/YYYY HH:MI:SS PM')) A, /* Number of Deposits SUBMITTED in a given time period */ (SELECT COUNT(*) AS DEPOSITS_CREATED FROM OTCNET.DEPOSIT WHERE DEPOSIT.STATUS_CD in ('SUBMITTED','AWAP') AND CREATED_TS BETWEEN TO_DATE('01/23/2017 01:00:00 PM', 'MM/DD/YYYY HH:MI:SS PM') AND TO_DATE('01/23/2017 11:59:59 PM', 'MM/DD/YYYY HH:MI:SS PM')) B, /* Number of Deposits CONFIRMED in a given time period */ (SELECT COUNT(*) AS DEPOSITS_CONFIRMED FROM OTCNET.DEPOSIT WHERE DEPOSIT.STATUS_CD='CONFIRMED'AND CONFIRMED_TS BETWEEN TO_DATE('01/23/2017 01:00:00 PM', 'MM/DD/YYYY HH:MI:SS PM') AND TO_DATE('01/23/2017 11:59:59 PM', 'MM/DD/YYYY HH:MI:SS PM')) C, /* Number of Batches CLOSED in a given time period */ (SELECT COUNT(*) AS BATCHES_CLOSED FROM OTCNET.BATCH WHERE BATCH_STATUS_CODE = 'X'AND LAST_UPDATE_TS BETWEEN TO_DATE('01/23/2017 01:00:00 PM', 'MM/DD/YYYY HH:MI:SS PM') AND TO_DATE('01/23/2017 11:59:59 PM', 'MM/DD/YYYY HH:MI:SS PM')) D"
SQL = "SELECT CP.IRN, CP.IRN, TD.FIELD_NAME, TD.FIELD_VALUE FROM OTCNET.CHECK_PAYMENT CP LEFT JOIN OTCNET.TRANSACTION_USER_DATA TD ON CP.CHECK_PAYMENT_ID=TD.CHECK_PAYMENT_ID where CP.IRN='990928082143960551041'"
#batch confirm
#SQL = "SELECT BATCH_NAME, created_ts, BATCH_STATUS_CODE FROM OTCNET.BATCH WHERE BATCH_STATUS_CODE='O'AND ACTIVE_FLG='N'AND CURR_NUM_OF_ITEMS = '10'AND CURR_DOLLAR_AMT = '10'AND CREATED_TS > '13-JAN-17'AND CREATED_LOGONID IN ('batchloader') order by created_ts desc"
# Network drive somewhere

startscript = time.time()
# You can set these in system variables but just in case you didnt
os.putenv('ORACLE_HOME', 'C:\Oracle') 
os.putenv('LD_LIBRARY_PATH', 'C:\Oracle') 
 
connection = cx_Oracle.connect(DB)
 
cursor = connection.cursor()
cursor.execute(SQL)
#Get columns names
with open("DBQuery.csv", "w", newline='') as csv_file:  # Python 3 version    
    csv_writer = csv.writer(csv_file)
    csv_writer.writerow([i[0] for i in cursor.description]) # write headers
    csv_writer.writerows(cursor)

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# Convert csv output to XLSX output
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
path = 'C:\\Python\\MyScripts\\'
#read the csv into a pandas dataframe
data = pd.read_csv(path + 'DBQuery.csv')    
print(data.head(5))
#setup the writer
writer = pd.ExcelWriter(path + 'DBQuery.xlsx', engine='xlsxwriter')
#write the dataframe to an xlsx file
data.to_excel(writer, sheet_name='mysheet', index=False)
writer.save()

#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
# display time took to run
#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
endscript = time.time()
endtime = endscript - startscript
print ('script run in ' + str(endscript - startscript) + ' seconds or ' + str((endscript - startscript)/60) + ' minutes')

This snippet took 0.00 seconds to highlight.

Back to the Entry List or Home.

Delete this entry (admin only).