Win7本地创建数据库文件:
注释:使用 Access 2000文件格式
在d:\Database1.mdb里创建表table1,其表结构如下:
Access的字段类型:
- 自动编号 数字(长整型)
- 文本 字符型 最多可以存储255个任意字符,也可以自定义字段大小
- 备注 字符型 可以存储63,999个任意字符,无法自定义字段大小
- 日期/时间(常规日期) 日期时间型 格式为 yyyy/m/d h:mm:ss
- 数字(字节) 数值型 可以存储 0 到 255 之间的数字 (不包括小数)
- 数字(整型) 数值型 可以存储 –32,768 到 32,767 之间的数字 (不包括小数)
- 数字(长整型) 数值型 可以存储 –2,147,483,648 到 2,147,483,647 之间的数字 (不包括小数)
- 数字(单精度型) 数值型 可以存储 –3.402823E38 到 –1.401298E–45 之间的负数和 1.401298E–45 到 3.402823E38 之间的正数 (包括小数)
- 数字(双精度型) 数值型 可以存储 –1.79769313486231E308 到–4.94065645841247E–324 之间的负数和 4.94065645841247E–324 到 1.79769313486231E308 之间的正数 (包括小数)
复制代码
Win7安装第三方库(pyodbc),可参考:Python3脚本管理MSSQL2014
######
例子一:
Win7本地测试与数据库的连接的脚本:(C:\Users\jacky\Desktop\xx.py)
#coding=utf-8
import pyodbc
def db_connect():
DBfile = "d:/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()
脚本运行的结果:(表table1存在时)
C:\Users\jacky\Desktop>python xx.py
数据库连接成功。
C:\Users\jacky\Desktop>
脚本运行的结果:(表table1不存在时)
C:\Users\jacky\Desktop>python xx.py
数据库连接失败,原因: ('42S02', "[42S02] [Microsoft][ODBC Microsoft Access 驱动程序] Microsoft Jet 数据库引擎找不到输入表或查询 'table1'。 确定它是否存在,以及它的名称的拼写是否正确。 (-1305) (SQLExecDirectW)")
C:\Users\jacky\Desktop>
######
例子二:
Win7本地测试表table1里是否有记录的脚本:
#coding=utf-8
import pyodbc
def db_connect():
DBfile = "d:/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.fetchone() #使用fetchone只能获取一个元素
if Result_1:
print("表table1中有记录。")
else:
print("表table1中没有记录。")
db.commit() #把执行任务提交到数据库
db.close()
def func_main():
try:
db_connect()
except Exception as e:
print(f"查询失败,原因: {e}")
if __name__ == "__main__":
func_main()
脚本运行的结果:(表table1中有记录时)
C:\Users\jacky\Desktop>python xx.py
表中有记录。
C:\Users\jacky\Desktop>
脚本运行的结果:(表table1中没有记录时)
C:\Users\jacky\Desktop>python xx.py
表table1中没有记录。
C:\Users\jacky\Desktop>
######
例子三:
Win7本地一次性插入多条记录的脚本:
#coding=utf-8
import pyodbc
import datetime
dt = datetime.datetime.now()
tt_1 = dt.strftime('%Y-%m-%d %H:%M:%S') #当前时间
def insert_record():
global db
DBfile = "d:/Database1.mdb"
db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";")
cursor = db.cursor()
#字段ID为主键、标识符列,会自动增长,不用写
Sql_1 = "INSERT INTO table1(name,age,grade,address,math,chinese,shijian)" \
" VALUES('李大杰',18,'三年一班','guangzhou',60.5,88,'2019-9-9 7:9:18')"
cursor.execute(Sql_1)
Key_1 = '范小冰'
Key_2 = 28
Key_3 = '三年一班'
Key_4 = 'guangzhou'
Key_5 = 70
Key_6 = 96.5
Key_7 = '2020-05-01 12:06:28'
Sql_2 = f"INSERT INTO table1(name,age,grade,address,math,chinese,shijian) VALUES('{Key_1}',{Key_2},'{Key_3}','{Key_4}',{Key_5},{Key_6},'{Key_7}')"
cursor.execute(Sql_2)
Key_1 = 'Zhuohua'
Key_2 = 38
Key_3 = '三年二班'
Key_4 = 'foshan'
Key_5 = -50.5
Key_6 = -99.5
Key_7 = tt_1
Sql_3 = f"INSERT INTO table1(name,age,grade,address,math,chinese,shijian) VALUES('{Key_1}',{Key_2},'{Key_3}','{Key_4}',{Key_5},{Key_6},'{Key_7}')"
cursor.execute(Sql_3)
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 = "d:/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]
age = Key_1[2]
grade = Key_1[3]
address = Key_1[4]
math = Key_1[5]
chinese = Key_1[6]
shijian = Key_1[7]
print(f"ID={ID},姓名={name},年龄={age},年级={grade},住址={address},数学成绩={math},语文成绩={chinese},时间={shijian}")
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, '李大杰', 18, '三年一班', 'guangzhou', 60.5, 88.0, datetime.datetime(2019, 9, 9, 7, 9, 18)), (2, '范小冰', 28, '三年一班', 'guangzhou', 70.0, 96.5, datetime.datetime(2020, 5, 1, 12, 6, 28)), (3, 'Zhuohua', 38, '三年二班', 'foshan', -50.5, -99.5, datetime.datetime(2020, 5, 10, 21, 27, 6))]
<class 'list'>
----------
ID=1,姓名=李大杰,年龄=18,年级=三年一班,住址=guangzhou,数学成绩=60.5,语文成绩=88.0,时间=2019-09-09 07:09:18
ID=2,姓名=范小冰,年龄=28,年级=三年一班,住址=guangzhou,数学成绩=70.0,语文成绩=96.5,时间=2020-05-01 12:06:28
ID=3,姓名=Zhuohua,年龄=38,年级=三年二班,住址=foshan,数学成绩=-50.5,语文成绩=-99.5,时间=2020-05-10 21:27:06
C:\Users\jacky\Desktop>
######
例子五:
Win7把输出结果写入(追加)到本地文件的脚本:
#coding=utf-8
import pyodbc
def query_data():
DBfile = "d:/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()
for Key_1 in Result_1: #自定义输出格式
ID = Key_1[0]
name = Key_1[1]
age = Key_1[2]
grade = Key_1[3]
address = Key_1[4]
math = Key_1[5]
chinese = Key_1[6]
shijian = Key_1[7]
print(f"ID={ID},姓名={name},年龄={age},年级={grade},住址={address},数学成绩={math},语文成绩={chinese},时间={shijian}")
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.txt
C:\Users\jacky\Desktop>
文件1.txt的内容:
ID=1,姓名=李大杰,年龄=18,年级=三年一班,住址=guangzhou,数学成绩=60.5,语文成绩=88.0,时间=2019-09-09 07:09:18
ID=2,姓名=范小冰,年龄=28,年级=三年一班,住址=guangzhou,数学成绩=70.0,语文成绩=96.5,时间=2020-05-01 12:06:28
ID=3,姓名=Zhuohua,年龄=38,年级=三年二班,住址=foshan,数学成绩=-50.5,语文成绩=-99.5,时间=2020-05-10 21:27:06
######
例子六:
Win7本地删除表table1的所有记录的脚本:
#coding=utf-8
import pyodbc
def delete_record():
DBfile = "d:/Database1.mdb"
db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";")
cursor = db.cursor()
Sql_1 = "DELETE FROM table1"
cursor.execute(Sql_1)
db.commit() #把执行任务提交到数据库
db.close()
print ("成功删除表table1的所有记录。")
def func_main():
try:
delete_record()
except Exception as e:
print(f"删除失败,原因: {e}")
if __name__ == "__main__":
func_main()
脚本运行的结果:
C:\Users\jacky\Desktop>python xx.py
成功删除表table1的所有记录。
C:\Users\jacky\Desktop>
######
例子七:
Win7把本地文件里的所有记录添加到数据库中的某个表的脚本:
#coding=utf-8
import pyodbc
def func1():
Path_1 = "./1.txt"
f_name = open(Path_1,'r')
Result_1 = f_name.readlines() #输出结果为列表,包含换行符
f_name.close()
print(Result_1)
print(type(Result_1))
insert_record(Result_1)
def insert_record(Result_1):
DBfile = "d:/Database1.mdb"
db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";")
cursor = db.cursor()
for Key_1 in Result_1:
#字段ID为主键、标识符列,会自动增长,不用写
name = Key_1.split(',')[1][3:] #去掉前面3个字符
age = int(Key_1.split(',')[2][3:]) #去掉前面3个字符,再转换为整数
grade = Key_1.split(',')[3][3:]
address = Key_1.split(',')[4][3:]
math = float(Key_1.split(',')[5][5:]) #去掉前面5个字符,再转换为浮点数
chinese = float(Key_1.split(',')[6][5:])
shijian = Key_1.split(',')[7][3:].rstrip('\n') #去掉前面3个字符,再去除右边的换行符
Sql_1 = f"INSERT INTO table1(name,age,grade,address,math,chinese,shijian) VALUES('{name}',{age},'{grade}','{address}',{math},{chinese},'{shijian}')"
cursor.execute(Sql_1)
db.commit() #把执行任务提交到数据库
db.close()
if __name__ == "__main__":
func1()
脚本运行的结果:
C:\Users\jacky\Desktop>python xx.py
['ID=1,姓名=李大杰,年龄=18,年级=三年一班,住址=guangzhou,数学成绩=60.5,语文成绩=88.0,时间=2019-09-09 07:09:18\n', 'ID=2,姓名=范小冰,年龄=28,年级=三年一班,住址=guangzhou,数学成绩=70.0,语文成绩=96.5,时间=2020-05-01 12:06:28\n', 'ID=3,姓名=Zhuohua,年龄=38,年级=三年二班,住址=foshan,数学成绩=-50.5,语文成绩=-99.5,时间=2020-05-10 21:27:06\n']
<class 'list'>
C:\Users\jacky\Desktop>
######
例子八:
Win7本地输出表table1的所有记录的脚本:
#coding=utf-8
import pyodbc
def query_data():
DBfile = "d:/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()
for Key_1 in Result_1: #自定义输出格式
ID = Key_1[0]
name = Key_1[1].upper() #英文转大写字母
age = Key_1[2]
grade = Key_1[3]
address = Key_1[4]
math = Key_1[5]
chinese = Key_1[6]
shijian1 = str(Key_1[7])[0:10] #输出前面10个字符
shijian2 = str(Key_1[7])[-8:] #输出最后8个字符
print(f"ID={ID},姓名={name},年龄={age},年级={grade},住址={address},数学成绩={math},语文成绩={chinese},日期={shijian1},时间={shijian2}")
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
ID=4,姓名=李大杰,年龄=18,年级=三年一班,住址=guangzhou,数学成绩=60.5,语文成绩=88.0,日期=2019-09-09,时间=07:09:18
ID=5,姓名=范小冰,年龄=28,年级=三年一班,住址=guangzhou,数学成绩=70.0,语文成绩=96.5,日期=2020-05-01,时间=12:06:28
ID=6,姓名=ZHUOHUA,年龄=38,年级=三年二班,住址=foshan,数学成绩=-50.5,语文成绩=-99.5,日期=2020-05-10,时间=21:27:06
C:\Users\jacky\Desktop>
######
例子九:
Win7本地输出表table1的所有记录的脚本:
#coding=utf-8
import pyodbc
def query_data():
DBfile = "d:/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()
for Key_1 in Result_1: #自定义输出格式
ID = Key_1[0]
name = Key_1[1].lower() #英文转小写字母
age = Key_1[2]
grade = Key_1[3]
address = Key_1[4]
math = Key_1[5]
math_abs = abs(Key_1[5])
chinese = Key_1[6]
chinese_abs = abs(Key_1[6])
print(f"ID={ID},姓名={name},年龄={age},年级={grade},住址={address},\n数学成绩={math},数学成绩的绝对值={math_abs},语文成绩={chinese},语文成绩的绝对值={chinese_abs}")
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
ID=4,姓名=李大杰,年龄=18,年级=三年一班,住址=guangzhou,
数学成绩=60.5,数学成绩的绝对值=60.5,语文成绩=88.0,语文成绩的绝对值=88.0
ID=5,姓名=范小冰,年龄=28,年级=三年一班,住址=guangzhou,
数学成绩=70.0,数学成绩的绝对值=70.0,语文成绩=96.5,语文成绩的绝对值=96.5
ID=6,姓名=zhuohua,年龄=38,年级=三年二班,住址=foshan,
数学成绩=-50.5,数学成绩的绝对值=50.5,语文成绩=-99.5,语文成绩的绝对值=99.5
C:\Users\jacky\Desktop>
######
例子十:
Win7本地输出数据库中某个表里符合指定条件的记录的脚本:
#coding=utf-8
import pyodbc
def query_data():
DBfile = "d:/Database1.mdb"
db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";")
cursor = db.cursor()
Sql_1 = "select * from table1 where chinese <= 90"
cursor.execute(Sql_1)
Result_1 = cursor.fetchall()
for Key_1 in Result_1: #自定义输出格式
ID = Key_1[0]
name = Key_1[1]
age = Key_1[2]
grade = Key_1[3]
address = Key_1[4]
math = Key_1[5]
chinese = Key_1[6]
shijian = str(Key_1[7])
print(f"ID={ID},姓名={name},年龄={age},年级={grade},住址={address},数学成绩={math},语文成绩={chinese},时间={shijian}")
print("-" * 10)
print(Result_1[1]) #列表的第二个元素,即第二条记录
print(Result_1[1][1])
print(type(Result_1[1][1]))
print(Result_1[1][2])
print(type(Result_1[1][2]))
print(Result_1[1][5])
print(type(Result_1[1][5]))
print(Result_1[1][6])
print(type(Result_1[1][6]))
print(Result_1[1][7])
print(type(Result_1[1][7]))
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
ID=4,姓名=李大杰,年龄=18,年级=三年一班,住址=guangzhou,数学成绩=60.5,语文成绩=88.0,时间=2019-09-09 07:09:18
ID=6,姓名=Zhuohua,年龄=38,年级=三年二班,住址=foshan,数学成绩=-50.5,语文成绩=-99.5,时间=2020-05-10 21:27:06
----------
(6, 'Zhuohua', 38, '三年二班', 'foshan', -50.5, -99.5, datetime.datetime(2020, 5, 10, 21, 27, 6))
Zhuohua
<class 'str'>
38
<class 'int'>
-50.5
<class 'float'>
-99.5
<class 'float'>
2020-05-10 21:27:06
<class 'datetime.datetime'>
C:\Users\jacky\Desktop>
######
例子十一:
Win7本地输出根据关键字搜索出来的记录的脚本:
#coding=utf-8
import pyodbc
def query_data():
DBfile = "d:/Database1.mdb"
db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";")
cursor = db.cursor()
Sql_1 = "select * from table1 where name = 'ZHuohua'" #关键字不区分英文字母大小写
cursor.execute(Sql_1)
Result_1 = cursor.fetchall()
print(Result_1)
print(type(Result_1))
print("-" * 10)
for Key_1 in Result_1: #自定义输出格式
ID = Key_1[0]
name = Key_1[1]
age = Key_1[2]
grade = Key_1[3]
address = Key_1[4]
math = Key_1[5]
chinese = Key_1[6]
shijian = str(Key_1[7])
print(f"ID={ID},姓名={name},年龄={age},年级={grade},住址={address},数学成绩={math},语文成绩={chinese},时间={shijian}")
print("-" * 10)
print(Result_1[0]) #列表的第一个元素,即第一条记录
print(Result_1[0][0])
print(type(Result_1[0][0]))
print(Result_1[0][1])
print(type(Result_1[0][1]))
print(Result_1[0][3])
print(type(Result_1[0][3]))
print(Result_1[0][4])
print(type(Result_1[0][4]))
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
[(6, 'Zhuohua', 38, '三年二班', 'foshan', -50.5, -99.5, datetime.datetime(2020, 5, 10, 21, 27, 6))]
<class 'list'>
----------
ID=6,姓名=Zhuohua,年龄=38,年级=三年二班,住址=foshan,数学成绩=-50.5,语文成绩=-99.5,时间=2020-05-10 21:27:06
----------
(6, 'Zhuohua', 38, '三年二班', 'foshan', -50.5, -99.5, datetime.datetime(2020, 5, 10, 21, 27, 6))
6
<class 'int'>
Zhuohua
<class 'str'>
三年二班
<class 'str'>
foshan
<class 'str'>
C:\Users\jacky\Desktop>
######
例子十二:
Win7本地输出根据聚合函数获得的数据的脚本:(结果只取一个值)
#coding=utf-8
import pyodbc
def query_data():
DBfile = "d:/Database1.mdb"
db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";")
cursor = db.cursor()
Sql_1 = "select max(chinese) from table1" #查询字段chinese中的最大值
cursor.execute(Sql_1)
Result_1 = cursor.fetchone()
print(Result_1)
print(type(Result_1))
print("-" * 10)
print(Result_1[0])
print(type(Result_1[0]))
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
(96.5, )
<class 'pyodbc.Row'>
----------
96.5
<class 'float'>
C:\Users\jacky\Desktop>
######
例子十三:
Win7本地更改记录的脚本:
#coding=utf-8
import pyodbc
def update_record():
global db
DBfile = "d:/Database1.mdb"
db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";")
cursor = db.cursor()
Sql_1 = "UPDATE table1 SET math = 66.5 WHERE name = 'zhuoHUA'" #关键字不区分英文字母大小写
cursor.execute(Sql_1)
Sql_2 = "UPDATE table1 SET math = 88 WHERE name like '__杰%'"
cursor.execute(Sql_2)
db.commit() #把执行任务提交到数据库;必须是所有SQL语句都执行成功才会提交
db.close()
def func_main():
try:
update_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本地删除记录的脚本:
#coding=utf-8
import pyodbc
def delete_record():
global db
DBfile = "d:/Database1.mdb"
db = pyodbc.connect(r"Driver={Microsoft Access Driver (*.mdb)};DBQ=" + DBfile + ";")
cursor = db.cursor()
Sql_1 = "DELETE FROM table1 WHERE name like '%Zhuo%'" #关键字不区分英文字母大小写
cursor.execute(Sql_1)
Sql_2 = "DELETE FROM table1 WHERE name = '李大杰'"
cursor.execute(Sql_2)
db.commit() #把执行任务提交到数据库;必须是所有SQL语句都执行成功才会提交
db.close()
def func_main():
try:
delete_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>
相关文章:
Python3命令集
网络爬虫_爬(blog.zhuohua.store)
Windows下Python3管理Access+绘图库Matplotlib |