Python MySQLdb
Module Report
Introduction
The MySQLdb
module, also known as MySQL-python
, is a Python library used for interfacing with MySQL databases. It provides a robust and efficient way to perform database operations such as querying, updating, and managing MySQL databases directly from Python applications.
Features
- Database Connectivity: Provides a connection to MySQL databases.
- SQL Execution: Allows execution of SQL queries and commands.
- Transaction Management: Supports transaction management with commit and rollback.
- Parameterized Queries: Supports parameterized queries to prevent SQL injection.
Installation
The MySQLdb
module is not included in the Python Standard Library and must be installed separately. It is often referred to as MySQL-python
. You can install it using pip:
Note: The package mysqlclient
is a fork of MySQL-python
that supports Python 3 and is recommended for new projects.
Basic Usage
Connecting to a MySQL Database
To connect to a MySQL database, you need to provide connection details such as hostname, username, password, and database name.
Example: Basic Connection
import MySQLdb
# Connect to the MySQL database
db = MySQLdb.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
db="yourdatabase"
)
# Create a cursor object using the connection
cursor = db.cursor()
# Close the connection
db.close()
In this example:
- MySQLdb.connect()
establishes a connection to the MySQL server.
- db.cursor()
creates a cursor object to execute SQL queries.
Executing SQL Queries
Example: Executing a Query
import MySQLdb
# Connect to the MySQL database
db = MySQLdb.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
db="yourdatabase"
)
# Create a cursor object
cursor = db.cursor()
# Execute a SQL query
cursor.execute("SELECT VERSION()")
# Fetch and print the result
version = cursor.fetchone()
print("Database version:", version[0])
# Close the connection
db.close()
In this example:
- cursor.execute()
runs a SQL query.
- cursor.fetchone()
retrieves the result of the query.
Example: Executing a Query with Parameters
import MySQLdb
# Connect to the MySQL database
db = MySQLdb.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
db="yourdatabase"
)
# Create a cursor object
cursor = db.cursor()
# Define the SQL query with parameters
query = "SELECT * FROM employees WHERE department = %s"
department = "Sales"
# Execute the query with parameters
cursor.execute(query, (department,))
# Fetch and print the results
results = cursor.fetchall()
for row in results:
print(row)
# Close the connection
db.close()
In this example:
- cursor.execute()
runs a query with parameters to prevent SQL injection.
- cursor.fetchall()
retrieves all rows from the result set.
Inserting and Updating Data
Example: Inserting Data
import MySQLdb
# Connect to the MySQL database
db = MySQLdb.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
db="yourdatabase"
)
# Create a cursor object
cursor = db.cursor()
# Define the SQL query for insertion
query = "INSERT INTO employees (name, department, salary) VALUES (%s, %s, %s)"
values = ("John Doe", "Engineering", 75000)
# Execute the query
cursor.execute(query, values)
# Commit the transaction
db.commit()
print("Record inserted.")
# Close the connection
db.close()
In this example:
- cursor.execute()
inserts a record into the employees
table.
- db.commit()
commits the transaction to save changes.
Example: Updating Data
import MySQLdb
# Connect to the MySQL database
db = MySQLdb.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
db="yourdatabase"
)
# Create a cursor object
cursor = db.cursor()
# Define the SQL query for updating
query = "UPDATE employees SET salary = %s WHERE name = %s"
values = (80000, "John Doe")
# Execute the query
cursor.execute(query, values)
# Commit the transaction
db.commit()
print("Record updated.")
# Close the connection
db.close()
In this example:
- cursor.execute()
updates a record in the employees
table.
- db.commit()
saves the changes to the database.
Deleting Data
Example: Deleting Data
import MySQLdb
# Connect to the MySQL database
db = MySQLdb.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
db="yourdatabase"
)
# Create a cursor object
cursor = db.cursor()
# Define the SQL query for deletion
query = "DELETE FROM employees WHERE name = %s"
values = ("John Doe",)
# Execute the query
cursor.execute(query, values)
# Commit the transaction
db.commit()
print("Record deleted.")
# Close the connection
db.close()
In this example:
- cursor.execute()
deletes a record from the employees
table.
- db.commit()
commits the transaction to ensure the deletion is applied.
Handling Exceptions
Example: Exception Handling
import MySQLdb
try:
# Connect to the MySQL database
db = MySQLdb.connect(
host="localhost",
user="yourusername",
passwd="yourpassword",
db="yourdatabase"
)
# Create a cursor object
cursor = db.cursor()
# Define the SQL query
query = "SELECT * FROM non_existent_table"
# Execute the query
cursor.execute(query)
except MySQLdb.Error as e:
print(f"Error: {e}")
finally:
# Close the connection
if db:
db.close()
In this example:
- try
and except
blocks handle potential exceptions that may occur during database operations.
- finally
ensures that the database connection is closed regardless of whether an exception occurred.
Best Practices
- Use Parameterized Queries: Always use parameterized queries to prevent SQL injection attacks.
- Handle Exceptions: Implement proper exception handling to manage errors and maintain application stability.
- Manage Transactions: Use transactions (commit and rollback) to ensure data integrity and consistency.
- Close Connections: Ensure database connections and cursors are closed to avoid resource leaks.
Common Pitfalls
- Ignoring Exception Handling: Failing to handle exceptions can lead to application crashes and data loss.
- Not Using Transactions: Not using transactions can result in partial updates or data inconsistency.
- Not Closing Connections: Failing to close database connections can lead to resource leaks and performance issues.
Conclusion
The MySQLdb
module, now maintained under the mysqlclient
package, is a powerful tool for interfacing with MySQL databases from Python. By following best practices and understanding the basic operations, you can effectively manage database interactions and ensure reliable and secure data handling in your applications.
References
- MySQL-python Documentation - Documentation for the
mysqlclient
library, a maintained fork ofMySQL-python
. - MySQL Documentation - Official documentation for MySQL, including setup and SQL syntax.