Connecting Python with MySQL involves using a MySQL connector or a library that allows Python to interact with MySQL databases. The most common library for this purpose is mysql-connector-python, which is officially maintained by Oracle. Below is a step-by-step guide on how to connect Python with MySQL.
First, you need to install the MySQL connector library if you don't already have it installed.
pip install mysql-connector-python
Once the connector is installed, you can import it in your Python script and use it to establish a connection to your MySQL database.
import mysql.connector
# Establish a connection to the MySQL database
connection = mysql.connector.connect(
host="localhost", # The host where the database is hosted
user="your_username", # Your MySQL username
password="your_password", # Your MySQL password
database="your_database" # The database you want to connect to
)
# Check if the connection was successful
if connection.is_connected():
print("Successfully connected to the database")
else:
print("Failed to connect to the database")
Once connected, you can create a cursor object that allows you to execute SQL queries.
# Create a cursor object
cursor = connection.cursor()
# Example: Create a new table
cursor.execute("""
CREATE TABLE IF NOT EXISTS employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
position VARCHAR(255),
hire_date DATE
)
""")
# Example: Insert data into the table
cursor.execute("""
INSERT INTO employees (name, position, hire_date)
VALUES (%s, %s, %s)
""", ("John Doe", "Software Engineer", "2024-01-01"))
# Commit the transaction
connection.commit()
# Example: Retrieve data from the table
cursor.execute("SELECT * FROM employees")
# Fetch all rows from the last executed statement
rows = cursor.fetchall()
# Display the retrieved data
for row in rows:
print(row)
It's important to handle errors when working with databases to ensure the program can handle situations like connection failures, query errors, etc.
try:
# Your database operations here
pass
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
# Ensure the connection is closed even if there was an error
if connection.is_connected():
cursor.close()
connection.close()
print("MySQL connection is closed")
After completing all your operations, it is good practice to close the connection to the database.
# Close the cursor and the connection
cursor.close()
connection.close()
import mysql.connector
try:
# Establish a connection to the MySQL database
connection = mysql.connector.connect(
host="localhost",
user="your_username",
password="your_password",
database="your_database"
)
if connection.is_connected():
print("Successfully connected to the database")
# Create a cursor object
cursor = connection.cursor()
# Create a new table
cursor.execute("""
CREATE TABLE IF NOT EXISTS employees (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255) NOT NULL,
position VARCHAR(255),
hire_date DATE
)
""")
# Insert data into the table
cursor.execute("""
INSERT INTO employees (name, position, hire_date)
VALUES (%s, %s, %s)
""", ("John Doe", "Software Engineer", "2024-01-01"))
# Commit the transaction
connection.commit()
# Retrieve data from the table
cursor.execute("SELECT * FROM employees")
rows = cursor.fetchall()
# Display the retrieved data
for row in rows:
print(row)
except mysql.connector.Error as err:
print(f"Error: {err}")
finally:
if connection.is_connected():
cursor.close()
connection.close()
print("MySQL connection is closed")