Python
Learn how to connect to MySQL databases in Sealos DevBox using Python
This guide will walk you through the process of connecting to a MySQL database using Python within your Sealos DevBox project.
Prerequisites
- A Sealos DevBox project with Python environment
- A MySQL database created using the Database app in Sealos
Activating the Python Environment
Before you start, you need to activate the Python virtual environment in your DevBox. Open the terminal within Cursor IDE and run:
You should see your prompt change, indicating that the virtual environment is now active.
Installing Required Packages
In your Cursor terminal, install the necessary packages:
This command installs:
mysql-connector-python
: The official MySQL driver for Pythonpython-dotenv
: A Python package that allows you to load environment variables from a .env file
Connection Setup
Set up the environment variables
First, let's set up the environment variables for our database connection. Create a .env
file in your project root with the following content:
Replace the placeholders with your actual MySQL credentials from the Database app in Sealos.
Create a database connection module
Create a new file named db_connection.py
with the following content:
This module provides two main functions:
-
get_db_connection()
: This function establishes a connection to the MySQL database using the credentials stored in the environment variables. It returns the connection object if successful, or None if an error occurs. -
close_connection(connection)
: This function closes the database connection when it's no longer needed.
Create a test script
Now, let's create a test script to verify our connection and perform some basic database operations. Create a file named test_mysql.py
with the following content:
Let's break down the main components of this script:
-
create_table(cursor)
: This function creates a table named 'employees' if it doesn't already exist. It demonstrates how to execute a CREATE TABLE SQL statement. -
insert_employee(cursor, name, email)
: This function inserts a new employee record into the 'employees' table. It shows how to use parameterized queries to safely insert data. -
get_all_employees(cursor)
: This function retrieves all records from the 'employees' table and prints them. It demonstrates how to execute a SELECT query and fetch results. -
main()
: This is the main function that ties everything together. It:- Establishes a database connection
- Creates the 'employees' table
- Inserts two sample employees
- Retrieves and prints all employees
- Handles any exceptions that might occur
- Ensures that the cursor and connection are properly closed
Running the Test Script
To run the test script, make sure your virtual environment is activated, then execute:
If everything is set up correctly, you should see output indicating successful connection, table creation, data insertion, and retrieval.
Best Practices
- Always activate the virtual environment before running your Python scripts or installing packages.
- Use environment variables to store sensitive information like database credentials.
- Close database connections and cursors after use to free up resources.
- Use parameterized queries to prevent SQL injection.
- Handle exceptions appropriately to manage potential errors.
Troubleshooting
If you encounter connection issues:
- Ensure you've activated the virtual environment with
source ./bin/activate
. - Verify that your MySQL database is running and accessible.
- Double-check your database credentials in the
.env
file. - Check the MySQL logs in the Database app for any error messages.
For more detailed information on using MySQL with Python, refer to the official MySQL Connector/Python documentation.
Last updated on