使用Python操作MySQL数据库

一、创建数据库连接

最基础写法

"""
    pymysql创建数据库连接 最基础写法
"""

from pymysql import Connection

# 创建数据库连接
con = Connection(
    host="localhost",  # 主机名
    port=3306,  # 端口号
    user="root",  # 用户名
    password="******",  # 密码
	database="studyPymysql",  # 数据库名
)

print(type(con))  # <class 'pymysql.connections.Connection'>
print(con.get_host_info())  # socket localhost:3306
print(con.get_server_info())  # 5.7.26

# 关闭数据库连接
con.close()

改进版

"""
    pymysql创建数据库连接 改进版
"""

from pymysql import Connection

con = None  # 先定义为全局变量防止下方finally中无法访问

try:
    # 创建数据库连接
    con = Connection(
        host="localhost",  # 主机名
        port=3306,  # 端口号
        user="root",  # 用户名
        password="******",  # 密码
    )

    print(type(con))  # <class 'pymysql.connections.Connection'>
    print(con.get_host_info())  # socket localhost:3306
    print(con.get_server_info())  # 5.7.26
except Exception as e:
    print("异常：", e)
finally:
    if con:
        # 关闭数据库连接
        con.close()

二、执行DDL语句

"""
    pymysql执行DDL语句
    DDL（Data Definition Language）语句是一组用于定义和管理数据库结构和对象的SQL语句。
"""

from pymysql import Connection

con = None  # 先定义为全局变量防止下方finally中无法访问

try:
    # 创建数据库连接
    con = Connection(
        host="localhost",  # 主机名
        port=3306,  # 端口号
        user="root",  # 用户名
        password="******",  # 密码
        database="studyPymysql",  # 数据库名
    )

    # 选择要操作的数据库
    # 方法一：就是上方创建连接时指定的database参数
    # 方法二：con.select_db("studyPymysql")
    # 方法三：cursor.execute("USE studyPymysql")

    # 创建游标对象
    cursor = con.cursor()

    # 定义一个建表的SQL语句
    sql = """
        CREATE TABLE t_student (
            id INT PRIMARY KEY,
            name VARCHAR(20) NOT NULL,
            age INT NOT NULL
        ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    """

    # 执行SQL语句
    cursor.execute(sql)
    
    # 关闭游标
    cursor.close() # 关闭游标后，游标对象不可再使用，实际操作中不需要关闭其实，关闭数据库时会自动关闭游标
    
    # 修改列的数据类型或属性
    sql1 = "ALTER TABLE t_student MODIFY COLUMN age VARCHAR(3);"
    # 重命名列
    sql2 = "ALTER TABLE t_student CHANGE COLUMN age age1 VARCHAR(3);"
    # 添加新列
    sql3 = "ALTER TABLE t_student ADD COLUMN email VARCHAR(50);"
    # 删除列
    sql4 = "ALTER TABLE t_student DROP COLUMN email;"
    # 设置默认值
    sql5 = "ALTER TABLE t_student ALTER COLUMN age1 SET DEFAULT 18;"
    # 删除默认值
    sql6 = "ALTER TABLE t_student ALTER COLUMN age1 DROP DEFAULT;"
    # 修改表名
    sql7 = "ALTER TABLE t_student RENAME TO t_student1;"
    # 删除表
    sql8 = "DROP TABLE t_student1;"
    
except Exception as e:
    print("异常：", e)
finally:
    if con:
        # 关闭数据库连接
        con.close()

三、执行DML语句

"""
    pymysql执行DML语句
    DML(Data Manipulation Language)数据操作语言，用于对数据库中的数据进行操作，包括增、删、改、查等操作。
"""

from pymysql import Connection

con = None  # 先定义为全局变量防止下方finally中无法访问

try:
    # 创建数据库连接
    con = Connection(
        host="localhost",  # 主机名
        port=3306,  # 端口号
        user="root",  # 用户名
        password="******",  # 密码
        database="studyPymysql",  # 数据库名
        autocommit=True,  # 自动提交事务
    )

    # 创建游标对象
    cursor = con.cursor()
    
    # —————————— 插入数据 ——————————
    cursor.execute("INSERT INTO t_student VALUES (1, '张三', 18);")
    # 插入多条数据
    cursor.executemany("INSERT INTO t_student VALUES (%s, %s, %s);", [(2, '李四', 19), (3, '王五', 20)])
    # 插入一条数据并返回自增ID
    cursor.execute("INSERT INTO t_student (name, age) VALUES ('赵六', 21);")
    print("使用 cursor.lastrowid 获取自增ID:", cursor.lastrowid)
    print("使用 con.insert_id() 获取自增ID:", con.insert_id())
    # 插入一条数据并返回受影响的行数
    cursor.execute("INSERT INTO t_student (name, age) VALUES ('孙七', 22);")
    print(cursor.rowcount)  # 1
    # 注意：
        # 在MySQL中，插入数据操作未生效的常见原因是未提交事务。
        # 默认情况下，MySQL数据库的连接是以自动提交模式工作的，但在某些情况下，特别是在使用一些库如pymysql时，默认设置可能会关闭自动提交。这意味着你需要显式地提交事务，以确保数据操作生效。
        # 可以在插入操作之后添加 con.commit() 来提交事务。
    
    
    # —————————— 查询数据 ——————————
    cursor.execute("SELECT * FROM t_student;")
    # 获取所有数据
    print(cursor.fetchall())  # ((1, '张三', 18), (2, '李四', 19), (3, '王五', 20), (4, '赵六', 21), (5, '孙七', 22))
    for row in cursor.fetchall():
        print(row)
        
    
    # —————————— 更新数据 ——————————
    cursor.execute("UPDATE t_student SET age=23 WHERE name='孙七';")
    
    
    # —————————— 删除数据 ——————————
    cursor.execute("DELETE FROM t_student WHERE name='孙七';")
    

except Exception as e:
    print("异常：", e)
finally:
    if con:
        # 关闭数据库连接
        con.close()

四、调用存储过程

在 MySQL 中，存储过程是一组预编译的 SQL 语句，可以通过名称和参数进行调用。使用存储过程可以简化复杂的数据库操作，并提高性能和可维护性。pymysql 允许你调用 MySQL 的存储过程，执行一系列的操作。

下面是一个创建和调用存储过程的示例。

1.创建存储过程

假设我们要创建一个简单的存储过程，它接受一个学生的名字并返回该学生的年龄。

DELIMITER //

CREATE PROCEDURE GetStudentAge(IN student_name VARCHAR(20), OUT student_age INT)
BEGIN
    SELECT age INTO student_age
    FROM t_student
    WHERE name = student_name;
END //

DELIMITER ;

这个存储过程 GetStudentAge 接受一个输入参数 student_name，并将查询到的年龄值存储到输出参数 student_age 中。

2. 使用 pymysql 调用存储过程

下面是一个示例代码，展示如何使用 pymysql 调用上述存储过程并获取结果。

import pymysql

# 创建数据库连接
con = pymysql.connect(
    host="localhost",
    port=3306,
    user="root",
    password="******",
    database="your_database_name"
)

try:
    # 创建游标对象
    cursor = con.cursor()
    
    # 定义输入参数和输出参数
    student_name = '张三'
    student_age = 0

    # 调用存储过程
    cursor.callproc('GetStudentAge', [student_name, student_age])
    
    # 获取存储过程的输出参数
    cursor.execute("SELECT @_GetStudentAge_1")
    result = cursor.fetchone()
    student_age = result[0]

    print(f"{student_name} 的年龄是: {student_age}")

except Exception as e:
    print("异常：", e)
finally:
    if con:
        # 关闭数据库连接
        con.close()

创建数据库连接：使用 pymysql.connect() 创建与 MySQL 数据库的连接。
创建游标对象：通过 con.cursor() 创建一个游标对象。
调用存储过程：使用 cursor.callproc('GetStudentAge', [student_name, student_age]) 调用存储过程。注意，这里 student_age 只是一个占位符，实际的输出参数值需要通过后续步骤获取。
获取输出参数：存储过程的输出参数可以通过查询会话变量（如 @_GetStudentAge_1）获取。这里使用 cursor.execute("SELECT @_GetStudentAge_1") 执行查询，并通过 cursor.fetchone() 获取结果。
处理异常和关闭连接：在 try-except-finally 块中处理可能的异常，并确保在操作结束后关闭数据库连接。

通过这种方式，可以使用 pymysql 调用 MySQL 的存储过程，并处理输入和输出参数。