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.
Table of Contents
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