MySQL is one of the most popular relational database management systems. Python provides the mysql-connector-python package to interact with MySQL databases, allowing you to connect, execute SQL queries, and manage data.
1. Installing MySQL Connector for Python
Before you start working with MySQL in Python, you need to install the MySQL connector library. You can do this using pip.
pip install mysql-connector-python
2. Connecting to a MySQL Database
To connect to a MySQL database, you need to provide the host, user, password, and database name. The following example shows how to establish a connection.
Example of Connecting to a MySQL Database:
import mysql.connector
# Connect to the MySQL database
connection = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
)
# Create a cursor object to execute SQL queries
cursor = connection.cursor()
# Perform database operations...
# Close the connection
connection.close()
3. Creating a Table
After connecting to the database, you can create tables to store your data using the CREATE TABLE SQL command.
Example of Creating a Table:
# Create a table named 'students'
cursor.execute('''
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100),
age INT
)
''')
# Commit changes
connection.commit()
4. Inserting Data into the Table
You can insert data into a table using the INSERT INTO SQL command. Make sure to use placeholders to avoid SQL injection attacks.
Example of Inserting Data:
# Insert data into the students table
cursor.execute("INSERT INTO students (name, age) VALUES (%s, %s)", ("John", 22))
connection.commit()
5. Querying Data from the Table
To retrieve data from the table, use the SELECT SQL command. You can fetch all or specific rows depending on your needs.
Example of Querying Data:
# Query all data from students table
cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
# Display all rows
for row in rows:
print(row)
6. Updating Data in the Table
To update data in a table, use the UPDATE SQL command.
Example of Updating Data:
# Update a student's age
cursor.execute("UPDATE students SET age = %s WHERE name = %s", (23, "John"))
connection.commit()
7. Deleting Data from the Table
To delete data from the table, use the DELETE SQL command.
Example of Deleting Data:
# Delete a student by name
cursor.execute("DELETE FROM students WHERE name = %s", ("John",))
connection.commit()
8. Closing the Connection
Always remember to close the database connection after performing your database operations.
Example of Closing the Connection:
# Close the connection to the database connection.close()
9. Using Context Manager with MySQL
Using a context manager is a more Pythonic way to manage resources, as it automatically closes the connection when the block of code is done.
Example Using Context Manager:
with mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
) as connection:
cursor = connection.cursor()
cursor.execute("SELECT * FROM students")
rows = cursor.fetchall()
for row in rows:
print(row)
10. Handling Exceptions
It’s important to handle exceptions when interacting with databases. Python provides try-except blocks to manage errors effectively.
Example of Handling Exceptions:
try:
connection = mysql.connector.connect(
host="localhost",
user="yourusername",
password="yourpassword",
database="yourdatabase"
)
cursor = connection.cursor()
cursor.execute("SELECT * FROM students")
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
if connection.is_connected():
connection.close()
Conclusion
MySQL is a powerful relational database that can be easily integrated with Python through the mysql-connector-python package. With this guide, you can now connect to a MySQL database, create tables, insert data, query, update, and delete records.