Safely Manage and Store MySQL Database Credentials in Python Scripts

To enhance security and prevent exposing MySQL database credentials in your Python scripts, it's a good practice to isolate credentials from the main code. This method ensures better maintainability and shields sensitive information when sharing or publishing your scripts.

Syntax

Here’s how you can structure your credential storage:

# credentials.py
db_config = {
    "host": "localhost",
    "user": "your_username",
    "password": "your_password",
    "database": "your_database"
}

Simple Example

Let’s see how to load the credentials from a separate file and connect to the database.

# main.py
import mysql.connector
from credentials import db_config

try:
    my_connect = mysql.connector.connect(**db_config)
    print("Connection successful")
except mysql.connector.Error as e:
    print(f"Connection error: {e}")

Output

Connection successful

Intermediate Example: Fetching Data

This example shows how to use the stored credentials to fetch data from a MySQL database.

# main.py
import mysql.connector  # Import the MySQL connector module
from credentials import db_config  # Import database configuration from credentials.py

try:  # Attempt to establish a database connection
    my_connect = mysql.connector.connect(**db_config)  # Use unpacked db_config to connect
    my_cursor = my_connect.cursor()  # Create a cursor object to execute SQL queries
    my_cursor.execute("SELECT * FROM student")  # Execute a query to fetch all records from the student table
    rows = my_cursor.fetchall()  # Retrieve all rows from the query result
    for row in rows:  # Iterate through each row
        print(row)  # Print each row
    my_connect.close()  # Close the database connection
except mysql.connector.Error as e:  # Handle any MySQL connection errors
    print(f"Database error: {e}")  # Print the error message

Output

(1, 'John Doe', 'john@example.com')
(2, 'Jane Smith', 'jane@example.com')

Advanced Example: Using Environment Variables

For additional security, credentials can be stored as environment variables and loaded using the os module.

pip install python-dotenv

# .env file
DB_HOST=localhost
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database

# credentials.py
import os  # Import the os module to access environment variables
from dotenv import load_dotenv  # Import load_dotenv to read the .env file

load_dotenv()  # Load the environment variables from the .env file

db_config = {
    "host": os.getenv("DB_HOST"),  # Retrieve DB_HOST from environment variables
    "user": os.getenv("DB_USER"),  # Retrieve DB_USER from environment variables
    "password": os.getenv("DB_PASSWORD"),  # Retrieve DB_PASSWORD from environment variables
    "database": os.getenv("DB_NAME")  # Retrieve DB_NAME from environment variables
}

# main.py
import mysql.connector  # Import the MySQL connector module
from credentials import db_config  # Import db_config from credentials.py

try:  # Attempt to establish a database connection
    my_connect = mysql.connector.connect(**db_config)  # Use unpacked db_config to connect
    print("Connection successful")  # Print success message if connection is established
except mysql.connector.Error as e:  # Handle database connection errors
    print(f"Connection error: {e}")  # Print the error message

Output

Connection successful

Using SQLAlchemy

To get started with SQLAlchemy and environment variables, install the required packages. SQLAlchemy is the ORM toolkit, pymysql is used to connect to MySQL, and python-dotenv manages environment variables.

# Installing required packages
pip install sqlalchemy pymysql python-dotenv

The .env file securely stores database credentials, keeping them out of the main codebase for better security and maintainability.

# .env file
DB_HOST=localhost
DB_USER=your_username
DB_PASSWORD=your_password
DB_NAME=your_database

The credentials.py script uses the `dotenv` library to load credentials from the .env file. These are used to construct the DATABASE_URL for SQLAlchemy.

# credentials.py
import os  # Import the os module to access environment variables
from dotenv import load_dotenv  # Import load_dotenv to read the .env file

load_dotenv()  # Load the environment variables from the .env file

#DATABASE_URL = f"mysql+pymysql://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}/{os.getenv('DB_NAME')}"
  # Construct the database URL
DATABASE_URL = f"mysql+mysqldb://{os.getenv('DB_USER')}:{os.getenv('DB_PASSWORD')}@{os.getenv('DB_HOST')}/{os.getenv('DB_NAME')}"
  # Construct the database URL

This simple example demonstrates how to connect to the database using SQLAlchemy by creating an engine and testing the connection.

# Simple Example: Connecting to the Database
from sqlalchemy import create_engine  # Import create_engine from SQLAlchemy
from credentials import DATABASE_URL  # Import the database URL from credentials.py

engine = create_engine(DATABASE_URL)  # Create a database engine

try:  # Test the connection
    with engine.connect() as connection:
        print("Connection successful")
except Exception as e:
    print(f"Connection error: {e}")

This intermediate example fetches all records from the student table using SQLAlchemy's core query interface and prints the result.

# Intermediate Example: Fetching Data
from sqlalchemy import create_engine, text
from credentials import DATABASE_URL

engine = create_engine(DATABASE_URL)

try:
    with engine.connect() as connection:
        result = connection.execute(text("SELECT * FROM student"))
        for row in result:
            print(row)
except Exception as e:
    print(f"Database error: {e}")

This code defines a model for the student table using SQLAlchemy ORM. The table structure is represented as a Python class.

# Advanced Example: Using SQLAlchemy ORM
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy import Column, Integer, String

Base = declarative_base()

class Student(Base):
    __tablename__ = "student"
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    mark = Column(Integer())

This advanced example demonstrates how to use SQLAlchemy ORM for querying the student table, showing a more structured and Pythonic approach to handling database records.

from sqlalchemy.orm import sessionmaker
from sqlalchemy import create_engine
from credentials import DATABASE_URL
from student_model import Student, Base

engine = create_engine(DATABASE_URL)
Base.metadata.create_all(engine)

Session = sessionmaker(bind=engine)
session = Session()

try:
    students = session.query(Student).all()
    for student in students:
        print(f"ID: {student.id}, Name: {student.name}, Mark: {student.mark}")
except Exception as e:
    print(f"Database error: {e}")
finally:
    session.close()

Benefits of This Approach

• Security: Avoid hardcoding sensitive credentials in your main script.
• Modularity: Allows you to reuse the same credentials file across multiple scripts.
• Ease of Maintenance: Updating credentials requires a single change in one file.