pythonoracle11gcx-oraclepython-oracledb

Python Script to extract data from Database and use it to email

I am trying to write a python script which reads filenames for example (COD_044916.pdf) here after "COD_" and before ".pdf" rest of the text is the customer number, the script should find this customer number in a table in a oracle database. Upon finding the row at which this customer number is it should then get the email id of that customer from the email column of that row in that table in database. Then send this file on that email.

This is what I've tried but I guess it's not working for Oracle 11g.

import re
import oracledb

# Database connection details
db_username = "username"
db_password = "password"
db_dsn = "mis"

# Regular expression pattern to extract customer number from filenames
filename_pattern = r"COD_(\d+)\.pdf"

def extract_customer_number(filename):
match = re.search(filename_pattern, filename)
if match:
    return match.group(1)
return None

def get_customer_email_from_database(customer_number):
try:
    connection = oracledb.connect(user=db_username, password=db_password, dsn=db_dsn, config_dir='C:/app/client/name/product/12.2.0/client_1/Network/Admin')
    cursor = connection.cursor()

    query = "SELECT t.int_addr FROM mis_customer t WHERE t.cus_no = :c_number"
    result = cursor.execute(query, c_number=customer_number).fetchone()

    cursor.close()
    connection.close()

    if result:
        return result[0]
    else:
        return None

except oracledb.DatabaseError as e:
    error_message = f"Error connecting to the database: {e}"
    raise Exception(error_message)

# Example filename
filename = "COD_044916.pdf"

try:
# Extract customer number from filename
customer_number = extract_customer_number(filename)
if customer_number:
    # Retrieve customer email from the database
    customer_email = get_customer_email_from_database(customer_number)
    if customer_email:
        print(f"Customer Number: {customer_number}")
        print(f"Customer Email: {customer_email}")
    else:
        print(f"Customer with number {customer_number} not found in the database.")
else:
    print("Filename does not match the expected pattern.")

except Exception as e:
print(f"An error occurred: {e}")
Solution

  • If you have not tried yet, you could consider using the with statement and context manager for the database connection and cursor. This should ensure proper closing of resources even in the case of exceptions: (modifies the logic atomicity and context)

    import re
import cx_Oracle

# Database connection details
db_username = "username"
db_password = "password"
db_dsn = "hostname:port/service_name"  # Update with correct DSN

# Regular expression pattern to extract customer number from filenames
filename_pattern = r"COD_(\d+)\.pdf"

def extract_customer_number(filename):
    match = re.search(filename_pattern, filename)
    if match:
        return match.group(1)
    return None

def get_customer_email_from_database(customer_number):
    try:
        with cx_Oracle.connect(db_username, db_password, db_dsn) as connection:
            with connection.cursor() as cursor:
                query = "SELECT t.int_addr FROM mis_customer t WHERE t.cus_no = :c_number"
                result = cursor.execute(query, {"c_number": customer_number}).fetchone()

                if result:
                    return result[0]
                else:
                    return None

    except cx_Oracle.DatabaseError as e:
        error_message = f"Error connecting to the database: {e}"
        raise Exception(error_message)

# Example filename
filename = "COD_044916.pdf"

try:
    # Extract customer number from filename
    customer_number = extract_customer_number(filename)
    if customer_number:
        # Retrieve customer email from the database
        customer_email = get_customer_email_from_database(customer_number)
        if customer_email:
            print(f"Customer Number: {customer_number}")
            print(f"Customer Email: {customer_email}")
        else:
            print(f"Customer with number {customer_number} not found in the database.")
    else:
        print("Filename does not match the expected pattern.")

except Exception as e:
    print(f"An error occurred: {e}")

    Not sure this will get you where you want to go. Otherwise, consider implementing more error logging to check where the disruptions happens (can it connect to db? can it checks the customer number againt a row? ...)

    Also, I'll say it because its better said than not: