import pyodbc
from datetime import datetime
import random
random_number = random.randint(1, 100)
current_datetime = datetime.now()   # 获取当前时间
path = r'.\Database1.accdb'     # 数据库所在的位置
datasheet1 = "datatable"
# 连接到Access数据库
conn_str = "DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=%s" % path
conn = pyodbc.connect(conn_str)
# 创建游标
cursor = conn.cursor()
# 执行SQL增加语句
sql_add = f"INSERT INTO {datasheet1} (ID,声音,频率_X,频率_Y,频率_Z,振幅_X,振幅_Y,振幅_Z) VALUES (" \f"'{current_datetime}','{random_number}','{random.randint(1, 100)}'," \f"'{random.randint(1, 100)}','{random.randint(1, 100)}','{random.randint(1, 100)}'," \f"'{random.randint(1, 100)}','{random.randint(1, 100)}')"
cursor.execute(sql_add)
conn.commit()   # 提交事务
# 执行SQL删除语句
sql_del = f"DELETE FROM {datasheet1} WHERE 声音 ='68'"
cursor.execute(sql_del)
conn.commit()   # 提交事务
# 执行SQL修改语句
sql_upd = f"UPDATE {datasheet1} SET 频率_X = 'cc', 频率_Y = 'dd' WHERE 声音 ='93';"
cursor.execute(sql_upd)
conn.commit()   # 提交事务
# 执行SQL查询语句
select_cmd = f"SELECT * FROM {datasheet1}"
cursor.execute(select_cmd)
result = cursor.fetchall()  # 获取查询结果# 遍历结果
for row in result:print(row)
cursor.close()
conn.close()