MySQL Python Connection and Usage

MySQL is one of the most popular open-source relational databases, and Python is one of the most popular programming languages today. Combining Python with MySQL allows us to easily develop database-driven applications.

This article will detail how to use Python to connect to and operate a MySQL database, covering the following:

  1. How to install the MySQL Python driver
  2. Establishing and closing database connections
  3. Executing various SQL queries
  4. Transaction management and error handling
  5. Best practices for database operations

Preparation

Install Required Software

Before you begin, please ensure you have installed the following software:

  • Python(Recommended 3.6 or higher)
  • MySQL Server(Community Edition is sufficient)
  • MySQL Connector/Python(Python's MySQL driver)

Install MySQL Connector/Python

You can install the official MySQL Python driver via pip:

pip install mysql-connector-python

Or install PyMySQL (another popular MySQL Python driver):

pip install pymysql

Connect to MySQL Database

Establish Basic Connection

The following is usingmysql-connector-pythonBasic code for establishing a database connection:

Example

import mysql.connector

# Create database connection
db = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

print("Database connection successful!")

Connection Parameter Explanation

  • host: MySQL server address (local is "localhost")
  • user: Database username
  • password: User password
  • database: Name of the database to connect to (optional)

Connect Using PyMySQL

If you choose to use PyMySQL, the connection method is slightly different:

Example

import pymysql

# Create database connection
db = pymysql.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

print("Database connection successful!")

Execute SQL Queries

Create Cursor Object

Before executing SQL statements, we need to create a cursor object:

Example

cursor = db.cursor()

Execute SELECT Query

Example

cursor.execute("SELECT * FROM your_table")

# Fetch all results
results = cursor.fetchall()

for row in results:
    print(row)

Execute INSERT, UPDATE, DELETE Operations

Example

# Insert data
sql = "INSERT INTO users (name, age) VALUES (%s, %s)"
values = ("Zhang San", 25)
cursor.execute(sql, values)

# Commit transaction
db.commit()

print(cursor.rowcount, " record(s) inserted successfully")

Use Parameterized Queries

To prevent SQL injection, you should always use parameterized queries:

Example

sql = "SELECT * FROM users WHERE name = %s"
name = ("Zhang San",)
cursor.execute(sql, name)

Transaction Management

Basic Concepts of Transactions

A MySQL transaction is a group of atomic SQL queries, where either all execute successfully or none execute at all.

Using Transactions

Example

try:
    # Begin transaction
    cursor.execute("START TRANSACTION")
   
    # Execute multiple SQL statements
    cursor.execute("UPDATE accounts SET balance = balance - 100 WHERE id = 1")
    cursor.execute("UPDATE accounts SET balance = balance + 100 WHERE id = 2")
   
    # Commit transaction
    db.commit()
    print("Transaction executed successfully")
except Exception as e:
    # An error occurred, roll back the transaction
    db.rollback()
    print("Transaction execution failed:", e)

Error Handling

Catching Database Errors

Example

try:
    cursor.execute("SELECT * FROM non_existent_table")
except mysql.connector.Error as err:
    print("Database error:", err)

Common Error Codes

  • 1045: Access denied (incorrect username or password)
  • 1049: Unknown database
  • 1146: Table does not exist
  • 1062: Duplicate key value

Closing Connections

Properly Closing Connections

After completing database operations, you should close the cursor and connection:

Example

cursor.close()
db.close()
print("Database connection closed")

Using the with Statement

Python'swithstatement can automatically manage resources:

Example

with mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
) as db:
    with db.cursor() as cursor:
        cursor.execute("SELECT * FROM users")
        results = cursor.fetchall()
        for row in results:
            print(row)
# The connection will automatically close after leaving the with block

Best Practices

Connection Pool

For applications that frequently connect to the database, it is recommended to use a connection pool:

Example

from mysql.connector import pooling

# Create connection pool
db_pool = pooling.MySQLConnectionPool(
    pool_name="mypool",
    pool_size=5,
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

# Get connection from the connection pool
db = db_pool.get_connection()

ORM Framework

For complex applications, you can consider using an ORM (Object-Relational Mapping) framework, such as SQLAlchemy or Django ORM.

Other Extensions