Menu
python

MySQL Connection

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.

 

 

Step 1: Install MySQL Connector

First, you need to install the MySQL connector library if you don't already have it installed.

pip install mysql-connector-python
 

 

Step 2: Import the Library and Connect to MySQL

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")
 

 

Step 3: Creating a Cursor Object and Executing Queries

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)
 

 

Step 4: Handling Errors

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")
 

 

Step 5: Closing the Connection

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()
 

 

Full Example Code

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")
 

 

python