实现图书管理系统，创建图书表、人员信息表，图书表包含id、书名、出版社，人员信息包含借书id，实现图书的增删改查

以下是一个简单的图书管理系统实现，基于Python和SQLite数据库：

1. 创建数据库和表

首先，我们需要创建一个SQLite数据库，并在其中创建两个表：books和people。

import sqlite3

# 连接数据库
conn = sqlite3.connect('library.db')

# 创建books表
conn.execute('''CREATE TABLE books
             (ID INT PRIMARY KEY NOT NULL,
             NAME TEXT NOT NULL,
             PUBLISHER TEXT NOT NULL);''')

# 创建people表
conn.execute('''CREATE TABLE people
             (ID INT PRIMARY KEY NOT NULL,
             NAME TEXT NOT NULL,
             BOOK_ID INT NOT NULL);''')

# 关闭数据库连接
conn.close()

2. 实现增删改查功能

现在，我们可以使用Python的sqlite3模块来实现图书的增删改查功能。

import sqlite3

def add_book(id, name, publisher):
    conn = sqlite3.connect('library.db')
    conn.execute("INSERT INTO books (ID, NAME, PUBLISHER) VALUES (?, ?, ?)", (id, name, publisher))
    conn.commit()
    conn.close()
    print("Book added successfully")

def remove_book(id):
    conn = sqlite3.connect('library.db')
    conn.execute("DELETE FROM books WHERE ID=?", (id,))
    conn.commit()
    conn.close()
    print("Book removed successfully")

def update_book(id, name=None, publisher=None):
    conn = sqlite3.connect('library.db')
    if name is not None:
        conn.execute("UPDATE books SET NAME=? WHERE ID=?", (name, id))
    if publisher is not None:
        conn.execute("UPDATE books SET PUBLISHER=? WHERE ID=?", (publisher, id))
    conn.commit()
    conn.close()
    print("Book updated successfully")

def search_book(id=None, name=None, publisher=None):
    conn = sqlite3.connect('library.db')
    query = "SELECT * FROM books"
    if id is not None:
        query += " WHERE ID=" + str(id)
    elif name is not None:
        query += " WHERE NAME='" + name + "'"
    elif publisher is not None:
        query += " WHERE PUBLISHER='" + publisher + "'"
    cursor = conn.execute(query)
    for row in cursor:
        print("ID = ", row[0])
        print("NAME = ", row[1])
        print("PUBLISHER = ", row[2])
        print("\n")
    conn.close()

类似地，我们也可以实现人员信息的增删改查功能：

import sqlite3

def add_person(id, name, book_id):
    conn = sqlite3.connect('library.db')
    conn.execute("INSERT INTO people (ID, NAME, BOOK_ID) VALUES (?, ?, ?)", (id, name, book_id))
    conn.commit()
    conn.close()
    print("Person added successfully")

def remove_person(id):
    conn = sqlite3.connect('library.db')
    conn.execute("DELETE FROM people WHERE ID=?", (id,))
    conn.commit()
    conn.close()
    print("Person removed successfully")

def update_person(id, name=None, book_id=None):
    conn = sqlite3.connect('library.db')
    if name is not None:
        conn.execute("UPDATE people SET NAME=? WHERE ID=?", (name, id))
    if book_id is not None:
        conn.execute("UPDATE people SET BOOK_ID=? WHERE ID=?", (book_id, id))
    conn.commit()
    conn.close()
    print("Person updated successfully")

def search_person(id=None, name=None, book_id=None):
    conn = sqlite3.connect('library.db')
    query = "SELECT * FROM people"
    if id is not None:
        query += " WHERE ID=" + str(id)
    elif name is not None:
        query += " WHERE NAME='" + name + "'"
    elif book_id is not None:
        query += " WHERE BOOK_ID=" + str(book_id)
    cursor = conn.execute(query)
    for row in cursor:
        print("ID = ", row[0])
        print("NAME = ", row[1])
        print("BOOK_ID = ", row[2])
        print("\n")
    conn.close()

3. 测试

最后，我们可以编写一些测试代码来测试我们的图书管理系统是否正常工作。

# 添加图书
add_book(1, "Python Programming", "O'Reilly Media")
add_book(2, "Introduction to Machine Learning", "Wiley")

# 搜索图书
search_book(name="Python Programming")

# 更新图书
update_book(1, publisher="Packt Publishing")

# 删除图书
remove_book(2)

# 添加人员信息
add_person(1, "Alice", 1)
add_person(2, "Bob", 2)

# 搜索人员信息
search_person(book_id=1)

# 更新人员信息
update_person(1, name="Charlie")

# 删除人员信息
remove_person(2)

输出：

Book added successfully
Book added successfully
ID =  1
NAME =  Python Programming
PUBLISHER =  O'Reilly Media


Book updated successfully
Book removed successfully
Person added successfully
Person added successfully
ID =  1
NAME =  Alice
BOOK_ID =  1


Person updated successfully
Person removed successfully