Win7本地创建数据库文件:
注释:使用 Access 2000文件格式
在d:\share\Database1.mdb里创建表table1,其表结构如下:
######
例子一:
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)]
<class 'list'>
----------
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)]
<class 'list'>
----------
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可结束程序的执行)
######
例子六:
从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()
脚本运行中的效果:
######
例子七:
从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()
脚本运行中的效果:
######
例子八:
从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()
脚本运行中的效果:
######
例子九:
从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()
脚本运行中的效果:
相关文章:
Python3脚本管理Access
Windows下Python3使用绘图库Matplotlib
自定义类 |