Python基础之与Mysql交互

1.概述

数据库是长期储存在计算机内、有组织的、可共享的数据集合。数据库中的数据指的是以一定的数据模型组织、描述和储存在一起、具有尽可能小的冗余度、较高的数据独立性和易扩展性的特点并可在一定范围内为多个用户共享。

这种数据集合具有如下特点：尽可能不重复，以最优方式为某个特定组织的多种应用服务，其数据结构独立于使用它的应用程序，对数据的增、删、改、查由统一软件进行管理和控制。

在信息化社会，充分有效地管理和利用各类信息资源，是进行科学研究和决策管理的前提条件。数据库技术是管理信息系统、办公自动化系统、决策支持系统等各类信息系统的核心部分，是进行科学研究和决策管理的重要技术手段。

MySQL是一种开放源代码的关系型数据库管理系统（RDBMS），使用最常用的数据库管理语言–结构化查询语言（SQL）进行数据库管理。MySQL因为其速度、可靠性和适应性而备受关注。

---

2.MySQL指令

MySQL指令属于基础中的基础，在此不再赘述。
如果你不了解MySQL相关指令，推荐参考MySQL 教程 | 菜鸟教程

---

3.Python与MySQL的交互

(1)安装pymysql模块

1. 在管理员身份下打开控制台
2. 输入 pip install pymysql
3. 等待完成，若输出successful则为成功。若失败请检查网络连接或当前身份

(2)引入pymysql模块

在Python文件中，使用import pymysql引入pymysql模块。

(3)连接数据库

conn = pymysql.connect(
    host="127.0.0.1", # 地址，默认只支持本地，如特别需要请修改mysql.user表
    port=3306, # 端口号
    user="root", # 用户名
    password="123", # 密码
    database="stu", # 所选数据库名称
    charset="utf8" # 编码方式
)
print(conn)
# <pymysql.connections.Connection object at 0x00000047CEBC9B00>
# 连接成功生成一个对象

(4)生成游标对象

游标相当于一个python中对mysql操作的管家，我们将需要执行的操作全部交给游标，游标再调用相应方法执行该操作。

# 生成游标对象
cursor = conn.cursor()

(5)创建数据表

服务器数据库创建数据表过程:

1. 连接数据库
2. 创建游标
3. 定义创建数据表sql语句
4. 执行sql语句
5. 关闭数据库连接

# 连接数据库 略

# 生成游标对象
cursor = conn.cursor()
# 创建表格
sql_create = """
    CREATE TABLE if NOT EXISTS student(
        sno VARCHAR(16) NOT NULL PRIMARY KEY ,
        name VARCHAR(16) NOT NULL ,
        sex CHAR(4) NOT NULL ,
        age INT(3) NOT NULL
    )ENGINE = InnoDB DEFAULT CHARSET = 'utf8'
"""
# 执行sql语句
result = cursor.execute(sql_create)

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

(6)插入数据

服务器数据库数据的插入过程:

1. 连接数据库
2. 获取游标对象
3. 定义插入语句
4. 执行executemany()完成多条数据的插入
5. 执行数据commit操作完成数据的提交(最终执行表格数据存储)
6. 关闭数据库

# 连接数据库

# 生成游标对象
cursor = conn.cursor()
# 执行插入数据操作 字符串处理
sql_insert = """
    INSERT INTO student(sno, name, sex, age) VALUES (%s, %s, %s, %s)
"""
# cursor.executemany() 添加任意多条数据
result = cursor.executemany(sql_insert, [("15", "二狗", "男", 20), ("16", "三毛", "男", 25), ("17", "狗蛋", "男", 35)])
print(result)
# 3

# 数据提交
conn.commit()

# 关闭数据库
conn.close()

注：切记数据的增、改、删等数据控制操作需要使用conn.commit()提交，否则数据库不会获取数据。
详见Python与MySQL交互中Python提交添加数据操作但数据库并未添加问题解决方案

(7)查询数据

服务器数据库数据的查询过程:

1. 连接数据库
2. 获取游标对象
3. 定义查询语句
4. 执行execute()完成数据查询
5. 使用cursor.fetchall()、cursor.fetchmany(size=None)、cursor.fetchone()获取数据
6. 关闭数据库

# 创建连接

# 生成游标对象 cursor=pymysql.cursors.DictCursor : 保证数据在查询之后以字典的形式返回给外界,默认是以元组类型返回
cursor = conn.cursor(cursor=pymysql.cursors.DictCursor)

sql_select = "select * from student"
result = cursor.execute(sql_select)
"""
获取查询结果操作
cursor.fetchall():从查询结果中返回所有查询内容
cursor.fetchmany(size=None):从查询结果中获取指定size的数据
cursor.fetchone():从查询结果中获取一条数据
"""
# print(cursor.fetchall())
# print(cursor.fetchone())
print(cursor.fetchmany(3))
# [{'sno': '11', 'name': '二狗', 'sex': '男', 'age': 20}, {'sno': '12', 'name': '狗剩子', 'sex': '男', 'age': 25}, {'sno': '13', 'name': '狗蛋', 'sex': '男', 'age': 35}]
# 每输出一条数据，cursor内数据都会减一条

# 关闭连接
conn.close()

(8)删除数据

服务器数据库数据的删除过程:

1. 连接数据库
2. 获取游标对象
3. 定义删除语句
4. 执行execute()完成数据的删除
5. 执行数据commit操作完成数据的提交(最终执行表格数据存储)
6. 关闭数据库

# 创建连接

# 生成游标对象
cursor = conn.cursor()
# 定义sql语句
sql_delete = "delete from student where sno = %s or name = %s"
# 执行
result = cursor.execute(sql_delete, ("11",))
# 提交
conn.commit()

# 关闭连接
conn.close()

(9)数据更新

服务器数据库数据的更新过程:

1. 连接数据库
2. 获取游标对象
3. 定义更新语句
4. 执行execute()完成数据的更新
5. 执行数据commit操作完成数据的提交(最终执行表格数据存储)
6. 关闭数据库

# 创建连接

# 生成游标对象
cursor = conn.cursor()
# 定义sql语句
sql_update = "update student set name = %s , sex = %s where sno = %s"
# 执行
result = cursor.execute(sql_update, ("狗剩子", "男", "12"))
# 提交
conn.commit()

# 关闭数据库
conn.close()