Python金融大数据分析课件 第12章Python数据库编程

第12章Python数据库编程汇报人:时间:2025/01/01学习目标理解数据库的基本概念掌握Python对MySQL数据库的操作掌握Python对SQLite数据库的操作目录12.1Matplotlib概述12.2Python操作MySQL数据库12.3Python操作SQLite数据库

11.4数据库金融数据实战数据库概述12.1Matplotlib概述20世纪60年代后期之后，计算机技术发展越来越成熟。硬件中出现了大容量硬盘和集成度更高、处理速度更快的处理器，软件的发展也日新月异。在此背景下，传统的数据管理系统已经不能满足应用需求，当今的数据库系统应运而生。数据库(Database)是按照数据结构来组织、存储和管理数据的仓库，可视为电子化的文件柜，用户可以对文件中的数据运行增加、修改、查询、删除等操作。数据库中的数据有较小的冗余度(redundancy)、较高的数据独立性(dataindependency)和易扩展性(scalability)，并可以为多个用户共享。从发展的历史看，数据库是数据管理的高级阶段，它是由文件管理系统发展起来的。数据库管理系统（DataBaseManagementSystem，DBMS）指一种操作和管理数据库的软件，用于建立、使用和维护数据库，并对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户可以通过数据库管理系统访问数据库中的数据。数据库发展到现在，类型基本可以分为两大类，关系型数据库和非关系型数据库。本章主要介绍Python对关系型数据库的访问和操作。Matplotlib概述SQL语言是高级的非过程化编程语言，允许用户在高层数据结构上工作。它不要求用户指定对数据的存放方法，也不需要用户了解具体的数据存放方式，所以具有完全不同底层结构的不同数据库系统可以使用相同的结构化查询语言作为数据输入与管理的接口。SQL语言语句可以嵌套，这使他具有极大的灵活性和强大的功能。可以把SQL分为两个部分：数据数据定义语言和数据操作语言。数据定义语言(DataDefinitionLanguage,DDL)是SQL语言集中负责数据结构定义与数据库对象定义的语言，实现对数据库内部的对象进行创建，删除，修改等的操作。DDL包括的主要语句及功能如表12-1所示。Matplotlib概述

数据操纵语言DML(DataManipulationLanguage)，用户通过它可以实现对数据库的基本操作。例如，对表中数据的查询、插入、删除和修改。Python操作MySQL数据库12.2Python操作MySQL数据库MySQL是一个多用户、多线程的关系型数据库管理系统，使用最常用的数据库管理语言（SQL）进行数据库管理，具有功能强大、跨平台、运行速度快、支持面向对象、安全性高、成本低和支持各类编程语言等优点。PyMySQL是Python中操作MySQL的模块，安装方式和其他第三方库一样，进入Python安装目录找到Scripts子目录，执行pip安装命令：pipinstallpymysql成功安装PyMySQL后，就可以使用“importpymysql”引入这个模块访问和操作MySQL数据库。PyMySQL遵循DBAPI2.0协议，其操作数据库的基本流程是：①调用connect方法打开数据库连接，该方法返回数据库连接对象。②通过数据库连接对象打开游标。③使用游标执行SQL语句（包括DDL和DML）。如果执行的是查询语句，则处理查询数据。④关闭游标。⑤关闭数据库连接。为了方便对MySQL数据库的操作，本章采用Navicat进行辅助。12.2.1访问数据库在访问并处理数据库中的数据之前，必须建立与MySQL数据库服务器的连接。PyMySQL提供了connect函数进行数据库连接，其语法格式为：connect(host,port,user,password,db,charset,connect_timeout,use_unicode)示例代码如下：importpymysql #连接数据库 connect=pymysql.Connect(host='localhost',#主机名，本地主机port=3306,#端口号user='root',#数据库用户名，root为管理员用户passwd='123456',#密码，建议使用安全度更高的密码db='test',#使用“test”数据库，为MySQL默认自带数据库charset='utf8'#编码格式)print("连接成功")connect.close()#关闭数据库连接print("关闭连接")12.2.2游标游标(Cursor)是处理数据的一种方法，可以指向结果集中的某一行，通过移动游标，用户可以遍历整个结果集，逐条处理数据。游标在定义以后存在两种状态：关闭和打开。当游标关闭时，其查询结果集不存在；只有当游标打开时，才能按行读取或修改结果集中的数据。12.2.3表的创建和删除完成数据库访问，获得游标对象后，就可以在数据库中创建表格。表格的创建需要使用SQL的“CREATETABLE”语句，删除使用“DROPTABLE”语句。【示例12-1】在“test”数据库中创建“stock”表格，字段包括id，stock_name（股票名称）、stock_area（地区）、stock_pe（市盈率）、stock_totals（总股本，亿）、stock_totalAssets（总资产，万）、stock_timeToMarket（上市日期）、stock_holders（股东人数）。importpymysql #连接数据库 connect=pymysql.Connect(host='localhost',#主机名，本地主机port=3306,#端口号user='root',#数据库用户名，root为管理员用户passwd='123456',#密码，建议使用安全度更高的密码db='test',#使用“test”数据库，为MySQL默认自带数据库charset='utf8'#编码格式)

12.2.3表的创建和删除cursor=connect.cursor()#获取游标对象cursor.execute("DROPTABLEIFEXISTSstock")sql="""CREATETABLE`stock`(`id`int(10)NOTNULL,`stock_name`char(20)NOTNULL,`stock_area`char(20)DEFAULTNULL,`stock_pe`floatDEFAULTNULL,`stock_totals`floatDEFAULTNULL,`stock_totalAssets`floatDEFAULTNULL,`stock_timeToMarket`dateDEFAULTNULL,`stock_holders`intDEFAULTNULL,PRIMARYKEY(`id`))ENGINE=InnoDBDEFAULTCHARSET=utf8;"""cursor.execute(sql)print("stock表创建成功")cursor.close()connect.close()#关闭数据库连接12.2.4表数据操作完成数据库表格的创建之后，可以对表格中的数据进行操作。表数据操作需要使用SQL的DML语句，包括INSERT、SELECT、UPDATE、DELETE。【示例12-2】

向“stock”表插入一条数据。importpymysql #连接数据库 connect=pymysql.Connect(host='localhost',#主机名，本地主机port=3306,#端口号user='root',#数据库用户名，root为管理员用户passwd='123456',#密码，建议使用安全度更高的密码db='test',#使用“test”数据库，为MySQL默认自带数据库charset='utf8'#编码格式)cursor=connect.cursor()#获取游标对象sql="INSERTINTO`stock`VALUES('1','牧原股份','河南','114.69',\'20.85','2764110','1998-03-03','5097');"12.2.4表数据操作cursor.execute(sql)mit()#提交到数据库执行print("数据插入成功")cursor.close()connect.close()#关闭数据库连接需要注意的是，同一条数据只能执行一次插入操作，第二次执行会因为主键已经存在而报错。对表数据的操作，必须执行commit()函数才能提交生效。为了后续查询代码的执行有更好的效果，这里继续插入3条测试数据。分别是（2,'N宇晶','湖南',22.99,1.0,57487.7,20181129,48968)，(3,'N海容','山东',22.25,0.8,120084.0,20181129,20203))，((4,'超华科技','广东',80.53,9.32,269044.0,200993,69214),)。12.2.4表数据操作【示例12-3】查询“stock”表中的数据。importpymysql #连接数据库 connect=pymysql.Connect(host='localhost',#主机名，本地主机port=3306,#端口号user='root',#数据库用户名，root为管理员用户passwd='123456',#密码，建议使用安全度更高的密码db='test',#使用“test”数据库，为MySQL默认自带数据库charset='utf8'#编码格式)cursor=connect.cursor()#获取游标对象sql="select*fromstock;"cursor.execute(sql)print(cursor.fetchone())#获取一条数据12.2.4表数据操作print(cursor.fetchmany(2))#获取两条数据print(cursor.fetchall())#获取剩余的全部数据mit()#提交到数据库执行cursor.close()connect.close()#关闭数据库连接需要注意的是，fetchone获取了查询结果集中的第一条数据，fetchmany(2)获取了后续的两条数据，fetchall获取了剩余的全部数据。因为只有4条测试数据，所以fetchall()只能获取一条数据。如果只执行fetchall()，则会一次性获取全部数据。【示例12-4】修改“stock”表中id=1的数据，将其股东人数改为8769人，总股本在原有基础上增加10亿。12.2.4表数据操作importpymysql #连接数据库 connect=pymysql.Connect(host='localhost',#主机名，本地主机port=3306,#端口号user='root',#数据库用户名，root为管理员用户passwd='123456',#密码，建议使用安全度更高的密码db='test',#使用“test”数据库，为MySQL默认自带数据库charset='utf8'#编码格式)cursor=connect.cursor()#获取游标对象sql="updatestocksetstock_totals=stock_totals+10,\stock_holders=8769whereid=1;"cursor.execute(sql)sql2="select*fromstockwhereid=1;"12.2.4表数据操作cursor.execute(sql2)mit()#提交到数据库执行print(cursor.fetchall())cursor.close()connect.close()#关闭数据库连接由结果可知牧原股份的股东人数改为了8769，总股本增加了10亿。【示例12-5】

删除“stock”表中股票名字开头为N，股东人数超过3000人，总股本小于30亿的股票。importpymysql #连接数据库 connect=pymysql.Connect(host='localhost',#主机名，本地主机port=3306,#端口号user='root',#数据库用户名，root为管理员用户passwd='123456',#密码，建议使用安全度更高的密码12.2.4表数据操作db='test',#使用“test”数据库，为MySQL默认自带数据库charset='utf8'#编码格式)cursor=connect.cursor()#获取游标对象sql="deletefromstockwherestock_namelike'n%'andstock_totals<30\andstock_holders>3000;"cursor.execute(sql)sql2="select*fromstock;"cursor.execute(sql2)print(cursor.fetchall())mit()#提交到数据库执行cursor.close()connect.close()#关闭数据库连接12.2.4表数据操作【示例12-6】向“stock”表中写入2条数据，并查询结果。importpymysql #连接数据库 connect=pymysql.Connect(host='localhost',#主机名，本地主机port=3306,#端口号user='root',#数据库用户名，root为管理员用户passwd='123456',#密码，建议使用安全度更高的密码db='test',#使用“test”数据库，为MySQL默认自带数据库charset='utf8'#编码格式)cursor=connect.cursor()#获取游标对象sql="INSERTINTO`stock`VALUES('%s','%s','%s','%s','%s','%s','%s','%s');"data1=(2,'N宇晶','湖南',22.99,1.0,57487.7,20181129,48968)data2=(3,'N海容','山东',22.25,0.8,120084.0,20181129,20203)12.2.4表数据操作cursor.execute(sql%data1)cursor.execute(sql%data2)sql2="select*fromstock;"cursor.execute(sql2)print(cursor.fetchall())mit()#提交到数据库执行print("数据插入成功")cursor.close()connect.close()#关闭数据库连接Python操作SQLite数据库12.3SQLite数据库SQLite数据库是一款非常小巧的嵌入式开源数据库软件，没有独立的维护进程，该数据库本身就是一个文件，将整个数据库（包括定义、表、索引以及数据本身）作为一个单独的、可跨平台使用的文件存储在主机中。因此，SQLite数据库实现了自给自足、无服务器、零配置，并支持事务。与关系数据库进行交互的标准SQLite命令类似于SQL，包括CREATE、SELECT、INSERT、UPDATE、DELETE和DROP。Python内置了SQLite3，所以在Python中使用SQLite数据库，不需要安装任何模块，可以直接使用。导入如下代码：importsqlite3Python访问和操作SQLite3数据库方式和PyMySQL类似，具体函数和说明可以参考PyMySQL。因为两个数据库性质的不同，所以体现在使用方式上最大的不同就是数据库的连接。SQLite3在调用connect函数连接数据库时，只需要指定库名称，而不需要像PyMySQL那样指定数据库服务器地址、端口、账号、密码等信息。如果指定的数据库存在就直接打开这个数据库，如果不存在就新创建一个再打开。默认是在当前文件夹下创建新的数据库，也可以指定具体位置。SQLite数据库【示例12-7】使用SQLite3创建学生成绩表，包括ID（学号）、name（姓名）、course（课程名称）、score（分数）。importsqlite3connect=sqlite3.connect('test.db')cursor=connnect.cursor()cursor.execute("DROPTABLEIFEXISTSxscj")sql="""CREATETABLE`xscj`(`ID`char(14)NOTNULL,`name`char(8)NOTNULL,`course`varchar(30)NOTNULL,`score`int(11)DEFAULTNULL,PRIMARYKEY(`course`,`ID`))"""cursor.execute(sql)print("xscj表创建成功")cursor.close()connect.close()#关闭数据库连接SQLite数据库【示例12-8】

对学生表中的数据进行增加、修改、删除、查询操作。importsqlite3importsqlite3connect=sqlite3.connect('test.db')cursor=connect.cursor()sql="INSERTINTO`xscj`VALUES('20221506210301','张三','Python','80');"sql2="INSERTINTO`xscj`VALUES(?,?,?,?);"#使用问号代表需要参数#第一种数据插入方法cursor.execute(sql)#第二种数据插入方法，使用参数cursor.execute(sql2,('20221506210302','李四','Python','96'))data=[('20221506210303','王小东','计算机网络','68'),\('20221506210304','张涵','计算机操作系统','77'),\('20221506210305','李琴琴','C语言','81'),\

SQLite数据库('20221506210306','赵钱','数据结构','56'),\('20221506210307','唐语宁','数据结构','79')]#第三种数据插入方法，执行多条SQL语句connect.executemany(sql2,data)sql3="select*fromxscj"cursor.execute(sql3)print("------------数据插入成功-----------")print(cursor.fetchall())#修改数据sql4="updatexscjsetscore=?whereid=?;"cursor.execute(sql4,('99','20221506210301'))cursor.execute(sql3)print("------------数据修改成功-----------")print(cursor.fetchone())SQLite数据库#删除数据sql5="deletefromxscjwherescore>?;"cursor.execute(sql5,('95',))cursor.execute(sql3)print("------------数据删除成功-----------")print(cursor.fetchall())mit()#提交cursor.close()connect.close()#关闭数据库连接

数据库金融数据实战12.4金融数据可视化实训实训目的（1）掌握数据库的基本概念。（2）掌握PyMySQL创建数据库表格的基本方法。（3）掌握PyMySQL对数据库表格中的数据的增加、修改、删除、查询。（4）掌握PyMySQL+文件读取+绘图的综合应用。实训要求读取文件stock.csv，使用PyMySQL将数据写入数据库，并实现数据可视化。数据库金融数据实战

任务1：读取stock.csv文件，将前2000条数据中的id、股票名称、地区、市盈率、总股本、总资产上市日期、股东人数写入stock表。importpymysqlimportnumpyasnpimportpandasaspd#读取数据data=pd.read_csv("stock.csv")stock_name=data['name']stock_area=data['area']stock_pe=data['pe']stock_totals=data['totals']stock_totalAssets=data['totalAssets']stock_timeToMarket=data['timeToMarket']stock_holders=data['holders']connect=pymysql.Connect(数据库金融数据实战host='localhost',#主机名，本地主机port=3306,#端口号user='root',#数据库用户名，root为管理员用户passwd='123456',#密码，建议使用安全度更高的密码db='test',#使用“test”数据库，为MySQL默认自带数据库charset='utf8'#编码格式)cursor=connect.cursor()#获取游标对象sql="INSERTINTO`stock`VALUES('%s','%s','%s','%s','%s','%s','%s','%s');"foriinrange(3567):cursor.execute(sql%(i+1,stock_name[i],stock_area[i],stock_pe[i],\stock_totals[i],stock_totalAssets[i],\stock_timeToMarket[i],stock_holders[i]))mit()#提交到数据库执行print("数据插入成功,共插入",i+1,"条数据")数据库金融数据实战sql2="select*fromstockwhereid<11;"cursor.execute(sql2)print("前10条数据是：")foriinrange(10):print(cursor.fetchone())cursor.close()connect.close()任务2：读取数据库stock表中的数据，找出总资产排名前50的企业，绘制总资产箱线图。importpymysqlimportnumpyasnpimportpandasaspdimportmatplotlib.pyplotaspltconnect=pymysql.Connec