Python and MySQL: Connecting and Managing Data
Introduction
The integration between Python and MySQL is one of the most popular ways to work with relational databases using programming. Python offers robust libraries to manipulate MySQL, allowing you to perform everything from simple queries to advanced operations like transactions and optimizations.
In this article, we’ll learn how to set up the environment, connect to MySQL, and execute basic operations. Additionally, we’ll create a table with random values, read the data, and export it to an HTML file.
1. Setting up the Environment
Before we begin coding, we need to ensure that everything is set up correctly. Follow these steps:
1.1 Installing MySQL
Install MySQL on your operating system. For example, if you are using Ubuntu, the following commands will help:
sudo apt update
sudo apt install mysql-server
sudo systemctl start mysql
sudo systemctl enable mysql
To verify if MySQL is running, use:
sudo systemctl status mysql
1.2 Installing MySQL Connector for Python
Python has an official connector called mysql-connector-python, which facilitates the connection to MySQL. Install it using pip:
pip install mysql-connector-python
1.3 Setting up the Database
After installing MySQL, let’s create a simple database to test our operations with Python:
CREATE DATABASE example_python;
USE example_python;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
email VARCHAR(255)
);
2. Connecting Python to MySQL
Now that we have MySQL running and a database configured, let’s connect Python to MySQL using the mysql.connector module.
2.1 Connection Example
Here’s a simple example of how to connect Python to MySQL:
import mysql.connector
# Connecting to the database
connection = mysql.connector.connect(
host="localhost",
user="root", # Change to your MySQL username
password="your_password", # Change to your MySQL password
database="example_python"
)
# Checking if the connection is successful
if connection.is_connected():
print("Successfully connected to MySQL!")
3. CRUD Operations in Python and MySQL
CRUD stands for Create, Read, Update, and Delete, which are the main operations for interacting with a database.
3.1 CREATE: Inserting Data into the Database
Let’s start by inserting new records into the users table.
cursor = connection.cursor()
sql = "INSERT INTO users (name, email) VALUES (%s, %s)"
values = ("John Doe", "john@email.com")
cursor.execute(sql, values)
connection.commit() # Confirms the transaction
print(cursor.rowcount, "record inserted successfully.")
3.2 READ: Reading Data from the Database
Now, let’s retrieve the stored data from the users table.
sql = "SELECT * FROM users"
cursor.execute(sql)
results = cursor.fetchall()
for row in results:
print(row)
3.3 UPDATE: Updating Data
Here’s an example of how to update a user’s data.
sql = "UPDATE users SET email = %s WHERE name = %s"
values = ("john_doe@newemail.com", "John Doe")
cursor.execute(sql, values)
connection.commit()
print(cursor.rowcount, "record(s) updated.")
3.4 DELETE: Deleting Data
Finally, here’s how to delete a record from the database:
sql = "DELETE FROM users WHERE name = %s"
values = ("John Doe",)
cursor.execute(sql, values)
connection.commit()
print(cursor.rowcount, "record(s) deleted.")
4. Creating a Table with Random Values
Now, we’ll create a table with 10 columns and insert random values to simulate a more complex scenario. We’ll use Python’s random library to generate the values and fill the columns.
4.1 Creating the Table
First, let’s create a new table with 10 columns to store our example data:
CREATE TABLE random_example (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
age INT,
salary FLOAT,
city VARCHAR(255),
email VARCHAR(255),
phone VARCHAR(20),
department VARCHAR(255),
hire_date DATE,
status VARCHAR(50),
supervisor VARCHAR(255)
);
4.2 Inserting Random Data
Now, we’ll generate and insert random values into the table using Python:
import random
from datetime import datetime
names = ["John", "Mary", "Peter", "Anna", "Carlos", "Julia", "Lucas", "Fernanda", "Paul", "Sofia"]
cities = ["New York", "Los Angeles", "Chicago", "Houston", "Phoenix"]
departments = ["Sales", "HR", "IT", "Finance", "Marketing"]
status_options = ["Active", "Inactive", "On Leave", "Contract"]
cursor = connection.cursor()
# Inserting 10 records with random values
for _ in range(10):
name = random.choice(names)
age = random.randint(20, 60)
salary = round(random.uniform(3000.00, 10000.00), 2)
city = random.choice(cities)
email = f"{name.lower()}@example.com"
phone = f"({random.randint(100, 999)}) {random.randint(1000, 9999)}-{random.randint(1000, 9999)}"
department = random.choice(departments)
hire_date = datetime.now().date()
status = random.choice(status_options)
supervisor = random.choice(names)
sql = """INSERT INTO random_example
(name, age, salary, city, email, phone, department, hire_date, status, supervisor)
VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"""
values = (name, age, salary, city, email, phone, department, hire_date, status, supervisor)
cursor.execute(sql, values)
connection.commit()
print("Random data inserted successfully.")
5. Reading Data and Exporting to HTML
Now that our table is filled with random data, let’s read the data and export it to an HTML file using Pandas.
5.1 Installing Pandas
Install the Pandas library if you haven’t already:
pip install pandas
5.2 Reading Data from the Table
We will use Pandas to read the data from the random_example table:
import pandas as pd
# Reading the data from the random_example table
sql = "SELECT * FROM random_example"
df = pd.read_sql(sql, connection)
print(df.head()) # Display the first 5 rows
5.3 Exporting Data to HTML
Finally, let’s export the resulting DataFrame to an HTML file:
# Exporting to HTML
df.to_html("random_table.html", index=False)
print("Table exported to HTML successfully.")
6. Error Handling and Best Practices
6.1 Handling Exceptions
It’s important to add error handling when connecting to the database and performing operations:
try:
connection = mysql.connector.connect(
host="localhost",
user="root",
password="your_password",
database="example_python"
)
if connection.is_connected():
print("Connection established.")
except mysql.connector.Error as err:
print(f"Error connecting to MySQL: {err}")
finally:
if connection.is_connected():
connection.close()
print("Connection closed.")
6.2 Closing Connections
Always remember to close the connection and cursor after completing operations to avoid memory leaks and unnecessary locks:
cursor.close()
connection.close()
7. Conclusion
Integrating Python with MySQL is an efficient way to manage and manipulate data in projects requiring persistent information storage. With basic CRUD operations, random data generation, and exporting tables to HTML, you can build robust applications, from simple data management systems to complex projects.
This comprehensive guide shows you how to interact with MySQL using Python, including creating tables with random values and exporting data in different formats.
edvaldo b. guimarães filho 
Leave a comment