Python MySQL
Python is a programming language, and MySQL is a database where we store information. When combined, Python can talk to MySQL to save, read, and manage data easily.
Real-world applications need to store vast amounts of data securely and persistently, which standard Python text files cannot handle efficiently. By integrating MySQL, Python gains the ability to manage millions of records, perform complex searches instantly, and allow multiple users to access data at the same time. It transforms a simple script into a powerful, data-driven application.
Installation and Tools
To make Python communicate with MySQL, you need a “driver” or “connector” library. We typically use the standard
mysql-connector-python. You will also need a MySQL Server installed on your machine (using tools like XAMPP, WAMP, or MySQL Workbench) to act as the database host. pip install mysql-connector-python
Connecting to MySQL Server
Before we can do anything, we must establish a “handshake” between your Python script and the database server. This requires providing authentication details like the host address (usually ‘localhost’), a username, and a password. This connection object creates a “cursor”, which acts like a pen used to execute SQL commands.
import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
print(mydb) # If successful, this prints the connection object
Creating a Database
A database is like a dedicated warehouse where we will store all our related data. In Python, we use the cursor to send the
CREATE DATABASE command. This is usually a one-time setup step done at the very beginning of a project. import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
mycursor = mydb.cursor()
# Creating a warehouse named 'PetCenter'
mycursor.execute("CREATE DATABASE PetCenter")
Creating Tables
If the database is the warehouse, tables are the organized shelves within it. We must define the structure of these shelves (columns) and what kind of data they hold (integers for age, text for names). Here, we create a table to store details about animals available for adoption.
import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
mycursor = mydb.cursor()
# Select the database first
mydb.database = "PetCenter"
# Create a shelf for pets
sql = "CREATE TABLE Pets (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), species VARCHAR(50), age INT)"
mycursor.execute(sql)
Insert Data
Inserting data means placing a new item onto our table shelf. We write a SQL query to add specific values into the columns we created earlier. In Python, we must remember to “commit” the change, which is like hitting the “Save” button to ensure the data stays there permanently.
import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
mycursor = mydb.cursor()
# Select the database first
mydb.database = "PetCenter"
sql = "INSERT INTO Pets (name, species, age) VALUES ('Buddy', 'Dog', 3)"
mycursor.execute(sql)
mydb.commit() # Essential: Saves the changes!
print(mycursor.rowcount, "record inserted.")
Select Data
Selecting is how we retrieve information from the database to use in our Python program. We can fetch all records or specific columns. The result is usually returned as a list of rows (tuples), which we can loop through to display the data to the user.
import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
mycursor = mydb.cursor()
# Select the database first
mydb.database = "PetCenter"
mycursor.execute("SELECT * FROM Pets")
myresult = mycursor.fetchall()
for x in myresult:
print(x) # Output: (1, 'Buddy', 'Dog', 3)
Update Data
Sometimes data changes; a pet might have a birthday, or their name might change. The
UPDATE statement allows us to modify existing records. It is crucial to always use a WHERE clause when updating, otherwise, you might accidentally update every single row in the table. import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
mycursor = mydb.cursor()
# Select the database first
mydb.database = "PetCenter"
# Buddy turned 4 years old
sql = "UPDATE Pets SET age = 4 WHERE name = 'Buddy'"
mycursor.execute(sql)
mydb.commit()
print(mycursor.rowcount, "record(s) affected")
Delete Data from Tables
When a record is no longer needed—for example, if a pet is adopted and leaves the center, we remove it using the
DELETE statement. Like updating, this is a dangerous command; without a WHERE clause, it will wipe out every record in your table instantly. import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
mycursor = mydb.cursor()
# Select the database first
mydb.database = "PetCenter"
# Remove the pet named 'Buddy'
sql = "DELETE FROM Pets WHERE name = 'Buddy'"
mycursor.execute(sql)
mydb.commit()
Where Clause (Filtering)
Often, we don’t want to see every single record; we only want data that matches specific criteria. The
WHERE clause allows us to filter results. For example, if a customer specifically wants to adopt a cat, we tell the database to only return rows where the species is ‘Dog’. import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
mycursor = mydb.cursor()
# Select the database first
mydb.database = "PetCenter"
# Find only the dogs
sql = "SELECT * FROM Pets WHERE species = 'Dog'"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Order By (Sorting)
Data in a database isn’t always stored in a convenient order. The
ORDER BY clause allows us to sort the results alphabetically or numerically before they reach Python. You can sort in ascending (default) or descending order, which is useful for things like “Price: Low to High” or “Newest First”. import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
mycursor = mydb.cursor()
# Select the database first
mydb.database = "PetCenter"
# Sort pets by age, youngest to oldest
sql = "SELECT * FROM Pets ORDER BY age"
mycursor.execute(sql)
myresult = mycursor.fetchall()
for x in myresult:
print(x)
Limit Clause
When dealing with massive databases containing thousands of rows, you rarely want to retrieve them all at once. The
LIMIT clause tells MySQL to return only a specific number of records. This is the technology behind “Page 1, Page 2” (pagination) on websites. import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
mycursor = mydb.cursor()
# Select the database first
mydb.database = "PetCenter"
# Get the first 5 pets only
mycursor.execute("SELECT * FROM Pets LIMIT 5")
myresult = mycursor.fetchall()
for pet in myresult:
print(pet)
SQL Injection Prevention
One of the biggest security risks in databases is “SQL Injection,” where hackers insert malicious code into your queries. To prevent this, never combine strings directly (e.g., using
+ or f-strings) for SQL commands. Instead, use placeholders (%s), which automatically sanitize the input and keep your data safe. import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
mycursor = mydb.cursor()
# Select the database first
mydb.database = "PetCenter"
# BAD (Vulnerable):
# sql = "SELECT * FROM Pets WHERE name = " + user_input
# GOOD (Secure):
sql = "SELECT * FROM Pets WHERE name = %s"
val = ("Buddy", ) # Tuple
mycursor.execute(sql, val)
for row in mycursor.fetchall():
print(row)
Joins
Real-world data is often split across multiple tables to stay organized (e.g., one table for Pets, one for Owners). A JOIN allows us to combine rows from two or more tables based on a related column between them. This lets us see connected data, like which owner adopted which pet.
Adoptions with columns (adoption_id, pet_id, owner_name). # Combine Pets and Adoptions to see who owns whom
sql = """
SELECT Pets.name, Adoptions.owner_name
FROM Pets
INNER JOIN Adoptions ON Pets.id = Adoptions.pet_id
"""
mycursor.execute(sql)
Drop Tables
Dropping a table is the nuclear option. It completely removes the table structure and all the data inside it from the database permanently. This is different from
DELETE, which removes data but keeps the empty table structure (shelf) intact. import mysql.connector
# Establishing the handshake
mydb = mysql.connector.connect(
host="localhost",
user="root",
password="password123"
)
mycursor = mydb.cursor()
# Select the database first
mydb.database = "PetCenter"
sql = "DROP TABLE Pets"
mycursor.execute(sql)