blog.zhuohua.store - Powered by Discuz! Board

标题: Windows下Python3管理Access+绘图库Matplotlib [打印本页]

作者: admin 时间: 2020-10-29 09:21 标题: Windows下Python3管理Access+绘图库Matplotlib

Win7本地创建数据库文件：图片1.png

注释：使用 Access 2000文件格式图片3.png

在d:\share\Database1.mdb里创建表table1，其表结构如下：图片4.png

###### 例子一： Win7本地测试与数据库的连接的脚本：（C:\Users\jacky\Desktop\xx.py） #coding=utf-8 import pyodbc def db_connect(): DBfile = "d:/share/Database1.mdb" db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";") #这是 Access 2000文件格式的连接方式 cursor = db.cursor() Sql_1 = "select * from table1;" #SQL语句 cursor.execute(Sql_1) #执行SQL语句 print("数据库连接成功。") db.commit() #把执行任务提交到数据库 db.close() def func_main(): try: db_connect() except Exception as e: print(f"数据库连接失败，原因: {e}") if __name__ == "__main__": func_main() 运行脚本的效果： C:\Users\jacky\Desktop>python xx.py 数据库连接成功。 C:\Users\jacky\Desktop> ###### 例子二： Win7本地一次性插入多条记录的脚本： #coding=utf-8 import pyodbc def insert_record(): global db DBfile = "d:/share/Database1.mdb" db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";") cursor = db.cursor() Key_1 = '苹果' Key_2 = 20 Key_3 = 40 Key_4 = 50 Key_5 = 30 Key_6 = 40 Key_7 = 50 Key_8 = 50 #字段ID为主键、标识符列，会自动增长，不用写 Sql_1 = f"INSERT INTO table1(水果名称,星期一,星期二,星期三,星期四,星期五,星期六,星期日) VALUES('{Key_1}',{Key_2},{Key_3},{Key_4},{Key_5},{Key_6},{Key_7},{Key_8})" cursor.execute(Sql_1) Key_1 = '香蕉' Key_2 = 10 Key_3 = 45 Key_4 = 55 Key_5 = 20 Key_6 = 40 Key_7 = 50 Key_8 = 30 Sql_2 = f"INSERT INTO table1(水果名称,星期一,星期二,星期三,星期四,星期五,星期六,星期日) VALUES('{Key_1}',{Key_2},{Key_3},{Key_4},{Key_5},{Key_6},{Key_7},{Key_8})" cursor.execute(Sql_2) db.commit() #把执行任务提交到数据库；必须是所有SQL语句都执行成功才会提交 db.close() def func_main(): try: insert_record() except Exception as e: print(f"插入记录失败，原因: {e}") db.rollback() #如果发生错误就回滚 else: print("插入记录成功。") if __name__ == "__main__": func_main() 运行脚本的效果： C:\Users\jacky\Desktop>python xx.py 插入记录成功。 C:\Users\jacky\Desktop> ###### 例子三： Win7本地输出表table1的所有记录的脚本： #coding=utf-8 import pyodbc def query_data(): DBfile = DBfile = "d:\\share\\Database1.mdb" db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";") cursor = db.cursor() Sql_1 = "select * from table1" cursor.execute(Sql_1) Result_1 = cursor.fetchall() #使用fetchall可以获取多个元素 print(Result_1) print(type(Result_1)) print("-" * 10) for Key_1 in Result_1: #自定义输出格式 ID = Key_1[0] Name = Key_1[1] Mon = Key_1[2] Tue = Key_1[3] Wed = Key_1[4] Thu = Key_1[5] Fri = Key_1[6] Sat = Key_1[7] Sun = Key_1[8] print(f"ID={ID},水果名称={Name},星期一={Mon},星期二={Tue},星期三={Wed},星期四={Thu},星期五={Fri},星期六={Sat},星期日={Sun}") db.commit() #把执行任务提交到数据库 db.close() def func_main(): try: query_data() except Exception as e: print(f"输出失败，原因: {e}") if __name__ == "__main__": func_main() 运行脚本的效果： C:\Users\jacky\Desktop>python xx.py [(1, '苹果', 20, 40, 50, 30, 40, 50, 50), (2, '香蕉', 10, 45, 55, 20, 40, 50, 30)] ---------- ID=1,水果名称=苹果,星期一=20,星期二=40,星期三=50,星期四=30,星期五=40,星期六=50,星期日=50 ID=2,水果名称=香蕉,星期一=10,星期二=45,星期三=55,星期四=20,星期五=40,星期六=50,星期日=30 C:\Users\jacky\Desktop> ###### 例子四： Win7本地输出数据库中某个表里符合指定条件的记录的脚本： #coding=utf-8 import pyodbc def func1(): DBfile = DBfile = "d:/share/Database1.mdb" db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";") cursor = db.cursor() Sql_1 = "select * from table1 where 水果名称 = '苹果'" cursor.execute(Sql_1) Result_1 = cursor.fetchall() print(Result_1) print(type(Result_1)) print("-" * 10) ID = Result_1[0][0] Name = Result_1[0][1] Mon = Result_1[0][2] Tue = Result_1[0][3] Wed = Result_1[0][4] Thu = Result_1[0][5] Fri = Result_1[0][6] Sat = Result_1[0][7] Sun = Result_1[0][8] print(ID) print(Name) print(Mon);print(Tue) print(Wed);print(Fri) print(Sat);print(Sun) db.commit() #把执行任务提交到数据库 db.close() if __name__ == '__main__': func1() 运行脚本的效果： C:\Users\jacky\Desktop>python xx.py [(1, '苹果', 20, 40, 50, 30, 40, 50, 50)] ---------- 1 苹果 20 40 50 40 50 50 C:\Users\jacky\Desktop> ###### 例子五：从Access的表table1中读取数据到图形里进行显示：（线形图） #coding=utf-8 import pyodbc import matplotlib.pyplot as plt import numpy as np def func1(): DBfile = DBfile = "d:\\share\\Database1.mdb" db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";") cursor = db.cursor() Sql_1 = "select * from table1 where 水果名称 = '苹果'" cursor.execute(Sql_1) Result_1 = cursor.fetchall() ID = Result_1[0][0] Name = Result_1[0][1] Mon = Result_1[0][2] Tue = Result_1[0][3] Wed = Result_1[0][4] Thu = Result_1[0][5] Fri = Result_1[0][6] Sat = Result_1[0][7] Sun = Result_1[0][8] db.commit() #把执行任务提交到数据库 db.close() func2(ID,Name,Mon,Tue,Wed,Thu,Fri,Sat,Sun) def func2(ID,Name,Mon,Tue,Wed,Thu,Fri,Sat,Sun): plt.rcParams['font.sans-serif'] = ['SimHei'] plt.rcParams['axes.unicode_minus'] = False plt.title(f"{Name}一周销量情况") #图形标题 plt.xlabel('时间') #x轴名称 plt.ylabel('销量（公斤）') #y轴名称 A1 = "星期一" A2 = "星期二" A3 = "星期三" A4 = "星期四" A5 = "星期五" A6 = "星期六" A7 = "星期日" x1 = [A1,A2,A3,A4,A5,A6,A7] #x轴的点的值 B1 = Mon B2 = Tue B3 = Wed B4 = Thu B5 = Fri B6 = Sat B7 = Sun y1 = [B1,B2,B3,B4,B5,B6,B7] #y轴的点的值 plt.plot(x1,y1,label=Name,color='blue',linewidth=1.0,linestyle='--') plt.legend() #显示图例（label） plt.grid(True,linestyle=':',color='red',alpha=0.6) #显示网格线 plt.show() #显示图形 if __name__ == '__main__': func1() 脚本运行中的效果：（组合键Ctrl+c可结束程序的执行）图片5.png

###### 例子六：从Access的表table1中读取数据到图形里进行显示：（饼形图） #coding=utf-8 import pyodbc import matplotlib.pyplot as plt import numpy as np def func1(): DBfile = DBfile = "d:\\share\\Database1.mdb" db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";") cursor = db.cursor() Sql_1 = "select * from table1 where 水果名称 = '苹果'" cursor.execute(Sql_1) Result_1 = cursor.fetchall() Name = Result_1[0][1] Mon = Result_1[0][2] Tue = Result_1[0][3] Wed = Result_1[0][4] Thu = Result_1[0][5] Fri = Result_1[0][6] Sat = Result_1[0][7] Sun = Result_1[0][8] db.commit() #把执行任务提交到数据库 db.close() func2(Name,Mon,Tue,Wed,Thu,Fri,Sat,Sun) def func2(Name,Mon,Tue,Wed,Thu,Fri,Sat,Sun): plt.rcParams['font.sans-serif'] = ['SimHei'] plt.rcParams['axes.unicode_minus'] = False plt.figure(figsize=(9,5)) #自定义窗体的宽和高 A1 = "星期一" A2 = "星期二" A3 = "星期三" A4 = "星期四" A5 = "星期五" A6 = "星期六" A7 = "星期日" Labels = [A1,A2,A3,A4,A5,A6,A7] B1 = Mon B2 = Tue B3 = Wed B4 = Thu B5 = Fri B6 = Sat B7 = Sun Data = [B1,B2,B3,B4,B5,B6,B7] plt.title(f"{Name}一周销量情况（百分比）") #图形标题 plt.pie(Data,labels=Labels,autopct='%1.1f%%') #百分比保留一位小数 plt.legend() #显示图例（label） plt.axis('equal') #设置x、y轴的尺寸相等，使饼形图为正圆形 plt.show() #显示图形 if __name__ == '__main__': func1() 脚本运行中的效果：图片7.png

###### 例子七：从Access的表table1中读取数据到图形里进行显示：（竖向条形图） #coding=utf-8 import pyodbc import matplotlib.pyplot as plt import numpy as np def func1(): DBfile = DBfile = "d:\\share\\Database1.mdb" db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";") cursor = db.cursor() Sql_1 = "select * from table1 where 水果名称 = '苹果'" cursor.execute(Sql_1) Result_1 = cursor.fetchall() Name = Result_1[0][1] Mon = Result_1[0][2] Tue = Result_1[0][3] Wed = Result_1[0][4] Thu = Result_1[0][5] Fri = Result_1[0][6] Sat = Result_1[0][7] Sun = Result_1[0][8] db.commit() #把执行任务提交到数据库 db.close() func2(Name,Mon,Tue,Wed,Thu,Fri,Sat,Sun) def func2(Name,Mon,Tue,Wed,Thu,Fri,Sat,Sun): plt.rcParams['font.sans-serif'] = ['SimHei'] plt.rcParams['axes.unicode_minus'] = False plt.title(f"{Name}一周销量情况") #图形标题 plt.xlabel('日期') #x轴名称 plt.ylabel('销量（公斤）') #y轴名称 N = 7 X = np.arange(N) Color=['red','peru'] A1 = "星期一" A2 = "星期二" A3 = "星期三" A4 = "星期四" A5 = "星期五" A6 = "星期六" A7 = "星期日" Labels = [A1,A2,A3,A4,A5,A6,A7] B1 = Mon B2 = Tue B3 = Wed B4 = Thu B5 = Fri B6 = Sat B7 = Sun Data = [B1,B2,B3,B4,B5,B6,B7] plt.bar(X,Data,alpha=0.8,color=Color,tick_label=Labels) plt.grid(True,linestyle=':',color='red',alpha=0.6) #显示网格线 plt.show() #显示图形 if __name__ == '__main__': func1() 脚本运行中的效果：图片8.png

###### 例子八：从Access的表table1中读取数据到图形里进行显示：（并列竖向条形图） #coding=utf-8 import pyodbc import matplotlib.pyplot as plt import numpy as np def func1(): global Name_1,Mon_1,Tue_1,Wed_1,Thu_1,Fri_1,Sat_1,Sun_1 DBfile = DBfile = "d:\\share\\Database1.mdb" db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";") cursor = db.cursor() Sql_1 = "select * from table1 where 水果名称 = '苹果'" cursor.execute(Sql_1) Result_1 = cursor.fetchall() Name_1 = Result_1[0][1] Mon_1 = Result_1[0][2] Tue_1 = Result_1[0][3] Wed_1 = Result_1[0][4] Thu_1 = Result_1[0][5] Fri_1 = Result_1[0][6] Sat_1 = Result_1[0][7] Sun_1 = Result_1[0][8] db.commit() #把执行任务提交到数据库 db.close() def func2(): global Name_2,Mon_2,Tue_2,Wed_2,Thu_2,Fri_2,Sat_2,Sun_2 DBfile = DBfile = "d:\\share\\Database1.mdb" db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";") cursor = db.cursor() Sql_1 = "select * from table1 where 水果名称 = '香蕉'" cursor.execute(Sql_1) Result_1 = cursor.fetchall() Name_2 = Result_1[0][1] Mon_2 = Result_1[0][2] Tue_2 = Result_1[0][3] Wed_2 = Result_1[0][4] Thu_2 = Result_1[0][5] Fri_2 = Result_1[0][6] Sat_2 = Result_1[0][7] Sun_2 = Result_1[0][8] db.commit() #把执行任务提交到数据库 db.close() def func3(): plt.rcParams['font.sans-serif'] = ['SimHei'] plt.rcParams['axes.unicode_minus'] = False plt.title(f"{Name_1} & {Name_2} 一周销量情况") #图形标题 plt.xlabel('日期') #x轴名称 plt.ylabel('销量（公斤）') #y轴名称 N = 7 X = np.arange(N) A1 = "星期一" A2 = "星期二" A3 = "星期三" A4 = "星期四" A5 = "星期五" A6 = "星期六" A7 = "星期日" Labels = [A1,A2,A3,A4,A5,A6,A7] Apple = [Mon_1,Tue_1,Wed_1,Thu_1,Fri_1,Sat_1,Sun_1] #苹果的y轴的点的值 Banana = [Mon_2,Tue_2,Wed_2,Thu_2,Fri_2,Sat_2,Sun_2] #香蕉的y轴的点的值 bar_width = 0.3 #条形的宽度 plt.bar(X, Apple, bar_width,label='苹果',color='red') plt.bar(X + bar_width, Banana, bar_width, label='香蕉',tick_label=Labels,color='blue') plt.legend() #显示图例（label） plt.grid(True,linestyle=':',color='red',alpha=0.6) #显示网格线 plt.show() #显示图形 if __name__ == '__main__': func1() func2() func3() 脚本运行中的效果：图片9.png

###### 例子九：从Access的表table1中读取数据到图形里进行显示：（并列竖向条形图） #coding=utf-8 import pyodbc import matplotlib.pyplot as plt import numpy as np class class1(): def func1(self): DBfile = DBfile = "d:/share/Database1.mdb" db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";") cursor = db.cursor() Sql_1 = "select * from table1 where 水果名称 = '苹果'" cursor.execute(Sql_1) Result_1 = cursor.fetchall() self.Name_1 = Result_1[0][1] self.Mon_1 = Result_1[0][2] self.Tue_1 = Result_1[0][3] self.Wed_1 = Result_1[0][4] self.Thu_1 = Result_1[0][5] self.Fri_1 = Result_1[0][6] self.Sat_1 = Result_1[0][7] self.Sun_1 = Result_1[0][8] db.commit() #把执行任务提交到数据库 db.close() self.func2() def func2(self): DBfile = DBfile = "d:\\share\\Database1.mdb" db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";") cursor = db.cursor() Sql_1 = "select * from table1 where 水果名称 = '香蕉'" cursor.execute(Sql_1) Result_1 = cursor.fetchall() self.Name_2 = Result_1[0][1] self.Mon_2 = Result_1[0][2] self.Tue_2 = Result_1[0][3] self.Wed_2 = Result_1[0][4] self.Thu_2 = Result_1[0][5] self.Fri_2 = Result_1[0][6] self.Sat_2 = Result_1[0][7] self.Sun_2 = Result_1[0][8] db.commit() #把执行任务提交到数据库 db.close() self.func3() def func3(self): plt.rcParams['font.sans-serif'] = ['SimHei'] plt.rcParams['axes.unicode_minus'] = False plt.title(f"{self.Name_1} & {self.Name_2} 一周销量情况") #图形标题 ##plt.xlabel('日期') #x轴名称 plt.ylabel('销量（公斤）') #y轴名称 N = 7 X = np.arange(N) A1 = "星期一" A2 = "星期二" A3 = "星期三" A4 = "星期四" A5 = "星期五" A6 = "星期六" A7 = "星期日" Labels = [A1,A2,A3,A4,A5,A6,A7] Apple = [self.Mon_1,self.Tue_1,self.Wed_1,self.Thu_1,self.Fri_1,self.Sat_1,self.Sun_1] #苹果的y轴的点的值 Banana = [self.Mon_2,self.Tue_2,self.Wed_2,self.Thu_2,self.Fri_2,self.Sat_2,self.Sun_2] #香蕉的y轴的点的值 bar_width = 0.3 #条形的宽度 plt.bar(X, Apple, bar_width,label='苹果',color='red') plt.bar(X + bar_width, Banana, bar_width, label='香蕉',tick_label=Labels,color='blue') plt.legend() #显示图例（label） plt.grid(True,linestyle=':',color='blue',alpha=0.6) #显示网格线 plt.show() #显示图形 if __name__ == '__main__': myclass = class1() myclass.func1() 脚本运行中的效果：图片10.png

图片附件: 图片1.png (2022-8-3 21:46, 21.19 KB) / 下载次数 54
http://blog.zhuohua.store/attachment.php?aid=20163&k=a7877de18660027ec461e057497b5f8c&t=1714256796&sid=y3qMqP

图片附件: 图片2.png (2022-8-3 21:46, 109.83 KB) / 下载次数 77
http://blog.zhuohua.store/attachment.php?aid=20164&k=9b109f427964e657e282f5c5c7facd09&t=1714256796&sid=y3qMqP

图片附件: 图片3.png (2022-8-3 21:46, 9.78 KB) / 下载次数 73
http://blog.zhuohua.store/attachment.php?aid=20165&k=225a1558c88981f0be62dd993a00c41e&t=1714256796&sid=y3qMqP

图片附件: 图片4.png (2022-8-3 21:46, 32.79 KB) / 下载次数 64
http://blog.zhuohua.store/attachment.php?aid=20166&k=92788f45386df13a9535a2be63cc5b7d&t=1714256796&sid=y3qMqP

图片附件: 图片5.png (2022-8-3 21:54, 11.49 KB) / 下载次数 61
http://blog.zhuohua.store/attachment.php?aid=20167&k=00d36dab48769b541f10781207b987f6&t=1714256796&sid=y3qMqP

图片附件: 图片6.png (2022-8-3 21:54, 147.76 KB) / 下载次数 62
http://blog.zhuohua.store/attachment.php?aid=20168&k=4ad366aeea0c2857409ffa9ad2b0f3c0&t=1714256796&sid=y3qMqP

图片附件: 图片7.png (2022-8-3 21:54, 77.87 KB) / 下载次数 56
http://blog.zhuohua.store/attachment.php?aid=20169&k=d2462779456001332abf1aac40825440&t=1714256796&sid=y3qMqP

图片附件: 图片8.png (2022-8-3 21:56, 123.3 KB) / 下载次数 35
http://blog.zhuohua.store/attachment.php?aid=20170&k=d32fe75f75f9f67bf97baa8ebf5d0116&t=1714256796&sid=y3qMqP

图片附件: 图片9.png (2022-8-3 21:57, 133.38 KB) / 下载次数 42
http://blog.zhuohua.store/attachment.php?aid=20171&k=7bc1db5832bf43febbe7f8ceb1d0694b&t=1714256796&sid=y3qMqP

图片附件: 图片10.png (2022-8-3 21:59, 112.75 KB) / 下载次数 35
http://blog.zhuohua.store/attachment.php?aid=20172&k=a5d8ebb26667fe36a1366e7cca6e8a78&t=1714256796&sid=y3qMqP

欢迎光临 blog.zhuohua.store (http://blog.zhuohua.store/)