Python and MySQL: A Practical Guide to Database Connectivity, Creation, and CRUD Operations

In today’s data‑driven world, Python developers often need to interact with relational databases. MySQL, an open‑source RDBMS, remains a popular choice for its robustness, community support, and ease of use. This guide walks you through every step—from installing MySQL and its Python connector to creating databases, tables, and performing basic CRUD operations—all while emphasizing best practices and clean code.

What is MySQL?

MySQL is a leading open‑source relational database management system (RDBMS). Co‑founder Michael Widenius, whose daughter inspired the name, built MySQL in 1995. Its SQL‑based interface, strong performance, and cross‑platform support make it a go‑to choice for web applications, data analytics, and more.

Installing MySQL on Linux/Unix

Download the official RPM package from MySQL Downloads. Open a terminal and run:

rpm -i <package_name>

For example:

rpm -i MySQL-5.0.9.0.i386.rpm

Verify the installation:

mysql --version

Installing MySQL on Windows

Download the Windows installer from the same MySQL website and follow the wizard. For a step‑by‑step visual guide, see this official tutorial.

Installing the MySQL Connector for Python

Use pip to install the connector library. The command varies slightly between Python 2 and 3:

pip install mysql-connector

pip3 install mysql-connector

Testing the MySQL Connection

Once the connector is installed, test connectivity by creating a simple connection object. Replace hostname, username, and password with your credentials.

import mysql.connector
conn = mysql.connector.connect(
    host="hostname",
    user="username",
    passwd="password"
)
print(conn)

Successful output will resemble:

<mysql.connector.connection.MySQLConnection object at 0x000002338A4C6B00>

Creating a Database via Python

Execute a CREATE DATABASE statement through a cursor object:

import mysql.connector
conn = mysql.connector.connect(
    host="localhost",
    user="root",
    passwd="root"
)
cursor = conn.cursor()
cursor.execute("CREATE DATABASE my_first_db")
cursor.execute("SHOW DATABASES")
for db in cursor:
    print(db)

Output:

("my_first_db",)

Creating Tables

Simple Table

Create a student table with two columns:

cursor.execute("CREATE TABLE student (id INT, name VARCHAR(255))")
cursor.execute("SHOW TABLES")
for tbl in cursor:
    print(tbl)

Output:

("student",)

Table with Primary Key

Define an employee table that includes an auto‑incrementing primary key:

cursor.execute("CREATE TABLE employee (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(255), salary INT(6))")
cursor.execute("SHOW TABLES")
for tbl in cursor:
    print(tbl)

Output:

("employee",) ("student",)

Altering a Table

Modify an existing column to become a primary key:

cursor.execute("ALTER TABLE student MODIFY id INT PRIMARY KEY")

Verification can be done by describing the table or inspecting the schema manually.

Inserting Data

Insert rows into both tables:

student_sql = "INSERT INTO student(id, name) VALUES (1, 'John')"
employee_sql = "INSERT INTO employee(id, name, salary) VALUES (1, 'John', 10000)"

cursor.execute(student_sql)
cursor.execute(employee_sql)
conn.commit()
print(cursor.rowcount, "Record Inserted")

Output:

2 Record Inserted

Next Steps

From here you can explore updates, deletes, and more advanced queries. Always remember to close your connection when done:

conn.close()

Happy coding!