PyMySQL is a library for connecting to MySQL servers from Python 3.x, replacing MySQLdb used in Python 2. Install it using pip:

pip install pymysql

Core Methods in PyMySQL

pymysql.connect() parameters:

• host (str): MySQL server address
• port (int): MySQL server port
• user (str): database usernmae
• password (str): database password
• database (str): database name
• charset (str): connection encoding

Connection object methods:

• cursor(): creates and returns a cursor
• commit(): commits current transaction
• rollback(): rolls back current transaction
• close(): closes the connection

Cursor object methods:

• execute(query): executes a database query
• fetchone(): retrieves next row from result set
• fetchmany(count): retrieves next several rows
• fetchall(): retrieves all rows from result set
• rowcount: returns number of affected rows
• close(): closes the cursor

Practical Database Operations Example

import pymysql

# Establish database connection
db_connection = pymysql.connect(
    host='localhost',
    port=3306,
    user='admin',
    password='securepass',
    database='school_data',
    charset='utf8mb4'
)

# Create cursor object
db_cursor = db_connection.cursor()

# Create database (commented out)
# create_db = "CREATE DATABASE IF NOT EXISTS school_data;"
# db_cursor.execute(create_db)

# Create tables (commented out)
# create_students = """
# CREATE TABLE students (
#     student_id INT PRIMARY KEY,
#     full_name VARCHAR(50),
#     gender CHAR(1),
#     class_code VARCHAR(10),
#     school_code VARCHAR(10),
#     home_address VARCHAR(100),
#     created_at TIMESTAMP,
#     updated_at TIMESTAMP
# ) DEFAULT CHARSET=utf8mb4;
# """
# db_cursor.execute(create_students)

# Insert single record (commented out)
# insert_grade = "INSERT INTO grades (grade_id, level) VALUES (1, 'Freshman');"
# db_cursor.execute(insert_grade)

# Insert multiple records (commented out)
# insert_students = """
# INSERT INTO students (student_id, gender, created_at) 
# VALUES (101, 'F', NOW()), (102, 'M', NOW());
# """
# db_cursor.execute(insert_students)

# Update records (commented out)
# update_all = "UPDATE students SET gender = 'Unknown';"
# db_cursor.execute(update_all)
# update_specific = "UPDATE students SET gender = 'F' WHERE student_id = 102;"
# db_cursor.execute(update_specific)

# Query data
select_query = "SELECT * FROM students;"
db_cursor.execute(select_query)

# Process all results
for record in db_cursor.fetchall():
    print(record)
print(f"Total records found: {db_cursor.rowcount}")

# Fetch single row
# first_row = db_cursor.fetchone()
# print(first_row)

# Fetch limited rows
# some_rows = db_cursor.fetchmany(5)
# print(some_rows)

# Delete records (commented out)
# delete_query = "DELETE FROM students WHERE gender = 'Unknown';"
# db_cursor.execute(delete_query)

# Drop table (commented out)
# drop_table = "DROP TABLE IF EXISTS grades;"
# db_cursor.execute(drop_table)

# Drop database (commented out)
# drop_db = "DROP DATABASE school_data;"
# db_cursor.execute(drop_db)

db_connection.commit()
db_cursor.close()
db_connection.close()