【数据库】基于PyMySQL连接并使用数据库（代码示例）

前言

本文演示了如何基于PyMySQL使用代码来创建数据库连接、新建数据库表、向表中插入数据、查询表中的相关记录、更新表中的相关记录、删除表中的相关记录等操作，最后，要记得关闭游标和连接。

1、安装PyMySQL

如果是使用Anaconda来管理包和Python环境，则可以使用$step1$和$step2$来创建一个独立的环境方便管理；如果不是可直接执行$step3$将PyMySQL包安装到本地已有的Python环境中。

$step1：$创建一个虚拟环境

conda create -n use_mysql python=3.6

$step2：$激活虚拟环境

conda activate use_mysql

$step3：$使用清华源安装PyMySQL

pip install PyMySQL -i https://pypi.tuna.tsinghua.edu.cn/simple

2、打开要连接的数据库

我这里使用的是Navicat Premium 16连接一个名称为 “test” 的数据库。

3、创建数据库连接

import pymysql

# 设置数据库连接参数
db_params = {
    'host': 'localhost',         # 数据库服务器地址
    'user': 'root',              # 数据库用户名
    'password': '123456',        # 数据库密码
    'db': 'test',                # 要连接的数据库名称
    'charset': 'utf8mb4',        # 编码方式
}

# 创建数据库连接
try:
    connection = pymysql.connect(**db_params)
    print("Database connection successful.")
except pymysql.MySQLError as e:
    print(f"Error connecting to MySQL Platform: {e}")

运行代码，连接成功后控制台会打印输出 “Database connection successful.”

4、获取数据库版本

# 获取游标对象
cursor = connection.cursor()

# 获取数据库版本
cursor.execute("SELECT VERSION();")
data = cursor.fetchone()
print("Database version: %s " % data)

5、新建数据库表

# 检查表student是否已经存在于数据库test中
cursor.execute("SHOW TABLES LIKE 'STUDENT'")
result = cursor.fetchone()
if result:
    print("Table 'STUDENT' already exists.")
else:
    # 创建表
    cursor.execute("""
    CREATE TABLE STUDENT (
        num  INT NOT NULL,
        name  CHAR(20),
        age INT,  
        sex CHAR(10)
    )
    """)
    connection.commit()
    print("Table 'STUDENT' created successfully.")

6、向表中插入数据

# 向student表中插入数据
insert_sql_1 = "INSERT INTO STUDENT(num, name, age, sex) VALUES (1, 'zhangsan', 20, 'Man')"
insert_sql_2 = "INSERT INTO STUDENT(num, name, age, sex) VALUES (2, 'lisi', 18, 'Woman')"
insert_sql_3 = "INSERT INTO STUDENT(num, name, age, sex) VALUES (3, 'wangwu', 21, 'Man')"
insert_sql_4 = "INSERT INTO STUDENT(num, name, age, sex) VALUES (4, 'zhaoliu', 19, 'Woman')"
try:
    cursor.execute(insert_sql_1)
    cursor.execute(insert_sql_2)
    cursor.execute(insert_sql_3)
    cursor.execute(insert_sql_4)
    connection.commit()
    print("Successfully inserted data into the table.")
except Exception as e:  # 捕获所有异常
    print(f"An error occurred: {e}")
    connection.rollback()

运行代码后，回到 Navicat Premium 16 查看插入的数据。

7、查询表中的相关记录

# 查询student表中name字段值为'zhangsan'的所有记录
query_sql = "SELECT * FROM student WHERE name='zhangsan'"
try:
    cursor.execute(query_sql)
    results = cursor.fetchall()  # 获取所有查询结果
    print("The query results are as follows:")
    for row in results:
        print(row)  # 打印每一行结果
    connection.commit()
except Exception as e:  # 捕获所有异常
    print(f"An error occurred: {e}")
    connection.rollback()

8、更新表中的相关记录

# 更新student表中num字段值为3的记录的name字段为'newname'：
update_sql = "UPDATE student SET name = 'newname' WHERE num = 3"
try:
    cursor.execute(update_sql)
    connection.commit()
    print("Successfully updated data in the table.")
except Exception as e:  # 捕获所有异常
    print(f"An error occurred: {e}")
    connection.rollback()

9、删除表中的相关记录

# 删除student表中num字段值为4的所有记录
delete_sql = "DELETE FROM student WHERE num = 4"
try:
    cursor.execute(delete_sql)
    connection.commit()
    print("Successfully deleted data from the table.")
except Exception as e:  # 捕获所有异常
    print(f"An error occurred: {e}")
    connection.rollback()

10、关闭游标和连接

# 关闭游标和连接
cursor.close()
connection.close()

完整代码

import pymysql

# 设置数据库连接参数
db_params = {
    'host': 'localhost',         # 数据库服务器地址
    'user': 'root',              # 数据库用户名
    'password': '123456',      # 数据库密码
    'db': 'test',                # 要连接的数据库名称
    'charset': 'utf8mb4',        # 编码方式
}

# 创建数据库连接
try:
    connection = pymysql.connect(**db_params)
    print("Database connection successful.")
except pymysql.MySQLError as e:
    print(f"Error connecting to MySQL Platform: {e}")

# 获取游标对象
cursor = connection.cursor()

# 获取数据库版本
cursor.execute("SELECT VERSION();")
data = cursor.fetchone()
print("Database version: %s " % data)

# 检查表student是否已经存在于数据库test中
cursor.execute("SHOW TABLES LIKE 'STUDENT'")
result = cursor.fetchone()
if result:
    print("Table 'STUDENT' already exists.")
else:
    # 创建表
    cursor.execute("""
    CREATE TABLE STUDENT (
        num  INT NOT NULL,
        name  CHAR(20),
        age INT,  
        sex CHAR(10)
    )
    """)
    connection.commit()
    print("Table 'STUDENT' created successfully.")

# 向student表中插入数据
insert_sql_1 = "INSERT INTO STUDENT(num, name, age, sex) VALUES (1, 'zhangsan', 20, 'Man')"
insert_sql_2 = "INSERT INTO STUDENT(num, name, age, sex) VALUES (2, 'lisi', 18, 'Woman')"
insert_sql_3 = "INSERT INTO STUDENT(num, name, age, sex) VALUES (3, 'wangwu', 21, 'Man')"
insert_sql_4 = "INSERT INTO STUDENT(num, name, age, sex) VALUES (4, 'zhaoliu', 19, 'Woman')"
try:
    cursor.execute(insert_sql_1)
    cursor.execute(insert_sql_2)
    cursor.execute(insert_sql_3)
    cursor.execute(insert_sql_4)
    connection.commit()
    print("Successfully inserted data into the table.")
except Exception as e:  # 捕获所有异常
    print(f"An error occurred: {e}")
    connection.rollback()

# 查询student表中name字段值为'zhangsan'的所有记录
query_sql = "SELECT * FROM student WHERE name='zhangsan'"
try:
    cursor.execute(query_sql)
    results = cursor.fetchall()  # 获取所有查询结果
    print("The query results are as follows:")
    for row in results:
        print(row)  # 打印每一行结果
    connection.commit()
except Exception as e:  # 捕获所有异常
    print(f"An error occurred: {e}")
    connection.rollback()

# 更新student表中num字段值为3的记录的name字段为'newname'：
update_sql = "UPDATE student SET name = 'newname' WHERE num = 3"
try:
    cursor.execute(update_sql)
    connection.commit()
    print("Successfully updated data in the table.")
except Exception as e:  # 捕获所有异常
    print(f"An error occurred: {e}")
    connection.rollback()

# 删除student表中num字段值为4的所有记录
delete_sql = "DELETE FROM student WHERE num = 4"
try:
    cursor.execute(delete_sql)
    connection.commit()
    print("Successfully deleted data from the table.")
except Exception as e:  # 捕获所有异常
    print(f"An error occurred: {e}")
    connection.rollback()

# 关闭游标和连接
cursor.close()
connection.close()