Python MySQL Tutorial

Introduction

MySQL is a popular relational database management system. Python, with its extensive libraries, can interact with MySQL databases to perform various database operations like creating, reading, updating, and deleting records. This tutorial covers the basics of connecting Python to a MySQL database and performing common database operations.

Table of Contents

1. Prerequisites
2. Installing MySQL Connector
3. Connecting to MySQL Database
4. Creating a Database
5. Creating a Table
6. Inserting Data
7. Querying Data
8. Updating Data
9. Deleting Data
10. Using Prepared Statements
11. Closing the Connection
12. Conclusion

1. Prerequisites

Before you start, make sure you have:

• Python is installed on your system.
• MySQL is installed and running on your system.
• Basic knowledge of SQL and Python.

2. Installing MySQL Connector

To interact with MySQL, you need to install the MySQL Connector for Python. You can install it using pip.

pip install mysql-connector-python

3. Connecting to MySQL Database

To connect to a MySQL database, you need to import the mysql.connector module and use the connect() method.

Example

import mysql.connector

# Establishing the connection
conn = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword"
)

# Creating a cursor object
cursor = conn.cursor()

# Checking if the connection was successful
if conn.is_connected():
    print("Connected to MySQL database")

4. Creating a Database

You can create a new database using the CREATE DATABASE SQL statement.

Example

cursor.execute("CREATE DATABASE mydatabase")
print("Database created successfully")

5. Creating a Table

To create a table, you need to select the database and then use the CREATE TABLE SQL statement.

Example

# Selecting the database
conn.database = "mydatabase"

# Creating a table
create_table_query = """
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(255) NOT NULL,
    last_name VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL
)
"""
cursor.execute(create_table_query)
print("Table created successfully")

6. Inserting Data

You can insert data into a table using the INSERT INTO SQL statement.

Example

# Inserting data
insert_query = "INSERT INTO employees (first_name, last_name, email) VALUES (%s, %s, %s)"
values = ("Ravi", "Kumar", "ravi.kumar@example.com")

cursor.execute(insert_query, values)
conn.commit()
print("Data inserted successfully")

7. Querying Data

You can query data from a table using the SELECT SQL statement.

Example

# Querying data
select_query = "SELECT * FROM employees"
cursor.execute(select_query)

# Fetching all rows
rows = cursor.fetchall()

# Printing the rows
for row in rows:
    print(row)

8. Updating Data

You can update data in a table using the UPDATE SQL statement.

Example

# Updating data
update_query = "UPDATE employees SET email = %s WHERE first_name = %s AND last_name = %s"
values = ("ravi.kumar@newemail.com", "Ravi", "Kumar")

cursor.execute(update_query, values)
conn.commit()
print("Data updated successfully")

9. Deleting Data

You can delete data from a table using the DELETE FROM SQL statement.

Example

# Deleting data
delete_query = "DELETE FROM employees WHERE first_name = %s AND last_name = %s"
values = ("Ravi", "Kumar")

cursor.execute(delete_query, values)
conn.commit()
print("Data deleted successfully")

10. Using Prepared Statements

Prepared statements help prevent SQL injection attacks and improve performance.

Example

# Using prepared statements to insert data
prepared_query = "INSERT INTO employees (first_name, last_name, email) VALUES (%s, %s, %s)"
values = [
    ("Anjali", "Sharma", "anjali.sharma@example.com"),
    ("Suresh", "Verma", "suresh.verma@example.com")
]

cursor.executemany(prepared_query, values)
conn.commit()
print("Multiple records inserted successfully")

11. Closing the Connection

After completing the database operations, it's important to close the cursor and the connection to free up resources.

Example

# Closing the cursor and connection
cursor.close()
conn.close()
print("MySQL connection closed")

12. Conclusion

Connecting Python to a MySQL database allows you to perform various database operations from your Python applications. This tutorial covered the basics of connecting to a MySQL database, creating databases and tables, inserting, querying, updating, and deleting data, using prepared statements, and closing the connection. By mastering these concepts, you can efficiently manage and interact with MySQL databases in your Python projects.