Python oracle db connection

#!/usr/bin/python
import sys
import re
import os
import win32com.client
import win32api
import win32con
import datetime as dt
import cx_Oracle

outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI")

inbox = outlook.GetDefaultFolder(6) # "6" refers to the index of a folder - in this case,
                                    # the inbox. You can change that number to reference
                                    # any other folder
messages = inbox.Items
message = messages.GetLast()
body_content = message.body
subject_content = message.subject

print (subject_content)
print (body_content)
paymentmsg = subject_content + body_content


#paymentmsg = "Hej 2 Jval01eff2rTEST01ey 1200,00 kr. +test2 +71<066825614704683+86870762Betalingsdato 18-07-2017"

# Delete Python-style comments
#num = re.sub(r'#.*$', "", phone)
#print ("Phone Num : ", num)

# Remove anything other than digits
#num = re.sub(r'\D', "", phone)  
#print ("Phone Num : ", num)

# Remove anything other than digits
#num = re.sub(r'\D', "", phone)  
#print ("Phone Num : ", num)

#phone1 = "xxx Your number is <b>123</b>  fdjsk"

#Clean the message for easier identifying elements
paymentmsg = paymentmsg.lower()
#date
paymentmsg = paymentmsg.replace('januar ', '01.')
paymentmsg = paymentmsg.replace('februar ', '02.')
paymentmsg = paymentmsg.replace('marts ', '03.')
paymentmsg = paymentmsg.replace('april ', '04.')
paymentmsg = paymentmsg.replace('maj ', '05.')
paymentmsg = paymentmsg.replace('juni ', '06.')
paymentmsg = paymentmsg.replace('juli ', '07.')
paymentmsg = paymentmsg.replace('august ', '08.')
paymentmsg = paymentmsg.replace('september ', '09.')
paymentmsg = paymentmsg.replace('oktober ', '10.')
paymentmsg = paymentmsg.replace('november ', '11.')
paymentmsg = paymentmsg.replace('december ', '12.')
#Clean the message for easier identifying elements
paymentmsg = paymentmsg.replace(" ", "")

print (paymentmsg)

  #format 10000,11kr
amount = re.search("(\d+\D{1}\d+)kr", paymentmsg)
if amount:
    print (amount.groups()[0])
    #Store Beløb
    amountStr = (amount.groups()[0])
else:
    #format 10.000,11kr
    amount0 = re.search("(\d+\D{1}\d+\D{1}\d+)kr", paymentmsg)
    if amount0:
        print (amount0.groups()[0])
        #Store Beløb
        amountStr = (amount0.groups()[0])
    else:
        #format 10.000kr
        amount4 = re.search("(\d+\D{1}\d+)kr", paymentmsg)
        if amount4:
            print (amount4.groups()[0])
            #Store Beløb
            amountStr = (amount4.groups()[0])
        else:
            #1000kr
            amount2 = re.search("(\d+)kr", paymentmsg)
            if amount2:
                print (amount2.groups()[0])
                #Store Beløb
                amountStr = (amount2.groups()[0])
            else:
                #any amount if 2 to 4 digits in its own line
                amount3 = re.search("\s(\d{2,4})\s", paymentmsg)
                if amount3:
                    print (amount3.groups()[0])
                    #Store Beløb
                    amountStr = (amount3.groups()[0])
     
#OCR linie  
OCR = re.search("\\+71<(\d+)", paymentmsg)
if OCR:
    print (OCR.groups()[0])
    #Store OCR
    ocrStr = (OCR.groups()[0])
else:
    #Identify 15 digit number in a string, assuming it's an OCR
    OCR2 = re.search("\D(\d{15})\D", paymentmsg)
    if OCR2:
        print (OCR2.groups()[0])
        #Store OCR
        ocrStr = (OCR2.groups()[0])
     
#Accepts format dd-mm-yyyy, dd/mm/yyyy , dd.mm.yyyy  , dd-mm-yy, dd/mm/yy , dd.mm.yy
paymentdate = re.search("(\d{1,2}\D{1}\d{1,2}\D{1}\d{2,4})", paymentmsg)
if paymentdate:
    print (paymentdate.groups()[0])
    #Store Dato
    paymentdateStr = (paymentdate.groups()[0])
else:
    paymentdate2 = re.search("(d.d)", paymentmsg)
    if paymentdate2:
        print (paymentdate2.groups()[0])
        #Store Dato with today's date
        paymentdateStr = dt.datetime.today().strftime("%m-%d-%Y")  

#Identify environment
env = re.search("(val\d+)", paymentmsg)
if env:
    print (env.groups()[0])
    #Store
    envStr = (env.groups()[0])
else:
    if env:
        env = re.search("(test\d+)", paymentmsg)
        print (env.groups()[0])
        #Store Miljø
        envStr = (env.groups()[0])
     
#Identify ventekonoto eller keyword Ingen OCR linie
Ingen = re.search("(Ingen)", paymentmsg)
VenteKonto = re.search("(ventekonto)", paymentmsg)
if Ingen or VenteKonto:
    print (Ingen.groups()[0])
    print (VenteKonto.groups()[0])
    #if ingen ocr, consider action required from user to validate before posting
    #os.system("The keyword 'Ingen' has been identified, please review the mail")

    result = win32api.MessageBox(None,'Should the following payment have incorrect OCR?','The keyword "Ingen" has been identified, please review the mail',win32con.MB_YESNO)
    if result == win32con.IDYES:
        print('Yes')
        #Set incorrect ocr line
        ocrStr = "999998888877777"
    else:
        print('No')
        result2 = win32api.MessageBox(None,'Continue payment by rewriting OCR Line?','The keyword "Ingen" has been identified, please review the mail',win32con.MB_YESNO)
        if result2 == win32con.IDYES:
            print('Yes2')
            #ask user to input ocr in shell
            ocrStr = input('Please write the new OCR line: ')
        else:
            print('Script has been stopped')
            #Exit script
            sys.exit()
         
netsPayment = re.search("(nets)", paymentmsg)
if netsPayment:
    print (netsPayment.groups()[0])
    #Store Beløb
    netsPaymentStr = (netsPayment.groups()[0])

path = "C:\\Users\\jelai\\Betalingsfil Generator V4.xlsm"

if os.path.exists(path):
    xl=win32com.client.Dispatch("Excel.Application")
    #xl.Workbooks.Open(Filename=path, ReadOnly=1)
    xl.Workbooks.Open(Filename=path)
    ws = xl.Worksheets("MENU")
    ws.Range("B3").Value = amountStr
    ws.Range("C3").Value = paymentdateStr.replace('.','-')
    ws.Range("D3").Value = "'" + ocrStr
    #Run macro

    if netsPayment == "nets":
        #nets payment
        ws.Range("E13").value = "NETS"
        ws.Range("E15").value = "Betalingsservice"
        xl.Application.Run("'Betalingsfil Generator V4.xlsm'!GogoM602")
    else:
        #cremul
        xl.Application.Run("'Betalingsfil Generator V4.xlsm'!Module3.GogoCREMUL")


    #sqlCode = ws.Range("G1").value
    sqlCode = ""
    #identifying last used row
    lastRow = ws.UsedRange.Rows.Count
 
    col = ws.Range("G1:G" + str(lastRow))
    #Loop through each cell in sql statement
    for c in col:
        #cell.Offset(1,2).Value = "W0000%s" % cell.Value
        #Do not include empty cells #and do not include the first sqlblankline code, not needed and cannot execute if not on it's own line
        if str(c.value) != "SET SQLBLANKLINES ON;":
            if str(c.value) != "COMMIT;": #We commit at the end of the connection string
                if str(c.value) != "None":
                    sqlCode = sqlCode + str(c.Value) + " "

    sqlCode = sqlCode.replace(';', '') #It cannot handle ; for some reason

    print (sqlCode)


#This is the same that is extracted from column G in the excel sheet. This is the original code, it wont work unless we use the above if-statements to remove the lines that fails through the execute(). Execute can only run 1 sql statement at a time: https://stackoverflow.com/questions/43244270/why-is-semicolon-an-invalid-character-in-cx-oracle
# sqlCode = """
SET SQLBLANKLINES ON;
INSERT INTO INTEGRATION_FILE ( INTEGRATION_FILE_TYPE_ID, FILENAME, STEP, STATUS, STATUS_TIME, STATUS_BY, FILE_GENERATED_TIME, CREATED_TIME, CREATED_BY, RAW_FILE_CONTENTS)
VALUES ( 4, 'CREMUL_145_JE', 'PROCESS', 'RETRIEVED_BY_INTEGRATION', CURRENT_TIMESTAMP, 'INTEGRATION_COMPONENT', CURRENT_TIMESTAMP, CURRENT_TIMESTAMP, 'INTEGRATION_COMPONENT',
UTL_RAW.CAST_TO_RAW (
'UNA:+,? ''
....
)); 
COMMIT;
"""

    #xl.ActiveWorkbook.Save() # if you want to save then uncomment this line and change delete the ", ReadOnly=1" part from the open function.
  #  xl.Quit() # Comment this out if your excel script closes
    del xl

env = envStr

if env == "test01" or env == "test1":
    ip = 'Localhost1'
    port = 1521
    SID = 'SID'
    dsn_tns = cx_Oracle.makedsn(ip, port, SID)
    username = 'USER'
    password= 'PWD'
elif env == "test02" or env == "test2":
    ip = 'Localhost222'
    port = 1521
    SID = 'SID'
    dsn_tns = cx_Oracle.makedsn(ip, port, SID)
    username = 'USER'
    password= 'PWD'
elif env ==  "val01"  or env == "val1":
    ip = 'Localhost0'
    port = 1521
    SID = 'SID'
    dsn_tns = cx_Oracle.makedsn(ip, port, SID)
    username = 'USER'
    password= 'PWD'
elif env ==  "val02" or env == "val2":
    ip = 'Localhost2'
    port = 1521
    SID = 'SID'
    dsn_tns = cx_Oracle.makedsn(ip, port, SID)
    username = 'USER'
    password= 'PWD'
elif env ==  "val03" or env == "val3":
    ip = 'Localhost3'
    port = 1521
    SID = 'SID'
    dsn_tns = cx_Oracle.makedsn(ip, port, SID)
    username = 'USER'
    password= 'PWD'

#db = cx_Oracle.connect(username, password, dsn_tns)
#This can also be done maually
# You can set these in system variables but just in case you didnt
os.putenv('ORACLE_HOME', 'C:\Python\instantclient_11_2')
#os.putenv('LD_LIBRARY_PATH', '/oracle/product/10.2.0/db_1/lib')

connection = cx_Oracle.connect(username, password, dsn_tns)
cursor = connection.cursor()
querystring = sqlCode #"select * from Integration_File"
cursor.execute(querystring)
connection.commit()
print (ip)
#Only works for select statements, do not include in update and insert
#for row in cursor.fetchall():
#    print (row)
 

Kommentarer

Send en kommentar

Populære opslag fra denne blog

Xpath cheat sheet

Billede
This is  Cheatsheets  — a collection of cheatsheets I've written. Xpath All the tools your team needs in one place. Slack: Where work happens. ads via Carbon Descendant selectors h1 //h1 ? div p //div//p ? ul > li //ul/li ? ul > li > a //ul/li/a   div > * //div/*   :root / ? :root > body /body   Attribute selectors #id //[@id="id"] ? .class //[@class="class"]   … kinda   input[type="submit"] //input[@type="submit"]   a#abc[for="xyz"] //a[@id="abc"][@for="xyz"] ? a[rel] //a[@rel]   a[href^='/'] //a[starts-with(@href, '/')] ? a[href$='pdf'] //a[ends-with(@href, '.pdf')]   a[href~='://'] //a[contains(@href, '://')]   … kinda   Order selectors ul > li:first-child //ul/li[1] ? ul > li:nth-child(2) //ul/li[2]   ul > li:last-child //ul/li[last()]   li#id:first-child //li[@id="id"][1]  ...
Læs mere

Reading Email

#!/usr/bin/python import sys import re import os import win32com.client import win32api import win32con import datetime as dt import cx_Oracle from selenium import webdriver from selenium.webdriver.support.ui import WebDriverWait from selenium.webdriver.support import expected_conditions as EC from selenium.common.exceptions import TimeoutException from selenium.webdriver.common.by import By outlook = win32com.client.Dispatch("Outlook.Application").GetNamespace("MAPI") inbox = outlook.GetDefaultFolder(6) # "6" refers to the index of a folder - in this case,                                     # the inbox. You can change that number to reference                                     # any other folder messages = inbox.Items message = messages.GetLast() body_content = message.body subject_c...
Læs mere

pip whl install python

Go to C:\Python34\Scripts (this is the folder where pip.exe is locateD) run below in cmd: python - m pip install requests above will install lib: requests pip install some - package . whl Note: if pip.exe is not recognized, you may find it in the "Scripts" directory from where python has been installed. If pip is not installed, this page can help:  How do I install pip on Windows? Note: for clarification If you copy the  *.whl  file to your local drive (ex.  C:\some-dir\some-file.whl ) use the following command line parameters -- pip install C :/ some - dir / some - file . whl
Læs mere