6.7打卡

python大作业数据库
• 所花时间：2
• 代码行数：458
• 博客容量：1
• 代码如下：

import pandas as pd
from tkinter import filedialog
import mysql.connector
import tkinter as tk
from tkinter import messagebox

# 假设 database.py 中已经有 connect_db 函数
import database as db

def connect_db():
    return mysql.connector.connect(
        host="localhost",
        user="root",
        password="1234",
        database="dormitory_management"
    )

def get_user_role(username, password):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("SELECT role FROM users WHERE username=%s AND password=%s", (username, password))
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result

def insert_dormitory(name, floors, rooms_per_floor, gender):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("INSERT INTO dormitories (name, floors, rooms_per_floor, gender) VALUES (%s, %s, %s, %s)",
                   (name, floors, rooms_per_floor, gender))
    conn.commit()
    cursor.close()
    conn.close()

def get_maintenance_requests():
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("SELECT id, reported_by, dormitory_id, room_number, description, handled FROM maintenance_requests")
    requests = cursor.fetchall()
    cursor.close()
    conn.close()
    return requests

def update_maintenance_request(request_id):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("UPDATE maintenance_requests SET handled=TRUE WHERE id=%s", (request_id,))
    conn.commit()
    cursor.close()
    conn.close()

def get_dormitory_info(username):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT dormitories.name, dormitories.floors, dormitories.rooms_per_floor, dormitories.gender, users.room_number FROM users JOIN dormitories ON users.dormitory_id = dormitories.id WHERE users.username=%s",
        (username,))
    result = cursor.fetchone()
    cursor.close()
    conn.close()
    return result

def get_roommates(username):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute(
        "SELECT username, name FROM users WHERE dormitory_id = (SELECT dormitory_id FROM users WHERE username=%s) AND room_number = (SELECT room_number FROM users WHERE username=%s)",
        (username, username))
    roommates = cursor.fetchall()
    cursor.close()
    conn.close()
    return roommates



    conn.commit()
    cursor.close()
    conn.close()
    messagebox.showinfo("成功", "学生数据导入成功")


def get_room_swap_requests():
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("""
        SELECT 
            id, 
            requester_username, 
            partner_username, 
            status 
        FROM room_swap_requests
    """)
    requests = cursor.fetchall()
    cursor.close()
    conn.close()
    return requests

def insert_maintenance_request(username, dormitory_id, room_number, description):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute(
        "INSERT INTO maintenance_requests (reported_by, dormitory_id, room_number, description) VALUES (%s, %s, %s, %s)",
        (username, dormitory_id, room_number, description))
    conn.commit()
    cursor.close()
    conn.close()

def check_partner_request(partner_username, username):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM room_swap_requests WHERE requester_username=%s AND partner_username=%s AND status='pending'",
                   (partner_username, username))
    partner_request = cursor.fetchone()
    cursor.close()
    conn.close()
    return partner_request

def get_user_info(username):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("SELECT dormitory_id, room_number FROM users WHERE username=%s", (username,))
    user_info = cursor.fetchone()
    cursor.close()
    conn.close()
    return user_info

def update_user_room(username, dormitory_id, room_number):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("UPDATE users SET dormitory_id=%s, room_number=%s WHERE username=%s",
                   (dormitory_id, room_number, username))
    conn.commit()
    cursor.close()
    conn.close()

def insert_room_swap_request(username, partner_username):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("INSERT INTO room_swap_requests (requester_username, partner_username) VALUES (%s, %s)",
                   (username, partner_username))
    conn.commit()
    cursor.close()
    conn.close()

def approve_room_swap_request(request_id):
    conn = connect_db()
    cursor = conn.cursor()
    cursor.execute("UPDATE room_swap_requests SET status='approved' WHERE id=%s", (request_id,))
    conn.commit()
    cursor.close()
    conn.close()