How to Connect to a MySQL Database Using MySQL-Connector Python

Nexgismo

1 month ago

MySQL-Connector Python is an Oracle-supported driver that will help you connect your Python applications to MySQL databases without any trouble. It adheres fully to Python Database API (DB-API) version 2.0 and has such features as connection pooling, transaction management and prepared statements.

Key Features:

Officially Supported: Developed and maintained by Oracle staff.
DB-API Compliant: Fully compatible with Python Database API (DB-API) Version 2.0.
Cross-Platform: Also available for Windows, macOS, Linux, etc
Feature-Rich: Supports connection pooling, transaction management, among others.

Installation

You need to install it before you start using MySQL-Connector. Use the following instruction to do this in the python package installer called pip. In your command prompt or terminal window type:

pip install mysql-connector-python

This will install the latest version of MySQL-Connector Python.

Establishing a Basic Connection

Before you can execute any operations on the MySQL database, you should first establish a connection. For instance here is how one can connect to a MySQL database utilizing MySQL-Connector Python:

import mysql.connector  
from mysql.connector import Error  
  
def create_connection():  
    try:  
        connection = mysql.connector.connect(  
            host='localhost',  
            database='your_database',  
            user='your_username',  
            password='your_password'  
        )  
        if connection.is_connected():  
            db_info = connection.get_server_info()  
            print(f"Connected to MySQL database... MySQL Server version on {db_info}")  
            return connection  
    except Error as e:  
        print(f"Error: {e}")  
        return None  
  
connection = create_connection()

Best Practices:

Environment Variables: Avoid hardcoding sensitive information in your code by storing your database credentials in environment variables.
Error Handling: Always handle exceptions so as to capture connection errors and provide useful information instead of them.

Executing SQL Queries

As soon as the connection is made, one can now go ahead and run SQL queries with the cursor object. This too is how you may create a table that has some unique constraints and data types:

def create_table(connection):  
    try:  
        cursor = connection.cursor()  
        cursor.execute("""  
            CREATE TABLE IF NOT EXISTS employees (  
                employee_id INT AUTO_INCREMENT PRIMARY KEY,  
                name VARCHAR(100) NOT NULL,  
                position VARCHAR(50),  
                hire_date DATE,  
                salary DECIMAL(10, 2) CHECK (salary > 0),  
                UNIQUE (name, position)  
            )  
        """)  
        print("Table created successfully")  
    except Error as e:  
        print(f"Error: {e}")  
  
create_table(connection)

Best Practices:

SQL Injection: Always have parameterized queries to guard against SQL injection attacks.
Error Handling: Implement robust error handling to catch and log any issues during query execution.

Getting Data

To get data from table, one might use SELECT statement. The following is an example of fetching it out and manipulating it to compute average salary:

def fetch_and_process_data(connection):  
    try:  
        cursor = connection.cursor()  
        cursor.execute("SELECT name, salary FROM employees WHERE salary IS NOT NULL")  
        rows = cursor.fetchall()  
        total_salary = 0  
        count = 0  
        for row in rows:  
            print(f"Employee Name: {row[0]}, Salary: {row[1]}")  
            total_salary += row[1]  
            count += 1  
        if count > 0:  
            print(f"Average Salary: {total_salary / count}")  
        else:  
            print("No salary data available")  
    except Error as e:  
        print(f"Error: {e}")  
  
fetch_and_process_data(connection)

Best Practices:

Efficient Fetching: Employ fetchone(), fetchmany(size), or use fetchall() depending on your needs for better performance.
Data Processing: Process data once retrieved in order to conserve memory usage.

Using Connection Pooling

Connection pooling helps you leverage multiple connections efficiently by reusing them. Here’s how you can configure connection pooling:

from mysql.connector import pooling  
  
def create_pool():  
    try:  
        pool = mysql.connector.pooling.MySQLConnectionPool(  
            pool_name="mypool",  
            pool_size=5,  
            host='localhost',  
            database='your_database',  
            user='your_username',  
            password='your_password'  
        )  
        return pool  
    except Error as e:  
        print(f"Error: {e}")  
        return None  
  
pool = create_pool()  
connection = pool.get_connection()

Best Practices:

Pool Size: Adjust the pool size according to the concurrency requirements of your application.
Resource Management: Always return back connections to the pool so that resources are not leaked out.

Handling Exceptions

It is important that exceptions should be properly handled in order for an application to be strong enough. This is shown below where exceptions are caught during a query execution process

def execute_query(connection, query, params=None):  
    try:  
        cursor = connection.cursor()  
        if params:  
            cursor.execute(query, params)  
        else:  
            cursor.execute(query)  
        connection.commit()  
        print("Query executed successfully")  
    except Error as e:  
        print(f"Error: {e}")  
  
execute_query(connection, "INSERT INTO employees (name, position, hire_date, salary) VALUES (%s, %s, %s, %s)", ('John Doe', 'Software Engineer', '2023-10-01', 75000.00))

Best Practices:

Exceptions: Catch specific exception like mysql.connector.errors.InterfaceError for more detailed error handling.
Logging: Use a logging framework to log exceptions for an easier debugging and monitoring activities.

Closing the Connection

However, always remember to close the connection after executing all operations in order to release resources :

def close_connection(connection):  
    if connection.is_connected():  
        connection.close()  
        print("MySQL connection is closed")  
  
close_connection(connection)

Best Practices:

Context Managers: Use context managers (with statements) to ensure connections are closed automatically.

Complete Example

This is a complete example that demonstrates how you can connect to a MySQL database, create table, insert data, fetch and process data and close the connection involved:

import mysql.connector  
from mysql.connector import Error  
  
def create_connection():  
    try:  
        connection = mysql.connector.connect(  
            host='localhost',  
            database='your_database',  
            user='your_username',  
            password='your_password'  
        )  
        if connection.is_connected():  
            db_info = connection.get_server_info()  
            print(f"Connected to MySQL database... MySQL Server version on {db_info}")  
            return connection  
    except Error as e:  
        print(f"Error: {e}")  
        return None  
  
def create_table(connection):  
    try:  
        cursor = connection.cursor()  
        cursor.execute("""  
            CREATE TABLE IF NOT EXISTS employees (  
                employee_id INT AUTO_INCREMENT PRIMARY KEY,  
                name VARCHAR(100) NOT NULL,  
                position VARCHAR(50),  
                hire_date DATE,  
                salary DECIMAL(10, 2) CHECK (salary > 0),  
                UNIQUE (name, position)  
            )  
        """)  
        print("Table created successfully")  
    except Error as e:  
        print(f"Error: {e}")  
  
def insert_data(connection, name, position, hire_date, salary):  
    try:  
        cursor = connection.cursor()  
        cursor.execute("INSERT INTO employees (name, position, hire_date, salary) VALUES (%s, %s, %s, %s)", (name, position, hire_date, salary))  
        connection.commit()  
        print("Data inserted successfully")  
    except Error as e:  
        print(f"Error: {e}")  
  
def fetch_and_process_data(connection):  
    try:  
        cursor = connection.cursor()  
        cursor.execute("SELECT name, salary FROM employees WHERE salary IS NOT NULL")  
        rows = cursor.fetchall()  
        total_salary = 0  
        count = 0  
        for row in rows:  
            print(f"Employee Name: {row[0]}, Salary: {row[1]}")  
            total_salary += row[1]  
            count += 1  
        if count > 0:  
            print(f"Average Salary: {total_salary / count}")  
        else:  
            print("No salary data available")  
    except Error as e:  
        print(f"Error: {e}")  
  
def close_connection(connection):  
    if connection.is_connected():  
        connection.close()  
        print("MySQL connection is closed")  
  
connection = create_connection()  
if connection:  
    create_table(connection)  
    insert_data(connection, 'Alice', 'Data Scientist', '2023-10-02', 95000.00)  
    insert_data(connection, 'Bob', 'System Analyst', '2023-10-03', 85000.00)  
    fetch_and_process_data(connection)  
    close_connection(connection)

Conclusion

When using Python’s MySQL-Connector module to establish a link with MySQL database, it will be very efficient and easy. The whole scope of installation up to execution of queries, fetching data, exception handling as well as use of connection pooling were covered by this comprehensive guide. With the unique examples provided together with best practices, your integration of MySQL into your Python programs should be straight forward.

Find out more about MySQL-Connector Python so as to discover its full potentiality and improve on your database interactions through making them highly efficient and firm. Enjoy!

References:

OWASP SQL Injection: OWASP SQL Injection
MySQL-Connector Python Documentation: Official MySQL Documentation

Table of Contents

Installation

Establishing a Basic Connection

Executing SQL Queries

Getting Data

Using Connection Pooling

Handling Exceptions

Closing the Connection

Complete Example

Conclusion