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,    # 端口，默认3306
    user = '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,                 # 端口，默认3306
    user = '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,                 # 端口，默认3306
    user = '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,                 # 端口，默认3306
    user = '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,                 # 端口，默认3306
    user = '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    # 订单ID
        self.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]:
        # 方法内部使用成员变量用self
        f = open(self.path, "r", encoding="UTF-8")
        # 类型注解
        record_list: list[Record] = []
        for line in f.readlines():
            # 消除读取到的每一行数据中的\n
            line = 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 = path

    def 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_list


if __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 json

from pymysql import Connection

f = 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()

运行结果