python 脚本对数据库的简单操作

import sqlite3
from datetime import datetime
'''
  数据库内容
  [ID] int null,
  [loginName] text(50), 
  [loginTime] text(50),    
  [logOutTime] text(50),  
  [operation] int null
'''

# 连接到数据库
conn = sqlite3.connect('test.db')
cursor = conn.cursor()

# ---------清空数据库---------
# 准备SQL删除语句
delete_query = "DELETE FROM LoginHistory_tb"

# 执行SQL删除操作
cursor.execute(delete_query)

# 提交事务
conn.commit()

# ---------插入信息到数据库中---------
# 准备SQL插入语句
insert_query = "INSERT INTO LoginHistory_tb (ID, loginName, loginTime, logOutTime, operation) VALUES (?, ?, ?, ?, ?)"

# 准备要插入的数据
login_data = [
    (1, 'user1',  datetime.now(), datetime.now(), 222),
    (2, 'user2',  datetime.now(), datetime.now(), 333),
    (3, 'user3',  datetime.now(), datetime.now(), 444),
    (4, 'user4',  datetime.now(), datetime.now(), 555),
    (5, 'user5',  datetime.now(), datetime.now(), 666)
]

# 执行SQL插入操作
for data in login_data:
    cursor.execute(insert_query, data)

# 提交事务
conn.commit()

# 准备SQL查询语句
query = "SELECT * FROM LoginHistory_tb"

# 执行SQL查询
cursor.execute(query)

# 获取所有结果
results = cursor.fetchall()

# 打印结果
for row in results:
    print(row)

# ---------删除数据库中指定数据---------
# 准备SQL删除语句
delete_query = "DELETE FROM LoginHistory_tb WHERE id = ?"

# 要删除的行的主键值
id_to_delete = 3

# 执行SQL删除操作
cursor.execute(delete_query, (id_to_delete,))

# 提交事务
conn.commit()

# 打印空行
print("---------------------------------------\n")

# ---------查询数据库---------
# 准备SQL查询语句
query = "SELECT * FROM LoginHistory_tb"

# 执行SQL查询
cursor.execute(query)

# 获取所有结果
results = cursor.fetchall()

# 打印结果
for row in results:
    print(row)

# 关闭连接
cursor.close()
conn.close()