当前位置： 首页 > article >正文

Python与MySQL

article 2024/10/26 12:25:58

一、Python 操作 MySQL 数据库软件

我们在上一篇文章《SQL入门》中使用了图形化工具DBeaver操作MySQL数据库软件，除了使用图形化工具以外，我们也可以使用编程语言来执行 SQL 从而操作数据库，可以在 Python 中，使用第三方库 pymysql 来完成对 MySQL 数据库的操作。

1.1第三方库 pymysql 的安装

使用快捷键Win+R打开运行窗口，然后输入"cmd"并按下回车键，调出CMD命令窗口，在命令窗口中输入“ pip install pymysql ”，按下回车键，等待安装成功即可，如下图所示，代表安装成功。

1.2创建到 MySQL 的数据库链接

from pymysql import Connection
#获取到MySQL数据库的链接对象
con=Connection(
    host='localhost',     #主机名（或IP地址）
    port=3306,            #端口，默认3306
    user='root',          #账户名
    password='123456'     #密码，在MySQL安装过程中自己设置的，密码因人而异
)
#打印MySQL数据库软件信息
print(con.get_server_info())
#关闭到数据库的链接
con.close()

如果能正常输出，就说明链接成功，输出的信息是MySQL 的版本，如下图所示：

1.3执行非查询性质SQL查询

from pymysql import Connection
#获取到MySQL数据库的链接对象
con=Connection(
    host='localhost',     #主机名（或IP地址）
    port=3306,            #端口，默认3306
    user='root',          #账户名
    password='123456'     #密码，在MySQL安装过程中自己设置的
)
#打印MySQL数据库软件信息
print(con.get_server_info())
#获取游标对象
cursor=con.cursor()
con.select_db('test')  #选择数据库
#使用游标对象，执行SQL语句，创建名为test_pymysql的表,表中有2列
cursor.execute('CREATE TABLE test_pymysql (id INT,info VARCHAR(255))') #代码中的SQL语句末尾不用加分号
#关闭到数据库的链接
con.close()

运行代码后，打开DBeaver，发现表test_pymysql已经被创建，两个列名也已经生成。

1.4执行查询性质SQL查询

为了便于演示，我们直接使用上一篇文章《SQL入门》中创建的表info，表info也在数据库test中。

from pymysql import Connection
#获取到MySQL数据库的链接对象
con=Connection(
    host='localhost',     #主机名（或IP地址）
    port=3306,            #端口，默认3306
    user='root',          #账户名
    password='123456'     #密码，在MySQL安装过程中自己设置的
)

#获取游标对象
cursor=con.cursor()
con.select_db('test')  #选择数据库
#使用游标对象，执行SQL语句，查询名为info的表中的全部内容
cursor.execute('SELECT * FROM info')
#获取查询结果
result:tuple=cursor.fetchall() #游标对象使用 fetchall() 方法，得到的是全部的查询结果，是一个元组,所以用元组来接收
for i in result:  #result这个元组的元素类型也是元组，即每一行查询结果是一个元组
    print(i)
#关闭到数据库的链接
con.close()

运行代码后得到输出：

(1, '张三', '男', 26)
(2, '李四', '女', 16)
(3, '王五', '男', 56)
(4, '小明', '男', 6)
(5, 'coco', '女', 36)

输出结果与表info中的内容一致。

1.5插入数据到 MySQL

from pymysql import Connection
#获取到MySQL数据库的链接对象
con=Connection(
    host='localhost',     #主机名（或IP地址）
    port=3306,            #端口，默认3306
    user='root',          #账户名
    password='123456'     #密码，在MySQL安装过程中自己设置的
)

cursor=con.cursor()    #获取游标对象
con.select_db('test')  # 选择数据库
cursor.execute("INSERT INTO info VALUES(06,'Mary','女',24)")#使用游标对象，执行SQL语句，插入数据
#关闭到数据库的链接
con.close()

运行代码后我们刷新（或者重新打开）表info，发现数据并没有成功插入。

这是因为pymysql 在执行数据插入或其它产生数据更改的 SQL 语句时，默认是需要提交更改的，即需要通过代码“确认”这种更改行为，在代码中通过链接对象 .commit() 即可确认此行为。

from pymysql import Connection
#获取到MySQL数据库的链接对象
con=Connection(
    host='localhost',     #主机名（或IP地址）
    port=3306,            #端口，默认3306
    user='root',          #账户名
    password='123456'     #密码，在MySQL安装过程中自己设置的
)

cursor=con.cursor()    #获取游标对象
con.select_db('test')  # 选择数据库
cursor.execute("INSERT INTO info VALUES(06,'Mary','女',24)")#使用游标对象，执行SQL语句，插入数据
con.commit()  #通过commit确认
#关闭到数据库的链接
con.close()

运行代码后我们刷新（或者重新打开）表info，发现数据成功插入。

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

from pymysql import Connection
#获取到MySQL数据库的链接对象
con=Connection(
    host='localhost',     #主机名（或IP地址）
    port=3306,            #端口，默认3306
    user='root',          #账户名
    password='123456'     #密码，在MySQL安装过程中自己设置的
    autocommit=True       #设置自动提交
)

cursor=con.cursor()    #获取游标对象
con.select_db('test')  # 选择数据库
cursor.execute("INSERT INTO info VALUES(07,'庄庄树','男',8)")#使用游标对象，执行SQL语句，插入数据
#关闭到数据库的链接
con.close()

运行代码后我们刷新（或者重新打开）表info，发现数据成功插入。

注意：pymysql 库在执行对数据库有修改操作的行为时，是需要通过链接对象的 commit成员方法来进行确认的，可以手动 commit 确认，也可以在构建链接对象的时设置自动 commit 的属性，只有确认的修改，才能生效。

二、案例—使用 Python 语言，读取数据，并将数据写入 MySQL

2.1案例需求

我们使用 Python 语言，读取文件“2011年1月销售数据”和文件“2011年2月销售数据JSON”中的数据，并将数据写入 MySQL 。

（文件链接: https://pan.baidu.com/s/1FWQhkToiJT_xZfYGfIWyvQ?pwd=a2hi 提取码: a2hi ）

文件数据格式如图所示：

2.2处理过程

将文件“2011年1月销售数据”和文件“2011年2月销售数据JSON”处理一下，步骤详见《综合案例—面向对象+数据可视化》中2.2读取数据，（链接：综合案例—面向对象+数据可视化-CSDN博客）

将文件中的数据处理成数组类型后，我们先在数据库test中建立2张表，分别用来存放1月和2月数据，然后往表中插入数据，代码如下：

from File_define import TextFileReader, JSONFileReader
from Record_define import Record
from pymysql import Connection

#读取并处理数据
text_file_reader=TextFileReader('E:/可视化案例数据/2011年1月销售数据.txt')
json_file_reader=JSONFileReader("E:/可视化案例数据/2011年2月销售数据JSON.txt")
jan_data:list[Record]=text_file_reader.read_data()
feb_data:list[Record]=json_file_reader.read_data()

#获取到MySQL数据库的链接对象
con=Connection(
    host='localhost',     #主机名（或IP地址）
    port=3306,            #端口，默认3306
    user='root',          #账户名
    password='123456'     #密码，在MySQL安装过程中自己设置的
    autocommit=True       #设置自动提交
)
#获取游标对象
cursor=con.cursor()
con.select_db('test')  #选择数据库
#使用游标对象，执行SQL语句，创建名为January_sales_data和February_sales_data的2张表,表结构相同，其中有4列
cursor.execute('CREATE TABLE January_sales_data (order_date DATE,order_id VARCHAR(255),money INT,province VARCHAR(20))')
cursor.execute('CREATE TABLE February_sales_data (order_date DATE,order_id VARCHAR(255),money INT,province VARCHAR(20))')
#往表中插入数据
for record in jan_data:
    sql=f"INSERT INTO January_sales_data VALUES('{record.date}','{record.order_id}',{record.money},'{record.province}')" #除了money是int类型，其他都要用单引号括起来
    cursor.execute(sql)  # 使用游标对象，执行SQL语句，插入数据
for record in feb_data:
    sql=f"INSERT INTO February_sales_data VALUES('{record.date}','{record.order_id}',{record.money},'{record.province}')" #除了money是int类型，其他都要用单引号括起来
    cursor.execute(sql)  # 使用游标对象，执行SQL语句，插入数据
#关闭到数据库的链接
con.close()

运行代码后打开DBeaver，2张表已经被创建，且按照预期一样存有数据。

其中模块“Record_define.py”的内容如下：

class Record:

    def __init__(self,date,order_id,money,province):  #运用__init__构造方法，便于给类对象赋值
        self.date=date          #订单日期
        self.order_id=order_id  #订单ID
        self.money=money        #订单金融
        self.province=province  #销售省份

    def __str__(self):   #运用__str__构造方法，便于控制类对象转换为字符串
        return f'{self.date},{self.order_id},{self.money},{self.province}'

其中模块“File_define.py”的内容如下：

import json
from Record_define import Record

# 先定义一个抽象类用来做顶层设计，确定有哪些功能需要实现
class FileReader:
    def read_data(self)->list[Record]:
        #读取文件的数据，将读到的每一条数据都转换为Record对象，将它们都封装到list内返回
        pass

#处理普通文本类型
class TextFileReader(FileReader):
    def __init__(self,path):
        self.path=path     #定义成员变量记录文件的路径
    #复写（实现抽象方法）父类的方法
    def read_data(self) ->list[Record]:
        #打开文件
        f=open(self.path,'r',encoding='UTF-8')
        #创建类型为Record类对象的列表以存储数据
        record_list:list[Record]=[]
        for line in f.readlines():
            line= line.strip()  # 消除读取到的每一行数据中的\n
            list=line.split(',')
            record=Record(list[0],list[1],int(list[2]),list[3]) #必须将类Record中第三个成员变量强制转换为int类型，否则后面无法正常累加销售额
            record_list.append(record)

        f.close()
        return record_list

#处理json类型
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():
            dict_line=json.loads(line)
            record=Record(dict_line['date'],dict_line['order_id'],int(dict_line['money']),dict_line['province'])#必须将类Record中第三个成员变量强制转换为int类型，否则后面无法正常累加销售额
            record_list.append(record)
        f.close()
        return record_list

http://www.kler.cn/news/365830.html

相关文章：

  • 语音识别——使用Vosk进行语音识别
  • 任务看板是什么？如何选择合适的任务看板工具？
  • 【SQL 数据库索引优化之 覆盖索引】原理深究剖析，拒绝假大空（VIP专属）
  • electron的常用api
  • react18中的函数组件底层渲染原理分析
  • 如何写一个视频编码器演示篇
  • 3.添加缓存和缓存更新策略
  • 前端拦截302重定向
  • flowable 去掉自带的登录权限
  • Scala的多态
  • 工作使用篇:如何在centos系统中安装anaconda
  • 【AI换装整合及教程】CatVTON：时尚与科技的完美融合
  • 【linux】服务器Ubuntu20.04安装cuda11.8教程
  • 从零到一：如何使用直播美颜SDK开发视频美颜平台
  • C# Window Form 基础
  • 初探Vue前端框架
  • 【等保测评】安全物理环境
  • 腾讯云DBA面试(一面)
  • 大语言模型训练方式探索(基于llma3模型)
  • ThinkPhp配置中间件解决跨域问题
  • C#WPF的XAML的语法详谈和特性
  • 【C/C++ Qt shared_ptr | make_shared | QSharedPointer 】绕圈圈
  • [Redis] Redis数据持久化
  • [项目详解][boost搜索引擎#2] 建立index | 安装分词工具cppjieba | 实现倒排索引
  • Element 的Table表格实现列合并（记得先排序、element-plus、列合并、线上已投入使用）
  • 同标签实现监听LocalStorage