Python学习从0到1 day25 第二阶段 SQL ② Python操作数据库

少年有梦，不应至于心动，更要付诸行动

—— 24.4.12

pymysql

除了使用图形化工具以外，我们也可以使用编程语言来执行SQL从而操作数据库

在Python中，使用第三方库：pymysql来完成对MySQl数据库的操作

安装

pip install pymysql

创建到MySQL的数据库链接

from pymysql import Connection# 获取到MySQL数据库的链接对象
conn = Connection(host = 'localhost', # 主机名（IP地址）port = 3306,    # 端口，默认3306user = 'root',   # 账户名password = 'xxxxxxx'    # 密码
)# 打印MySQL数据库软件信息
print(conn.get_server_info())# 关闭到数据库的链接
conn.close()

pymysql库的基本操作

非查询性质SQL

'''
演示Python pymysql库的基本操作
'''
from pymysql import Connection# 获取到MySQL数据库的链接对象
conn = Connection(host = 'localhost',          # 主机名（IP地址）port = 3306,                 # 端口，默认3306user = 'root',               # 账户名password = '954926928lcl'    # 密码
)# 执行非查询性质SQL# 获取游标对象
cursor = conn.cursor()# 选择数据库
conn.select_db("test")# 使用游标对象，执行sql语句
cursor.execute("create table pythonTest(id int)；")    # 分号可省略

查询性质SQL

'''
演示Python pymysql库的基本操作
'''
from pymysql import Connection# 获取到MySQL数据库的链接对象
conn = Connection(host = 'localhost',          # 主机名（IP地址）port = 3306,                 # 端口，默认3306user = 'root',               # 账户名password = '954926928lcl'    # 密码
)# 执行非查询性质SQL# 获取游标对象
cursor = conn.cursor()# 选择数据库
conn.select_db("twe_8")# 使用游标对象，执行sql语句# 执行查询性质SQl# 获取查询结果
cursor.execute("select * from dept")# 拿到查询结果
results = cursor.fetchall()# 打印出查询结果
for r in results:print(r)# 打印MySQL数据库软件信息
# print(conn.get_server_info())# 关闭到数据库的链接
conn.close()

如何获取链接对象

①from pymysql import Connection 导包

②Connection(主机，端口，账户，密码)即可得到链接对象

③链接对象.close() 关闭和MySQL数据库的链接

如何执行SQL查询

通过链接对象调用cursor()方法，得到游标对象

游标对象.execute()执行SQL语句

游标对象.fetchall()得到全部的查询结果封装入元组内

数据插入

commit提交

经过python执行的数据插入操作是无法将数据插入到数据表中

***因为pymysql是在执行数据插入或其他产生数据更改的SQL语句时，默认需要提交更改的，即：需要通过代码”确认“这种更改行为

通过链接对象.commit() 即可确认此行为，只有确认的修改，才能生效

'''
演示使用pymysql库进行数据插入的操作
'''# 导包
from pymysql import Connection# 构建MySQL数据库的链接
conn = Connection(host = 'localhost',          # 主机名（IP地址）port = 3306,                 # 端口，默认3306user = 'root',               # 账户名password = 'xxxxx'    # 密码
)# 执行非查询性质SQL
cursor = conn.cursor()  # 获取到游标对象
# 选择数据库
conn.select_db("ele_29")
# 执行sql
cursor.execute("insert into dept values(45,'小明')")
# 通过commit确认
conn.commit()
# 关闭链接
conn.close()

自动commit

如果不想手动commit确认，可以在构建链接对象的时候，设置自动commit的属性

autocommit = True

# 构建到MySQL数据库的链接
conn = Connection(host = "localhost",    # 主机名（IP）port = 3306,           # 端口user = "root",         # 账户password = "xxxxxx",   # 密码autocommit = True      # 设置自动提交
)

如上代码进行设置，即可自动提交无需手动commit了

示例：

'''
演示使用pymysql库进行 数据插入 的操作
'''# 导包
from pymysql import Connection# 构建MySQL数据库的链接
conn = Connection(host = 'localhost',          # 主机名（IP地址）port = 3306,                 # 端口，默认3306user = 'root',               # 账户名password = '954926928lcl',   # 密码autocommit = True            # 设置自动提交（确认）
)# 执行非查询性质SQL
cursor = conn.cursor()  # 获取到游标对象
# 选择数据库
conn.select_db("ele_29")
# 执行sql
cursor.execute("insert into dept values(1001,'林俊接')")
# # 通过commit确认
# conn.commit()
# 关闭链接
conn.close()

运行结果

综合案例

使用SQL语句和pymysql库完成综合案例的开发

我们使用前文中的销售数据集，完成使用Python语言，读取数据，并将数据写入MySQL的功能

1.DDL定义

本次需求开发我们需要新建一个数据库来使用，数据库名称：py_sql

基于数据结构，我们可以得到建表语句

create table orders(order_date DATE,order_in varchar(255)money int,province varchar(10)
)

① 创建数据库

② 使用py_sql数据库

use py_sql;

③ 建表语句

create table orders(order_date date,	order_id varchar(255),money int,province varchar(10)
);

④ 数据定义的类

'''
数据定义的类1. 设计一个类，可以完成数据的封装'''class Record:# 使用构造方法定义成员变量 方便在构造类对象的时候直接赋值def __init__(self,data,order_id,money,province):self.data = data            # 订单日期self.order_id = order_id    # 订单IDself.money = money          # 订单金额self.province = province    # 销售省份# 定义一个魔术方法
# 定义一个成员方法,不然直接返回会返回一个内存地址def __str__(self):return f"{self.data},{self.order_id},{self.money},{self.province}"

⑤ 文件相关的类

'''
和文件相关的类定义在这里2. 设计一个抽象类，定义文件读取的相关功能，并使用子类实现具体功能
'''import json# 导包
from data_define import Record# 定义一个抽象类用来做顶层设计，确定类中有哪些功能需要实现
class FileReader:# 顶层设计 pass变为抽象方法def read_data(self) -> list[Record]:    # 返回值是一个Record类的列表List# 读取文件的数据，将读到的每一条数据都转换为我们定义的Record类对象，使用list将record对象封装起来返回即可pass# 文本数据的文件读取器，继承抽象类
class TextFileReader(FileReader):# 定义一个构造方法def __init__(self, path):self.path = path  # 定义成员变量记录文件的路径# 复写(实现抽象方法)父类的方法def read_data(self) -> list[Record]:# 方法内部使用成员变量用selff = open(self.path, "r", encoding="UTF-8")# 类型注解record_list: list[Record] = []for line in f.readlines():# 消除读取到的每一行数据中的\nline = line.strip()# 以逗号用split方法进行切割data_list = line.split(",")# 金钱数字进行转换int()record = Record(data_list[0], data_list[1], int(data_list[2]), data_list[3])record_list.append(record)# 关闭文件对象f.close()# 返回record对象列表return record_list# JSON文件读取器2
# 同样继承于FileReader
class JsonFileReader(FileReader):def __init__(self, path):# 定义成员变量记录文件的路径self.path = pathdef read_data(self) -> list[Record]:f = open(self.path, "r", encoding="UTF-8")record_list: list[Record] = []for line in f.readlines():data_dict = json.loads(line)record = Record(data_dict["date"], data_dict["order_id"], int(data_dict["money"]), data_dict["province"])# 将record对象放入record_list中，并返回record_list.append(record)# 关闭文件对象f.close()return record_listif __name__ == '__main__':text_file_reader = TextFileReader("E:\python.learning\第13章资料/2011年1月销售数据.txt")json_file_reader = JsonFileReader("E:\python.learning\第13章资料/2011年2月销售数据JSON.txt")list1 = text_file_reader.read_data()list2 = json_file_reader.read_data()for l in list1:# 魔术方法print(l)for l in list2:print(l)

⑥ 综合案例main代码

# 导包
from file_define import FileReader, TextFileReader, JsonFileReader
from data_define import Record
from pymysql import  Connection# 读取数据，将读取到的数据保存在变量中
text_file_reader = TextFileReader("E:\python.learning\第13章资料/2011年1月销售数据.txt")
json_file_reader = JsonFileReader("E:\python.learning\第13章资料/2011年2月销售数据JSON.txt")# 一月份数据
jan_data: list[Record] = text_file_reader.read_data()   # 类型注解
# 二月份数据
feb_data: list[Record] = json_file_reader.read_data()   # 类型注解# 将两个月份的数据合并为1个list来存储，通过加法存储
all_data: list[Record] = jan_data + feb_data            # 类型注解# 构建MySQL链接对象
conn = Connection(host = "localhost",port = 3306,user = "root",password ="954926928lcl",autocommit = True
)# 获得游标对象
cursor = conn.cursor()# 选择数据库
conn.select_db("py_sql")# 组织SQL语句
for record in all_data:sql = f"insert into orders(order_date,order_id,money,province)" \f"values('{record.data}', '{record.order_id}', '{record.money}', '{record.province}')"# 通过游标对象传递SQL语句传给游标对象 执行SQL语句cursor.execute(sql)# 关闭MySQL链接对象
conn.close()

结果

⑦ 课后作业

将我们写入到MySQL的数据，通过Python代码读取出来，再反向写出如图的文件

代码


# 导包
import jsonfrom pymysql import Connectionf = open("D:PythonSqlExa.txt", "w", encoding="UTF-8")# 构建MySQL链接对象
conn = Connection(host="localhost",port=3306,user="root",password="954926928lcl",autocommit=True
)# 获得游标对象
cursor = conn.cursor()
# 选择数据库
conn.select_db("py_sql")
# 选择表
cursor.execute("select * from orders")
# 存储数据
data_tuple = cursor.fetchall()f = open("E:\python.learning\pythonSql.txt", "w", encoding="UTF-8")data_dict = {}
for record in data_tuple:data_dict["data"] = str(record[0])data_dict["order_id"] = record[1]data_dict["money"] = int(record[2])data_dict["province"] = record[3]f.write(json.dumps(data_dict, ensure_ascii=False))f.write("\n")f.close()conn.close()

运行结果