Android Programming – Improved Python Scripts (Insert, MySQL)

In a prior post, I shared my Python script for inserting the contents of a CSV file into a MySQL database. I’ve since made a minor enhancement to the code, and I’d like to share it with you. The motivation behind this code modification stems from the fact that the previous version did not provide error notifications when the script failed to execute as expected. With this update, error causes are now displayed in the console or terminal for improved debugging. Here’s the revised script:

import csv
import mysql.connector

# Set the following variables to match your environment
MYSQL_USER = "johnny"
MYSQL_PASSWORD = "JohnUnity0216$#@!"
MYSQL_DATABASE = "budget_tracker_db"
MYSQL_TABLE = "user_spending"
CSV_FILE = "/var/lib/mysql-files/BUDGET_TRACKER_demo.csv"

try:
    # Connect to MySQL
    conn = mysql.connector.connect(
        user=MYSQL_USER,
        password=MYSQL_PASSWORD,
        database=MYSQL_DATABASE,
    )
    cursor = conn.cursor()

    # Create the LOAD DATA INFILE statement
    LOAD_DATA_INFILE_STATEMENT = f"LOAD DATA INFILE '{CSV_FILE}' REPLACE INTO TABLE {MYSQL_TABLE} FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'"

    # Execute the LOAD DATA INFILE statement
    cursor.execute(LOAD_DATA_INFILE_STATEMENT)

    # Commit the changes
    conn.commit()

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

finally:
    # Close the cursor and connection in the finally block to ensure they are closed even if an exception occurs.
    cursor.close()
    conn.close()