CodeCapsule

Python MySQL

by

🐍 Python MySQL — Complete Guide

Python can interact with MySQL databases using modules like mysql-connector-python or PyMySQL.
This allows you to store, retrieve, and manipulate data from Python programs.

🔹 1️⃣ Install MySQL Connector

pip install mysql-connector-python

🔹 2️⃣ Connecting to MySQL

import mysql.connector

# Connect to database
conn = mysql.connector.connect(
host=“localhost”,
user=“root”,
password=“your_password”,
database=“testdb”
)

print(“Connection Successful”)

Replace testdb with your database name.

🔹 3️⃣ Creating a Cursor

cursor = conn.cursor()

  • Cursor is used to execute SQL queries.

🔹 4️⃣ Creating a Database

cursor.execute("CREATE DATABASE IF NOT EXISTS testdb")

🔹 5️⃣ Creating a Table

cursor.execute("""
CREATE TABLE IF NOT EXISTS students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50),
age INT
)
""")

🔹 6️⃣ Inserting Data

sql = "INSERT INTO students (name, age) VALUES (%s, %s)"
values = ("Vipul", 25)
cursor.execute(sql, values)conn.commit() # save changes
print(cursor.rowcount, “record inserted.”)

🔹 7️⃣ Inserting Multiple Records

sql = "INSERT INTO students (name, age) VALUES (%s, %s)"
values = [
("Riya", 22),
("Amit", 24),
("Sita", 23)
]cursor.executemany(sql, values)
conn.commit()
print(cursor.rowcount, “records inserted.”)

🔹 8️⃣ Reading Data

cursor.execute("SELECT * FROM students")
result = cursor.fetchall()for row in result:
print(row)

  • fetchall() → fetch all rows

  • fetchone() → fetch single row

🔹 9️⃣ Updating Data

sql = "UPDATE students SET age = %s WHERE name = %s"
values = (26, "Vipul")cursor.execute(sql, values)
conn.commit()
print(cursor.rowcount, “record updated.”)

🔹 10️⃣ Deleting Data

sql = "DELETE FROM students WHERE name = %s"
value = ("Amit",)cursor.execute(sql, value)
conn.commit()
print(cursor.rowcount, “record deleted.”)

🔹 11️⃣ Closing Connection

cursor.close()
conn.close()

Always close cursor and connection after operations.

🔹 12️⃣ Exception Handling

import mysql.connector
from mysql.connector import Errortry:
conn = mysql.connector.connect(
host=“localhost”,
user=“root”,
password=“your_password”,
database=“testdb”
)
if conn.is_connected():
print(“Connected to MySQL”)

except Error as e:
print(“Error:”, e)

finally:
if conn.is_connected():
conn.close()
print(“Connection closed”)

🔹 Summary Table

Operation Method
Connect mysql.connector.connect()
Cursor conn.cursor()
Execute Query cursor.execute(sql)
Insert Multiple cursor.executemany(sql, values)
Fetch Data cursor.fetchall() / cursor.fetchone()
Commit Changes conn.commit()
Close Connection cursor.close() & conn.close()

CodeCapsule

Sanjit Sinha — Web Developer | PHP • Laravel • CodeIgniter • MySQL • Bootstrap Founder, CodeCapsule — Student projects & practical coding guides. Email: info@codecapsule.in • Website: CodeCapsule.in

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *