Establishing Database Connections in Python with MySQL Connector
Python's mysql-connector-python package provides a standard interface for interacting with MySQL databases. Begin by installing the necessary driver via pip.
pip install mysql-connector-python
Following installation, you can implement a connection utility. This function encapsulates the connection logic and provides error handling.
import mysql.connector
from mysql.connector import Error
def establish_db_session(server, username, passkey, db_name):
"""
Creates a connection to a specified MySQL database.
Args:
server (str): Hostname or IP address of the MySQL server.
username (str): Authorized database user.
passkey (str): User's authentication password.
db_name (str): Name of the target database.
Returns:
mysql.connector.connection.MySQLConnection: A database connection object on success, otherwise None.
"""
db_connection = None
try:
db_connection = mysql.connector.connect(
host=server,
user=username,
password=passkey,
database=db_name
)
if db_connection.is_connected():
print("Database connection established successfully.")
except Error as connection_error:
print(f"Failed to connect: {connection_error}")
return db_connection
To utilize the connecsion function, define your configuration parameters and call the functon. It's crucial to manage resources by closing the curser and connection after operations are complete.
# Configuration parameters for the database
SERVER_HOST = "localhost"
DB_USER = "your_mysql_user"
DB_PASSWORD = "your_secure_password"
DB_NAME = "your_target_database"
# Establish the connection
conn = establish_db_session(SERVER_HOST, DB_USER, DB_PASSWORD, DB_NAME)
if conn and conn.is_connected():
# Create a cursor object for executing queries
db_cursor = conn.cursor()
db_cursor.execute("SELECT id, name FROM sample_table")
# Fetch and print all resulting rows
result_set = db_cursor.fetchall()
for record in result_set:
print(record)
# Clean up resources
db_cursor.close()
conn.close()
print("Connection closed.")
Replace placeholder values like your_mysql_user, your_secure_password, your_target_database, and sample_table with your actual MySQL credentials and schema details. This foundational example should be extended with robust transaction handling and connection pooling for production applications.