Python操作MySQL数据库方法汇总

安装必要的库

在Python中操作MySQL数据库，最常用的库是mysql - connector - python和pymysql。这里以mysql - connector - python为例进行介绍，首先需要安装它。你可以使用pip工具进行安装：

pip install mysql - connector - python

如果使用pymysql，安装命令为：

pip install pymysql

连接MySQL数据库

使用mysql - connector - python连接 连接MySQL数据库时，需要提供主机地址、用户名、密码、数据库名称等信息。示例代码如下：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

print(mydb)

在上述代码中，host指定了MySQL服务器的地址，如果是本地服务器，可以使用localhost；user是你的MySQL用户名；password是对应的密码；database是要连接的数据库名称。如果连接成功，会打印出连接对象的信息。

使用pymysql连接

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase',
    charset='utf8mb4'
)

print(mydb)

这里charset指定了字符集，utf8mb4可以支持更多的字符，例如emoji等。

创建数据库

使用mysql - connector - python创建数据库

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE DATABASE IF NOT EXISTS yourdatabase")

在这段代码中，先建立连接，然后获取游标对象mycursor。通过游标执行CREATE DATABASE语句，如果数据库不存在则创建。IF NOT EXISTS关键字用于避免创建已存在数据库时的错误。

使用pymysql创建数据库

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword'
)

mycursor = mydb.cursor()

sql = "CREATE DATABASE IF NOT EXISTS yourdatabase"
mycursor.execute(sql)

这里同样是先连接数据库，获取游标，然后执行创建数据库的SQL语句。

创建表

使用mysql - connector - python创建表 假设我们要在刚刚创建的数据库中创建一个customers表，包含id（自增主键）、name（字符串）和email（字符串）字段，示例代码如下：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

sql = """
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
)
"""

mycursor.execute(sql)

在这个SQL语句中，INT AUTO_INCREMENT表示id字段是自增的整数类型，并且作为主键。VARCHAR用于定义字符串类型，括号内的数字表示字符串的最大长度。

使用pymysql创建表

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

mycursor = mydb.cursor()

sql = """
CREATE TABLE customers (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255)
)
"""

mycursor.execute(sql)

pymysql的创建表操作与mysql - connector - python类似，都是通过获取游标执行SQL语句。

插入数据

插入单条数据 - mysql - connector - python

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, email) VALUES (%s, %s)"
val = ("John Doe", "johndoe@example.com")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

在上述代码中，sql语句使用占位符%s，val是要插入的值的元组。通过execute方法执行插入语句，然后使用commit方法提交事务，使插入操作生效。rowcount属性可以获取插入的行数。

插入单条数据 - pymysql

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, email) VALUES (%s, %s)"
val = ("Jane Smith", "janesmith@example.com")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record inserted.")

pymysql插入单条数据的方式与mysql - connector - python基本相同，只是库的导入和连接方式略有差异。

插入多条数据 - mysql - connector - python

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, email) VALUES (%s, %s)"
val = [
    ("Alice", "alice@example.com"),
    ("Bob", "bob@example.com"),
    ("Charlie", "charlie@example.com")
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "records inserted.")

这里使用executemany方法来插入多条数据，val是一个包含多个元组的列表，每个元组对应一条要插入的数据。

插入多条数据 - pymysql

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, email) VALUES (%s, %s)"
val = [
    ("Eve", "eve@example.com"),
    ("Frank", "frank@example.com"),
    ("Grace", "grace@example.com")
]

mycursor.executemany(sql, val)

mydb.commit()

print(mycursor.rowcount, "records inserted.")

pymysql插入多条数据的操作与mysql - connector - python类似，同样是使用executemany方法。

查询数据

查询所有数据 - mysql - connector - python

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

在这段代码中，SELECT * FROM customers语句用于选择customers表中的所有数据。fetchall方法获取查询结果的所有行，并通过循环打印每一行数据。

查询所有数据 - pymysql

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM customers")

myresult = mycursor.fetchall()

for row in myresult:
    print(row)

pymysql查询所有数据的操作与mysql - connector - python类似，都是执行查询语句后使用fetchall获取结果并遍历。

查询特定列 - mysql - connector - python

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT name, email FROM customers")

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

这里SELECT name, email FROM customers语句只选择了name和email两列的数据。

查询特定列 - pymysql

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

mycursor = mydb.cursor()

mycursor.execute("SELECT name, email FROM customers")

myresult = mycursor.fetchall()

for row in myresult:
    print(row)

pymysql查询特定列的方法与mysql - connector - python一致。

条件查询 - mysql - connector - python

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE name = %s"
val = ("John Doe",)

mycursor.execute(sql, val)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

在这个例子中，WHERE name = %s是条件语句，通过占位符%s传递具体的条件值。

条件查询 - pymysql

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

mycursor = mydb.cursor()

sql = "SELECT * FROM customers WHERE name = %s"
val = ("Jane Smith",)

mycursor.execute(sql, val)

myresult = mycursor.fetchall()

for row in myresult:
    print(row)

pymysql的条件查询与mysql - connector - python类似，都是通过SQL语句的WHERE子句和占位符来实现。

更新数据

使用mysql - connector - python更新数据

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET email = %s WHERE name = %s"
val = ("newemail@example.com", "John Doe")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")

在上述代码中，UPDATE customers SET email = %s WHERE name = %s语句用于更新customers表中满足条件的数据。SET关键字后面指定要更新的列和新的值，WHERE子句用于指定更新的条件。

使用pymysql更新数据

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

mycursor = mydb.cursor()

sql = "UPDATE customers SET email = %s WHERE name = %s"
val = ("newemail2@example.com", "Jane Smith")

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) affected")

pymysql更新数据的方式与mysql - connector - python基本相同，都是执行更新SQL语句并提交事务。

删除数据

使用mysql - connector - python删除数据

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE name = %s"
val = ("John Doe",)

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

这里DELETE FROM customers WHERE name = %s语句用于从customers表中删除满足条件的数据。通过WHERE子句指定删除的条件。

使用pymysql删除数据

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

mycursor = mydb.cursor()

sql = "DELETE FROM customers WHERE name = %s"
val = ("Jane Smith",)

mycursor.execute(sql, val)

mydb.commit()

print(mycursor.rowcount, "record(s) deleted")

pymysql删除数据的操作与mysql - connector - python类似，都是执行删除SQL语句并提交事务。

使用事务处理

mysql - connector - python中的事务处理 在一些情况下，需要确保多个数据库操作要么全部成功，要么全部失败，这就需要使用事务。例如，在一个银行转账操作中，从一个账户扣除金额，同时向另一个账户增加金额，这两个操作必须作为一个整体。示例代码如下：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

try:
    # 开始事务
    mydb.start_transaction()

    # 从账户A扣除金额
    sql1 = "UPDATE accounts SET balance = balance - %s WHERE account_id = %s"
    val1 = (100, 'A')
    mycursor.execute(sql1, val1)

    # 向账户B增加金额
    sql2 = "UPDATE accounts SET balance = balance + %s WHERE account_id = %s"
    val2 = (100, 'B')
    mycursor.execute(sql2, val2)

    # 提交事务
    mydb.commit()
    print("Transaction successful")
except Exception as e:
    # 回滚事务
    mydb.rollback()
    print("Transaction failed: ", e)
finally:
    mycursor.close()
    mydb.close()

在这个例子中，通过start_transaction开始事务，执行多个数据库操作后，如果没有异常则使用commit提交事务，如果出现异常则使用rollback回滚事务，确保数据的一致性。

pymysql中的事务处理

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

mycursor = mydb.cursor()

try:
    # 开始事务
    mydb.autocommit(False)

    # 从账户A扣除金额
    sql1 = "UPDATE accounts SET balance = balance - %s WHERE account_id = %s"
    val1 = (100, 'A')
    mycursor.execute(sql1, val1)

    # 向账户B增加金额
    sql2 = "UPDATE accounts SET balance = balance + %s WHERE account_id = %s"
    val2 = (100, 'B')
    mycursor.execute(sql2, val2)

    # 提交事务
    mydb.commit()
    print("Transaction successful")
except Exception as e:
    # 回滚事务
    mydb.rollback()
    print("Transaction failed: ", e)
finally:
    mycursor.close()
    mydb.close()

pymysql中通过设置autocommit(False)来开始事务，后续的提交和回滚操作与mysql - connector - python类似。

防止SQL注入

使用占位符防止SQL注入 - mysql - connector - python SQL注入是一种常见的安全漏洞，恶意用户可以通过在输入字段中插入SQL语句来破坏数据库或获取敏感信息。使用占位符是防止SQL注入的有效方法。例如，在前面的条件查询中，我们已经使用了占位符。假设我们有一个用户输入的用户名，用于查询用户信息：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

username = input("Enter username: ")
sql = "SELECT * FROM users WHERE username = %s"
val = (username,)

mycursor.execute(sql, val)

myresult = mycursor.fetchall()

for x in myresult:
    print(x)

在这个例子中，用户输入的值通过占位符%s传递给SQL语句，而不是直接拼接在SQL语句中，这样可以有效防止SQL注入。

使用占位符防止SQL注入 - pymysql

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

mycursor = mydb.cursor()

username = input("Enter username: ")
sql = "SELECT * FROM users WHERE username = %s"
val = (username,)

mycursor.execute(sql, val)

myresult = mycursor.fetchall()

for row in myresult:
    print(row)

pymysql同样通过使用占位符来防止SQL注入，保证数据库操作的安全性。

处理大数据量

逐行读取大数据 - mysql - connector - python 当查询结果数据量很大时，一次性将所有数据加载到内存可能会导致内存不足。可以逐行读取数据来解决这个问题。

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM large_table")

while True:
    row = mycursor.fetchone()
    if row is None:
        break
    print(row)

在这段代码中，使用fetchone方法逐行获取数据，直到所有数据读取完毕。

逐行读取大数据 - pymysql

import pymysql

mydb = pymysql.connect(
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase'
)

mycursor = mydb.cursor()

mycursor.execute("SELECT * FROM large_table")

while True:
    row = mycursor.fetchone()
    if row is None:
        break
    print(row)

pymysql也可以通过fetchone方法逐行读取大数据，避免一次性加载过多数据到内存。

优化数据库操作

批量操作优化 在插入或更新大量数据时，批量操作比单个操作效率更高。前面我们已经展示了插入多条数据的executemany方法。例如，在插入1000条数据时，使用executemany：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

sql = "INSERT INTO customers (name, email) VALUES (%s, %s)"
vals = []
for i in range(1000):
    name = f"User_{i}"
    email = f"user_{i}@example.com"
    vals.append((name, email))

mycursor.executemany(sql, vals)

mydb.commit()

print(mycursor.rowcount, "records inserted.")

这样可以减少与数据库的交互次数，提高效率。

使用索引优化查询 在数据库表上创建适当的索引可以显著提高查询性能。例如，如果经常根据name字段查询customers表，可以为name字段创建索引：

import mysql.connector

mydb = mysql.connector.connect(
    host="localhost",
    user="yourusername",
    password="yourpassword",
    database="yourdatabase"
)

mycursor = mydb.cursor()

mycursor.execute("CREATE INDEX idx_name ON customers (name)")

通过创建索引，数据库在执行涉及name字段的查询时，可以更快地定位数据，从而提高查询效率。

数据库连接池

为什么使用数据库连接池 在高并发的应用程序中，如果每次数据库操作都创建和销毁连接，会带来很大的性能开销。数据库连接池可以预先创建一定数量的连接，并将这些连接保存在池中，应用程序需要数据库连接时，直接从池中获取，使用完毕后再放回池中，这样可以大大减少连接创建和销毁的开销。
使用DBUtils实现连接池 - mysql - connector - python 首先安装DBUtils库：

pip install DBUtils

示例代码如下：

from dbutils.pooled_db import PooledDB
import mysql.connector

# 创建连接池
pool = PooledDB(
    creator=mysql.connector,
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase',
    autocommit=True,
    maxconnections=10
)

# 从连接池获取连接
conn = pool.connection()
mycursor = conn.cursor()

mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()

for x in myresult:
    print(x)

# 关闭游标和连接，将连接放回池中
mycursor.close()
conn.close()

在上述代码中，PooledDB创建了一个连接池，creator指定使用mysql.connector来创建连接，maxconnections指定了连接池中的最大连接数。通过pool.connection()获取连接，使用完毕后关闭连接，连接会自动放回池中。

使用DBUtils实现连接池 - pymysql 同样先安装DBUtils库，然后示例代码如下：

from dbutils.pooled_db import PooledDB
import pymysql

# 创建连接池
pool = PooledDB(
    creator=pymysql,
    host='localhost',
    user='yourusername',
    password='yourpassword',
    database='yourdatabase',
    autocommit=True,
    maxconnections=10
)

# 从连接池获取连接
conn = pool.connection()
mycursor = conn.cursor()

mycursor.execute("SELECT * FROM customers")
myresult = mycursor.fetchall()

for row in myresult:
    print(row)

# 关闭游标和连接，将连接放回池中
mycursor.close()
conn.close()

这里使用pymysql作为连接创建者，其他部分与使用mysql.connector的连接池类似。通过使用连接池，可以有效提高应用程序在高并发场景下与MySQL数据库交互的性能。

与ORM框架结合使用

什么是ORM框架 ORM（Object - Relational Mapping）框架允许开发者使用面向对象的方式操作数据库，而不需要编写大量的SQL语句。它将数据库表映射为Python类，表中的行映射为类的实例，表中的列映射为类的属性。常见的Python ORM框架有SQLAlchemy和Django ORM等。
使用SQLAlchemy操作MySQL数据库 首先安装SQLAlchemy：

pip install sqlalchemy

示例代码如下：

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

# 创建数据库引擎
engine = create_engine('mysql+mysqlconnector://yourusername:yourpassword@localhost/yourdatabase')

# 创建基类
Base = declarative_base()

# 定义表模型
class Customer(Base):
    __tablename__ = 'customers'

    id = Column(Integer, primary_key=True, autoincrement=True)
    name = Column(String(255))
    email = Column(String(255))

# 创建表
Base.metadata.create_all(engine)

# 创建会话
Session = sessionmaker(bind=engine)
session = Session()

# 插入数据
new_customer = Customer(name='Tom', email='tom@example.com')
session.add(new_customer)
session.commit()

# 查询数据
customers = session.query(Customer).all()
for customer in customers:
    print(customer.name, customer.email)

# 关闭会话
session.close()

在这段代码中，首先创建了数据库引擎，然后定义了表模型类Customer，通过Base.metadata.create_all(engine)创建表。使用会话来进行插入和查询操作，会话管理数据库事务。

使用Django ORM操作MySQL数据库（假设已经创建了Django项目） 在Django项目的models.py文件中定义模型：

from django.db import models

class Customer(models.Model):
    name = models.CharField(max_length=255)
    email = models.EmailField()

    def __str__(self):
        return self.name

在视图函数或其他地方进行数据库操作：

from django.http import HttpResponse
from.models import Customer

def add_customer(request):
    new_customer = Customer(name='Jerry', email='jerry@example.com')
    new_customer.save()
    return HttpResponse('Customer added successfully')

def list_customers(request):
    customers = Customer.objects.all()
    result = ''
    for customer in customers:
        result += f"{customer.name}: {customer.email}<br>"
    return HttpResponse(result)

Django ORM通过模型类的方法来进行数据库操作，save方法用于插入或更新数据，objects.all()用于查询所有数据。

通过上述对Python操作MySQL数据库的各种方法的介绍，包括基本的连接、增删改查操作，到事务处理、防止SQL注入、大数据处理、优化操作、连接池以及与ORM框架结合等方面，相信你已经对如何在Python中高效、安全地操作MySQL数据库有了全面的了解，可以根据实际项目需求选择合适的方法来实现功能。