You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
111 lines
3.4 KiB
111 lines
3.4 KiB
import pymysql
|
|
import os
|
|
|
|
|
|
# 连接数据库的函数
|
|
def connect_db(database=None):
|
|
conn = pymysql.connect(
|
|
host="localhost",
|
|
port=3306,
|
|
user="root",
|
|
password="123456",
|
|
db=database,
|
|
charset="utf8"
|
|
)
|
|
return conn
|
|
|
|
|
|
# 创建数据库的函数（如果数据库存在，删除并重新创建）
|
|
def create_database_if_not_exists():
|
|
conn = connect_db()
|
|
cur = conn.cursor()
|
|
|
|
# 检查数据库是否存在
|
|
cur.execute("SHOW DATABASES LIKE 'attendance_system'")
|
|
result = cur.fetchone()
|
|
|
|
if result is None:
|
|
# 数据库不存在，创建数据库
|
|
cur.execute("CREATE DATABASE attendance_system")
|
|
print("Database 'attendance_system' created successfully.")
|
|
else:
|
|
# 数据库存在，删除该数据库并重新创建
|
|
cur.execute("DROP DATABASE attendance_system")
|
|
print("Database 'attendance_system' dropped successfully.")
|
|
cur.execute("CREATE DATABASE attendance_system")
|
|
print("Database 'attendance_system' created successfully.")
|
|
|
|
# 关闭初始连接
|
|
conn.close()
|
|
|
|
|
|
# 执行 SQL 文件的函数
|
|
def execute_sql_file(cur, file_path):
|
|
"""
|
|
执行指定路径的 SQL 文件。
|
|
:param cur: 数据库游标
|
|
:param file_path: SQL 文件路径
|
|
"""
|
|
with open(file_path, 'r', encoding='utf-8') as file:
|
|
sql = file.read()
|
|
for statement in sql.split(';'):
|
|
if statement.strip(): # 忽略空的语句
|
|
cur.execute(statement)
|
|
|
|
|
|
# 清空数据库中的所有表
|
|
def drop_all_tables(cur):
|
|
cur.execute("SHOW TABLES")
|
|
tables = cur.fetchall()
|
|
for table in tables:
|
|
cur.execute(f"DROP TABLE IF EXISTS {table[0]}")
|
|
print(f"Table '{table[0]}' dropped.")
|
|
|
|
|
|
# 主要执行函数
|
|
def main():
|
|
# 创建数据库（如果不存在）
|
|
create_database_if_not_exists()
|
|
|
|
# 连接到数据库
|
|
conn = connect_db(database="attendance_system")
|
|
cur = conn.cursor()
|
|
|
|
# 清空数据库中的所有表
|
|
drop_all_tables(cur)
|
|
|
|
# 脚本文件路径
|
|
sql_scripts_dir = "sql_scripts"
|
|
|
|
# 初始化脚本文件
|
|
sql_files = [
|
|
'attendance_system_create_db.sql', # 创建数据库和表的脚本（假设这是你缺失的文件，应该包括数据库和表的创建）
|
|
'initialize_data.sql', # 插入初始数据
|
|
'view_creation_scripts.sql', # 创建视图
|
|
'index_creation_scripts.sql', # 创建索引
|
|
# 'triggers.sql', # 创建触发器 未运行成功。
|
|
'attendance_system_security_permissions.sql', # 用户权限控制
|
|
# 'stored_procedures.sql', # 存储过程 未运行成功，和创建触发器一样，语法错误
|
|
# 'business_queries.sql', # 业务查询
|
|
# 'data_update_scripts.sql' # 数据更新
|
|
]
|
|
|
|
# 遍历并执行所有 SQL 脚本
|
|
for sql_file in sql_files:
|
|
sql_file_path = os.path.join(sql_scripts_dir, sql_file)
|
|
if os.path.exists(sql_file_path):
|
|
print(f"Executing {sql_file}...")
|
|
execute_sql_file(cur, sql_file_path)
|
|
else:
|
|
print(f"SQL script file '{sql_file}' not found. Skipping...")
|
|
|
|
# 提交更改并关闭连接
|
|
conn.commit()
|
|
conn.close()
|
|
print("All SQL scripts executed successfully!")
|
|
|
|
|
|
# 执行主函数
|
|
if __name__ == '__main__':
|
|
main()
|