mysql图书管理系统代码

CREATE TABLE books (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    author VARCHAR(255) NOT NULL,
    isbn VARCHAR(13) UNIQUE NOT NULL,
    published_date DATE,
    available BOOLEAN DEFAULT TRUE
);

CREATE TABLE users (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    email VARCHAR(255) UNIQUE NOT NULL,
    role ENUM('borrower', 'admin') DEFAULT 'borrower'
);

CREATE TABLE borrow_records (
    id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT NOT NULL,
    user_id INT NOT NULL,
    borrow_date DATE NOT NULL,
    return_date DATE,
    FOREIGN KEY (book_id) REFERENCES books(id),
    FOREIGN KEY (user_id) REFERENCES users(id)
);

import mysql.connector

def add_book(title, author, isbn, published_date):
    conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='library')
    cursor = conn.cursor()
    query = "INSERT INTO books (title, author, isbn, published_date) VALUES (%s, %s, %s, %s)"
    values = (title, author, isbn, published_date)
    cursor.execute(query, values)
    conn.commit()
    cursor.close()
    conn.close()

# 示例调用
add_book('Python Programming', 'John Doe', '1234567890123', '2020-01-01')

def search_books(keyword):
    conn = mysql.connector.connect(user='root', password='password', host='127.0.0.1', database='library')
    cursor = conn.cursor()
    query = "SELECT * FROM books WHERE title LIKE %s OR author LIKE %s OR isbn LIKE %s"
    values = (f'%{keyword}%', f'%{keyword}%', f'%{keyword}%')
    cursor.execute(query, values)
    results = cursor.fetchall()
    cursor.close()
    conn.close()
    return results

# 示例调用
books = search_books('Python')
for book in books:
    print(book)